Data Modeling and Schema Design Questions

Focuses on designing efficient, maintainable data schemas for transactional and analytical systems. Candidates should demonstrate understanding of normalization principles and normal forms, when and why to denormalize for performance, and schema design patterns for different use cases. Expect dimensional modeling topics including fact and dimension tables, star and snowflake schemas, grain definition, slowly changing dimensions, and strategies for handling historical data. The topic also includes trade offs between online transaction processing and online analytical processing designs, query performance considerations, indexing and partitioning strategies, and the ability to evaluate and improve existing schemas to meet business requirements and scale.

HardTechnical

58 practiced

A streaming ingestion pipeline writes into a delta/iceberg table with append-only commits. Describe an efficient strategy to implement deduplication and idempotent upserts at scale, given that events may be duplicated in the stream and arrive out-of-order. Include discussion of keys, compaction, and impact on partitioning.

Sample Answer

Situation / goal (brief): We ingest an append-only stream into a Delta/Iceberg table but must ensure deduplication and idempotent upserts at scale despite duplicates and out-of-order events. The solution must be efficient (avoid full-table rewrites), scalable, and operational.

Strategy (high-level steps)1. Keys & versioning- Define a stable primary key (business_id) and a monotonic/timestamp version (event_time or event_seq). Use (business_id) as record identity and event_time/sequence as version for “latest wins”.- Include a producer-supplied idempotency token (write_id or batch_id) on each write to avoid double-committing the same micro-batch.

2. Streaming-side pre-dedupe & buffering- In the streaming job (Spark Structured Streaming / Flink), maintain short stateful dedupe windows keyed by business_id: - Keep the highest-seen version per key within a configured lateness window (watermark), emit only the latest per window. - This reduces downstream merge volume and handles short out-of-order arrivals.

3. Append-only writes + metadata for upserts- Keep the table append-only at the file level; avoid per-record deletes in the streaming hot path.- Write compact micro-batches that contain only the candidate latest records (business_id + version + payload).- Use transactional commits (Delta transactions or Iceberg snapshots) and idempotent write tokens to guarantee exactly-once semantics for the same batch.

4. Periodic upsert/compaction job (merge-on-read)- Run a background compaction/upsert job (batch) that: - For targeted partitions (see partitioning below), MERGE incoming micro-batch data into the main table using business_id and version: WHEN MATCHED AND incoming.version > table.version THEN UPDATE; WHEN NOT MATCHED THEN INSERT. - Or use Iceberg’s row-level operations (rewrite manifests or MERGE) to atomically replace older rows with newer versions.- This keeps streaming writes simple (append) but makes table state reflect latest rows via atomic merges.

5. Compaction and file-level dedupe- Periodically compact small files into larger columnar files and perform file-level dedupe: rewrite per-partition files retaining only the latest version per business_id.- Use partition-aware compaction: read only affected partitions (based on event_date or ingest_date) to limit I/O.- Use Z-ordering / sorting by business_id to improve locality and make future compactions cheaper.

Partitioning considerations- Partition by coarse time bucket (event_date or ingest_date), not by high-cardinality business_id.- To avoid “hot” partitions, keep daily/hourly partitions depending on throughput.- For even distribution within partition, combine partitioning with bucketing/hashing (e.g., hash(business_id) % N) or Iceberg partition transforms to prevent skew and enable parallel compaction.- Ensure partitioning aligns with your query patterns (most queries filter by date/time).

Scaling & performance optimizations- Keep streaming write micro-batches compact (reduce duplicates upstream) to limit MERGE input size.- Use partition-pruning in MERGE: restrict source to relevant partitions and filter by event_time ranges to minimize scanned data.- Use metadata indexes (Delta’s data skipping or Iceberg’s partition/manifest indexes, Bloom filters) to speed MERGE matching.- Parallelize compaction by partition/bucket and throttle to avoid impacting cluster.

Idempotency & correctness guarantees- Use producer idempotency tokens + transactional write API to ensure the same micro-batch is not applied twice.- Maintain event_time + version comparisons in MERGE so out-of-order late events that are older than current table state are ignored.- Define a lateness policy: accept late arrivals within watermark windows via streaming pre-dedupe; beyond that, treat as backfill and run full re-processing/merge for affected partitions.

Edge cases & operational notes- Late events after compaction: backfill merges limited to partitions and keys affected.- Very late duplicates: maintain an audit/change-log table or use Delta’s change data feed / Iceberg snapshots for incremental reconciliation.- Monitor tombstone/garbage accumulation; schedule VACUUM / expire_snapshots to reclaim storage once safe.

Why this works- Streaming pre-dedupe reduces merge load and network/compute costs.- Append-only streaming writes avoid expensive record-level operations in hot path.- Periodic, partition-scoped merges and compaction converge the table to the correct “latest wins” state with manageable IO.- Partitioning + bucketing keeps compaction and merges parallel and localized for scale.

Example (pseudo-flow)- Stream: stateful dedupe within watermark -> write append file containing (business_id, event_time, payload, write_id).- Every hour: batch job reads recent partitions -> MERGE INTO main_table USING staged_files ON business_id WHEN MATCHED AND staged.event_time > main.event_time THEN UPDATE WHEN NOT MATCHED THEN INSERT.- Nightly: partition-level compaction: read partitions, keep max(event_time) per business_id, rewrite files.

This pattern balances low-latency append ingestion with efficient, scalable idempotent upserts and controlled compaction for production-grade Delta/Iceberg pipelines.

EasyTechnical

34 practiced

What does 'grain' mean in dimensional modeling? Given a Sales fact table, list three possible grain choices (e.g., per-order-line, per-invoice, per-day-per-product) and explain the implications of each choice for aggregation, joins to dimensions, deduplication and ETL complexity.

