What is a Data Warehouse?

How it works

An operational (OLTP) database is designed to insert one order quickly and safely. A warehouse is designed to answer “revenue by month, category and customer segment for three years” quickly and consistently. Its value comes from modelling, reliable loading and agreed definitions more than from any particular product.

Data flows through it in layers:

1. Sources: the shop database, payments, CRM, web events.
2. Ingestion in batch or through change data capture into a raw layer, kept unchanged so models can be rebuilt.
3. Transformation into cleaned staging tables and then marts: fact tables of business events and dimension tables of descriptive context, usually arranged as star schemas. In ELT this runs inside the warehouse in SQL, often with dbt.
4. Serving: dashboards, ad hoc SQL, a semantic layer of certified metrics, reverse ETL and ML features.

Cloud warehouses such as Snowflake, BigQuery and Redshift use columnar storage, cost-based optimisers and caching, and separate storage from compute so each scales on its own. They enforce schemas and provide transactions, which makes them easy for analysts and BI tools. The trade-offs are an extra copy of the data, a mostly proprietary storage format, and a weaker fit for raw files such as images or heavy Python workloads. A lakehouse brings many warehouse guarantees to open files in object storage instead.

Example

The typical warehouse query: one fact table joined to its dimensions, filtered, grouped and aggregated.

CREATE TABLE dim_customer (customer_key int PRIMARY KEY, segment text);
CREATE TABLE dim_date (date_key int PRIMARY KEY, month text);
CREATE TABLE fact_sales (date_key int, customer_key int, amount numeric(10,2));

INSERT INTO dim_customer VALUES (1, 'Consumer'), (2, 'Business');
INSERT INTO dim_date VALUES (20260301, '2026-03'), (20260402, '2026-04');
INSERT INTO fact_sales VALUES
  (20260301, 1, 120.00), (20260301, 2, 240.00), (20260402, 1, 45.00);

SELECT d.month, c.segment, sum(f.amount) AS revenue
FROM fact_sales f
JOIN dim_date d     ON d.date_key = f.date_key
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY d.month, c.segment
ORDER BY d.month, c.segment;

  month  | segment  | revenue
---------+----------+---------
 2026-03 | Business |  240.00
 2026-03 | Consumer |  120.00
 2026-04 | Consumer |   45.00

Common mistakes

• Copying operational tables into the warehouse unchanged and calling it modelled; analysts then rebuild the same joins in every report.
• Running analytical queries against the production OLTP database instead of a warehouse or replica.
• Letting each team define “revenue” differently, which defeats the point of a single warehouse.
• Skipping reconciliation checks between source and warehouse totals.