Modeling & transformation

Normalization removes redundancy so each fact lives in one place; denormalization duplicates it to avoid joins. The tradeoff is writes-and-integrity vs reads-and-speed.

Dimension attributes change over time. SCD is how a warehouse handles that: Type 1 overwrites, Type 2 keeps history as versioned rows, Type 3 keeps the previous value.

A star schema puts a central fact table of measurable events around dimension tables of context — denormalized so analytics queries stay fast.

dbt runs the T in ELT inside the warehouse: you write SELECT statements as models, and dbt orders dependencies, materializes tables, tests, and docs.

Dataform is a SQL-based transformation framework, now part of Google Cloud, for managing ELT inside BigQuery with SQLX models, ref dependencies, and tests.

SQLMesh parses your SQL to understand columns and dependencies, and uses virtual environments to preview changes without rebuilding production tables.