Data Modeling: Dimensional & Normalized

Interviewers ask about data modeling because it is the one topic in a data engineering loop that reveals whether a candidate has actually owned a warehouse, not just written queries against one someone else designed. Anyone can SELECT from a well-built star schema. Far fewer people can explain why it's a star and not the normalized schema the OLTP system uses, what happens to a fact table's row count when a dimension changes, or why a "just flatten everything into one table" proposal is sometimes the right call and sometimes a way to quietly triple your storage bill and corrupt your historical reporting. The topic sits at the intersection of theory (normal forms, which most engineers half-remember from a database course) and hard-won production judgment (grain, SCD handling, when denormalization is a feature and when it's a bug) — and interviewers use it specifically because the theory alone doesn't get you to a good answer.

The discipline splits into two traditions that solve different problems. Normalization (1NF through 3NF and beyond) is a set of rules for eliminating redundancy in a schema that many concurrent transactions write to — it's optimized for correctness and write efficiency, and it's what your OLTP application database should look like. Dimensional modeling (Kimball's star schema) is a set of rules for structuring data that analysts and BI tools read, optimized for query simplicity and aggregate performance, and it deliberately reintroduces redundancy that normalization would forbid. A strong candidate can explain both, explain why they're not in tension (they're solving different problems for different systems), and knows the modern wrinkle: columnar cloud warehouses have made some of dimensional modeling's cost trade-offs less clear-cut than they were when Kimball wrote the canon in the 1990s. This subject covers all of it — normalization and why OLTP wants it, the mechanics of building a star schema around grain, Slowly Changing Dimensions worked through concrete rows, the three fact table types, the One Big Table debate, and Data Vault as the enterprise alternative — with runnable DDL throughout, because "I understand it conceptually" and "I can design the schema" are different skills and interviewers are testing the second one.