The Data Warehouse
An analytical database built for large scans and aggregations over structured, modelled data — and what it gives you that a pile of files cannot.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What does an analytical database do that a query engine over files does not, and what are you paying for it in flexibility?
Analysts and BI tools running many concurrent, ad-hoc, aggregate-heavy queries over modelled tables, expecting consistent results, predictable response, and permissions that stop at the column they are not allowed to see. They do not want to think about files, formats, partitions or compaction, and a warehouse's job is to make that reasonable (Who Actually Consumes This Data).
One row of a modelled table, at a grain the model declares — one order, one order line, one customer-day. The warehouse enforces the *type* of every column and nothing about the grain, which is why a table with a perfectly enforced schema can still produce a wrong metric (Grain: What Does One Row Represent?).
Keep the analytical data in the same relational database the application uses, add a few reporting tables and some indexes. This works for a long time and deserves respect: one system, one backup, one set of permissions, no copies to reconcile, and the freshest data available anywhere (Workload Isolation).
A query aggregating two years of orders reads far more of the table than any index can help with, because the predicate is not selective — it wants *most* of the rows. Row-oriented storage means every column is read to answer a question about three (Row vs Column Storage).
- A query aggregating two years of orders reads far more of the table than any index can help with, because the predicate is not selective — it wants *most* of the rows. Row-oriented storage means every column is read to answer a question about three (Row vs Column Storage).
- That same query competes for buffer pool and CPU with the checkout path. Either the application slows down or the analyst is told to run it at night (Workload Isolation).
- Twelve analysts run twelve such queries at once. The transactional engine has no concept of workload isolation between them, so the twelfth query and the checkout transaction wait in the same place (Queueing: Why Systems Get Slow Before They Get Broken).
- A question needs order history joined to a CRM, a payment provider and an event stream. None of that is in the application database, and adding it turns the OLTP schema into a warehouse without any of the design that word implies.
- The reporting tables acquire logic — a
revenuecolumn computed one way here and another way there — and two dashboards disagree, both defensibly (The Metrics Layer).
What is actually happening
- A warehouse is a database whose every design decision was made for the opposite workload from an OLTP system: few, large, read-mostly queries touching many rows and few columns, rather than many small transactions touching one row and all its columns (OLTP vs OLAP).
- That inverts the storage layout. Data is stored by column, so a query reads only the columns it names, and values within a column compress far better than values within a row because they are the same type and often similar (Why Analytical Data Compresses, Dictionary, Run-Length, Delta and Bit Packing).
- Execution is vectorised and parallel: operators process batches of column values at a time rather than a row at a time, and work is split across many workers that each scan a slice (Columnar Execution, Distributed Query Execution).
- The optimiser is cost-based and is doing a different job from an OLTP planner — choosing join orders and distribution strategies for tables where a bad choice means shuffling enormous volumes across the network rather than one extra index lookup (Query Optimizers, The Shuffle).
- On top of the engine sit the parts people forget are part of the product: workload management so one analyst cannot starve the others, result caching, fine-grained access control, audit, and a metadata surface that makes queries introspectable (Data Access Control).
- Modern warehouses separate storage from compute, so the same stored table can be read by several independently sized compute groups. That is an architectural property with real consequences, not a marketing line (Separating Storage from Compute).
What happens to an aggregate query inside a warehouse
The clearest way to see what a warehouse is is to follow one query through it. The query below is unremarkable — a year of orders, grouped by country and month — and it is exactly the shape an OLTP engine handles worst and an analytical one handles best.
Read the guarantees column rather than the does column. Most of the stages promise something narrow and specific, and the interesting thing about the chain is where the promises stop: the engine will guarantee you a consistent snapshot of the bytes it read, and nothing at all about whether those bytes mean what the query author assumed.
Notice also how much of the work is decided before the query runs. Pruning depends on the partition and clustering scheme; the scan volume depends on which columns exist and how they were encoded. By the time the query arrives, most of its cost is already fixed (Physical Data Layout).
- 1Parse and bind
Resolves table and column names against the catalog and checks types.
guarantees The query refers to objects that exist, with compatible types. Nothing about semantics.
fails by Binding successfully to a column whose *meaning* changed, which is the failure no parser can catch (Semantic Changes).
- 2Plan
Cost-based optimiser chooses join order, join algorithm and data distribution using table statistics.
guarantees A plan the optimiser believes is cheapest given its statistics.
fails by Stale or missing statistics producing a plan that shuffles a large table where a broadcast would have done (Broadcast Joins).
- 3Prune
Eliminates partitions and blocks that cannot satisfy the predicate, using min/max metadata.
guarantees Skipped data provably could not match. Pruning is never wrong, only absent.
fails by Predicates the engine cannot push down — a function wrapped around the partition column defeats it entirely (Partition Pruning).
- 4Scan
Reads only the referenced column chunks, decoding them in batches.
guarantees A consistent snapshot of the table as of the query start.
fails by Reading every column because the query said
SELECT *, turning a narrow query into a full-table read (Projection Pushdown). - 5Exchange
Redistributes rows across workers so that matching keys meet on the same worker.
guarantees All rows with the same join or group key end up together.
fails by Skew: one key holding a large share of rows, so one worker decides the runtime of the whole query (Data Skew).
- 6Aggregate
Combines partial aggregates per worker, then merges.
guarantees Arithmetic correctness over the rows it was given.
fails by Being given the wrong rows — a fan-out join upstream inflates every SUM and no stage detects it (Grain: What Does One Row Represent?).
- 7Return
Streams the result to the client, possibly from a result cache.
guarantees The rows the plan produced, and consistency with the snapshot it read.
fails by A cached result served after the underlying table changed, if the cache invalidation is coarser than the consumer assumes.
The failsBy column is almost entirely made of failures that produce a successful query with a wrong answer. That is the category the whole domain is organised around (Trusting Data).
1SELECT2 d.country,3 DATE_TRUNC('month', f.order_date) AS month,4 COUNT(*) AS orders,5 SUM(f.net_amount) AS revenue6FROM fct_orders f7JOIN dim_customer d ON d.customer_key = f.customer_key8WHERE f.order_date >= DATE '2025-09-01'9 AND f.order_date < DATE '2026-09-01'10GROUP BY 1, 2;11 12-- Three columns of fct_orders are read. The table may have sixty.13-- One year of partitions is read. The table may hold ten.14-- dim_customer is small, so it is broadcast rather than shuffled.15-- None of that is in the SQL. All of it is in the layout and the optimiser.The same text against a row-oriented OLTP table reads every column of every qualifying row, because rows are stored contiguously and you cannot fetch a third of a row.
Why the operational database cannot simply be made bigger
The most common architectural conversation in a growing company is whether the reporting problem can be solved by a bigger instance and a few more indexes. Sometimes it genuinely can, and reaching for a warehouse too early is a real and expensive mistake. But the reason it eventually cannot is structural rather than a matter of size.
An OLTP engine stores rows contiguously because its workload fetches whole rows by key. An index exists to make a predicate selective — to turn "find these few rows" into a small number of page reads. An analytical query is the opposite request: it wants most of the rows and few of the columns, and there is no index that makes "most of the table" cheap (An Index Scan Is Not Automatically Faster).
Once the layout is wrong for the workload, everything else follows. The scan pulls columns the query never mentions. The buffer pool fills with pages the transactional workload needed. Compression is poor because adjacent values in a row are unrelated types. Parallelism is limited because the engine was built to run many small queries, not to split one large one (The Buffer Pool).
That is the argument for a separate analytical system, and it is worth being precise about it, because "the database was slow" is also what you say when you are missing an index. The test is the shape of the predicate: if it is selective, you have an indexing problem. If it wants most of the rows, you have a storage-layout problem, and no amount of hardware turns one into the other.
Decided by partition pruning, clustering and whether people select the columns they need. The most controllable driver and the one people optimise last.
A nightly CREATE OR REPLACE over all history costs proportional to history, and history grows monotonically while the daily change does not.
Grows with join fan-out and with skew; a single hot key can make one worker's share dominate the whole query.
Only applies on time-based pricing models, where it can dominate everything else. On scan-based models it is zero, which is why the two models reward opposite behaviour.
Accumulates silently — nothing ever prompts you to shorten a retention window, and versions kept for recovery are storage you are paying for.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a typical modelled warehouse, shown to establish an ordering rather than to predict any bill. The ordering is the teaching: layout and rebuild strategy dominate, and both are decided months before anyone looks at cost.
Rows are stored together. To sum one column across a year, the engine reads pages containing every column of every qualifying row, evicting the pages the checkout path needs. An index on `order_date` narrows the range but does not reduce the width, and once the range is most of the table the planner will prefer a sequential scan anyway.
Each column is stored and compressed separately, in blocks carrying min/max metadata. The query reads three column chunks for the qualifying partitions and skips the rest of the table entirely, at both the partition and the block level, and decodes values in batches rather than row by row.
The cost of an analytical scan is driven by bytes read, and bytes read is decided by *how many columns* the layout forces you to touch, not by how clever the query is. Columnar storage changes the width of the read; an index only changes its length. Compression compounds the effect because a column holds values of one type, which is what makes encoding schemes effective at all (Why Analytical Data Compresses).
BigQuery, Snowflake, Redshift and ClickHouse all realise the columnar-plus-parallel model, but they differ architecturally in ways that outlive any feature list: how storage and compute are separated, whether the unit you provision is a cluster or a query, how concurrency is isolated between groups of users, and what the pricing dimension is. Those differences change which optimisations pay off. Check current documentation for specifics before assuming any of them behaves like another.
Structure is enforced; meaning is not
The strongest thing a warehouse gives you over a lake is that it refuses bad structure. A string will not land in an integer column. A required column cannot be null. That is a real guarantee and it eliminates an entire class of the failures that plague schema-on-read systems.
It is also the source of the most confident wrong reasoning in the domain. Structural enforcement says nothing about whether the rows are the ones the source produced, whether one row means what the query assumes, or whether a column's definition changed last Tuesday. A perfectly typed table can be missing a third of its rows and no constraint will notice.
This is why every model needs tests that express *intent* — grain uniqueness, referential integrity, accepted values, plausible ranges — and why those tests must run before the publish rather than after. A test that runs after the swap tells you that the dashboard is already wrong.
And it is why reconciliation against the source is not optional. Internal tests answer "is this table self-consistent". Only a comparison with the system of record answers "is this table complete", and the two questions have almost nothing to do with each other (Reconciliation).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
order_id is unique and not null. | The declared grain: one row per order. | Fan-out from a dimension join with duplicate keys; a non-idempotent merge re-inserting rows. | Duplicates that differ in the key — the same order re-emitted with a new surrogate id looks like two legitimate orders (Surrogate Keys). |
Every customer_key exists in dim_customer. | Referential integrity across the star. | A dimension loaded after the fact, late-arriving members, a broken key derivation. | A key that resolves to the *wrong* customer, which is referentially perfect and semantically catastrophic. |
status is one of a declared set of values. | The producer has not invented a new category. | A new state added upstream that a WHERE status = 'complete' filter silently excludes from every metric. | An existing value whose meaning changed — complete now meaning fulfilled rather than paid fingerprints identically (Semantic Changes). |
Row count and summed net_amount for a closed period match the source system. | Completeness and value fidelity end to end. | Missing rows, dropped partitions, an extract window that closed early, a filter that was too broad. | Any period still open; errors present identically at both ends because they share upstream logic; and every column it does not sum. |
Run all four before publishing, against the candidate table, and swap only if they pass. Tests that run after publish are a notification system, not a control (Atomic Publish).
How to build it
Most important first.
- Model before you load. A warehouse rewards a declared grain, conformed dimensions and a stable set of facts, and punishes a copy of the operational schema — the join patterns are different and so is the query shape (Analytical Data Modeling, Star Schema).
- Let the warehouse do the transformation where the data already lives, rather than pulling it out to transform and pushing it back. That is the whole ELT argument, and it is an argument about where compute sits relative to data (ELT: Load First, Transform Where the Data Lives, Where the Transformation Actually Runs).
- Layer the models — staging that cleans, intermediate that joins, marts that serve — so consumers depend on the outer layer only and inner changes are not breaking changes (Model Layering). Define each metric once inside that structure, because two correct implementations of the same metric will still disagree (The Metrics Layer).
- Publish atomically. A warehouse gives you transactional writes; a pipeline that writes in ten statements has ten observable intermediate states, and someone will read one of them (Atomic Publish).
- Make incremental the default for anything large. A nightly full rebuild costs proportional to all history, and history only grows (Incremental Processing, Full Refresh vs Incremental).
- Push access control into the warehouse rather than into the BI tool. The tool is one client of many, and the row a user should not see is a property of the data (Row and Column Security).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- Type and constraint enforcement on write, within the warehouse. This is a real guarantee and it is stronger than anything a raw lake offers (Nullability & Defaults).
- Transactional visibility of a publish: readers see the previous state or the new one, not a mixture — provided the publish is one statement or one explicit transaction.
- Snapshot-consistent reads, so a long-running query is not reading a table that changes under it. The isolation level and its exact semantics vary between products (Isolation Levels).
- No guarantee about grain, meaning, or completeness relative to the source. Every column can be the right type and every row can be present and the metric can still be wrong (The Pipeline Succeeded. The Data Is Wrong.).
- No guarantee that the warehouse knows what it does not have. Rows the pipeline never delivered are indistinguishable from rows that never existed (Missing Rows).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The check with the best ratio of effort to value is a per-model contract test suite that runs before publish: not-null on keys, uniqueness on the declared grain, referential integrity to dimensions, and accepted values on categorical columns (Data Tests).
- Combine it with a reconciliation of a closed period against the source, on both row count and a summed measure, because the tests above are all *internal* — they can pass on a table that is internally perfect and missing a third of the source (Reconciliation).
- Both miss semantic error. A
revenuecolumn that quietly changed from gross to net passes every structural test, reconciles against a source that made the same change, and reports the wrong number to the business (Semantic Changes).
- A warehouse introduces the latency of whatever loads it, not latency of its own — a loaded row is queryable essentially immediately. The staleness a consumer feels is the load schedule plus the transformation schedule (Cost vs Freshness).
- Continuous small loads make data fresher and make the physical layout worse, because they produce many small commits. Most warehouses reorganise in the background; that work is real and it is not free (File Compaction).
- Freshness is per-table. A platform-level freshness number hides exactly the model that has not refreshed since Friday (Freshness Monitoring).
- Adding a nullable column is safe and routine. Changing a type, renaming, or changing a column's meaning is a contract change affecting every consumer, including the BI tool nobody remembers exists (Backward Compatibility).
- The warehouse enforces structure, which means structural changes fail loudly — a genuine advantage over schema-on-read. It enforces nothing about meaning, which means semantic changes fail silently, exactly as they do everywhere else (Schema Evolution).
- Because consumers write SQL directly against tables, the blast radius of a rename is much wider than an API change. Views over models are the cheapest indirection layer available and are usually worth their weight (Impact Analysis).
- Rebuild from the layer below. If raw is retained outside the warehouse and models are deterministic functions of it, any modelling error costs compute rather than data (Keeping Raw History: The Recovery Position and the Liability).
- Time travel — querying a table as of an earlier point — is the fastest recovery for an accidental overwrite, and its window is bounded and product-specific. It is a safety net, not an archive.
- Backfill into a shadow table, validate it, then swap. Rewriting the table consumers are reading while they read it is how a bad fix becomes an incident (Planning a Backfill).
What can go wrong
- A full refresh that truncates and reloads, failing after the truncate, leaving an empty table that every dashboard renders as zero.
- An unbounded query from one consumer consuming the compute the whole team shares, turning one careless
SELECT *into a platform outage (Scan Cost). - Silent duplicate rows from a merge whose key is not actually unique, inflating every measure downstream (Upserts and Merges).
- A model that succeeds against an empty upstream and publishes zero rows over yesterday's correct data.
- The mitigation failing: tests that run *after* publish, so the alert arrives at the same time as the bad data reaches the dashboard (Contract Enforcement).
- "A data warehouse is just a bigger database." It is a database with the opposite storage layout, a different optimiser, a different concurrency model and a different cost function. Sizing up an OLTP system does not produce one, which is why every attempt to do so eventually stops (The OLTP to OLAP Journey).
- "The warehouse is the source of truth." It is a *copy*, downstream of systems that own the data. If the two disagree, the warehouse is wrong until proven otherwise (Source of Truth).
- "It enforces schema, so the data is clean." It enforces types. Nothing about a type says the value is right, the row belongs, or the grain is what the query assumes (The Dimensions of Data Quality).
- "Warehouses are expensive, lakes are cheap." They shift cost between storage, compute, engineering time and governance work. Comparing them on one of those four and calling it a comparison is how platforms are chosen badly (Lake vs Warehouse vs Lakehouse).
- "
SELECT *is fine, it is only analytics." Columnar storage meansSELECT *reads every column chunk. It is the single easiest way to turn a cheap query into an expensive one (Projection Pushdown).
- A warehouse is usually the best governance surface in the platform: it can express row-level policies, column masking, roles and audited access in one place, which a bucket cannot (Row and Column Security, Data Masking, Tokenisation & Encryption).
- That makes it the natural place to enforce classification — but only for the data that actually landed there. Extracts into notebooks and BI caches leave the governed boundary immediately and inherit none of it (PII in Pipelines).
- Time travel and long retention interact badly with deletion requests: deleting a row from the current table does not remove it from the versions still queryable behind it (Deletion Requests).
Operating it
- Bytes scanned per query, grouped by consumer and by model. It is the single most actionable number in a warehouse and it is usually available without any instrumentation (Cost Attribution).
- Per-model freshness and row count with their own history alongside, so a partial load looks like an anomaly rather than a slow day (Volume Anomalies).
- Queue time versus execution time for queries. Rising queue time with flat execution time is a concurrency problem, not a query problem (Queueing: Why Systems Get Slow Before They Get Broken).
- Test results as a first-class signal published with the model, so a consumer can see whether the table they are reading passed its own contract (The Data Quality Dashboard).
- At 10x volume, full-refresh models stop fitting in their window and incremental processing becomes mandatory. This is the most common forced migration in a warehouse's life (Incremental Processing).
- At 100x, physical layout inside the warehouse — clustering, sort order, partitioning — decides feasibility, and the fact that the warehouse manages files for you does not exempt you from thinking about them (Clustering and Sort Order).
- Consumer count scales concurrency and governance rather than storage. Separated compute lets you give the finance team their own compute group so an analyst's runaway query cannot affect the month-end close (Separating Storage from Compute).
- Bytes scanned dominates for engines that charge by scan; compute hours held dominates for engines that charge by cluster time. The two lead to opposite optimisations, and knowing which model you are on is the first cost question (What Actually Drives Data Platform Cost).
- Bytes shuffled across the network during joins and wide aggregations, which grows super-linearly with skew because one worker moves far more than its share (Data Skew).
- Repeated computation of unchanged history — the nightly full rebuild — which costs in proportion to everything rather than to what changed (Compute Waste).
- Retained bytes, including the versions kept for time travel, which are storage you are paying for and often forgetting about.
- A warehouse buys enforced structure, fast aggregation, real concurrency control and fine-grained governance. It costs flexibility: the data has to be modelled before it is useful, and formats it does not natively ingest need a landing zone elsewhere (The Data Lake).
- Managed warehouses trade operational work for a coupling: your data is in their storage layer, in their format, behind their engine. Table formats over a lake are the response to that, and they trade back some of the governance and performance (The Lakehouse).
- Loading raw into the warehouse and transforming in place is simple and puts every byte in one billing surface. Landing raw in a lake first is cheaper to keep and more work to query (ELT: Load First, Transform Where the Data Lives).
What a warehouse does with your query
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Parse and bind | |
|---|---|
| What happens | The text becomes a tree, and every name in it is resolved against the catalog. |
| Why it matters | A column that does not exist fails here, before anything is read. A column that exists with a different meaning does not fail at all. |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALColumnar storage, vectorised parallel execution, cost-based optimisation and workload management are properties of the analytical-database category rather than of any product. What differs between products is how each is realised and what it charges for.
- WAREHOUSE-SPECIFICThe cost model differs fundamentally: some products charge for bytes scanned per query, others for compute time held regardless of what it scans. The first rewards pruning and column selection, the second rewards keeping clusters busy and shutting them down — opposite optimisations from the same table.
- SCALE-SPECIFICBelow the point where analytical queries affect production or a second source appears, a read replica plus scheduled reports beats a warehouse on every axis including correctness, because there is no copy to reconcile.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what a "consistent snapshot" means once the storage layer is itself replicated across zones, and what a warehouse's transactional publish is really coordinating.
- — DevOps / Production Engineering owns how model code is versioned, tested in CI and rolled back — a warehouse model is software and deserves the same delivery discipline as any service.