Data Warehousing and Dimensional Modelling
Design star schemas the Kimball way, keep history with slowly changing dimensions, and build and load a working warehouse in DuckDB with SQL you run.
Intermediate · Data Engineering · about 18 hours · 5 modules · certificate on completion
Taught by Thandiwè Dlaminì, Ingridè Bjornssòn, AI Learning Companions.
About this course
A data warehouse turns the records a business keeps into answers it can trust: revenue by region and month, stock that's about to run out, customers as they were last year and as they are today. This course teaches the design behind that, the dimensional modelling Ralph Kimball made standard, and has you build it. You'll start from a co-op's normalised sales database and see why analysts need a different shape, then declare a grain, design facts and dimensions with surrogate keys, keep history with type 1 and type 2 slowly changing dimensions, and compare star and snowflake schemas, aggregate tables and partitioned Parquet files.
Every module has a lab in DuckDB, a free analytical database that runs on any laptop with no server, with starter files and data that are identical on every computer, so your results can be checked to the penny. You'll write the SQL that builds the star, applies customer changes and looks up the right version of each customer, then a Python loader that's incremental, idempotent and audited. The last module places all of this in today's stack of cloud warehouses, ELT and lakehouse table formats such as Apache Iceberg and Delta Lake, and the capstone has you design and build a medicine-supply warehouse for rural clinics with three kinds of fact table.
What you'll learn
- Explain how analytical workloads differ from transactional ones, and why warehouses store data by column
- Design a dimensional model with Kimball's four steps: business process, grain, dimensions and facts
- Keep history with type 1 and type 2 slowly changing dimensions, and find the right version when loading facts
- Choose between star and snowflake designs, and build aggregates and partitioned Parquet that reconcile with the base facts
- Write incremental, idempotent loads with unknown and inferred members, and explain where lakehouse table formats fit
Skills you'll gain
Dimensional modelling, Star schema design, Fact and dimension tables, Slowly changing dimensions, Analytical SQL, Aggregate tables and partitioning, Incremental warehouse loading, Lakehouse table formats
Tools
DuckDB, Python, Apache Parquet, PostgreSQL
Before you start
SQL for Data Analysis with DataGrip, or the same SQL: joins, GROUP BY, CTEs and window functions. Data Engineering Foundations: Batch and Streaming Pipelines helps but isn't required. Install Python 3.10 or later and the duckdb package (pip install duckdb); PostgreSQL is optional. Analytics Engineering with dbt and DuckDB and Enterprise Data and ML Platforms make good next steps.
Modules
Analytical workloads and the data warehouse
Why the database that records sales is the wrong shape for analysing them: OLTP versus OLAP, what a data warehouse is and how it's built, and how columnar engines such as DuckDB run analytical queries.
- OLTP and OLAP: two workloads, two designs (12 min)
- What a data warehouse is, and how it's built (12 min)
- Columnar storage and how analytical engines run queries (13 min)
- Hands-on lab: Ask the operational database an analytical question
Dimensional modelling: facts, dimensions and grain
Kimball's four-step design process, the kinds of fact table and measure, how to design dimensions, and the surrogate keys, conformed dimensions and bus matrix that tie a warehouse together.
- The four-step design process and the grain (12 min)
- Fact tables and their measures (13 min)
- Designing dimensions (12 min)
- Surrogate keys, conformed dimensions and the bus matrix (12 min)
- Hands-on lab: Build the co-op's star schema
Slowly changing dimensions
How dimensions keep up with a changing world: SCD types 0, 1, 2 and 3, the mechanics of type 2 versions, and the SQL that applies changes and finds the right version when facts arrive.
- Why dimensions change, and the SCD types (12 min)
- Type 2 mechanics: versions, dates and lookups (13 min)
- Applying changes with SQL (12 min)
- Hands-on lab: Track customer history with type 1 and type 2 changes
Star or snowflake, aggregates and physical design
When to normalise a dimension and when not to, how aggregate tables and materialised views speed up common questions without changing the answers, and the partitioning, sorting and file choices that keep large fact tables fast.
- Star and snowflake schemas (12 min)
- Aggregate tables and materialised views (12 min)
- Physical design: partitions, sorting and files (12 min)
- Hands-on lab: Snowflake, aggregate and partition the sales star
Loading the warehouse, and the lakehouse
Load patterns that keep a warehouse right night after night (incremental and idempotent loads, unknown and inferred members, audit columns), and where open table formats, lakehouses and cloud warehouses fit around a dimensional model.
- Incremental, idempotent loads (13 min)
- From warehouse to lakehouse: open table formats (12 min)
- Dimensional models in today's data stack (12 min)
- Hands-on lab: Load nightly extracts safely
Project: Build a medicine-supply warehouse for rural clinics
A district health office wants to know which clinics are about to run out of essential medicines, how quickly suppliers deliver, and how dispensing changes over time. Design and build its warehouse in DuckDB (or PostgreSQL) from the extracts that make_clinic_data.py writes: clinics, medicines, suppliers, daily dispensing, weekly stock counts and purchase orders, plus a clinic change file dated 2025-04-01. Model three business processes with conformed clinic, medicine and date dimensions: dispensing as a transaction fact, stock on hand as a weekly periodic snapshot (a semi-additive measure), and purchase orders as an accumulating snapshot with ordered, shipped and received dates (a role-playing date dimension). Treat clinic_type as type 2 (Lowveld was upgraded to a health centre) and clinic_name and district as type 1 corrections. Load everything with scripts you can re-run from scratch, build at least one aggregate, prove every table reconciles with its source, answer the office's questions in SQL, and explain your design.
Part of
The products this course names are trademarks of their owners, who haven't made or endorsed it.