Notesstorage🌱 seedling

Data Warehouse

Also: Warehouse · Analytical Database · ETL · ELT · Extract-Transform-Load · Cloud Data Warehouse

A separate database holding read-mostly, historical copies of data from many operational systems, organised for analysis. Data arrives by ETL (transform, then load) or ELT (load raw, transform inside the warehouse).

Why it exists

Each OLTP system belongs to one team and is tuned for its own transactions. Analysts need all of that data in one place, in a shape that's easy to query, without slowing down production.

How it works

  • Extract from sources: periodic dumps, or a continuous stream via Change Data Capture.
  • Transform into an analysis-friendly schema, usually a Star Schema: clean, deduplicate, join, conform keys.
  • Load into storage laid out for scans: Column-Oriented Storage with Columnar Compression.
  • ELT flips the last two steps: cheap cloud compute makes it practical to load raw data and transform it with SQL in place.
  • Separated storage and compute: modern warehouses keep tables as immutable files in object storage and run queries on independent compute clusters that only cache data locally.

Trade-offs

Gains Costs
analytics can't hurt production data is a copy, minutes to a day stale
one place to join data across systems ETL pipelines break on upstream schema changes
storage built for scans proprietary formats lock data in (one driver of the Data Lakehouse)

In practice

  • Snowflake (generally available June 2015) stores tables in cloud object storage (originally S3) and runs "virtual warehouses" that scale independently of storage.
  • BigQuery runs on Dremel, which in 2020 described its core ideas as disaggregated storage and compute, in-situ analysis and columnar storage.
  • Amazon's 2024 Redshift study: 98% of tables have under a billion rows, and in half of clusters 80% of queries repeat exactly.

Pitfalls

  • Treating the warehouse as a system of record: it's derived data, rebuildable from the sources.
  • Skipping data contracts: an upstream column rename silently breaks every downstream report.

Recall

These cards are also in your review deck, scheduled with spaced repetition.