Data Modeling: Dimensional & Normalized
Normalization for OLTP, Kimball star schemas for analytics, Slowly Changing Dimensions, fact table grain, the One Big Table debate, and when Data Vault beats both
A practitioner's tour of data modeling as a data engineering interview topic: why OLTP systems normalize to 3NF and what breaks when you don't, how Kimball dimensional modeling turns a normalized source into a queryable star schema built around grain, the four ways to handle a dimension that changes over time (SCD 0-3) worked through concrete before/after rows, the three fact table types (transaction, periodic snapshot, accumulating snapshot) and which questions each one answers, the modern argument for denormalizing into One Big Table on a columnar warehouse and where that argument breaks down, and a brief look at Data Vault as the alternative enterprises reach for when Kimball's assumptions stop holding.
Practice questions (5)
-
View →
Design a Star Schema for a Subscription Business
Intermediate · Free -
View →
Fix a Fact Table With Mixed Grain
Intermediate -
View →
Choose an SCD Strategy for Each Attribute in a Customer Dimension
Intermediate -
View →
Evaluate a Proposal to Replace the Star Schema With One Big Table
Intermediate -
View →
Recommend a Modeling Approach for a Multi-Source Enterprise Warehouse
Intermediate