Comparing Analytical Warehouses
Six axes that actually separate analytical warehouses — architecture, storage/compute coupling, latency profile, concurrency model, cost-model shape and workload fit — and why a product name is the last thing to decide.
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.
Three teams recommend three different warehouses. What are you comparing them on, before anyone runs a benchmark?
The people who will live with the answer: analysts who need an interactive response while they think, a dashboard tier that fans out many small queries at once, batch transformations that want throughput and do not care about latency, and an application that may embed analytical results in a user-facing page. These four have genuinely different requirements and one warehouse rarely serves all of them equally.
The unit of comparison is a workload, not a product: a family of queries with a shared shape, concurrency profile and latency expectation. Comparing "BigQuery versus ClickHouse" is meaningless until you say which workload, because the ranking reverses between a nightly aggregation and a per-page-load lookup.
Load a representative dataset into two candidates, run your ten slowest queries on each, and pick the one that wins more of them. This is a real and useful exercise — it beats reading marketing pages, it surfaces obvious misfits fast, and it forces you to actually define the queries.
The benchmark ran one query at a time. Production runs eighty dashboard queries at 09:00 while a batch transformation holds most of the cluster, and the axis that decides the outcome — concurrency isolation — was not exercised at all (Queueing: Why Systems Get Slow Before They Get Broken).
- The benchmark ran one query at a time. Production runs eighty dashboard queries at 09:00 while a batch transformation holds most of the cluster, and the axis that decides the outcome — concurrency isolation — was not exercised at all (Queueing: Why Systems Get Slow Before They Get Broken).
- The test dataset was loaded in one pass, so it is perfectly laid out on both sides. Production data arrives continuously in small increments, and the two systems degrade very differently as file or part counts grow (File Size and the Small-Files Problem).
- The queries were rewritten slightly to run on both. The rewrite removed the pattern that was actually expensive — a wide join at the wrong grain — so the benchmark measured a workload nobody runs (Microbenchmark or End-to-End: Why p99 Did Not Move).
- Nobody modelled the cost-model shape. One candidate bills by work performed and the other by time held, so the cheaper option flips depending on whether the platform is busy for two hours a day or twenty (Cost vs Freshness).
- The comparison ignored everything that is not query execution: how a schema change is deployed, how access is granted, how a backfill is published atomically, how lineage is captured. Those decide the platform's liveability and none of them appear in a query timing (Data Platform Engineering).
- Freshness was assumed rather than tested. A warehouse that ingests in batches and one that accepts continuous inserts are not interchangeable for a dashboard that promises minutes (The Freshness SLO).
What is actually happening
- Every analytical warehouse is the same three components in different proportions: a storage layer holding columnar data, an execution layer that scans and aggregates it in parallel, and a coordination layer that plans queries, tracks metadata and enforces access. What differs is where the boundaries are drawn and who owns each side (Columnar Execution).
- The first axis is architecture: a shared-nothing cluster where each node owns a slice of data; a disaggregated design where compute is stateless and storage is remote; or a single-process engine where all three components are one library (Separating Storage from Compute).
- The second is storage and compute coupling. When they are coupled, resizing means moving data, and idle compute is compute you are still holding. When separated, resizing is instantaneous and the network becomes part of every scan — which is why the metadata layer that prunes files before reading them matters so much more in a separated design (Partition Pruning).
- The third is latency profile. Some engines are built so that the fixed cost of starting a query is very small and a point-ish lookup is viable; others amortise a larger planning and provisioning step over a big scan. Neither is faster; they have different fixed costs, and the fixed cost is what decides whether a warehouse can sit behind a user-facing page (Latency Is a Distribution, Not a Number).
- The fourth is concurrency: what happens when many queries arrive at once. Options are a shared pool that everyone queues for, independently sized compute clusters per workload, or per-query resource allocation. This axis decides whether one analyst's runaway query is everybody's problem (Workload Isolation).
- The fifth is cost-model shape — not amounts, shape. Billing by work performed makes the query text the cost lever; billing by time held makes the *schedule* the cost lever; billing by provisioned nodes makes capacity planning the cost lever. Each shape rewards a different optimisation and punishes a different mistake (What Actually Drives Data Platform Cost).
Six axes, and which of them are decisions
A warehouse comparison goes wrong in a predictable way: it becomes a performance argument, because performance is the one axis with a number attached. The number is real and it is also the axis most contaminated by layout, data shape and the specific queries chosen, which is why two honest teams routinely reach opposite conclusions from the same exercise.
The six axes below are ordered so that the ones you choose come before the ones you observe. Architecture and cost-model shape are decisions with long consequences. Latency and cost are outcomes of those decisions combined with your layout and your schedule, and they can usually be improved a great deal without changing the product (Physical Data Layout).
Read the third column as the question to bring to a vendor call or a proof of concept. It is a question about mechanism, and mechanism is what stays true after the release notes change.
| Axis | What actually varies | The question to ask |
|---|---|---|
| Architecture | Shared-nothing cluster with data local to nodes; disaggregated compute over remote storage; single-process engine over local or remote files. | What happens when I need more compute — do I move data, add stateless workers, or nothing at all? |
| Storage / compute coupling | Whether resizing implies redistribution, whether idle compute is still held, whether many compute clusters can read one copy of the data. | Can two workloads read the same table with completely independent compute, and what does the second one cost when it is idle? |
| Latency profile | The fixed cost of starting a query: planning, provisioning, metadata resolution, first byte from remote storage. | What is the floor — the time a query touching almost nothing takes — and can this sit behind a user-facing page? |
| Concurrency model | Shared pool with queueing; independently sized compute per workload; per-query allocation with admission control. | When eighty dashboard queries and one runaway scan arrive together, who waits and who is unaffected? |
| Cost-model shape | Billed by work performed, by time held, or by provisioned capacity. Not amounts — which lever moves the number. | If I halve the bytes my queries read, does the bill change? If I halve the hours the platform is busy, does it? |
| Workload fit | Batch transformation, interactive exploration, dashboard fan-out, low-latency embedded analytics, ad-hoc joins over messy data. | Which of my four consumer classes is this engine actually built for, and which am I planning to serve badly? |
Three architectural shapes
Almost every analytical engine is a variation on three shapes. In a shared-nothing cluster, data is distributed across nodes and each node scans its own slice; adding capacity means redistributing data, and the cluster exists whether or not anyone is querying. In a disaggregated design, storage is remote and shared, compute is stateless and can be created and destroyed at will, and a metadata layer decides which files a query needs to touch before any of them are read. In an in-process engine, all three components are a library inside your program.
Each shape puts the hard problem somewhere different. Shared-nothing puts it in data placement — the distribution key decides which joins are local and which are a network shuffle. Disaggregated puts it in metadata and pruning, because the network sits in the scan path and the only defence is not reading the file at all. In-process puts it in the machine, because there is no second machine (Separating Storage from Compute, Predicate Pushdown).
This is also why "which is faster" has no answer. A query that reads a small, well-pruned slice of a huge table can favour the design that never provisioned a cluster. A query that joins two enormous tables on a key both are distributed by can favour the design where the join is local. The shape decides which queries are cheap, and your workload decides which of those you run (The Shuffle).
Load a snapshot into each candidate, run the ten queries people complain about, one at a time, and record how long each takes. Pick the engine that wins the most rows in the spreadsheet.
Capture a day of production query text with arrival timestamps, reproduce the ingestion pattern rather than a bulk load, replay the day against each candidate at the real concurrency, and compare the completion-time distribution per consumer class. Diff the query *results* across engines as well as the timings.
The axes that decide whether a warehouse works — concurrency isolation, behaviour as small files or parts accumulate, write visibility, and whether the results agree — are all invisible when queries are run one at a time against a perfectly laid-out bulk load. A serial timing measures the engine on a workload nobody runs, and result divergence from type coercion or null handling in aggregates is a correctness finding that no timing spreadsheet has a column for.
Cost-model shape decides which optimisation pays
Cost models come in three shapes, and the shape — not the amount — is what a data engineer needs. When you are billed for work performed, the lever is the query and the layout: reading fewer bytes is the whole game, and an idle platform costs nothing. When you are billed for time held, the lever is the schedule: a query that reads half as much but runs inside the same hour changes nothing, while consolidating scattered jobs into one window changes everything. When you are billed for provisioned capacity, the lever is capacity planning and the failure mode is a cluster sized for a peak that occurs twice a week (Fixed vs Variable Cost).
This is why cost optimisation advice does not transfer between platforms and why it so often reads as contradictory. "Select fewer columns" is excellent advice under one shape and nearly irrelevant under another. "Consolidate the schedule" is the reverse. Both are correct; they are answers to different billing shapes (Cost vs Freshness).
The bars below are relative and unitless — they say which drivers move the number under each shape, not what anything costs. That is deliberate: a ratio published here would be wrong for most readers within a year, and a driver will still be a driver.
Dominant under work-based billing and largely irrelevant under time-held billing, where a query that scans less but occupies the same window changes nothing. Moved by partition pruning, projection and clustering rather than by engine choice.
Dominant under time-held and provisioned billing. Driven by the schedule, by autosuspend behaviour and by how many separate compute clusters exist, not by how efficient the SQL is.
Shows up under every shape because it lengthens queries as well as consuming network. Driven by join keys, grain and distribution rather than by the warehouse.
Grows monotonically under every shape and is nobody's job by default. History features are storage that exists whether or not anyone reads it.
Forty dashboards computing the same aggregate from the same fact table, and every full re-run of a pipeline that could have been incremental. Independent of product and usually the largest single win available.
Only exists under provisioned billing, where it is the whole problem: capacity sized for a twice-weekly peak is unused most of the time and cannot be handed back mid-week.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Weights are relative to the largest driver in this comparison and nothing more. The point of the device is the ranking under each shape, which is why the same platform rewards opposite optimisations depending on how it bills.
Billing shapes are not fixed per product. Several warehouses now offer more than one — a provisioned mode and an on-demand mode, or per-second compute with different suspension behaviour — and editions have been renamed and restructured repeatedly. Never carry an assumption about which shape a product uses from one year to the next; verify current documentation, and check which shape your own account is actually on.
How to build it
Most important first.
- Write the workload profile before looking at any product: query shapes, rows touched, concurrency at peak, latency expectation per consumer class, freshness requirement, and how data arrives. This document is the comparison; the products are just candidates against it (Who Actually Consumes This Data).
- Test with your own data, your own arrival pattern and your own concurrency. A single-query timing on a pre-loaded dataset tells you almost nothing about a platform that ingests continuously and serves eighty dashboards at once (Load Test Shapes: The Shape Is the Hypothesis).
- Separate the axes that are decisions from the axes that are consequences. Architecture and cost-model shape are decisions. Latency and cost are consequences of those decisions plus your layout, and can be improved by changing the layout without changing the product (Physical Data Layout).
- Score operational burden explicitly: who compacts, who reclusters, who resizes, who upgrades, who is paged at 03:00. This is the axis that decides how the platform feels in year two, and it never appears in a benchmark (Scoring Operational Complexity).
- Assume more than one engine. Most mature platforms end up with an interactive warehouse, a batch processing engine and something serving low-latency aggregates, all reading from the same governed storage. Designing for that from the start is cheaper than converging on it by accident (The Lakehouse, Query Engines).
- Keep the data in a form the loser can still read. If the comparison's outcome can be revisited without a migration, the decision stops being a one-way door (Open Table Formats).
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.
- All of these give you SQL and a planner. That is the shared floor, and it is the reason SQL transformations are the most portable asset a platform has (SQL Transformations).
- Transactional guarantees differ sharply. Some warehouses give you multi-statement transactions and snapshot isolation over tables; some give atomic single-table writes only; some give you eventual consistency between an insert and what a query sees (Transactions and ACID).
- None of them guarantees freshness. Freshness is a property of your ingestion path, and the warehouse only decides how quickly a written row becomes visible to a reader (Freshness Checks).
- None of them guarantees that concurrency is isolated by default. Whether a heavy query can starve an interactive one is an architectural property you have to look up, not assume (Concurrency Limits: An Unbounded Server Is a Slower Server).
- Completeness and correctness are guaranteed by nothing here. A warehouse faithfully aggregates whatever it was given (Data Quality).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The comparison check that matters is a replayed production workload: capture a real day of query text and arrival times, replay it against each candidate at the real concurrency, and compare completion profiles rather than single-query times (Load Testing: What Question Is This Test Answering?).
- It misses everything about the year after the decision — how the platform behaves when the data has doubled, when three new teams have onboarded, and when nobody has compacted anything for six months.
- It also misses correctness. Two engines can return different results for the same SQL because of type coercion, null handling in aggregates, timestamp semantics or floating-point summation order. Diff the *results*, not just the timings, and treat any divergence as a finding rather than noise (Reconciliation).
- The warehouse contributes two latency terms: write visibility — how long after a load a row can be read — and query latency. Only the second one gets benchmarked, and the first one is what breaks a freshness SLO.
- Architectures that batch writes into large immutable files trade write visibility for scan efficiency. Architectures that accept small continuous inserts trade scan efficiency for write visibility, and pay it back with background merging (File Compaction).
- A warehouse cannot make data fresher than the pipeline feeding it. Choosing a low-latency engine to fix a freshness problem caused by an hourly batch extract is a common and expensive misdiagnosis (Batch vs Streaming Ingestion).
- Schema evolution behaviour differs enough to matter: whether a column can be added without rewriting, whether a type can be widened in place, and whether a rename is a metadata operation or a full rewrite (Schema Evolution).
- The migration path away from each candidate is an evolution question you should answer before choosing. Data in an open format on object storage evolves independently of the engine; data inside a proprietary storage layer evolves at the vendor's pace (Open Table Formats).
- The comparison itself has to be re-run occasionally. Architectural categories are stable; where a given product sits within them has moved more than once, as engines added separation, caching layers or streaming ingest (Data Platform Engineering).
- Ask each candidate the recovery questions, not the performance ones: can I restore a table to a point in time, can I publish a backfill without consumers reading a half-written state, can I re-run a load without duplicating rows (Atomic Publish, Idempotent Data Pipelines).
- Time-travel-style features are genuinely useful and are bounded by a retention window that is a configuration, not a law. A recovery plan that depends on one is a plan with an expiry date (Planning a Backfill).
- The strongest recovery position is independent of the warehouse: retained raw data plus deterministic transformations means any warehouse can be rebuilt, including a different one (Keeping Raw History: The Recovery Position and the Liability).
What can go wrong
- A benchmark that measured the wrong axis, chosen because it was the easy axis to measure.
- A warehouse chosen for interactive analytics and then used for batch transformation, so heavy jobs and human queries compete for the same capacity (Workload Isolation).
- A cost model whose lever nobody understood — optimising query text on a platform billed by hours held, or optimising the schedule on one billed by bytes scanned (Scan Cost).
- Concurrency collapse at 09:00 that no single-query test predicted (The Backlog Arithmetic: Four Levers and a Drain Time).
- The mitigation failing too: a workload replay built once for the evaluation and never run again, so nobody notices the platform outgrew the decision (Regression or Tuesday? Telling a Real Change from Noise).
- A decision made on a product comparison when the real problem was layout — the same query on the same engine with a sensible partition key would have settled it (Partitioning).
- "This one won the benchmark, so it is faster." It was faster for those queries, at that concurrency, on that layout, on that day. Change any of the four and the ranking can reverse — which is exactly why this domain does not publish ratios (Benchmark Fallacies: Confident Numbers That Are Wrong).
- "Separated storage and compute is the modern architecture, so it is better." It is a different set of trade-offs: elastic resizing and workload isolation, paid for with network in the scan path and a heavier dependence on metadata-driven pruning (Separating Storage from Compute).
- "We need one warehouse for everything." Most platforms end up with several engines over shared governed storage, and pretending otherwise usually means one workload is being served badly to preserve the story (Query Engines).
- "The cost model is a billing detail." It decides which optimisation pays. A team tuning SQL on a platform billed by hours held is working hard on the wrong lever (Cost Attribution).
- "A warehouse is just a bigger database with more storage." Different workload, different layout, different execution model, different failure modes. The name overlap is historical (OLTP vs OLAP).
Operating it
- Query concurrency and queue wait time at peak, per workload class. This is the signal that tells you whether the concurrency axis was answered correctly (Queueing: Why Systems Get Slow Before They Get Broken, Saturation: The Reading Utilization Cannot Give You).
- Bytes scanned per query and per dashboard. It is the layout signal and, on work-billed platforms, the cost signal too (Scan Cost).
- Completion-time distribution rather than an average — the tail is what an analyst experiences and what a dashboard timeout hits (Percentiles: Which One, and How Many Users Is That?, Tail Latency: Why p50 Being Fine Does Not Help).
- Write-visibility lag: time between a load completing and the rows being visible to a reader. Rarely instrumented, and the usual cause of an inexplicable freshness miss (Freshness Monitoring).
- At 10x data, the axis that usually breaks first is layout rather than engine: a partition scheme that was fine at one scale starts scanning far more than the query needs (Partition Cardinality).
- At 100x, architecture decides. Coupled storage and compute means resizing involves moving data; separated designs mean the metadata layer and the network become the limits.
- Consumer count scales concurrency, which is the axis most likely to have been ignored during the evaluation. Ten analysts and eighty dashboards are different workloads even at identical data volume (Who Actually Consumes This Data).
- Bytes scanned, which is decided by layout and by projection far more than by engine choice (Projection Pushdown, Partition Pruning).
- Compute hours held, which is decided by the schedule and by autosuspend behaviour rather than by query efficiency (Compute Waste).
- Bytes shuffled across the network during joins and aggregations, which is decided by the model and the join keys (The Shuffle).
- Retained bytes including all the history features — snapshots, time travel, clones — which are storage that exists whether or not anyone reads it (Storage Lifecycle).
- Repeated work: the same aggregation computed by forty dashboards because there is no serving layer between them and the fact table (Data Marts).
- Comparing on axes rather than on timings takes longer and produces a less satisfying answer — a shortlist and a set of conditions rather than a winner. It is also the only version of the exercise that survives contact with production.
- Designing for more than one engine buys fit-for-purpose serving and costs you a governance surface that now spans several systems, plus the permanent risk of two engines disagreeing about the same table (Two Dashboards, Two Numbers).
- Keeping data in an open format preserves optionality and gives up some of the performance and convenience a tightly-integrated proprietary storage layer provides. That is a real trade, not a free lunch.
Dataset review questions
This lesson uses the shared review exercise.
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.
- WAREHOUSE-SPECIFICWhere a given product sits on the architecture, concurrency and coupling axes is a property of that product and its current generation; several engines have moved between categories by adding storage/compute separation, result caching or streaming ingest, so the categories are stable while the placements are not.
- GENERALThe six axes themselves — architecture, coupling, latency profile, concurrency model, cost-model shape, workload fit — apply to any analytical engine including on-premise MPP systems that predate cloud warehouses entirely.
- SCALE-SPECIFICBelow roughly a single machine's worth of data the entire comparison collapses: an in-process engine over Parquet answers the workload and the distributed architectures are pure overhead. The axes only start separating candidates once data or concurrency exceeds one node.
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 why a shuffle is expensive and what a coordinator must guarantee when it fans a query across workers that can fail independently.
- — DevOps / Production Engineering owns how a warehouse migration is actually executed: parallel running, cutover, rollback and the deployment of SQL models against two engines at once.