Data Warehousing and Dimensional Modeling

How analytics data is stored and shaped: OLTP and OLAP workloads, ETL and ELT, facts, dimensions and grain, star schemas, slowly changing dimensions, how cloud warehouses scale, and loading data incrementally when some of it arrives late.

Overview

A data warehouse is a database built for analysis. It collects data from the systems that run the business (orders, payments, sign-ups, support tickets) and keeps it in a shape that makes questions like 'revenue by region by month' quick and easy to ask.

The production database is the wrong place for those questions. It is tuned for many small reads and writes by key, and one large report would compete with checkout for disk and CPU. It also keeps only the current state: when a customer moves, the old city is gone. A warehouse keeps history, joins data from many sources, and stores it so that scanning billions of rows is cheap.

Two skills make up this topic. Dimensional modeling is how you design the tables: events go into fact tables, the context about them goes into dimension tables, and history is kept on purpose. Warehouse engineering is how the data gets in and how the engine stays fast: ETL or ELT pipelines, columnar storage, partitioning and clustering, and incremental loads.

What you will be able to do

  • Explain why analytics runs in a separate system, and choose between ETL and ELT.
  • Design a star schema with the Kimball four-step process, starting from the grain.
  • Keep dimension history with slowly changing dimension types 1, 2 and 3, and write the type 2 update in SQL.
  • Join each fact to the dimension version that was true when the event happened, including late-arriving facts.
  • Describe how Snowflake, BigQuery and Redshift scale, using what their docs actually say.

Key concepts

  • OLTP and OLAP
  • ETL and ELT
  • Grain
  • Facts and dimensions
  • Star schema
  • Conformed dimensions
  • Surrogate keys
  • Slowly changing dimensions
  • Columnar storage
  • Incremental loads

Interview questions

  • Design a dimensional model for an online store's orders.
  • A customer moves from Austin to Denver. How do you keep last year's reports correct?
  • Star schema or snowflake schema?
  • Why do warehouses use surrogate keys instead of the source system's IDs?
  • ETL or ELT: which would you choose, and why?
  • How do you handle facts and dimensions that arrive late?
  • A 10 TB fact table is slow to query. What do you check?
  • How do incremental models work, and what goes wrong with them?
  • What is a conformed dimension, and why does it matter?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio