Data Warehouses Guide

TL;DR

• A data warehouse is a columnar, query-optimized store for historical analytics - not for transactional application writes.

• OLAP (Online Analytical Processing) enables fast aggregations across millions of rows - slice by region, time, product in sub-second queries.

• Star schema is the default modeling pattern - fact tables (metrics) surrounded by dimension tables (context).

• Warehouses are fed by ETL/ELT pipelines from operational systems, SaaS tools, and data lakes.

• Governed metrics in the warehouse are the source of truth for dashboards - not ad-hoc spreadsheet definitions.

Why This Matters

Your CEO asks: "What was Q2 revenue by region, broken down by product line, compared to last year?" Answering this from production PostgreSQL means expensive JOINs across tables designed for order inserts, not billion-row aggregations. Running it on raw S3 files requires writing Spark jobs.

A data warehouse answers this in seconds with a SQL query against pre-modeled tables. Finance, product, marketing, and operations teams depend on warehouse-backed dashboards - Snowflake, BigQuery, Redshift, Databricks SQL - for daily decisions.

If you build data pipelines, the warehouse is usually the destination your ETL serves. If you build analytics or AI features, the warehouse provides curated, governed features. Understanding warehouse architecture prevents the "three different revenue numbers" problem that destroys organizational trust.

The Problem Warehouses Solve

Operational databases (OLTP) optimize for row-level reads and writes. Analytics needs the opposite:

Without a warehouse, every team queries production databases (risking outages), exports CSVs (stale immediately), or builds shadow metrics in spreadsheets (inconsistent definitions).

Warehouses provide one governed place where "revenue" means the same thing everywhere.

What Is a Data Warehouse?

A data warehouse is a centralized repository that stores integrated data from multiple sources, optimized for analytical queries and reporting. Key characteristics:

• Subject-oriented - Organized by business concepts (customers, orders, revenue), not application tables.

• Integrated - Data from CRM, billing, and product DBs unified with consistent definitions.

• Time-variant - Historical snapshots; SCD tracks how dimensions change.

• Non-volatile - Append and update via controlled ETL; not a live application backend.

Modern cloud warehouses add elastic compute, separation of storage and compute, and near-zero administration compared to on-premise appliances (Teradata, Netezza).

GraphRAG builds a knowledge graph from source documents, clusters entities into communities, and retrieves graph-aware context for complex multi-hop questions.

OLAP Explained

OLAP (Online Analytical Processing) describes workloads that aggregate large datasets across multiple dimensions:

-- Classic OLAP query: slice and dice
SELECT
    d_region.region_name,
    d_product.category,
    SUM(f_revenue.amount) AS total_revenue,
    COUNT(DISTINCT f_revenue.customer_key) AS unique_customers
FROM fact_revenue f_revenue
JOIN dim_date d_date ON f_revenue.date_key = d_date.date_key
JOIN dim_region d_region ON f_revenue.region_key = d_region.region_key
JOIN dim_product d_product ON f_revenue.product_key = d_product.product_key
WHERE d_date.fiscal_quarter = '2026-Q2'
GROUP BY 1, 2
ORDER BY total_revenue DESC;

OLAP operations:

Columnar storage makes OLAP fast - the engine reads only amount and date_key columns, not entire rows.

Star Schema and Dimensional Modeling

Star schema is the default warehouse design - a central fact table connected to dimension tables like a star.

The Kimball star schema places a central fact table at the center, surrounded by denormalized dimension tables for time, customers, products, and regions. This layout optimizes analytical queries with simple joins.

Fact Tables

Store measurable events - one row per business event at a defined grain:

Grain is sacred. If grain is order-line, never store order-total metrics without careful semi-additive handling.

Dimension Tables

Provide context for slicing facts - wide, denormalized, human-readable:

Star vs Snowflake

Default to star unless storage or slowly-changing category hierarchies force snowflaking.

Tip

Pre-join dimension attributes analysts filter on. A dim_product with category and subcategory columns beats a 4-table snowflake for dashboard performance.

How Data Gets In

Warehouses are populated by ETL or ELT pipelines:

1. Extract - Pull from sources (databases, APIs, data lake files).

2. Stage - Land in staging schema, raw or lightly cleaned.

3. Transform - dbt models build dimensions and facts with business logic.

4. Load - Merge into production tables (incremental or full refresh).

5. Test - Uniqueness, referential integrity, metric reconciliation.

Modern warehouse pipelines use ELT: raw data lands in staging tables, dbt transforms it into dimension and fact models, tests run in-warehouse, and analytics marts serve BI tools.

Source: dbt Labs

Modern pattern: ELT with dbt - load raw to staging, transform in warehouse SQL, version control models in Git.

Real Production Example

A subscription SaaS company models MRR (monthly recurring revenue) in Snowflake:

