Choosing an Analytical Platform
Eight questions that turn "which warehouse should we use" into a list of required capabilities, two or three candidate architectures, and the trade-off each one asks you to accept. The output is never a single product.
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.
Someone asks which analytical platform to adopt. What do you have to know before that question has an engineering answer rather than a preference?
The people who will be constrained by the answer for years: analysts whose SQL habits it shapes, engineers who will operate it, finance who will see its shape in a budget, and the consumers of every dashboard built on it. None of them can evaluate a product; all of them can tell you their requirement, which is the input this decision actually runs on (Who Actually Consumes This Data).
The unit of the decision is a workload: a family of queries with a shared shape, concurrency profile, latency expectation and freshness requirement. A platform decision made at the granularity of "the company" is a decision made at the wrong grain, and it is why most platforms end up serving one workload well and two badly (Grain: What Does One Row Represent?).
Ask which warehouse the team already knows, or which one the cloud provider bundles, and use that. This is a defensible default and often the right answer: familiarity is real leverage, integration removes work, and a platform nobody can operate is worse than a slightly ill-fitting one that everybody can.
The chosen platform is excellent at batch aggregation and the product now needs an in-product analytics panel with sub-second response. No amount of tuning changes a fixed per-query cost, and a second engine has to be introduced under time pressure (Query Engines).
- The chosen platform is excellent at batch aggregation and the product now needs an in-product analytics panel with sub-second response. No amount of tuning changes a fixed per-query cost, and a second engine has to be introduced under time pressure (Query Engines).
- The evaluation measured single-query latency. At 09:00 the dashboard tier, the transformation schedule and an analyst's exploratory scan collide, and the axis nobody measured — concurrency isolation — is the one that decides the experience (Queueing: Why Systems Get Slow Before They Get Broken).
- Freshness was specified as "real time" by a stakeholder who reads the dashboard once each morning. The platform was chosen for continuous ingestion, and the whole architecture now carries streaming complexity for a daily decision (Batch vs Streaming Ingestion).
- The cost-model shape was never named, so the team optimises query text on a platform billed by hours held and spends a quarter achieving nothing (Cost Attribution).
- Operational burden was assumed to be zero because the product is managed. Somebody still has to compact, recluster, resize, upgrade and be paged, and that somebody was not staffed (Scoring Operational Complexity).
- The data was loaded into a proprietary storage layer, so the decision cannot be revisited without a migration — and it needs revisiting eighteen months later (Open Table Formats).
What is actually happening
- A platform decision is a capability requirement plus a set of constraints, and the product is whatever satisfies both. Reversing the order — starting from a product and discovering the requirement — is what produces platforms that have to be replaced (Data Architecture Patterns).
- Eight questions cover almost all of it: data size, concurrency, latency, streaming freshness, SQL complexity, operational burden, cloud constraint and cost-model shape. They are not equally weighted; the first four eliminate candidates and the last four decide between survivors.
- Data size is asked per query, not in total. A hundred terabytes queried one day at a time is a different problem from a hundred gigabytes joined across its whole history, and the second may be the harder one (Scan Cost). Concurrency and latency together decide whether an engine must have a small fixed cost per query. A user-facing panel needs a floor low enough for a page load and enough parallelism for every viewer at once — a very different requirement from an analyst who will wait (Tail Latency: Why p50 Being Fine Does Not Help).
- Streaming freshness is the question most often answered wrongly, because "real time" is what people say and "before the morning meeting" is what they mean. The right form of the question is "what decision does this number drive, and how stale can it be before that decision changes" (The Freshness SLO).
- SQL complexity decides how much of your logic the engine can express: deep multi-table joins at query time, window functions, semi-structured access, and update-and-merge patterns are not equally supported across the categories (SQL Transformations).
- Operational burden is who compacts, reclusters, resizes, upgrades and is paged. It is the axis that decides how the platform feels in year two and the one that never appears in an evaluation (Data Platform Engineering). Cost-model shape — billed by work performed, by time held, or by provisioned capacity — decides which optimisation pays, and therefore what your engineers will spend their time on (What Actually Drives Data Platform Cost).
Eight questions, asked before any product is named
The most common failure in this decision is not choosing badly, it is choosing before the requirement exists. A shortlist assembled from products people have heard of quietly excludes the architectural category that would have fitted, and then the evaluation compares two members of the same category very carefully (No Cargo-Cult Infrastructure).
These eight questions are ordered so that the eliminating ones come first. Data size, concurrency, latency and freshness rule categories in or out. SQL complexity, operational burden, cloud constraint and cost-model shape then decide between whatever survived. Ask them per workload — the batch transformation, the dashboard tier, the exploratory analyst, the embedded panel — because the answers differ per workload and averaging them produces a requirement nobody has.
The third column is the one to argue about. It says what a given answer eliminates, and elimination is what makes this a decision rather than a discussion. If none of your answers eliminates anything, the questions were answered too vaguely to be useful.
| Question | Ask it like this | What an answer eliminates |
|---|---|---|
| Data size | How much does one query touch — not how much do we store? | If one query touches what fits on a machine, distributed architectures are overhead. If it touches history in bulk, an in-process engine is out. |
| Concurrency | How many queries arrive at once at peak, and from whom? | High concurrency from many independent consumers eliminates anything with a shared pool and no isolation, and anything with a large fixed cost per query. |
| Latency | What is the floor — a query touching almost nothing — and who is waiting? | A user-facing page eliminates engines whose fixed per-query cost is measured in seconds. An analyst who will wait eliminates nothing. |
| Streaming freshness | What decision does this number drive, and how stale can it be before that decision changes? | A genuine seconds-level requirement eliminates batch-load architectures. A daily decision eliminates the argument for streaming complexity entirely. |
| SQL complexity | Do we join large tables at query time, use window functions, read semi-structured data, run merges? | Heavy multi-table joins at query time eliminate engines designed around wide denormalized tables. Frequent row-level merges eliminate append-optimised designs. |
| Operational burden | Who compacts, reclusters, resizes, upgrades, and gets paged — by name? | No available operator eliminates anything self-managed, however good. This is the axis that most often overrides the technically-best answer. |
| Cloud constraint | Are we bound to a provider by contract, regulation or residency? | A hard constraint eliminates everything not available there. A soft preference eliminates nothing and should not be allowed to pretend it does. |
| Cost-model shape | Which lever moves the bill — bytes read, hours held, or capacity provisioned? | It eliminates nothing and decides everything downstream: which optimisation pays, what engineers spend time on, and how the platform is budgeted. |
From answers to architectures
The output of the eight questions is a set of required capabilities, and capabilities map to architectural categories rather than to products. That indirection is the point: categories are stable for years and products move between them, so a decision expressed as "we need isolated compute per workload and atomic publish over open storage" survives a rename and a decision expressed as a product name does not (Data Architecture Patterns).
The options below are architectures, not recommendations, and there is deliberately no winner. Read the when column as a set of answers to the eight questions and the cost column as what you are agreeing to live with. Most real platforms end up combining two of these — very commonly open storage plus a warehouse, or a warehouse plus a low-latency serving store — and that combination is a mature outcome rather than an indecisive one (The Lakehouse).
One option is easy to skip and worth taking seriously: not building a platform. If the questions come back small — one machine, few consumers, daily freshness — then an in-process engine over Parquet in a scheduled job answers everything, and every other row here is overhead bought against a requirement that does not exist (DuckDB Concepts).
Given the eight answers, what shape of analytical platform is required?
when One query touches what fits on a machine, work is single-tenant and bounded, freshness is scheduled rather than continuous, and nobody is available to operate infrastructure.
cost No concurrency story, no shared governance surface, no stable endpoint for consumers, and atomic publish becomes your responsibility. The model ends abruptly rather than degrading (DuckDB Concepts).
when Data must stay in an open format, several engines will read it, and the priority is optionality and governed shared storage over peak convenience.
cost You own table maintenance — compaction, clustering, snapshot expiry — and you give up some of the integration and performance a proprietary storage layer provides (The Lakehouse, Open Table Formats).
when Query volume is spiky, nobody wants to size capacity, workloads should not contend, and the cost model billing by work performed matches how the platform is used.
cost Layout is your only lever, so a workload whose predicates do not match the partition column has no remedy. Elasticity also means an expensive query has nothing stopping it (BigQuery Concepts).
when Several workloads must be strictly isolated, load is predictable enough to schedule, and per-workload cost attribution matters.
cost Idle compute is held compute, each cluster has its own cold cache, and cluster sprawl converts an isolation feature into a spending problem (Snowflake Concepts).
when Query concurrency is high, the fixed cost per query must be small, freshness is seconds, and the data is flat event-shaped rather than a normalised star.
cost Narrow on joins, updates and multi-table transactions, and correctness responsibility moves into the read path where merge-time semantics apply (ClickHouse Concepts).
when The workloads genuinely have opposite requirements — batch transformation, interactive analysis, and something user-facing.
cost A governance surface spanning several systems, two definitions of every metric unless a metrics layer prevents it, and the standing risk of two engines disagreeing about one table (The Metrics Layer, Two Dashboards, Two Numbers).
The mapping from architectural category to product is the part of this lesson that will date. Engines have added storage/compute separation, streaming ingestion, open-format support and serverless modes, moving between the categories above more than once, and pricing shapes have been restructured alongside. Verify current documentation before assuming a named product still sits where you last placed it.
The output is a shortlist and a test plan
A finished platform decision has three parts: the required capabilities, two or three candidate architectures that provide them, and the trade-off each one asks you to accept. It does not have a winner, because the remaining differences are the ones only a realistic test can settle — and a test with no acceptance criteria written in advance is a demonstration, not an evaluation (Load Test Shapes: The Shape Is the Hypothesis).
The checks below are what a proof of concept should assert. Note that only the first is about speed, and note the misses column throughout: a proof of concept is run by the most motivated engineer on the team during the period of maximum attention, which is precisely the condition under which operational burden is invisible. Score that axis by asking other users of the platform, not by observing your own trial (Scoring Operational Complexity).
The last thing to write down is what you are optimising for — time to first dashboard, steady-state cost, minimum operational headcount, maximum optionality. These pull against each other, and an objective that is never stated gets decided by whoever argues longest. Writing it down is also what makes the decision reviewable in a year, which is the only way a platform choice stays correct as its inputs change.
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Replay a real day of query text at real arrival times and concurrency; compare completion-time distributions per consumer class. | The platform serves the actual workload, not a serial approximation of it. | Concurrency collapse, queueing under load, an engine whose fixed per-query cost is too high for a dashboard tier. | Year two entirely — twice the data, three more teams, and nobody compacting anything. It also misses any workload that does not exist yet (Percentiles: Which One, and How Many Users Is That?). |
| Diff query results between the incumbent and each candidate on a fixed set of business-critical queries. | The platforms agree about what the numbers are. | Type coercion differences, null handling in aggregates, timestamp semantics, floating-point summation order — real divergences that no timing test surfaces. | Divergence on queries not in the set, and any case where both engines are consistently wrong because the model is wrong (Reconciliation). |
| Reproduce the real ingestion pattern — continuous or incremental — rather than a single bulk load, and measure write visibility. | The freshness requirement is met by the whole path, not just by the query engine. | Small-file or part-count degradation, write-visibility lag, background maintenance that cannot keep up with the arrival rate. | Behaviour after months of accumulation, which is when maintenance debt actually shows (File Compaction). |
| Execute the recovery runbook: publish a backfill atomically, restore a table to a prior state, and revoke access to a restricted column. | The operational commitments the platform will have to meet are actually meetable. | A missing atomic-publish path, a recovery window shorter than assumed, an access model that cannot express the required policy. | Whether anyone will still remember the runbook during an incident at 03:00, which no evaluation can test (Rolling Back Data). |
Every row here is a criterion to write down *before* the trial starts. A proof of concept whose success condition is agreed afterwards will succeed, which is why it is worth so little (Data Tests).
Present a comparison table with a winner, a benchmark chart, and a recommendation. Decide, procure, migrate, and defend the choice when someone questions it in eighteen months.
Publish the eight answers per workload, the required capabilities they imply, two or three candidate architectures with what each costs, the acceptance criteria the proof of concept must meet, and the conditions under which the decision should be re-opened. Then run the test and record which criteria each candidate met.
A recommendation is only defensible while its inputs hold, and it carries none of them, so when data volume, consumer count or a new workload changes the answer nobody can tell whether the platform is wrong or merely unfamiliar. Recording the inputs makes the decision arguable on its merits and makes the annual review an hour rather than a project — and it also exposes the case where two candidates are genuinely equivalent, which a table with a winner is structurally unable to say.
How to build it
Most important first.
- Answer the eight questions per workload before naming a product. Write them down; the document is the decision, and it is what makes the choice re-examinable later (Dataset Documentation). Convert answers into required capabilities — "a query floor low enough for a page load", "isolated compute per workload", "atomic publish for backfills", "row-level access policy" — and only then ask which architectures provide them. Capabilities survive product churn; product comparisons do not.
- Expect the answer to be more than one engine, and design the shared substrate accordingly: open storage, one catalog, one set of models. Several engines over one governed copy is a normal mature platform, not a failure of decisiveness (The Lakehouse, Metadata: Technical, Operational and Business).
- Test the shortlist with a replayed production workload at real concurrency and a realistic ingestion pattern, and diff results across engines as well as timings (Load Test Shapes: The Shape Is the Hypothesis, Reconciliation).
- Score operational burden explicitly, with names attached. "Managed" describes what the vendor operates, not what your team stops doing (Shared Responsibility).
- Keep the door open. Data in an open format under a table format means the decision can be revisited without a migration — the single most valuable hedge available in this decision (Open Table Formats, Keeping Raw History: The Recovery Position and the Liability).
- Decide what you are optimising for and say it out loud: time to first dashboard, cost at steady state, lowest operational headcount, or maximum optionality. These pull in different directions and an unnamed objective is decided by whoever argues longest.
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.
- This process guarantees a defensible decision, not a correct one. The value is that the reasoning is written down and can be re-run when the workload changes (Data Platform Engineering).
- It does not guarantee the shortlist contains the eventual answer. Products move between architectural categories, and the category matters more than the name (Comparing Analytical Warehouses).
- It guarantees nothing about cost. Cost is a consequence of the cost-model shape multiplied by your workload and your layout, and it is only knowable after a realistic test (Scan Cost).
- Nothing here guarantees data correctness. A platform decision changes where the data lives, never whether it is right (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 check on this decision is a proof of concept with acceptance criteria written before it starts: the replayed workload, the concurrency, the ingestion pattern, the freshness target, and the specific queries whose results must match the current platform exactly.
- It misses year two. Nothing in a proof of concept exercises what happens when data has doubled, three teams have onboarded, nobody has compacted anything, and the person who set it up has left (Data Platform Anti-Patterns).
- It also misses the operational axis almost entirely, because during an evaluation the platform has the full attention of its most motivated engineer. Score that axis by asking other users, not by observing your own trial (Scoring Operational Complexity).
- The freshness requirement is an input to this decision and one of the two or three that eliminate candidates outright. Getting it wrong in the demanding direction is expensive; getting it wrong in the relaxed direction is a rebuild (Cost vs Freshness).
- Ask it as a decision question, never as a latency number. "The on-call engineer must see an anomaly within minutes" and "finance reviews yesterday each morning" are the same word — real time — and different platforms (The Freshness SLO).
- A platform cannot be fresher than its ingestion path. Choosing a low-latency engine to fix an hourly extract is a misdiagnosis that costs a migration (Incremental Extraction).
- The answers to all eight questions change. Data grows, consumer count grows, a product decides it wants embedded analytics, and a decision that was correct becomes a constraint (Who Actually Consumes This Data).
- Re-run the decision on a schedule — annually is reasonable — as a short review of the written answers rather than a new evaluation. Most years nothing changes and the review takes an hour (Dataset Documentation).
- Design so the re-run is cheap. Open storage, portable SQL and a catalog that is not owned by one engine are what make "we should reconsider" a conversation rather than a project (Open Table Formats).
- Recovery from a wrong platform decision is a migration, and its cost is set almost entirely by how portable the data was. Open files on object storage: expensive but bounded. Proprietary storage: bounded by an export path nobody has rehearsed (Cloud Data Services).
- Rehearse the export before you need it. An export path that has never been run is a plan, not a capability (Validating a Backfill Before You Publish).
- The durable position underneath any platform decision is retained raw data plus deterministic transformations. That is what makes a migration a re-run rather than a reconstruction (Keeping Raw History: The Recovery Position and the Liability, Reprocessing vs Retrying).
What can go wrong
- A decision made at the wrong grain — one platform chosen for "the company" rather than per workload — so one workload is served well and the others are tolerated.
- An evaluation that measured the easy axis. Single-query latency is measurable in an afternoon; concurrency isolation, operational burden and result agreement are not, so they get skipped (Benchmark Fallacies: Confident Numbers That Are Wrong).
- A freshness requirement taken at face value, buying streaming complexity for a daily decision (Batch vs Streaming Ingestion).
- Operational burden assumed away because the product is managed, and then discovered as unstaffed work (Shared Responsibility).
- The mitigation failing too: a written decision document nobody re-reads, so the platform is defended by inertia long after its inputs changed (Data Platform Anti-Patterns).
- A shortlist assembled from products the team has heard of, which quietly excludes the architectural category that fits (No Cargo-Cult Infrastructure).
- "Which warehouse is best?" has no answer, and the question is a category error rather than a hard problem. Best for which workload, at what concurrency, with what freshness, operated by whom (Comparing Analytical Warehouses).
- "We ran a benchmark, so we have evidence." You have evidence about those queries, at that concurrency, on that layout, on that day. Whether it generalises is the whole question and the benchmark cannot answer it (Benchmark Fallacies: Confident Numbers That Are Wrong).
- "One platform for everything is simpler." It is simpler to procure and frequently harder to live with, because a single engine has to serve workloads with genuinely opposite requirements (Query Engines).
- "Managed means no operational burden." It means a different burden. Layout, schema, cost, freshness and correctness never moved (Shared Responsibility).
- "We can migrate later if we need to." Only if the data is portable and the export path has been rehearsed. Otherwise "later" is a rewrite with a deadline set by whatever forced it (Cloud Data Services).
Operating it
- The written answers to the eight questions, dated, next to the decision. This is the artefact that makes the next review cheap and the current choice arguable on its merits (Dataset Documentation).
- Per-workload latency distribution and queue wait against the target set during the evaluation — the evidence that the decision is still correct (Percentiles: Which One, and How Many Users Is That?, Queueing: Why Systems Get Slow Before They Get Broken).
- Cost per workload, in whatever unit the platform's billing shape uses, attributed to a team. Without attribution nobody can tell a growing workload from a degrading one (Cost Attribution).
- The count of workloads the platform is serving badly. It only ever goes up, and it is the signal that the decision needs re-running (Who Actually Consumes This Data).
- At 10x data, the answers to data size and cost-model shape change and the others usually do not. That is a review, not a re-decision.
- At 100x, or when a fundamentally new workload appears — embedded analytics, an operational dashboard, a machine-learning feature store — the decision genuinely re-opens, and a platform designed for one engine over closed storage re-opens it painfully (Feature Pipelines).
- Consumer count is the axis that most often forces a second engine, because concurrency and latency requirements diverge as the audience widens beyond analysts (Who Actually Consumes This Data).
- The dominant cost of this decision is not the platform, it is migration risk: how much it costs to be wrong, which is set by portability rather than by the product (Open Table Formats).
- Evaluation itself costs real engineering time — building the replay harness, loading realistic data, diffing results. It is worth it in proportion to how long the decision will bind (Load Testing: What Question Is This Test Answering?).
- Operating cost, once running, is the cost-model shape multiplied by the workload and the layout. Only the shape is knowable in advance; the rest is measured (What Actually Drives Data Platform Cost).
- The cost of serving a workload on the wrong engine, paid continuously and usually invisibly, as scans that should not happen and dashboards that are slower than they need to be (Compute Waste).
- Running this process costs weeks and produces a less satisfying artefact than a recommendation — a shortlist with conditions. That is the honest shape of the answer, and pretending otherwise is how a preference gets dressed as an evaluation.
- Designing for more than one engine buys fit-for-purpose serving and costs a governance surface that spans several systems, plus the standing risk of two engines disagreeing about the same table (Two Dashboards, Two Numbers).
- Optimising for optionality — open formats, portable SQL, an engine-independent catalog — costs some performance and a good deal of convenience. It is worth it in proportion to how uncertain the requirements are, which early in a platform's life is very (The Lakehouse).
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.
- GENERALThe eight questions and the capability-before-product ordering apply to any analytical platform decision, including on-premise ones and including the decision not to build a platform at all; nothing in the process depends on which products exist this year.
- ORG-SPECIFICOperational burden, cloud constraint and the objective being optimised for are organisational rather than technical inputs, and they routinely overturn the technically-best answer — a platform nobody on the team can operate is a worse choice than a slightly ill-fitting one everybody can.
- SCALE-SPECIFICBelow roughly one machine's worth of data with a handful of consumers, the honest answer is often an in-process engine over Parquet and no platform at all, and every architecture in the decision below is overhead; the questions 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 the guarantees a candidate architecture can and cannot offer across machines, which is what turns a required capability such as atomic publish into a question with a real answer.
- — DevOps / Production Engineering owns executing whatever this decision concludes: parallel running of two platforms, cutover, rollback, and managing warehouse configuration as code so the choice is reproducible.