Data Warehousing
Beginner 12 min read Data Warehousing

Warehouse Architecture

Where cleaned, queriable truth lives

A data warehouse separates storage from compute, organizes data by subject, and gives business analysts an SQL surface over clean, versioned, governed data.

warehouseETL vs ELTSQLgovernanceseparation of storage and compute
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Describe the warehouse's role between sources and dashboards
  • Contrast warehouse, database, and data lake responsibilities
  • Explain why storage/compute separation changed the economics
Introduction

What is this?

A warehouse is a managed, SQL-queryable store for large amounts of cleaned, aligned, versioned data. It separates storage from compute so you can scale queries independently of data size, and gives analysts one governed surface instead of many bespoke databases.

Motivation

Why do you need it?

  • Business decisions read from one consistent picture, not five divergent dashboards.
  • Clean, modeled data drops the time from 'ingest' to 'report that can be trusted'.
  • The architecture drives the rest of this module: dimensional modeling, SCDs, and incremental loads.
Think about it

Mental model

Imagine

A city water plant

Think of

A data warehouse

Reservoirs (sources) feed a treatment process (modeling); clean water is piped to every faucet (dashboard/BI). Everyone drinks from the same treated system rather than bottling swamp water per household.

Mechanics

How it works

  • Sources land raw data via ELT pipelines into staging or raw layers (often a lake or stage tables).
  • Modeling transforms stage data into conformed facts and dimensions that a BI tool can join.
  • Storage and compute are decoupled: you pay/scale for queries without copying the dataset.
  • Time travel and versioning let analysts re-run historical queries without a restore.
  • Access control, lineage, and freshness SLAs wrap the surface that analysts actually see.

Layers of a modern warehouse architecture.

Sources
Landing / staged
Modeled core
Semantic layer
Dashboards & BI
Hands-on

Examples

The seam the warehouse owns

The seam the warehouse owns
-- Analysts see THIS (modeled, governed):
SELECT d.region, f.amount FROM fact_sales f
JOIN dim_customer d ON f.customer_key = d.customer_key
WHERE f.sold_date >= '2026-01-01';
 
-- Engineers own the pipeline that created fact_sales:
INSERT INTO fact_sales
SELECT ... FROM raw_orders o JOIN dim_customer d ON o.cust_id = d.source_id;

The warehouse is the contract: analysts query modeled tables; engineers own how those were built and kept fresh.

Real world

In production

  • A finance org runs all P&L from a single fact table, so the CFO sees the same numbers as the pivot analysis.
  • Product analytics joins event and subscription dimensions in one SQL surface instead of merging CSVs.
  • A data team adds compute capacity for a dashboard spike without duplicating data or re-ingesting.
Guidelines

Best practices

  • Layering raw → staging → modeled keeps reversible, auditable transformations.
  • Model to subject, not to dashboard: one conformed schema serves many reports.
  • Encode freshness and quality expectations into the warehouse's SLOs.
Watch out

Common mistakes

  • Modeling directly for one expensive dashboard and hardening the wrong grain.
  • Treating the warehouse as a cache for source tables instead of a modeled layer.
  • Ignoring governance until two teams embed conflicting definitions.
When things break

Debugging tips

  • When dashboards disagree, check whether they read from raw vs modeled layers.
  • A missing day after a nightly load is almost always a pipeline watermark or SLA, not data loss.
  • EXPLAIN on a big join tells you whether the query or the model is the cost.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

The warehouse's core product is…

2

Storage/compute separation means scaling query capacity can be done without copying data.

Go deeper

Official documentation

Kimball dimensional modeling overview
docs
Snowflake architecture docs
docs
BigQuery architecture
docs