Data warehouse questions come up in almost every ETL tester, BI developer and data engineer interview. This page collects 23 data warehouse interview questions with short, clear answers, grouped from basics to dimensional modeling, slowly changing dimensions and data warehouse testing. Practise saying each answer out loud in under a minute.
Data Warehouse Basics
1. What is a data warehouse?
A central database designed for analysis and reporting. It integrates data from multiple source systems, keeps history and is organised for fast queries rather than fast transactions.
2. What is the difference between OLTP and OLAP?
OLTP systems handle day-to-day transactions with many small inserts and updates and normalised tables. OLAP systems, such as a data warehouse, handle large analytical queries over historical data, usually with denormalised star or snowflake schemas.
3. What is the difference between a data warehouse and a data mart?
A data warehouse covers the whole organisation. A data mart is a smaller subset focused on one business area, such as sales or finance, often built from the warehouse.
4. What is a staging area?
An intermediate layer where raw source data lands before transformation. It allows cleansing and validation without touching the source systems and gives testers a checkpoint between source and target.
5. What is the difference between ETL and ELT?
In ETL, data is transformed before loading into the warehouse. In ELT, raw data is loaded first and transformed inside the warehouse, which is common with cloud platforms. See ETL vs ELT.
Dimensional Modeling
6. What is a fact table?
A table that stores measurable events, such as sales amount or quantity, at a defined grain, with foreign keys to dimension tables.
7. What is a dimension table?
A table that stores descriptive attributes used to filter and group facts, such as customer, product, date or store.
8. What is the difference between a star schema and a snowflake schema?
In a star schema, each dimension is a single denormalised table joined directly to the fact. In a snowflake schema, dimensions are normalised into several related tables, which saves space but needs more joins.
9. What is the grain of a fact table?
The level of detail one row represents, for example one row per order line per day. Defining the grain is the first step of fact design, and testers use it to write aggregation checks.
10. What are additive, semi-additive and non-additive facts?
Additive facts (sales amount) can be summed across all dimensions. Semi-additive facts (account balance) can be summed across some dimensions but not time. Non-additive facts (ratios, percentages) cannot be summed.
11. What is a surrogate key?
A system-generated key, usually an integer, used as the primary key of a dimension instead of the business key. It allows SCD Type 2 versions and isolates the warehouse from source key changes.
12. What is a conformed dimension?
A dimension shared with the same meaning and keys across multiple fact tables or data marts, such as a common date or customer dimension.
13. What is a factless fact table?
A fact table with no measures that records the occurrence of an event, such as student attendance, or coverage, such as which products were on promotion.
14. What is a junk dimension?
A dimension that groups low-cardinality flags and indicators (such as is_gift or payment_type) into one table to keep the fact table narrow.
Slowly Changing Dimensions
15. What is a slowly changing dimension?
A dimension whose attributes change occasionally over time, such as a customer address. The SCD type defines how history is handled.
16. Explain SCD Type 1, 2 and 3.
Type 1 overwrites the old value. Type 2 adds a new row with effective dates and a current flag, keeping full history. Type 3 keeps the previous value in an extra column.
17. How do you test SCD Type 2?
Check that a change closes the old row and creates a new current row, that there is exactly one current row per business key, that effective dates do not overlap or leave gaps, and that facts join to the version current on the transaction date. See SCD testing.
Data Warehouse Testing
18. What is data warehouse testing?
Validating that data loaded into the warehouse is complete, accurate, consistent and timely, including structure, transformations, data quality, integrity, performance and regression. See DWH testing.
19. What checks do you run after a warehouse load?
Metadata checks, record counts, missing and extra records, transformation rules, null and duplicate checks, referential integrity between facts and dimensions, and reconciliation of totals.
20. How do you find orphan records in a fact table?
LEFT JOIN the fact to the dimension on the foreign key and filter where the dimension key IS NULL. The result should be empty, or the rows should map to an agreed unknown member.
21. How do you test an incremental load?
Load a baseline, then insert, update and delete source rows and run the delta. Verify only changed rows are processed, updates are applied, deletes follow the spec and a re-run creates no duplicates.
22. What is data reconciliation?
Comparing counts and totals between source and target, usually by day or another business grouping, to prove nothing was lost or duplicated.
23. What is the hardest data warehouse defect to catch?
Defects where counts match but values are wrong, such as truncated text, swapped date parts or a wrong lookup. Column profiling and rule-based comparisons catch them; counts alone do not.
SQL tasks you may be asked to write
-- Typical interview task: find customers with more than one current SCD2 row SELECT customer_id, COUNT(*) AS current_rows FROM dim_customer WHERE is_current = 1 GROUP BY customer_id HAVING COUNT(*) > 1; -- Typical interview task: daily sales reconciliation between staging and fact SELECT s.sale_date, s.total AS stg_total, f.total AS fact_total FROM (SELECT sale_date, SUM(amount) AS total FROM stg.sales GROUP BY sale_date) s LEFT JOIN (SELECT d.calendar_date AS sale_date, SUM(f.amount) AS total FROM fact_sales f JOIN dim_date d ON d.date_sk = f.date_sk GROUP BY d.calendar_date) f ON f.sale_date = s.sale_date WHERE f.total IS NULL OR s.total <> f.total;
For more query questions, see SQL queries for ETL testing interviews.
Prepare for your ETL testing interview: top 50 questions · in-depth answers · for experienced (3–5 years) · for 10 years experience · SQL query questions · scenario-based questions
Frequently Asked Questions
What are the most common data warehouse interview questions?
Expect questions on OLTP vs OLAP, fact and dimension tables, star vs snowflake schema, grain, surrogate keys, SCD types, ETL vs ELT, and how you test a warehouse load.
How do I prepare for a data warehouse interview?
Learn dimensional modeling basics, practise explaining SCD Type 1, 2 and 3 with an example, and be ready to write SQL for joins, aggregations, duplicates and orphan records.
Are data warehouse questions asked in ETL testing interviews?
Yes. ETL testers validate data in the warehouse, so interviewers expect you to understand facts, dimensions, SCDs and reconciliation, and to write SQL that tests them.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.