Day 14Phase 03 · Data Engineering Fundamentals

Facts, dimensions and grain

The curriculum for this day stays visible. The lesson, code, and workspace unlock with a pass.

By the end of today you can

  • Define the exact grain of a business process before writing table DDL.
  • Model relational data into Kimball star schemas with fact and dimension tables.
  • Generate surrogate keys and prevent join fan-out row count inflation.

Normalization (3NF) vs. Denormalization (Star Schema)

In Day 3, you learned how relational databases split data across tables using joins. In operational systems like PostgreSQL (OLTP), databases follow Third Normal Form (3NF).

In 3NF, data is split into multiple tables so every non-key column depends strictly on the primary key. For example, a customer's city name lives in a separate cities table to prevent duplicating the string “Bengaluru” across 100,000 customers. This ensures updates are fast: changing a postal code touches exactly 1 row.

This is the first section of the lesson. The rest unlocks with a pass.

Core topics: OLTP vs OLAP, fact tables, dimension tables, star vs snowflake, surrogate vs natural keys, declaring grain

Build: Kimball Star Schema in DuckDB (orders, customers, products, dates) with grain fan-out debugger.

Declare the grain first: grain dictates primary keys, valid metrics, and prevents join fan-out.

Already have a pass? Go to your account. Need a pass? See pricing.