CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 4 of 5 · 2 min read

The data warehouse, kept separate

A data warehouse holds a copy of the operational data, built specifically for analytic queries, so they never touch the live transactional system.

The data warehouse, kept separate

Think of that auditor again, now given their own full photocopy of every receipt. That copy is kept in a separate back room. The auditor can flip through it as slowly as needed. No customer at the counter is ever slowed down.

In plain words: a data warehouse is a separate database holding a copy of an organization's operational data, built and structured specifically for analytic queries. Data is moved from the operational system into the warehouse through a process called extract, transform, load, ETL for short. Extract pulls data out of the operational system. Transform cleans it up and reshapes it into a schema suited for analysis. Load writes it into the warehouse. Because the warehouse is a separate copy, an analyst can run however large a scan is needed there without any risk of slowing down the live operational system.

A worked example. Every night, the bookstore's ETL process extracts the day's new orders from the operational database, transforms them into the warehouse's analysis-friendly schema, and loads them into the warehouse. The next morning, an analyst's three-year revenue query runs entirely against the warehouse, never touching the operational database customers are checking out against.

The key lines. No new SQL; ETL is a data-movement process run on a schedule, not a single query.

Data warehouse, ETL

A data warehouse is a separate database holding a copy of an organization's operational data, structured specifically for analytic queries, so those queries never compete with operational transactions for resources. ETL, extract, transform, load, is the process that moves data from an operational system into a data warehouse: extracting it, transforming it into the warehouse's schema, and loading it in.

Example: the bookstore's nightly process pulling new orders out of the operational database, reshaping them, and loading them into a separate warehouse is ETL feeding a data warehouse.

Remember this

OLTP touches a few rows by key, fast, reflecting the current state. OLAP scans huge numbers of rows to compute an aggregate, typically over history. Running both on one system causes resource contention, which is why a separate data warehouse, populated through ETL, exists to keep analytic scans away from operational transactions. The next page covers how that warehouse's own schema is typically shaped.

Quick notes

  • OLTP looks up a small number of records by key and performs small writes from user input, reflecting the current state of data.
  • OLAP scans a large number of records to compute an aggregate, typically over data accumulated across time.
  • Running both workloads on one database causes resource contention: a long analytic scan competes with operational transactions for memory, I/O, and locks.
  • A data warehouse is a separate database holding a copy of operational data, built specifically for analytic queries.
  • ETL, extract, transform, load, is the process moving data from an operational system into a data warehouse.