Data warehouse interview questions: concepts, design and performance

Fifteen common data warehouse interview questions

1. OLTP versus OLAP — what's different?
2. What is a data warehouse, and how does it differ from a data lake?
3. What are fact and dimension tables?
4. Star schema versus snowflake schema?
5. What is the grain of a fact table?
6. Additive, semi-additive and non-additive measures?
7. What are slowly changing dimensions? Explain types 1, 2 and 3.
8. What is a surrogate key, and why use it?
9. ETL versus ELT?
10. Why are analytical databases columnar?
11. How do partitioning and clustering speed up queries?
12. What is a materialised view?
13. How do you handle late-arriving facts?
14. How do cloud warehouses separate storage and compute?
15. A dashboard query is slow. How do you speed it up?

Core concepts

Dimensional modelling

• Fact tables record events or measurements with numeric measures and foreign keys to dimensions; dimension tables describe who, what, where and when.
• Grain: what one row represents — “one row per order line” — declared before anything else.
• Measures: additive (sales amount sums across all dimensions), semi-additive (account balance sums across accounts but not across time), non-additive (ratios, which must be recomputed from components).
• Surrogate keys: warehouse-generated keys that decouple the warehouse from source system keys and support history tracking.
• Star vs snowflake: denormalised dimensions one join away versus normalised dimensions with more joins and less redundancy.

Loading and history

ETL transforms data before loading it; ELT loads raw data first and transforms inside the warehouse using its compute, which suits cloud warehouses and keeps raw data available for reprocessing. For history, type 2 slowly changing dimensions add a new row with effective dates when attributes change, so facts join to the version true at the time. Late-arriving facts are joined to the dimension version valid at the event time; late-arriving dimensions may need a placeholder row updated later. See data modeling interview questions.

Performance

Columnar storage reads only the columns a query needs and compresses well because similar values sit together. Massively parallel processing spreads scans across many nodes. Partition large tables by a commonly filtered column such as date so queries skip irrelevant partitions, and cluster or sort by other frequent filters. Materialised views precompute expensive aggregations. For a slow dashboard: check scanned data volume, add partition filters, avoid SELECT *, pre-aggregate into a summary table, and check for exploding joins between facts at different grains.

Cloud warehouses

Modern cloud warehouses separate storage from compute, so you can scale compute independently, isolate workloads with separate compute clusters, and pause compute when idle. Pricing is typically by compute time or data scanned, which makes query efficiency a cost issue as well as a speed one. See Google Cloud interview questions for BigQuery specifics.

A modelling exercise to rehearse

“Model subscriptions for a streaming service.” Declare grains first: a subscription events fact (one row per start, upgrade, downgrade or cancellation) and a daily subscription snapshot fact (one row per subscription per day) for active counts and revenue. Dimensions: customer (type 2 for country and plan history), plan, date and channel. Explain how churn and monthly recurring revenue are calculated from the snapshot, and why the event fact alone makes point-in-time counts hard.

Practising

The fastest way to find gaps is to be asked. Run a spoken mock round on data warehousing topics — a free AI interview assistant such as CoPilot Interview builds one from your résumé and the job description and scores every answer — then study whatever you stumbled on. Model a retail sales process from scratch — declare the grain, list facts and dimensions, and handle a customer who moves city. See also analytics engineer questions.

Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly. Everything else on this page applies whichever tool you use, or none.

Frequently asked questions

What are common data warehouse interview questions?

OLTP versus OLAP, warehouses versus lakes, facts and dimensions, star versus snowflake schemas, grain, measure types, slowly changing dimensions, surrogate keys, ETL versus ELT, columnar storage, partitioning and query tuning.

What is the difference between ETL and ELT?

ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse, which suits scalable cloud warehouses.

What is the grain of a fact table?

The level of detail one row represents, such as one row per order line or per daily account snapshot. Declaring it prevents mixing incompatible data.

Why are data warehouses columnar?

Analytical queries read a few columns across many rows. Columnar storage reads only those columns and compresses similar values efficiently, making scans much faster.