-- dim_customer with SCD Type 2 for segment changes
CREATE TABLE analytics.dim_customer AS
SELECT
    customer_id,
    customer_name,
    segment,
    country,
    valid_from,
    valid_to,
    is_current
FROM staging.customer_snapshots;

-- fact_subscriptions - grain: one row per customer per month
CREATE TABLE analytics.fact_subscriptions AS
SELECT
    d.date_key,
    c.customer_key,
    p.product_key,
    s.mrr_amount,
    s.is_active,
    s.is_churned
FROM staging.subscription_monthly s
JOIN analytics.dim_date d ON s.month_start = d.full_date
JOIN analytics.dim_customer c
    ON s.customer_id = c.customer_id
    AND s.month_start >= c.valid_from
    AND s.month_start < COALESCE(c.valid_to, '9999-12-31')
JOIN analytics.dim_product p ON s.product_id = p.product_id;

Finance dashboard queries fact_subscriptions with dim_date and dim_customer - MRR definition lives in one dbt model, not twelve spreadsheets.

Design Decisions

⚠ Common Mistakes

1. Wrong grain on fact tables. Mixing order-level and line-level metrics produces double-counting.

2. Querying staging tables in dashboards. Staging is dirty; production marts only.

3. No surrogate keys. Using source IDs directly breaks when source systems merge records. Generate customer_key independent of CRM ID.

4. Ignoring slowly changing dimensions. Customer segment changes; overwriting loses history finance needs.

5. Fan-out joins. Joining facts to dimensions at wrong grain multiplies metrics. One $100 order becomes $500 after five promo code rows.

6. Three definitions of revenue. Document metric logic in dbt descriptions and enforce with tests.

Where It Breaks Down

Unstructured data. Warehouses excel at structured analytics. Text, images, and raw JSON blobs belong in data lakes - warehouse stores extracted features or aggregates.

Real-time dashboards. Warehouse queries take seconds to minutes. Sub-second operational dashboards need streaming layers or pre-computed caches.

Cost at scale. Full table scans on petabyte facts are expensive. Partition pruning, clustering, and pre-aggregation manage cost.

Schema rigidity for exploration. New data sources wait for modeling. Lake-first exploration, warehouse for governed metrics.

Production Checklist

Important

One definition per metric. "Revenue" documented in dbt with tested logic - not redefined in every Looker explore.

Ecosystem

• Cloud warehouses: Snowflake, Google BigQuery, Amazon Redshift, Azure Synapse, Databricks SQL.

• Transformation: dbt, SQLMesh, stored procedures.

• Ingestion: Fivetran, Airbyte, ETL pipelines from data lakes.

• BI tools: Looker, Tableau, Metabase, Power BI, Mode.

• Reverse ETL: Census, Hightouch - sync warehouse data back to SaaS tools.

• Quality: dbt tests, Monte Carlo, Elementary.

Related Technologies

• ETL: Pipelines that populate warehouse staging and marts.

• Data Lakes: Raw storage layer; warehouse consumes curated lake data.

• Lakehouse: Unified architecture blending lake and warehouse capabilities.

Learning Path

Prerequisites: ETL

Next topics: Data Lakes · Lakehouse

Estimated time: 50 min · Difficulty: Intermediate

FAQs

What is the difference between a database and a data warehouse?

A database (OLTP) handles application transactions - fast row lookups and writes. A warehouse (OLAP) handles analytical queries - aggregations over large historical datasets with columnar storage.

What is a star schema?

A dimensional model with a central fact table containing metrics, surrounded by dimension tables containing descriptive attributes. Named for the star shape in ER diagrams.

When should I use a warehouse vs a data lake?

Warehouse for governed BI metrics and SQL analytics. Lake for raw, diverse, large-scale data and ML. Most organizations use both - lake for raw, warehouse for curated.

What is OLAP vs OLTP?

OLTP (Online Transaction Processing) is operational - orders, logins, payments. OLAP (Online Analytical Processing) is analytical - revenue trends, cohort analysis, aggregations.

How does dbt fit with warehouses?

dbt transforms data inside the warehouse using SQL models - version-controlled, tested, documented. It is the standard transformation layer for modern ELT.

What is a fact table grain?

The level of detail each row represents - one row per order line, per day per customer, per session. All metrics on a fact table must be valid at that grain.

References

• Hugging Face Transformers Documentation
• LangChain Documentation
• OpenAI API Documentation

Further Reading

• Anthropic Documentation
• Google AI for Developers
• LlamaIndex Documentation
• Pinecone Learning Center

Summary

• Data warehouses optimize for OLAP - fast aggregations over historical structured data.
• Star schema with fact and dimension tables is the default modeling approach.
• Grain definition on fact tables is the most critical modeling decision.
• Warehouses are fed by ETL/ELT; dbt is the standard transformation tool.
• Governed metrics in the warehouse eliminate conflicting dashboard definitions.
• Lakes store raw; warehouses serve curated analytics - most platforms use both.