Building a Data Warehouse: A Step-by-Step Guide

A data warehouse can sound like a large programme, but its core is simple: raw data in, a few well-designed tables out, and tests that prove the tables are right. Building a small one end to end is the best way to learn the pattern. This guide builds a working star schema from a month of New York taxi trips in DuckDB, a free, embedded analytical database, so every step runs on a laptop. The concepts behind it are covered in data warehouses explained.

Step 1: design before you load

Dimensional design starts with four decisions, in order: select the business process, declare the grain, identify the dimensions, identify the facts (Kimball Group: four-step process). For this warehouse:

1. Business process: taxi trips.
2. Grain: one row per completed trip. Kimball calls declaring the grain the pivotal step, because it fixes exactly what a fact row represents (Kimball Group: grain).
3. Dimensions: date, zone and payment type. Zone plays two roles, pickup and drop-off, from one table.
4. Facts: distance, fare, tip, total and trip minutes.

The source is January 2024 yellow taxi data (NYC TLC: trip record data), with codes and field meanings from the TLC's data dictionary (TLC: data dictionary).

Steps 2 to 5: stage, model, test, serve

DuckDB reads Parquet directly and can create tables from it (DuckDB docs: Parquet), so the whole build is a few SQL statements run from Python:

import urllib.request
from pathlib import Path
import duckdb

base = "https://d37ci6vzurychx.cloudfront.net"
for name, url in {"yellow_tripdata_2024-01.parquet": f"{base}/trip-data/yellow_tripdata_2024-01.parquet",
                  "taxi_zone_lookup.csv": f"{base}/misc/taxi_zone_lookup.csv"}.items():
    if not Path(name).exists():
        urllib.request.urlretrieve(url, name)

con = duckdb.connect("warehouse.duckdb")
con.execute("""
-- 1. staging: the sources, loaded as they are
CREATE OR REPLACE TABLE stg_trips AS SELECT * FROM 'yellow_tripdata_2024-01.parquet';
CREATE OR REPLACE TABLE stg_zones AS SELECT * FROM read_csv('taxi_zone_lookup.csv', header = true);

-- 2. dimensions: descriptive context
CREATE OR REPLACE TABLE dim_zone AS
SELECT LocationID AS zone_key, Zone AS zone, Borough AS borough, service_zone FROM stg_zones;
CREATE OR REPLACE TABLE dim_payment AS
SELECT * FROM (VALUES (0, 'Flex fare'), (1, 'Credit card'), (2, 'Cash'), (3, 'No charge'),
                      (4, 'Dispute'), (5, 'Unknown'), (6, 'Voided trip')) AS t(payment_key, payment_type);
CREATE OR REPLACE TABLE dim_date AS
SELECT CAST(d AS DATE) AS date_key, dayname(d) AS weekday, isodow(d) >= 6 AS is_weekend
FROM range(DATE '2024-01-01', DATE '2024-02-01', INTERVAL 1 DAY) AS r(d);

-- 3. fact: one row per trip, keys plus measures, rules applied
CREATE OR REPLACE TABLE fact_trip AS
SELECT CAST(tpep_pickup_datetime AS DATE) AS date_key, PULocationID AS pickup_zone_key, DOLocationID AS dropoff_zone_key,
       payment_type AS payment_key, trip_distance, fare_amount, tip_amount, total_amount,
       date_diff('second', tpep_pickup_datetime, tpep_dropoff_datetime) / 60.0 AS minutes
FROM stg_trips
WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01'
  AND tpep_dropoff_datetime >= tpep_pickup_datetime AND fare_amount >= 0;
""")

# 4. tests: row counts and orphan keys
for table in ["stg_trips", "fact_trip", "dim_date", "dim_zone", "dim_payment"]:
    print(f"{table:<12} {con.sql(f'SELECT COUNT(*) FROM {table}').fetchone()[0]:>8} rows")
orphans = con.sql("""SELECT
    (SELECT COUNT(*) FROM fact_trip f ANTI JOIN dim_zone z ON f.pickup_zone_key = z.zone_key),
    (SELECT COUNT(*) FROM fact_trip f ANTI JOIN dim_zone z ON f.dropoff_zone_key = z.zone_key),
    (SELECT COUNT(*) FROM fact_trip f ANTI JOIN dim_payment p ON f.payment_key = p.payment_key),
    (SELECT COUNT(*) FROM fact_trip f ANTI JOIN dim_date d ON f.date_key = d.date_key)""").fetchone()
