Data Warehouse Interview Questions cheat sheet
Dimensional modeling, slowly changing dimensions, fact grain, loading patterns, and the warehouse design questions interviewers actually ask.
Dimensional modeling
What is the difference between a star schema and a snowflake schema?- A star keeps each dimension denormalized in one wide table, so every fact-to-attribute path is a single join. A snowflake normalizes hierarchies into sub-tables, saving storage and centralizing hierarchy maintenance at the cost of extra joins. Columnar engines compress repeated dimension strings so well that the storage argument is mostly dead, which is why star is the default; reach for snowflaking only on very large or heavily shared dimensions.
Walk me through the four-step Kimball dimensional design process.- Select the business process, declare the grain, identify the dimensions, then identify the facts. Declaring the grain is the step teams skip and the one that causes rework, because every dimension and measure must be true at exactly that grain. Interviewers follow up by asking you to state the grain of a table you have built, in one sentence, as a single row meaning.
What is a conformed dimension and why does it matter?- A dimension shared across multiple fact tables with identical keys and attribute meanings, so measures from different processes can be compared or drilled across. Without conformance, customer in the billing mart and customer in the support mart silently mean different populations and any cross-process metric is wrong. The bus matrix is the artifact that documents which facts share which conformed dimensions.
Kimball or Inmon — which would you choose and why?- Inmon builds a normalized third-normal-form enterprise warehouse first and derives departmental marts from it; Kimball builds conformed dimensional marts directly against a bus matrix. Inmon is slower to first value but stronger on enterprise-wide consistency and lineage. Most 2026 stacks are hybrids — a raw and normalized landing layer plus a dimensional serving layer, which is exactly what medallion architecture formalizes.
What is a degenerate dimension?- A dimension attribute that lives on the fact table with no corresponding dimension table, almost always a transaction identifier such as order number or invoice number. It has no useful attributes of its own, so creating a dimension table with one row per fact row would be pure overhead. It still matters because it identifies the grain and enables grouping back to the source transaction.
What is a junk dimension and when would you build one?- A junk dimension collapses several low-cardinality flags and indicators — order channel, payment type, gift flag, priority — into one dimension holding the distinct combinations that actually occur. It replaces five narrow columns on a billion-row fact with a single small key. Build it when the flags have no natural home and the observed combinations number in the hundreds, not millions.
Slowly changing dimensions
Explain SCD types 0, 1, 2, and 3.- Type 0 never changes, used for durable attributes like original signup date. Type 1 overwrites in place and loses history. Type 2 closes the current row and inserts a new version, preserving full history with valid_from, valid_to, and a current flag. Type 3 adds a previous-value column, which captures exactly one prior state and is used for things like a single realignment of sales territory.
How do you actually implement SCD Type 2 in a warehouse?- Compute a hash of the tracked attribute columns, compare it to the hash on the current row, and where they differ run a MERGE that stamps valid_to and sets is_current to false on the old row while inserting a new row with the new valid_from. Keep valid_to exclusive or use a far-future sentinel like 9999-12-31 consistently, because mixing inclusive and exclusive bounds is the classic source of duplicate rows in point-in-time joins.
Why does SCD Type 2 force you to use surrogate keys?- Type 2 produces multiple rows per natural key, so the natural key alone can no longer identify a dimension row and a fact must point at the version that was in effect at event time. A surrogate key also insulates the warehouse from source key format changes and lets you merge the same entity from multiple source systems without key collisions.
What is SCD Type 6?- A hybrid of types 1, 2, and 3 — the dimension keeps Type 2 history rows and additionally carries a current-value column that is overwritten on every row for that natural key. That lets one query report a sale under the region that was in effect at the time, and another report the same sale under the region the customer sits in today, without a second join.
How do you handle a fact that arrives before its dimension row?- Insert an inferred member — a placeholder dimension row carrying the natural key with attributes set to unknown defaults — and point the fact at that surrogate key immediately. When the real dimension record lands, update the placeholder in place as a Type 1 change so existing facts stay correctly joined. The alternative, routing the fact to an error table, delays the measure and usually gets forgotten.
Which columns should get Type 2 tracking?- Only attributes whose historical value is actually reported on — segment, region, tier, status. Tracking volatile operational fields like last_login_at or a recomputed score turns a dimension of one million members into hundreds of millions of versions and makes point-in-time joins expensive. The honest answer is that this is a business question, and the follow-up interviewers like is how you would retrofit tracking onto a column later.
Fact tables and grain
What are the three types of fact tables?- Transaction facts record one row per event at the finest grain. Periodic snapshot facts record the state of something at regular intervals, such as daily account balance. Accumulating snapshot facts hold one row per process instance with multiple date columns that get updated as the instance moves through milestones, which makes them the only fact type you routinely update.
Explain additive, semi-additive, and non-additive measures.- Additive measures sum across every dimension, like revenue. Semi-additive measures sum across some dimensions but not time — an account balance can be summed across accounts but must be averaged or taken as the last value across days. Non-additive measures such as ratios and percentages cannot be summed at all, which is why you store the numerator and denominator and divide at query time.
What is a factless fact table?- A fact table with only foreign keys and no measures, used for event tracking such as student attendance, or for coverage such as which products were on promotion in which stores. Coverage tables answer the questions plain event data cannot, like which promoted products sold nothing — you find them by anti-joining coverage against sales.
How do you model a many-to-many relationship between a fact and a dimension?- Insert a bridge table keyed by a group key on the fact, with one row per member of the group and an optional allocation factor. Summing without the allocation factor double counts, so the interviewer will ask whether you want the impact-weighted answer (allocate) or the correlation answer (do not allocate, and warn the user that totals will exceed the true total).
Why should you avoid storing ratios and percentages in a fact table?- Ratios are non-additive, so any aggregation of stored ratios computes an average of averages, which is wrong whenever the denominators differ. Store the two components as additive measures and compute the ratio in the semantic layer or the query. The same argument applies to storing pre-aggregated year-to-date columns.
How would you handle a fact table that has grown to a trillion rows?- Confirm the grain is really needed, then partition on event date, cluster or sort on the highest-cardinality filter column, and add aggregate tables or materialized views for the common rollups. Push cold partitions to cheaper storage with a retention policy. The follow-up is whether an aggregate can serve the query without the user knowing, which is what aggregate navigation in a semantic layer provides.
Loading and incremental patterns
How do you make a warehouse load idempotent?- Make the write operation a function of the input rather than an append — delete-and-insert by partition, insert-overwrite of the affected partitions, or MERGE on a stable business key. Then a retry after a partial failure converges to the same state. Plain appends with retries are the single most common cause of silent duplicate rows in production.
How do you detect and apply deletes from a source system?- Soft deletes with a flag are easiest, log-based CDC gives real delete events, and where neither exists you reconcile by comparing the full set of source keys against the warehouse on a schedule and marking absent keys as deleted. Interviewers probe the third case because a nightly full key comparison is often the only correct option and it must be cheap — key-only extracts and hash comparison keep it affordable.
Hash surrogate keys or sequence surrogate keys?- Hashing the natural key with a stable function lets every worker generate keys independently with no lookup and no coordination, which is why it dominates in distributed ELT and dbt projects. Sequences give compact ordered integers with better join and compression characteristics but need a central allocator. Collision risk on a 128-bit hash is negligible in practice; the real cost is wider keys and losing load order.
How do you handle late-arriving facts?- Partition on event time rather than load time, keep a bounded lookback window — commonly 3 to 7 days — and reprocess those partitions on each run rather than only the current day. Anything arriving outside the window goes through an explicit backfill. State the tradeoff, which is that a wider lookback costs compute on every run and a narrower one silently drops data.
What is the purpose of a staging or raw layer if you transform everything anyway?- It gives you an untransformed, replayable copy of what the source actually sent, so you can rebuild every downstream model after a logic bug without re-extracting from a system that may no longer hold the history. It also isolates source schema volatility at one boundary. Keeping it transient with short retention controls the cost.
How would you backfill three years of history without disrupting production?- Run the backfill into a separate table on a dedicated warehouse or cluster so it cannot contend with production, process partition by partition with checkpointing so a failure resumes rather than restarts, validate row counts and column checksums against the source, then swap the tables atomically. Announce the swap because downstream caches and BI extracts will need to refresh.
Architecture and the modern stack
What is the difference between OLTP and OLAP systems?- OLTP handles many short read-write transactions against few rows, normalized to avoid update anomalies, indexed for point lookups, tuned for latency and concurrency. OLAP scans large row counts over few columns, denormalized for join avoidance, stored columnar with compression, tuned for throughput. The practical consequence is that running analytics on the OLTP replica works until it does not, usually at the first full table scan during business hours.
What is a lakehouse and how is it different from a warehouse?- A lakehouse stores data as open table formats such as Apache Iceberg or Delta Lake on object storage, adding ACID transactions, schema evolution, and time travel on top of Parquet, so multiple engines can read and write the same tables. A classic warehouse owns a proprietary storage format that only its engine can touch. By 2026 the distinction has blurred substantially because Snowflake, BigQuery, Databricks, and Trino all read and write Iceberg natively.
Why did ELT largely replace ETL?- Cloud warehouse compute became elastic and cheap enough that transforming inside the warehouse beat maintaining a separate transformation cluster, and loading raw first means you can re-derive models without re-extracting. The counter-argument interviewers want to hear is cost — ELT pushes unbounded transformation spend into the warehouse bill, and PII that should never land raw still needs masking or tokenization before load.
One big table or star schema for the serving layer?- One big table flattens dimensions into the fact so BI tools issue no joins, which is fast on columnar storage and simple for analysts. The costs are duplicated conformance logic across every wide table, awkward handling of Type 2 history, and expensive full rebuilds when a dimension attribute changes. A common compromise is a star as the modeled truth plus generated wide tables for specific dashboards.
What is a semantic layer and why has it become important again?- It centralizes metric definitions — the joins, filters, and aggregation rules behind revenue or active user — so BI tools, notebooks, reverse ETL, and LLM-driven query interfaces all resolve the same number. The renewed interest comes from AI query agents, which will confidently produce a plausible wrong revenue figure unless a governed metric definition constrains them.
When would you use a materialized view instead of a table or a view?- A view is free to maintain and always fresh but pays full compute on every read. A table is cheap to read but you own the refresh orchestration. A materialized view is the middle ground where the engine maintains it, but every engine restricts what it can incrementally refresh — outer joins, non-deterministic functions, and some window functions typically force a full recompute or are rejected outright, so check the restriction list before promising freshness.
Performance and physical design
Partitioning, clustering, and indexing — what is the difference?- Partitioning physically splits a table into directories or file groups so the engine can skip whole partitions, which is coarse-grained and usually limited to a low-cardinality column like date. Clustering or sorting orders data within files so min/max statistics prune more effectively on higher-cardinality columns. Indexes are separate lookup structures, common in OLTP and rare in columnar warehouses, which lean on statistics-based pruning instead.
How do you choose a partition column and granularity?- Pick the column that appears in almost every query filter, which is nearly always an event date, then size partitions so each holds enough data to be worth a task — roughly 100 MB to 1 GB. Daily partitions suit most workloads; hourly only makes sense above a few hundred gigabytes per day. Over-partitioning creates the small file problem and inflates metadata listing time, which is a bigger practical risk than under-partitioning.
What is data skew and how do you fix it?- Skew is uneven distribution of a join or group key so one task processes far more data than the rest, leaving the job waiting on a single straggler while the cluster idles. Fixes are salting the hot key with a random suffix and aggregating in two passes, broadcasting the small side to avoid the shuffle entirely, or isolating known hot keys and handling them separately. Diagnose it by comparing max task duration to median in the query profile.
Why are small files a problem and how do you fix them?- Every file costs a metadata listing, an open, and a footer read, so thousands of tiny files turn a scan into per-file overhead and starve parallelism. They appear from frequent micro-batch writes and over-partitioning. Fix with periodic compaction — OPTIMIZE in Delta, rewrite_data_files in Iceberg, or a plain insert-overwrite — targeting file sizes in the 128 MB to 512 MB range.
Explain broadcast joins versus shuffle joins.- A broadcast join ships the small table to every node so the large table is never shuffled, which is dramatically faster when the small side fits in memory. A shuffle join repartitions both sides by the join key across the network. The threshold is configurable and adaptive execution can switch strategies at runtime, but a badly estimated small side is a classic out-of-memory cause, so know how to check the estimate in the plan.
Your warehouse bill doubled this quarter. How do you investigate?- Start from query history or the billing views and rank spend by warehouse, by user or service account, and by query fingerprint, because cost is almost always concentrated in a handful of repeated queries or one runaway job. Then check for tables that lost partition pruning after a filter changed, dashboards refreshing far more often than anyone reads them, and warehouses with long auto-suspend timeouts sitting idle. Report the top three drivers with a number attached to each rather than a list of generic tips.
From DataLane — tutorials at/blog, practice SQL live in theplayground.