Sample Answer

Grain = the atomic level of detail a fact table represents — one row equals one occurrence of that event at that level. Defining grain precisely is critical because it determines how you aggregate, join to dimensions, deduplicate, and build ETL.

Example grains for a Sales fact table:

1) Per-order-line (one row = one order line / product on an order)- Aggregation: natural for SKU-level metrics (quantity, line revenue); easy to roll up to order, customer, product, date.- Joins: direct joins to product, order, customer, promotion dims.- Deduplication: straightforward — dedupe by order_id + line_id.- ETL complexity: moderate — need to explode orders into lines, preserve line identifiers and price adjustments.

2) Per-invoice / per-order (one row = entire order/invoice)- Aggregation: good for order-level KPIs (order revenue, discounts); losing line-level granularity prevents SKU-level analysis without a separate table.- Joins: joins to customer, order-level promo, shipping; product joins require an additional order-line lookup.- Deduplication: dedupe by invoice_id/order_id.- ETL complexity: simpler upstream (summarize lines into totals) but you must ensure consistent summarization and handle multi-SKU reconciliation.

3) Per-day-per-product (one row = product x date, aggregated)- Aggregation: optimized for time-series reporting and fast rollups; not suitable for reconstructing individual orders.- Joins: joins to product and date dims; customer or order-level joins are impossible without linking back to transactional source.- Deduplication: requires careful incremental logic to avoid double-counting (e.g., late-arriving transactions).- ETL complexity: higher for incremental aggregation (windowing, re-aggregation on updates, late arrives), but yields smaller tables and faster analytics.

Trade-offs summary: finer grain (order-line) preserves flexibility but increases storage and ETL detail; coarser grain (daily-product) improves performance and storage but sacrifices analytic flexibility and complicates certain joins and reconciliation. Choose grain to match primary reporting use cases and upstream system capabilities.

HardTechnical

31 practiced

Your analytics workload requires per-day per-product sales metrics and also the ability to reconstruct historical daily cohorts quickly. Propose a schema and storage layout for daily snapshots that balance storage cost with query speed and support efficient point-in-time queries. Describe how to store deltas vs whole-day snapshots and how to rebuild a historical snapshot for a given date.

Sample Answer

Requirements clarity:- Need fast per-day per-product metrics (common queries), plus point-in-time reconstruction of the entire daily state for cohorting/historical analysis.- Balance storage cost vs query latency.

Proposed schema (columnar table for analytics, immutable storage for snapshots/deltas):1) Base daily full snapshot (monthly cadence, compressed Parquet/ORC) — stores product_id, date_snapshot, metric fields (sales_count, revenue, returns…), last_updated_ts.2) Daily deltas (append-only) — store only changed product rows for that day in small Parquet files or a Delta Lake table partitioned by date and product_id: (product_id, change_date, op_type {upsert/delete}, metric_fields, effective_from_ts).3) An index/manifest table (small) that maps snapshot_date → base_snapshot_path + list of delta files to apply.

Storage layout:- Partition base snapshots by month: /snapshots/YYYY-MM/base_snapshot.parquet- Store daily deltas under /deltas/YYYY-MM-DD/*.parquet- Keep manifests in cheap key-value (DynamoDB / BigQuery metadata table / Hive metastore).

How to store deltas vs whole-day snapshots:- Whole-day snapshots every N days (e.g., monthly) — tradeoff: fewer rewinds to apply but larger storage.- Daily deltas capture only changed rows; retain deltas for full retention window (e.g., 2 years). Periodically compact deltas into new monthly snapshot.

Rebuilding historical snapshot for date T:1) Locate latest base snapshot with date S <= T (from manifest).2) Load base snapshot into compute (columnar prune product_id/date).3) Sequentially apply deltas for dates (S+1) .. T in order. For each delta, apply upserts/deletes (SQL merge semantics). Example SQL (Spark/Delta):

sql

-- load base into temp table base
   -- for each delta_date in range:
   MERGE INTO base b
   USING delta_table d
   ON b.product_id = d.product_id
   WHEN MATCHED THEN UPDATE SET *
   WHEN NOT MATCHED THEN INSERT *

4) Result is point-in-time table for date T; persist or query in-memory.

Optimizations:- Use columnar storage + predicate pushdown; prune non-changed partitions.- Keep daily materialized aggregates for most-used metrics (per-day per-product) in a rollup table to serve fast queries without reconstruction.- Use compaction/merge-on-read (Delta Lake / Iceberg) so deltas are fast and can be compacted into snapshots automatically.- Maintain bloom filters/secondary indexes for hot product lookups.- Consider storing cumulative snapshots (running totals) for cohort endpoints to avoid applying long delta chains.

Trade-offs:- More frequent full snapshots → faster restores but higher storage; longer delta chain → lower storage but slower rebuilds.- Choose snapshot cadence (monthly/weekly) based on rebuild SLAs and change density.

This design gives cheap storage for most history, fast daily queries via rollups, and bounded-cost point-in-time reconstruction using base+delta application.

MediumTechnical

56 practiced

Provide a step-by-step approach to evaluate and improve an index strategy on a production database with frequent schema changes. Include how you'd collect evidence, prioritize indexes, and safely deploy index changes.

MediumTechnical

41 practiced

You have a fact table that is heavily used for aggregations by customer and product. Query profiling shows many slow GROUP BY queries. Propose schema-level and physical-design changes to speed up aggregation queries without significantly increasing write latency.

Unlock Full Question Bank

Get access to hundreds of Data Modeling and Schema Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.