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
- 13
- Reading time
- ~3 h
Your progress
Saved in this browser onlyCourse structure
- 1 lessonStart hereThe complete overview of the course in one read.
- 3 lessonsBeginnerCore concepts you will use every day.
- 4 lessonsIntermediatePatterns used in production pipelines.
- 3 lessonsAdvancedPerformance, internals and edge cases.
Practise
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
- AdvancedDesign a Cloud Data Warehouse for a SaaS ProductDesign a cloud data warehouse for a B2B SaaS company that consolidates its multi-tenant product database, product usage events, billing system and CRM, so the company can run trusted revenue and usage reporting (MRR, churn, activation) and offer usage analytics to its own customers without one customer ever seeing another's data.
- AdvancedDesign a Cost-Optimised Warehouse StrategyA company's cloud warehouse bill has tripled in a year to well over the budget, while data volume only doubled. Nobody can say which teams, pipelines or dashboards drive the spend. Design a strategy that makes cost visible and attributable, cuts waste without hurting SLAs, chooses the right pricing model, and keeps cost growing slower than usage from now on.
- 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 Data Mesh ArchitectureA large retailer's central data team of 25 engineers is a bottleneck for 15 business domains: requests wait months, the team does not understand every domain's data, and quality problems are found far from where they start. Leadership wants to move to a data mesh. Design the architecture and operating model: how domains own and publish data products, what the shared platform provides, how governance works across domains, and how to migrate without breaking existing reporting.
- 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 Self-Serve Analytics PlatformDesign a platform that lets 1,000 employees across product, marketing, finance and operations find trustworthy data, answer their own questions with SQL or a BI tool, and build their own dashboards and models, without a central data team writing every query, and without losing control of data quality, access to sensitive data or cost.
- AdvancedDesign a Slowly Changing Dimension FrameworkA warehouse has 60 dimensions (customers, products, stores, sales territories, employees) whose attributes change over time. Each team handles history differently, some overwrite values and lose history, others produce overlapping or duplicate versions. Design a reusable slowly changing dimension framework that applies the right history rules per attribute, loads changes idempotently from batch and CDC sources, handles late and out-of-order changes, and lets facts join to the version that was valid at the time.
- 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.