Data Modelling and Warehousing (DE-201)

A source database answers "what is true right now" for one order at a time. An analytics warehouse answers "what happened last quarter, by region, by cohort" for millions of rows at once. The same data, two jobs — and the shape that makes the first job fast makes the second job slow and error-prone. When a finance dashboard disagrees with the source system, the cause is almost never a broken query. It is a model that was never designed for the question being asked.

DE-201 is where you learn to design that model on purpose. You will move from the normalized tables an application writes to, to the dimensional models a warehouse reads from — deciding the grain of a fact table, choosing between a star and a snowflake, and tracking how a customer's attributes change over time without losing last year's truth. You finish able to take a normalized source and build the analytical schema it should feed.

🎯 What you'll learn

• Explain what normalization buys a source system and why analytics undoes some of it on purpose
• Decide when a normalized 3NF model fits and when a denormalized one wins, using access patterns rather than dogma
• Design a dimensional model: choose the grain of a fact table, separate facts from dimensions, and lay out a star schema
• Compare star and snowflake schemas and justify the extra join a snowflake costs
• Implement a slowly changing dimension (SCD Type 2) that preserves history
• Choose a warehouse loading pattern — full reload, insert-only, or upsert — that matches the table it loads

Who this course is for

DE-201 is a Practitioner course in the School of Data Engineering. It is written for the learner who can already query data and now needs to design the tables other people query:

• The analyst crossing over who lives in a BI tool, has felt the pain of a data model that fights every question, and wants to build the model instead.
• The backend engineer pivoting into data work who knows normalization from application databases and needs to learn where — and why — warehouses break those rules.
• The cohort learner already inside the migrated data-modelling material, converting hands-on progress into the full credentialed course.

The assumption is that you can read and write SQL joins and GROUP BY comfortably. This course builds the modelling judgment on top of that.

Prerequisites

• DE-102 SQL for Data Engineers is recommended. This course leans on the joins, aggregation, and window-function fluency DE-102 builds, and the lab reuses the same local sqlite3 workflow.
• Comfort with multi-table JOIN, GROUP BY, and reading a result set to tell whether a query dropped or doubled rows.
• Python 3.10 or newer for the lab. It uses only the standard-library sqlite3 module — nothing to install, no server, no cost.

You do not need a warehouse account, a cloud subscription, or any paid tool. Practice runs in the SQL Kingdom playground, and the lab runs entirely on your machine.

Modules

DE-201 is roughly twenty hours of effort across seven modules. This slice delivers three core lessons — the normalization trade-off, dimensional modelling, and slowly changing dimensions — plus a hands-on lab that builds a star schema end to end.

#	Module	What you leave with
1	Modelling for analytics	Grain, facts, and dimensions as first principles
2	Star and snowflake schemas	When each shape earns its complexity
3	Slowly changing dimensions	Types 1 and 2, and the cost of keeping history
4	Normalization vs. denormalization	3NF sources, dimensional targets, and access patterns
5	Warehouse design workshop	From business questions to a bus matrix
6	Modern layering	Staging, core, and marts (dbt-style, tool-agnostic)
7	Anti-patterns clinic	One-big-table, snowflake sprawl, premature aggregation

The three lessons and the lab below cover the core of Modules 1–4 and give you the schema-design skill the rest of the course extends.

Outcomes

By the end of this course slice you can:

• Look at a normalized source schema and name what each normal form protects, then decide where analytics should denormalize and why.
• Design a star schema for a business process: fix the fact grain, pick the measures, and split the descriptive context into conformed dimensions.
• Choose a star or a snowflake for a given dimension and defend the join count.
• Track a changing dimension attribute with SCD Type 2 so historical facts still join to the attribute that was true when they occurred.
• Match a loading pattern — reload, append, or upsert — to each table in a model.

Where this leads

The model you design here is the target that everything downstream fills. In DE-202 Batch Processing with PySpark you will express the same joins, aggregations, and dimension loads at scale, and in DE-203 Data Engineering on AWS you will land these tables in a cloud warehouse. The modelling judgment you build in DE-201 — the right grain, the right dimension, the right loading pattern — carries straight into both, and into the school capstone.

tip

Do the lessons in order. Normalization comes first because it frames the whole course: dimensional modelling is a deliberate, disciplined denormalization, and slowly changing dimensions are what you add once the star schema exists. The lab then builds a star schema from a normalized source, so it assumes all three.