Data Warehousing Interview Questions

Sample Answer

Model the core fact at the line item level with one row per order_item per recognized revenue event, and model returns as separate negative adjustments at the same grain. Line item grain preserves joinability to product, promotion, and fulfillment dimensions, and it avoids double counting when shipments split or partial returns happen. You keep immutable transaction facts and compute net sales as $\text{gross} - \text{discounts} - \text{refunds}$ with finance owned rules for recognition timing. You also add a reconciliation key to source ledgers, so month end rollups tie out deterministically.

Sample Answer

You could do SCD Type 2 on the user dimension, or you could snapshot the user attributes into the purchase fact at write time. SCD Type 2 wins here because you want consistent historical point in time joins across many facts, not just purchases, and you do not want to copy user attributes into every fact. You handle late arriving updates by using effective_start and effective_end timestamps with a deterministic tie breaker, and by reprocessing affected windows when corrections arrive. You also keep a stable surrogate key per SCD version, so facts can join to the correct historical row.

Sample Answer

First, you decide the atomic grain: one row per event with a stable set of required columns, plus a semi structured payload for the rest, so new fields do not force backfills. Next, you define derived entities as separate tables: a session fact at one row per session, and a user day fact for retention, both built from events with deterministic sessionization rules. Then you pick dimensions that are stable, like user, device, and content, and you version any volatile dimensions like app build or experiment assignments. Finally, you optimize for speed by pre aggregating common funnel steps and partitioning or clustering by event_date and user_id, while keeping the raw event table as the source of truth.

Sample Answer

You could do append-only into partitions and run periodic recompute jobs, or you could do incremental upserts with a rolling lookback window. Upserts with a 7 to 8 day lookback wins here because it captures late arrivals every day while keeping work bounded. You load raw events into a bronze table, then rebuild only the affected date partitions in silver, using a stable event_id to dedupe and a replace-partition or merge strategy. For a month backfill, you run the same logic with a widened date range and treat it as a full partition rewrite so counts stay consistent.

Sample Answer

First, you land every CDC event with metadata like key, op type, event time, and a strictly ordered version such as offset or LSN. Next, you dedupe within the micro-batch by key plus version, then sort changes per key by version so you can apply them deterministically even if event_time is messy. Then you merge into the SCD2 table: close the current row by setting end_ts when a newer change arrives, and insert a new row with start_ts at the change time, handling deletes by inserting a tombstone or closing the row. Finally, you enforce uniqueness on the current flag per key and make the merge predicate depend on version, not ingestion time, so replays are safe.

Sample Answer

First, confirm the symptom: file count per partition is exploding, so query planning and metadata reads dominate even if bytes scanned are stable. Next, look at write patterns, microbatches are creating tiny files, so you compact into fewer larger files per partition and enforce target file sizes (for example 256 MB to 1 GB) with periodic compaction. Then, if filters are selective, add clustering within partitions so compaction also improves data skipping rather than just reducing file count. Finally, validate by comparing (1) files and bytes per partition, (2) planning time versus scan time, and (3) end to end latency before and after, while keeping the microbatch watermark unchanged.

Sample Answer

This question is checking whether you can align physical layout to real access patterns, and whether you understand the tradeoff between partition pruning and data clustering. You keep date partitioning if time ranges still matter for pruning, but you add clustering on user_id so micro-partition pruning or file skipping works for the dominant filter and join key. If queries often hit a small set of users across long time ranges, consider an additional derived table keyed by user_id, or a materialized view, rather than repartitioning and risking skew. You also watch clustering maintenance cost, because over-clustering can increase write amplification and degrade load performance.

Sample Answer

This question is checking whether you can separate SQL correctness from storage level pruning and metadata health. You should inspect the query profile for partitions scanned versus total, and validate clustering depth and pruning metrics for the filtered columns. If pruning is weak, you either filtered on an uncorrelated column or clustering has degraded, so the engine must scan many micro partitions. The change is to recluster or adjust the clustering key toward the high selectivity predicates, and verify pruning improves on the next run.

Sample Answer

The standard move is to filter on the partitioning column so pruning kicks in, here that means adding an ingestion time predicate to bound the scan. But here, event_date matters because late arriving data can land in newer partitions, so you need a safe ingestion window, for example ingestion_time >= now() - 14 days, then filter event_date to 7 days for correctness. If you control the model, the longer term fix is to repartition by event_date or maintain a derived table partitioned by event_date for reporting. Validate with EXPLAIN or query statistics that bytes scanned drop materially.

Sample Answer

Get this wrong in production and your dashboard can take down the cluster by saturating CPU and I/O for hours. The right call is to suspect stale or skewed statistics, so you check ANALYZE freshness, row count changes, and whether the planner misestimated join cardinality. You also confirm indexes exist and are being considered, but you do not start by rewriting SQL. First action is to refresh statistics, then recheck the plan, only then consider targeted indexes or query changes if the plan remains wrong.

Sample Answer

The standard move is workload isolation with right-sized, auto-scaling compute and explicit query routing for BI versus ETL. But here, concurrency matters because dozens of small queries can thrash shared resources, so you also need result caching, materialized aggregates, and queueing or admission control for worst offenders. Put budgets and per-workload caps in place, then use query tagging and chargeback to make the cost drivers visible by team and dashboard. Track $p95$ latency, queue time, bytes scanned per query, cache hit rate, and cost per dashboard view or per 1,000 queries.

Sample Answer

Get this wrong in production and you end up with millions of small files, runaway metadata, and deletes that require full rewrites, which explodes both latency and cloud bill. The right call is to use a transaction log table format with partitioning by event date, plus clustering or z-ordering on high-selectivity fields to reduce scan. You schedule compaction, optimize clustering, and vacuum or retention policies that balance time travel needs with storage growth, for example keep 7 to 30 days of versions, then aggressively clean. For privacy deletes, you batch them, rewrite only affected files via delete vectors or targeted rewrites, and monitor file counts, average file size, and scan-to-return ratio.