Data Model Design and Access Patterns Questions
Discuss how you'd design data models based on access patterns. Understand relational vs. NoSQL trade-offs. Know when to denormalize, how to handle distributed transactions, and strategies for scaling databases (sharding, partitioning). Discuss read vs. write optimization.
HardTechnical
32 practiced
Join-heavy analytical queries across partitioned tables are causing expensive cross-partition joins. Propose data model and operational changes such as co-partitioning (colocated joins), denormalization, precomputed join tables or materialized pre-joins, and query rewrites. Discuss how each approach affects write complexity, storage requirements, and latency.
Sample Answer
Situation: Large analytical workloads join partitioned fact and dimension tables and suffer expensive cross-partition network-shuffles and slow queries. Below I propose concrete data-model and operational options, with their impacts on write complexity, storage, and latency — plus guidance on when to pick each.1) Co-partitioning / colocated joins- What: Ensure joined tables use the same partitioning key and placement (same hash/ range and physical node/partition).- Operational changes: Align partition keys at design time; migrate or re-shard existing data; ensure the query engine uses colocated join policy.- Impact: - Write complexity: Low-to-moderate — may require computing/propagating partition key on every write; re-sharding cost if changing keys. - Storage: No extra storage. - Read latency: Greatly reduced for joins that match the partition key (eliminates network shuffle).- When: When you can choose a stable join key used widely (e.g., tenant_id).2) Denormalization- What: Embed dimension attributes inside the fact (wide rows) to avoid joins.- Operational changes: Update ETL and upstream services to write denormalized records; add versioning or background reconciliation for dimension updates.- Impact: - Write complexity: High — writes need to populate/maintain duplicates; updates to dimension require fan-out or eventual consistency. - Storage: Increases storage proportional to duplicated fields (can be large). - Read latency: Very low; single-table scans/filters.- When: When dimension data is relatively static or read performance is critical.3) Precomputed join tables (denormalized staging)- What: Maintain separate pre-joined tables for common query patterns (e.g., customer_orders_by_region).- Operational changes: Implement ETL or streaming pipelines (batch or CDC-based) to populate and refresh these tables; maintain lifecycle policies and provenance.- Impact: - Write complexity: Moderate — additional pipeline writes; can be batched to reduce pressure. - Storage: Moderate to high depending on number of prejoins. - Read latency: Fast; queries hit ready-made rows.- When: Workloads have a small set of high-frequency join patterns.4) Materialized views / materialized pre-joins (DB-managed)- What: Database-maintained views that store join results and can be incrementally maintained (if system supports).- Operational changes: Use DB features (e.g., incremental refresh, CDC integration); monitor view-refresh lags and invalidations.- Impact: - Write complexity: Low at application level — DB handles maintenance, but DB write path may have additional cost. - Storage: Increased by view size. - Read latency: Fast and consistent if kept fresh; trade-off between strict consistency and refresh cost.- When: Preferable when DB supports efficient incremental maintenance and transactional freshness is required.5) Query rewrites and push-downs- What: Rewrite queries to filter earlier, use predicate push-down, bloom filters, or rewrite joins into semi-joins/exists; push heavy filters into storage layer.- Operational changes: Implement query templates, cost-based planner hints, or materialized filter structures.- Impact: - Write complexity: None. - Storage: Small additional metadata (bloom filters, stats). - Read latency: Can reduce scanned data significantly without schema changes.- When: Quick wins, useful when joins are selective or when refactoring data model is costly.Operational patterns and best practices- Incremental maintenance: Use CDC or streaming (Kafka, Debezium) to update pre-joins/materialized views to reduce full-refresh overhead.- TTL and retention: Prune precomputed tables to limit storage growth.- Monitoring: Track refresh lag, join latency, network IO, and write amplification.- Hybrid approach: Combine colocated partitions for large-ratio joins, materialized views for top N queries, and query rewrites for ad-hoc analytics.Summary guidance- If you can control partition keys and have stable joins: co-partitioning is lowest storage cost and high impact.- If reads dominate and dimension changes are rare: denormalize.- If specific multi-table patterns are hot: precomputed tables or materialized views (choose DB-managed when you need transactional guarantees).- Always complement schema changes with incremental pipelines, retention policies, and query-level optimizations to balance write cost, storage, and latency.
MediumTechnical
54 practiced
Given query patterns such as WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 50, and also queries that filter only by status, recommend composite and covering indexes that support both efficiently. Explain how index column ordering and selectivity impact index choice and performance.
Sample Answer
Recommendation (practical): create two targeted composite, covering indexes:- For the common user+status ordered query: (user_id, status, created_at DESC) INCLUDE(<other_returned_columns>)- For queries that filter only by status: (status, created_at DESC) INCLUDE(<other_returned_columns>)Why these choices- ORDER BY created_at DESC + LIMIT 50: putting created_at at the end of the key (with DESC defined if the DB supports direction in index) lets the engine read the index in sorted order and stop after 50 rows -> very fast index-range scan without sorting.- WHERE predicates: the index can be used when the leading columns match equality/range predicates. (user_id, status, created_at) supports WHERE user_id = ? AND status = ? and provides ordered created_at. (status, created_at) supports WHERE status = ?.Covering: INCLUDE non-filter/select columns so queries can be satisfied by the index alone (index-only scan), eliminating lookups to the base table and reducing I/O.Column ordering and selectivity rules- Put equality filters first; among equalities, put the more selective column earlier to reduce rows scanned. If user_id is highly selective, (user_id, status, ...) reduces work vs (status, user_id,...).- However, to support queries that only filter by status you need an index that starts with status. If you have conflicting ordering requirements, either maintain two indexes (recommended when both query shapes are frequent) or pick the one that benefits the highest-volume path and accept trade-offs.- created_at should be last because it’s used only for ordering/range; it should follow the equality predicates so the index order is meaningful.Trade-offs and operational considerations- More indexes → faster reads for those queries but higher write/space cost. Measure write throughput and storage.- Consider partial indexes (e.g., WHERE status IN ('active')) if only a few statuses are hot — reduces index size and improves selectivity.- Always validate with EXPLAIN/EXPLAIN ANALYZE and real data distributions; cardinality and planner stats can change the optimal choice.Summary: prefer two covering composites targeted to each query shape for best read performance; if write overhead is a concern, pick the index that serves the highest-volume queries and supplement with partial indexes or INCLUDE columns to create index-only scans.
HardSystem Design
38 practiced
Architect a solution for a mixed OLTP/OLAP workload where OLTP must remain low-latency and analytics should be near-real-time. Compare the architectures: HTAP database, change-data-capture into analytic store, and streaming aggregations. For each option, discuss data modeling, storage formats, latency, and the implications for consistency and write amplification.
Sample Answer
Requirements clarification:- OLTP: sub-10ms reads/writes, high concurrency, ACID or bounded transactional semantics.- Analytics: near-real-time (seconds to low tens of seconds), ad-hoc queries, aggregates, historical scans.- Constraints: limited operational overhead, budget for extra infra, tolerance for eventual consistency for analytics.Comparison summary (HTAP vs CDC→Analytic Store vs Streaming Aggregations)1) HTAP database (e.g., Single-node or distributed HTAP: TiDB/YugaByte/Oracle Autonomous)- Data modeling: single canonical transactional schema used for both workloads; may require additional analytic-friendly columns/indexes.- Storage formats: unified storage often row-oriented for OLTP with columnar or hybrid OLAP acceleration (column-store or memory column cache).- Latency: OLTP remains low if HTAP supports workload isolation (separation of compute paths). Analytics can be sub-second to seconds if columnar acceleration present.- Consistency: strong transactional consistency across both workloads (single source of truth).- Write amplification: moderate to high — maintaining both row and columnar layouts, background compaction/transformations increase IO and CPU.- Pros/Cons: Simplifies architecture and operational model; risk of resource contention unless workload isolation is robust; higher license/complexity.2) CDC into analytic store (e.g., Debezium → Kafka → Snowflake/Redshift/BigQuery)- Data modeling: OLTP canonical model maintained; analytic store transforms via ETL/ELT into star/snowflake or denormalized wide tables and materialized views for query performance.- Storage formats: transactional store remains row-oriented; analytic store uses columnar, compressed immutable files (Parquet/ORC).- Latency: near-real-time depends on pipeline: seconds to minutes. Micro-batches give ~seconds to low tens of seconds; full transactional ordering and schema evolution require careful handling.- Consistency: eventual consistency for analytics; can achieve transactional ordering per partition with metadata. Secondary indexes or lookups can lag.- Write amplification: lower on OLTP (no extra layout maintenance), but additional storage & IO in analytic store for incremental files; CDC metadata adds overhead.- Pros/Cons: Clear separation of concerns, scalable analytic queries, lower risk of OLTP interference; more components, operational complexity, possible latency and consistency lag.3) Streaming aggregations (e.g., Kafka Streams/Flink ktable → serving store)- Data modeling: OLTP model plus stream-derived materialized views (pre-aggregated keys) tailored to analytic queries; requires design of aggregation keys and windowing.- Storage formats: OLTP stays row-oriented; streaming state stores often embedded RocksDB or in-memory + changelog to Kafka; final analytics can be served from these stores or pushed to columnar sinks.- Latency: very low for aggregates—sub-second to seconds. Best for operational analytics (KPIs, dashboards).- Consistency: near-real-time with exactly-once (depending on framework) for aggregations; full historical, ad-hoc scans still limited unless persisted to analytic store.- Write amplification: OLTP unaffected; streaming state changelogs and materialized view writes increase write IO; compaction/changelog retention impacts storage.- Pros/Cons: Excellent for low-latency operational metrics and alerting; not a replacement for full analytic exploration over raw history.Recommendation pattern:- If single-source consistency and simplified ops are highest priority and data volume moderate: HTAP.- If scalable, cost-effective analytical queries, complex transforms, and official separation of workloads are needed: CDC → analytic store.- If primary need is sub-second operational metrics and you can design queries as bounded aggregations: Streaming aggregations (possibly combined with CDC to persist historical snapshots).Hybrid approach (practical): Keep OLTP optimized for latency; run CDC into a columnar analytic store for broad BI + run streaming aggregations for operational dashboards. This gives strong OLTP isolation, near-real-time analytics, and ultra-low-latency KPIs while absorbing trade-offs (extra components, storage, and moderate write amplification).
HardTechnical
57 practiced
You need to reduce storage costs for an analytics platform storing petabytes of data. Propose a tiered storage architecture that transparently moves cold data to object storage while keeping hot indexes or metadata in the DB. Discuss how pointers to external blobs are modeled, impact on joins and query planning, and the latency trade-offs when rehydrating cold data.
Sample Answer
Requirements & goals:- Reduce storage cost for petabytes while preserving queryability and acceptable latency for analytics.- Keep hot index/metadata in DB for fast access; move cold bulk blobs to cheaper object storage (S3/GS/Blob).- Transparent to users/queries, support rehydration on demand, retain ACID/consistency guarantees for metadata.High-level architecture:- Primary OLAP/metadata store (columnar DB or transactional DB) holds row-level metadata, small indexes, pointers to external blobs.- Object storage holds large raw payloads (parquet/avro/ndjson) partitioned by time/tenant.- Tiering controller (service) monitors access patterns (LRU, TTL, access frequency, size) and moves cold partitions to object store; updates pointers/manifest in DB.- Cache / short-term object cache (edge or warm-store like EBS, Azure Files, or warm S3 tiers) for recently rehydrated blobs.- Orchestrator for background compaction, lifecycle policies, and GC.Modeling pointers / manifests:- Use stable content-addressed object IDs (bucket + prefix + objectKey + version/timestamp) plus small manifest record stored in DB: {row_id, partition_key, blob_id, offset, length, schema_version, checksum, compression, last_accessed}- For multi-row objects, manifest maps row ranges -> offsets (like an index file) so DB needs only small metadata per logical row.- Store schema and statistics (min/max, bloom filters) in DB to enable predicate pushdown and skip scanning large blobs.Impact on joins & query planning:- Planner treats external-blobs as “external tables” with available statistics. For joins: - Prefer broadcast/join on metadata-only columns when possible. - Push filters to metadata using statistics and bloom filters to avoid rehydration. - For remaining rows, planner can: - Rehydrate only necessary object byte ranges (range GET) using offsets from manifest. - Execute late binding: fetch payloads for matched keys post-join to minimize data movement.- Join cost model augmented with rehydration cost; planner favors join orders minimizing expected rehydrations.Latency & rehydration trade-offs:- Cold-object read adds network + object-store latency (tens to hundreds ms) and possibly cold-start egress costs.- Mitigations: - Pre-warm: lazy prefetch of predicted-access partitions. - Store small hot portions (indexes, minimal projection) in DB to satisfy most queries without rehydration. - Batch rehydration: amortize cost for scan-heavy queries. - Use multi-tiered object classes (S3 IA, S3 Standard) and configure retrieval SLAs per tenant.- SLA examples: sub-100ms for metadata queries; 100ms–2s for single-row rehydration; seconds+ for large analytic rehydrations.Operational concerns & trade-offs:- Consistency: use two-phase update (move blob, update manifest atomically) and tombstones for GC.- Security: encrypt objects, propagate ACLs, use signed URLs for temporary access.- Cost/complexity: extra orchestration, more complex planner, monitoring; but 3–10x storage cost reduction depending on data temperature.- Observability: track access metrics, hit/miss rates, rehydration latency, and incorporate into tiering thresholds.This design balances cost and responsiveness by keeping small, query-critical metadata hot while moving large cold payloads to cheap object storage, and by making the query planner rehydration-aware so it only fetches blobs when necessary.
MediumTechnical
38 practiced
Explain polyglot persistence and when it makes sense to use multiple specialized databases (relational + document + graph) within a single product. Provide an example architecture for an ecommerce platform that uses each type, and describe patterns to keep data consistent across different stores (CDC, event bus, best-effort eventual consistency).
Sample Answer
Polyglot persistence is the pragmatic use of multiple, purpose-built data stores in a single system so each data model and workload uses the best tool (e.g., relational for transactions, document for flexible product catalogs, graph for relationships/recommendations). It makes sense when different subsystems have distinct access patterns, performance or schema-flexibility needs that a single DB would handle inefficiently.When to use multiple DBs:- Strong ACID transactional needs (orders, payments) → relational DB (Postgres, MySQL)- Flexible, evolving product data and fast reads for catalogs → document DB (MongoDB, DynamoDB)- Complex relationships, recommendations, social features → graph DB (Neo4j, Amazon Neptune)- Analytics/OLAP → columnar store or data warehouseExample ecommerce architecture:- Frontend/API layer (microservices) - Orders service → PostgreSQL (ACID) - Catalog service → MongoDB (documents with denormalized variants) - Customer/Recommendations service → Neo4j (purchase graph, user-product edges)- Event bus (Kafka) for domain events (OrderCreated, ProductUpdated, InventoryChanged)- CDC connectors (Debezium) streaming changes from Postgres to Kafka- Materialized read stores / caches (ElastiCache, read replicas) for low latency- Data warehouse (Snowflake/Redshift) via Kafka sink for analyticsKeeping data consistent across stores:- Use CDC to capture authoritative changes from transactional stores and publish to the event bus.- Design events as source-of-truth messages (immutable, with version/timestamp); consumers update their local stores idempotently.- Adopt eventual consistency and best-effort reconciliation: background jobs validate and repair mismatches (periodic reconciliation, hash checksums).- Use Sagas for multi-step business transactions that span services (choreography via events or orchestration) to ensure compensating actions on failure.- Make reads tolerant: expose consistency level (strong for checkout, eventual for recommendations) and surface stale-read indicators where needed.Trade-offs:- Operational complexity, data duplication, and debugging overhead increase.- Observability (tracing events, monitoring CDC lag) and idempotent consumers are essential.- Choose polyglot persistence when benefits (performance, developer velocity, features) outweigh operational cost.
Unlock Full Question Bank
Get access to hundreds of Data Model Design and Access Patterns interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.