Course · Data platforms
Data modeling
Data modeling and warehousing: star schemas and grain, fact and dimension design, SCDs, Data Vault and other methods, dbt, semantic layers and incremental models.
- Lessons
- 11
- Interview questions
- 0
- Projects & case studies
- 9
- Reading time
- ~3 h
About this course
A warehouse is organised for questions, not for transactions. Data modelling decides how it answers them: measurements (facts) at a declared grain, descriptive context (dimensions) with keys that keep history, and definitions that stay consistent across teams.
Start with the star schema and the grain, then fact and dimension design, slowly changing dimensions and the harder patterns (bridges, hierarchies, late data). Finish with the methodologies (Kimball, Inmon, Data Vault, Anchor, medallion, Activity Schema) and modern practice with dbt, semantic layers and incremental models. Every lesson uses the same fictional online shop, Kestrel Market, with SQL verified on PostgreSQL.
Your progress
Saved in this browser onlyPractise
Course structure
Lessons
Work through the lessons in order. Completed lessons show a tick; lessons you have opened are outlined.
Start here
The complete overview of the course in one read.
Beginner
Core concepts you will use every day.
- Star, Snowflake and Galaxy Schemas and the GrainBuild a star schema for an online shop, declare the grain, snowflake a dimension, and query a galaxy of fact tables without double counting. Verified SQL.
- Normalization, Denormalization and Wide TablesNormalise a messy orders extract step by step to 1NF, 2NF, 3NF and BCNF, then learn when to denormalise into stars or One Big Table, with verified SQL.
- Data Lake vs Data Warehouse vs LakehouseCompare data lakes, data warehouses and lakehouses by storage, schema, transactions, cost and users, and learn which questions decide the right architecture.
Intermediate
Patterns used in production pipelines.
- Fact Tables: Measures, Snapshots and Late-Arriving FactsDesign fact tables that sum correctly: additive and semi-additive measures, transaction, periodic and accumulating snapshots, factless facts and late data.
- Dimension Tables: Keys, Conformed, Junk, Degenerate and Role-Playing DimensionsDesign dimension tables that join reliably: surrogate and natural keys, conformed, degenerate, junk and role-playing dimensions, and inferred members for late data.
- Slowly Changing Dimensions: Types 0 to 6Every SCD type from 0 to 6 with rerunnable PostgreSQL loads: overwrite with MERGE, Type 2 versioning, point-in-time joins, mini-dimensions and hybrid Type 6.
- Partitioning, Clustering and Data LayoutHow partitioning and clustering let warehouses and lakehouses skip data, how to choose keys, and why too many small partitions or files make queries slower.
Advanced
Performance, internals and edge cases.
- Bridge Tables, Many-to-Many Relationships and HierarchiesModel many-to-many relationships with weighted bridge tables, and fixed or ragged hierarchies with recursive SQL and closure tables, without double counting.
- Data Modeling Methodologies: Kimball, Inmon, Data Vault, Anchor and MedallionCompare Kimball, Inmon, Data Vault 2.0, Anchor modelling, medallion layers and Activity Schema with working tables for one shop, and learn when each fits.
- Modern Data Modeling: Streaming Models, Semantic Layers and dbtModel streaming data, define metrics once in a semantic layer, structure a dbt project and build incremental models that are safe to rerun, with verified SQL.
Projects and case studies
Apply what you learned and prepare material to discuss in interviews.
Projects
- BeginnerCSV to Data Warehouse PipelineA small online shop exports orders as daily CSV files. Build a pipeline that loads them into a star schema so that sales can be reported reliably, even when files are resent or contain bad rows.
- IntermediateE-commerce Analytics Data PlatformAn online store has orders, customers, products and web sessions in separate systems. Build an ELT platform that models them into trusted marts for revenue, retention and product performance.
System design case studies
- IntermediateDesign a Cloud Data Warehouse PlatformDesign a cloud data warehouse that consolidates data from SaaS tools and operational databases so a mid-sized company can run trusted reporting and self-service analytics.
- AdvancedDesign a Customer 360 PlatformA retailer holds customer data in a CRM, an e-commerce platform, a support desk, a loyalty app, marketing tools and web analytics, each with its own ids. Design a Customer 360 platform that resolves these into one customer, builds a trusted profile with history and consent, serves it to analysts and to real-time applications, and respects privacy law.
- AdvancedDesign a Marketing Attribution PipelineDesign a pipeline that credits conversions (sign-ups, purchases) to the marketing touchpoints that preceded them, joins that to ad spend from each advertising platform, and gives the marketing team daily return-on-spend by channel and campaign.
- AdvancedDesign a Metrics and KPI PlatformLeadership tracks about 80 company KPIs (revenue, active users, conversion, retention, delivery time) in dashboards, spreadsheets, board decks and product experiments, and the numbers rarely agree. Design a metrics platform where every KPI is defined once, computed consistently at any grain and dimension, versioned, monitored for anomalies, and served to every tool through one interface.
- AdvancedDesign a Near-Zero Downtime Data Platform MigrationDesign the migration of a live on-premises data warehouse, the ETL jobs that load it and the dashboards that read it to a cloud warehouse or lakehouse, so that consumers see no more than a few minutes of disruption and every number can be proved to match before the old system is switched off.
- AdvancedDesign an Analytics and BI PlatformDesign the analytics and BI platform for a mid-sized company where executives, finance, marketing and operations all need dashboards and self-service analysis, data comes from about 40 sources, and today different teams report different numbers for the same metric.
- AdvancedDesign a Time-Series Metrics StoreDesign a store for operational and business metrics (request latency, error counts, CPU, orders per minute) that ingests millions of samples per second from thousands of services, answers dashboard and alerting queries in under a second, and keeps a year of history affordably.
Resources
Related courses
- SQLSQL is the core language of data work: querying, transforming and modelling data in warehouses, lakehouses and Spark. Start here before any other tool.
- ETL and ELTETL transforms data before loading it; ELT loads first and transforms inside the warehouse or lakehouse. Learn when each fits.
- Delta LakeDelta Lake adds ACID transactions, schema enforcement and time travel to files in a data lake, which is the foundation of the lakehouse pattern.