Data Warehouse Interview Questions Answered! Data Warehousing Explained
We build one online store's warehouse as we go: a stock table you can't add up across days, a fulfillment row that gets updated in place, a customer who moves from Leeds to Bristol, and a sale that arrives before its customer. For each question you get the weak answer, why it loses points, and the answer strong candidates give. The questions cover four areas: definitions (Inmon's four properties, OLTP Vs. OLAP), design (grain, additivity, fact table types, surrogate keys, conformed, role-playing, degenerate and junk dimensions), history and loading (slowly changing dimension types 0 to 7, Kimball's Type 4 mini dimension, late-arriving dimensions and facts), and architecture and platform (Kimball Vs. Inmon, data marts, the ODS, staging, ETL Vs. ELT, columnar storage and MPP, warehouse Vs. lakehouse). The recap at the end works as a revision sheet. ⏱️ Chapters 0:00 - 25 questions, 4 areas, and one idea: the grain 0:46 - Q1: What is a data warehouse? 2:02 - Q2: OLTP Vs. OLAP 2:28 - Q3: Why not report off the production database? 2:58 - Q4: Fact table Vs. dimension table 3:16 - Q5: What is the grain? 3:56 - Q6: The stock table trap (semi-additive facts) 5:10 - Q7: The types of fact table 5:35 - Q8: How to model order fulfillment 6:19 - Q9: Factless fact tables 6:54 - Q10: Surrogate keys Vs. natural keys 7:33 - Q11: Conformed dimensions 8:00 - Q12: Role-playing dimensions 8:14 - Q13: Degenerate dimensions 8:32 - Q14: Junk dimensions 8:52 - Q15: Slowly changing dimensions, types 0 to 7 9:59 - Q16: SCD Type 4 is the mini dimension 10:44 - Q17: A sale arrives before its customer 11:45 - Q18: Late-arriving facts 12:20 - Q19: Kimball Vs. Inmon 12:39 - Q20: What is a data mart? 13:02 - Q21: Operational data store (ODS) 13:18 - Q22: What the staging area is for 13:29 - Q23: ETL Vs. ELT 13:57 - Q24: Why cloud warehouse scans are cheap 14:35 - Q25: Data warehouse Vs. lakehouse 15:05 - Recap: all 25 questions on one sheet 🔗 Links Kimball Group dimensional modeling techniques: https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/ Kimball Design Tip #152 (SCD types 0–7, Margy Ross): https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/ IBM, OLTP Vs. OLAP: https://www.ibm.com/think/topics/olap-vs-oltp IBM, ETL Vs. ELT: https://www.ibm.com/think/topics/elt-vs-etl Amazon Redshift columnar storage: https://docs.aws.amazon.com/redshift/latest/dg/c_columnar_storage_disk_mem_mgmnt.html Snowflake architecture: https://docs.snowflake.com/en/user-guide/intro-key-concepts Databricks lakehouse: https://www.databricks.com/glossary/data-lakehouse Databricks medallion architecture: https://www.databricks.com/glossary/medallion-architecture Delta Lake: https://delta.io #DataWarehouse #DataEngineering #InterviewPrep