print("fact rows with no matching dimension row:", orphans)

# 5. serve: an analytical question in one pass over the star
print(con.sql("""
SELECT z.borough, d.is_weekend AS weekend, COUNT(*) AS trips, ROUND(AVG(f.fare_amount), 2) AS avg_fare,
       ROUND(AVG(f.minutes), 1) AS avg_minutes
FROM fact_trip f
JOIN dim_zone z ON f.pickup_zone_key = z.zone_key
JOIN dim_date d ON f.date_key = d.date_key
WHERE z.borough IN ('Manhattan', 'Queens', 'Brooklyn')
GROUP BY ALL ORDER BY z.borough, weekend""").fetchdf().to_string(index=False))
con.close()

stg_trips     2964624 rows
fact_trip     2927103 rows
dim_date           31 rows
dim_zone          265 rows
dim_payment         7 rows
fact rows with no matching dimension row: (0, 0, 0, 0)
  borough  weekend   trips  avg_fare  avg_minutes
 Brooklyn    False   19372     30.08         33.6
 Brooklyn     True    5555     26.25         21.6
Manhattan    False 1940995     15.18         14.0
Manhattan     True  675420     14.69         13.1
   Queens    False  202067     51.97         33.4
   Queens     True   64696     51.95         29.0

What each step does

Staging keeps the raw data untouched. stg_trips holds all 2,964,624 source rows exactly as delivered. Transformations read from staging, so a bug in a rule is fixed by rerunning the SQL, never by re-downloading.

Dimensions give context. dim_zone comes from the TLC's zone lookup; dim_payment turns numeric codes into labels from the data dictionary, so no report ever has to remember that 2 means cash; dim_date is generated, one row per day, with attributes such as weekday. In a larger warehouse the date dimension carries holidays, fiscal periods and anything else people filter by.

The fact table holds keys and measures at the declared grain. It keeps 2,927,103 trips: the rules remove pickups outside January, trips that end before they start and negative fares. Measures such as minutes are computed once here, so every report uses the same definition.

Tests prove the joins are safe. An orphan, a fact row whose key has no dimension row, silently disappears from any report that joins to that dimension, so totals shrink with no error anywhere. The row counts matter too: comparing staging with the fact table shows exactly how many rows the rules removed, a number worth tracking over time. All four checks return zero, including the role-playing drop-off zone. In production these checks run after every load and fail the pipeline if they do not pass.

The star answers questions in one pass. The query joins the fact to two dimensions and reads naturally. Its answers are plausible, which is itself a check: Queens pickups, dominated by the airports, average about 52 dollars, against about 15 in Manhattan.

Mistakes first warehouses make

• Mixing grains in one fact table, such as trip rows alongside daily totals, so sums double-count. One fact table, one grain.
• Designing without the people who will use it. Dimensions should carry the attributes people actually filter and group by, which only they can tell you.
• Letting reports read staging tables, which bypasses every rule and test in the model.
• Skipping tests because the numbers look right today. Orphan keys, duplicate rows and missing days appear later, when a source changes.
• Loading everything, answering nothing. Start from a handful of real questions and build the tables that answer them; extend from there.

Choosing where it runs

The same design works on very different platforms. A local engine such as DuckDB suits learning and small data; cloud warehouses such as Snowflake, BigQuery and Redshift, and lakehouse platforms that query open files, suit shared, growing data. Data lake vs data warehouse vs lakehouse compares the architectures.

What production adds

This build reloads everything each time. A production warehouse adds:

• Incremental loads instead of full rebuilds; see incremental data loads.
• Surrogate keys and history in dimensions, so a zone renamed next year does not rewrite last year's reports; see slowly changing dimensions.
• More design depth, from snapshot fact tables to junk dimensions; see designing fact and dimension tables and data modelling.
• Orchestration that runs loads on a schedule, in order, with alerts.
• A semantic layer or BI model on top, so business users see friendly names and shared measures.

The design steps, grain first, then dimensions and facts, then tests, stay the same whether the warehouse runs in DuckDB, Snowflake, BigQuery or Fabric.

Data warehouse concepts, star and snowflake schemas and data modelling techniques are part of the data warehousing module, and a cloud-based data warehouse is one of the projects, in the Data Engineering course.

Sources

• Four-step dimensional design process (Kimball Group) — Kimball Group
• Grain (Kimball Group) — Kimball Group
• Reading and writing Parquet files (DuckDB documentation) — DuckDB
• Data dictionary: yellow taxi trip records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission