Real DWH Question and Answers

Data Warehouse Interview Q&A · All 45 questions

This page lists every post in the Real DWH Question and Answers series. Each post answers one real data warehouse interview question: the answer to say first, a small sample data set you can create, and every SQL query with its real output, so you can practice along.

Start at question 1 and go in order, or jump straight to the topic you need for your next interview.

Foundations: OLTP, OLAP, warehouses, lakes

1. What is the difference between OLTP and OLAP?
2. Why can't we just run our reports on the OLTP database?
3. What is a data warehouse?
4. What is the difference between ETL and ELT?
5. What is a data lake, and why put it in front of the warehouse?
6. Data lake vs data warehouse vs lakehouse: what is the difference?
7. What is the difference between a row store and a column store?

Data modeling basics

8. What is data modeling?
9. What's wrong with keeping everything in one flat table?
10. What is normalization? Explain 1NF, 2NF and 3NF.
11. Primary key, foreign key, natural key, composite key: what is the difference?
12. What is denormalization, and when should you use it?
13. What is cardinality, and how do you read an ER diagram?

Dimensional modeling: facts, dimensions, grain

14. What is dimensional modeling?
15. What is a fact table? What are additive, semi-additive and non-additive measures?
16. What is a dimension table, and what makes a good one?
17. What is the grain of a fact table, and why does it matter so much?
18. What is the difference between a star schema and a snowflake schema?
19. What is a surrogate key, and why do warehouses use them?
20. What is a date dimension, and why not just use date functions?
21. What are the types of fact tables?
22. What is a factless fact table?
23. What are aggregate tables, and when should you build them?

Advanced dimensions

24. What is a conformed dimension, and what is the bus matrix?
25. What is a degenerate dimension?
26. What is a junk dimension?
27. What is a role-playing dimension?
28. What are mini-dimensions and outriggers?
29. What is a bridge table, and how do you handle many-to-many in a star schema?
30. How do you handle late-arriving dimensions?
31. How do you handle late-arriving facts?

Slowly changing dimensions

32. What are slowly changing dimensions? Explain Types 0, 1, 2 and 3
33. How do you implement SCD Type 2?
34. How do you load SCD Type 2 with a MERGE statement?
35. What are SCD Type 4 and SCD Type 6?

Loading the warehouse

36. In what order do you load dimensions and facts, and why?
37. Full load vs incremental load: what is CDC?
38. What is a staging area? What are bronze, silver and gold layers?
39. What is a data mart, and how is it different from a data warehouse?
40. What is an idempotent load, and how do you add data quality checks?

Architecture and design

41. Kimball vs Inmon: what is the difference?
42. What is a Data Vault? Explain hubs, links and satellites
43. One big table (OBT) or star schema: which is better?
44. How do you make a data warehouse fast?
45. Design a star schema for an online store

Each post has a matching video on the TechBrothersIT YouTube channel.