The Data Warehouse

A data warehouse is a storage layer built for 1 kind of read: scan many rows, a few columns at a time, and aggregate them. It is good at that because of 3 properties, and you can identify a warehouse by them: a columnar layout, a schema the system enforces, and storage kept separate from compute.

Columnar layout

Take the orders table with its 6 columns. A row layout writes order 8841201 in full, then 8841202 in full, and so on down the disk. A columnar layout writes every order_id, then every customer_id, then every city, each column in its own run of bytes.

Now run the morning dashboard's query: revenue by city for the last 30 days.

SELECT
  city,
  SUM(total_cents) AS revenue_cents
FROM orders
WHERE placed_at >= TIMESTAMP '2026-09-22 00:00:00'
GROUP BY city

The query names 3 columns, city along with total_cents and placed_at. In the row layout the disk reads every byte of all 6 columns, because the 3 the query wants are interleaved with the 3 it does not. In the columnar layout the disk reads the 3 column runs and skips the other 3, about half the bytes. A real orders table at a mature product carries 40 to 60 columns while a dashboard query reads 3 or 4, so the saving grows with the width of the table. Over 60 million orders, that is the difference between a query that returns in seconds and one that returns in minutes.

Enforced schema

A warehouse table declares its columns and their types before a single row arrives, and it rejects any row that breaks the declaration.

CREATE TABLE orders (
  order_id BIGINT,
  customer_id BIGINT,
  city VARCHAR,
  total_cents BIGINT,
  status VARCHAR,
  placed_at TIMESTAMP
)

With total_cents declared as BIGINT, a load that tries to insert the string 'twenty-four ninety-nine' fails at the table's edge and the analyst's SUM never meets a string. Dozens of dashboards and hundreds of saved queries lean on those types, and 1 wrong row would break all of them at once. The price is that changing a column's shape takes a deliberate step, which a warehouse team pays so every reader can trust every type.

Storage separated from compute

In older warehouse designs 1 machine, or 1 fixed cluster, held both the disks and the processors. When finance ran month-end at the same hour marketing analysed a campaign, both fought for the same processors and both slowed down, and adding capacity meant buying a bigger machine for everyone. A modern warehouse keeps table data in a storage service and runs queries on compute clusters that attach to that storage on demand. Finance gets a cluster, marketing gets another, both read the same tables with no copy, and a cluster sitting idle is switched off and billed nothing while the tables stay put.

Workloads every warehouse carries

• Daily revenue dashboard: Runs every morning after the overnight load and reads 3 or 4 columns.
• Month-end finance close: Needs every amount typed as a number and every date typed as a date, because the result goes to the auditors.
• Experiment analysis: Groups weeks of orders by the variant each customer saw, a read that touches 2 or 3 columns across tens of millions of rows.

The warehouse's columnar layout speeds up some reads far more than others. Which of these queries gains the least from it?

• SUM(total_cents) grouped by status over every order placed in the last year
• COUNT(*) of orders per city per day for all of 2025
• AVG(total_cents) for every order delivered in Denver
• SELECT * for order 8841207, found by its order_id

About this lesson

This section is part of the Storage Layers: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.