Data Modeling
Star and snowflake schemas, slowly-changing dimensions, fact and dimension tables, and the modeling patterns analytics engineers ship in dbt.
Writing a correct query and designing the tables it should run against are different skills. This hub focuses on designing staging, intermediate, and mart layers with explicit grains and keys.
This hub covers the modeling patterns analytics engineers use in dbt every day: star schemas, slowly-changing dimensions, fact and dimension tables, normalization vs denormalization, and the trade-offs that come up in real warehouse work. The articles are written for people who write SQL and want to write better SQL — not academic dimensional modeling theory.
Pair this hub with the dbt and SQL hubs, then build the capstone, where you'll design a star-schema mart layer for a marketing-attribution dataset in BigQuery.
By the end of this path you can…
- Design star schemas and know when to denormalize
- Implement Type 2 slowly-changing dimensions in dbt with snapshots
- Pick the right grain for a fact table
- Choose between surrogate and natural keys with eyes open
- Model a marketing-attribution or e-commerce dataset end-to-end
- Answer modeling questions in technical screens
From beginner to job-ready.
- 01 · FundamentalsTables, rows, keys, relationships — the vocabulary the rest of the curriculum is built on. Open this guide →
- 02 · Normalization1NF, 2NF, 3NF in practice — and why analytics teams denormalize on purpose. Open this guide →
- 03 · Star + snowflake schemasFact and dimension tables, conformed dimensions, the grain conversation. Open this guide →
- 04 · Slowly-changing dimensionsType 1, 2, 3 — and SCD2 with dbt snapshots in production. Open this guide →
- 05 · KeysSurrogate vs natural; composite vs single-column; warehouse-specific patterns. Open this guide →
- 06 · Modeling in dbtHow the staging / intermediate / mart pattern maps to the modeling concepts above. Open this guide →
- 07 · Model a real source shapeUse linked ecommerce source tables to declare grain, design dimensions, and prevent double-counted revenue. Open this guide →
Read the playbook.
- Data Modeling
Data Warehouse Schema Design: Star Schema to Galaxy
A hands-on walkthrough of data warehouse schema design. Model a star schema for orders, decide when to snowflake, and scale to a galaxy with multiple fact tables—plus SQL/dbt examples.
- Data Modeling
Fact Table vs Dimension Table: Differences, Grain, and Examples
Explore the roles of fact and dimension tables in data warehousing. Learn how they work together to enhance data analysis and business insights.
- Data Modeling
Data Modeling Basics: Star Schema vs. Snowflake Schema Explained
Learn the differences between star and snowflake schemas in data modeling. Discover how each affects query performance, storage, and maintenance.
- Data Modeling
Slowly Changing Dimensions: Types 1, 2, and 3 with SCD Type 2 Examples
Explore Type 2 Slowly Changing Dimensions to maintain historical data records. Learn how to structure tables and implement tracking methods effectively.
- Fundamentals
Natural Key vs Surrogate Key: Definitions, Examples, and When to Use Each
Learn the differences between surrogate and natural keys in databases. This guide covers their benefits, drawbacks, and when to use each type effectively.
- Data Modeling
Normalization vs Denormalization: Normal Forms and When to Denormalize
Discover how database normalization and denormalization impact data storage and retrieval. Learn when to use each approach for optimal system performance.
- dbt
dbt Project Structure: Staging, Intermediate, and Marts Done Right
A proven dbt project structure you can copy today: clear folders, schema mapping in dbt_project.yml, and tight naming conventions. Includes SQL, YAML, and real-world tips.
Show, don't just claim.
- intermediate · open →
Sports Equipment Pro Shop
E-commerce orders, inventory, and revenue modeling project
- intermediate · open →
Data Forge: The Lost Metrics
Metric-layer recovery and analytics debugging project (dbt + BigQuery)
- beginner · open →
Champion Fantasy League
Event and performance analytics modeling project
Common questions about this topic.
Access, pacing, grading, support, and the capstone—answered directly.
Star schema or snowflake schema?
Star, almost always, for analytics workloads. Snowflaking adds joins; modern columnar warehouses (BigQuery, Snowflake, Databricks) handle wide denormalized tables fine. The exception is huge dimension tables where storage matters more than query speed.
When do I need SCD2?
Whenever a dimension's history affects analysis — customer subscription tier, employee department, product price tiers. If reporting only ever cares about 'as of now', SCD1 (overwrite) is fine. dbt's snapshot block makes SCD2 the easiest pattern to implement.
What's the right grain for a fact table?
The most atomic event the business actually wants to analyze. Orders → one row per order_item, not one row per order, because line-item analysis comes up. Web events → one row per event. Aggregate later; never lose grain.
Surrogate or natural keys?
Surrogate keys for warehouse-side joins (cheap to generate with dbt_utils.generate_surrogate_key). Natural keys for source-system idempotency. The article on the topic walks through both with examples.
Start practicing this topic.
Graded exercises with hints and worked solutions. Free to start, no credit card.