A data warehouse is a database optimised for analytical queries - scans, aggregates, and joins over history - rather than for point reads and writes. Where an operational database answers “what is the state of order 42”, a warehouse answers “how did orders trend across every region for three years”. The distinction drives everything: storage layout, query engine, pricing model, and the modelling discipline on top.
Warehouse versus lake versus lakehouse
- Warehouse (Snowflake, BigQuery, Redshift, Synapse): managed, columnar, ACID tables with the storage opaque to you - you load data and query it; the platform owns the files.
- Data lake: object storage holding files in whatever format you wrote them - cheap, flexible, and without a table contract.
- Lakehouse: the convergence - open table formats (Iceberg, Delta, Hudi) on object storage giving warehouse properties (atomic commits, schema evolution, time travel) to files you own, plus query engines on top. The boundary is genuinely blurry now: Snowflake reads and writes Iceberg; Databricks added warehouse-class SQL to the lake.
The practical difference that survives the convergence: who owns the storage and the metadata. Warehouse-native storage is simpler and faster inside that platform; open-table storage is readable by engines you have not chosen yet.
What makes a warehouse fast
- Columnar storage. Columns stored together so a query reading three of two hundred columns reads only those - the same property Parquet gives files, applied to managed storage.
- MPP (massively parallel processing). The query is compiled into a plan whose stages run across many nodes; each node processes a slice and the result is assembled at the end. Scale-out rather than scale-up is the architecture.
- Result and metadata caching. Repeat queries hit caches; micro-partition/zone-map pruning skips data that cannot match. A “slow” warehouse is often a modelling or clustering problem, not an engine problem.
- Separation of storage and compute. The modern warehouses price and scale compute (the virtual warehouse / slot / node) independently of stored data - the property that made them workable for variable analytical load.
Dimensional modelling: the discipline on top
The warehouse’s physical strengths only pay off with a schema shaped for analytics:
- Facts and dimensions. A fact is a measurable event (orders, clicks, payments); a dimension is the context around it (customer, product, date, region). A star schema puts a fact table in the centre with dimensions radiating out - the shape the query engines are tuned for.
- Slowly changing dimensions. When a dimension attribute changes (a customer moves region, a product is recategorised), decide what history means: SCD type 1 overwrites (the report shows the world as it is now), SCD type 2 inserts a new row with validity windows (the report can answer “as it was then”). SCD2 is what dbt snapshots implement; choosing 1 or 2 is a modelling decision about history, not a technical detail.
- One-big-table and wide tables. The alternative pattern - pre-join everything into a denormalised table - trades storage and staleness for query simplicity. It wins when the consumer is BI tooling that cannot join well and loses when the underlying data changes shape.
Where the warehouse sits
In an ELT stack the warehouse is the destination and the compute engine: raw data lands from pipelines, dbt or its equivalents transform in place, and BI and downstream consumers query the results. The warehouse’s cost model - paying for compute per second or per query - is what makes “run it less often” and “cluster the table better” real financial decisions, not just performance tuning.
Failure modes and diagnostics
- Full scans on unclustered data. A query that reads everything because the table is not partitioned or clustered for its access pattern - the bill and the latency grow together.
- Copy-paste SQL drift. The same metric computed five different ways in five models - the warehouse surfaces it as dashboards that disagree.
- SCD2 gaps and overlaps. Snapshot logic that produces overlapping validity windows or open-ended rows that were never closed - the audit queries find them, but only if someone writes the audit.
- Compute that outruns the workload. An always-on warehouse sized for the peak paying for the trough - the separation of storage and compute exists so this can be fixed by schedule, not by migration.
What this is not for
A warehouse is not an operational database: no sub-millisecond point reads, no OLTP write pattern, no role serving an application’s hot path. It is not a streaming engine - it reads micro-batches well but is not built for per-event latency. And it is not where raw data should live unprocessed: “the warehouse” implies the modelled, quality-checked layer; the raw zone belongs upstream of it.