OLAP Workloads
Few queries, each reading a large range of history and collapsing it into a handful of numbers. Judged on throughput rather than latency, bound by bytes moved rather than by seeks, and unhelped by almost every index you could add.
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 query actually ask for, and why does no index make it fast?
An analyst, a finance close, an experiment readout, a training set, an agent's retrieval corpus, a dashboard tile. None of them wants a row. Each wants an aggregate over a range, computed the same way every time, and comparable with the same aggregate computed last month (Who Actually Consumes This Data).
The input grain is one fact — one order, one session, one event — and the output grain is one group: one country-month, one cohort-week. The workload *is* the move from the first grain to the second, and most analytical bugs are a disagreement about which one a given number is at (Grain: What Does One Row Represent?).
Write the analytical query as ordinary SQL against whatever tables exist, add an index when it is slow, and add hardware when the index does not help. It is the same language against the same-looking tables, so it ought to be the same kind of problem with the same kind of fix.
The index is not used and the planner is right: a predicate matching most of the table is cheaper to satisfy by scanning than by hundreds of millions of index lookups, each of which is a random read (Cost-Based Optimization, An Index Scan Is Not Automatically Faster).
- The index is not used and the planner is right: a predicate matching most of the table is cheaper to satisfy by scanning than by hundreds of millions of index lookups, each of which is a random read (Cost-Based Optimization, An Index Scan Is Not Automatically Faster).
- A BI tool generates
SELECT *against a hundred-column table to compute an average of one column. In a row-oriented store there is no way to *not* read the other ninety-nine (Row vs Column Storage). - The query joins the fact table to a dimension whose key is not unique, and every measure is silently multiplied. It is fast, it succeeds, and revenue is reported as several times its real value (Fact Tables, Duplicate Rows).
- Two analysts compute "active users" two defensible ways and both numbers reach the same executive. Nothing failed; the workload simply has no definition layer (The Metrics Layer, Two Dashboards, Two Numbers).
- The query is quick in development against three months of data and impossible in production against five years, because runtime scales with bytes read and nothing about the query got more selective (Scan Cost).
What is actually happening
- An analytical query is a scan plus a reduction. It reads a large contiguous range, applies a filter, projects a few columns and collapses many rows into few using a hash aggregate or a sort (How a Query Executes: Planner and Executor, Aggregation: COUNT, SUM, AVG, GROUP BY, HAVING).
- Because the read is large and sequential, the binding resource is I/O and memory bandwidth rather than seek count. The question stops being "how many pages did we touch" and becomes "how many bytes had to travel" (When the Memory Bus Is the Bottleneck, Latency and Bandwidth Are Different Resources).
- Selectivity is why indexes stop helping. An index converts a scan into a set of lookups, which wins only while the set is small relative to the table. Past a threshold the planner switches to a scan deliberately, and forcing the index back makes the query slower (Sequential Scan, Page by Page).
- What does help is reading less: fewer columns, fewer files, fewer blocks, fewer partitions. Every analytical optimisation is a version of the same sentence — *prove you are allowed to skip these bytes* (Projection Pushdown, Predicate Pushdown, Partition Pruning).
- Concurrency inverts. An operational system multiplexes thousands of tiny units of work across shared resources; an analytical one splits one enormous unit across many workers and worries about the slowest of them (Distributed Query Execution, Straggler Tasks).
- The data is append-mostly and effectively immutable once written, and that is the precondition for everything else: columnar layout, aggressive encoding, per-block statistics and precomputation all assume the rows do not change after they land (Why Analytical Data Compresses).
The shape of an analytical query
Here is the whole workload in one statement. Read it looking for a key, and notice there is not one — the WHERE clause is a date range that matches most of the table, and the GROUP BY is over a dimension with a few hundred values. There is nothing selective for an index to exploit.
What the query does name is four columns, out of however many the fact table has. That is the fact the physical design has to exploit, because it is the only lever available: the number of rows in range is fixed by the question, so the only remaining question is how many bytes each of those rows costs to touch.
This is why the analytical version of "make it faster" almost never means "restructure the SQL". It means change what the storage layer is obliged to read — a decision made in the layout, the format and the model, long before anyone opened a query editor.
1SELECT d.country,2 date_trunc('month', f.order_ts) AS month,3 sum(f.net_revenue) AS revenue,4 count(DISTINCT f.customer_id) AS buyers5FROM fct_orders f6JOIN dim_customer d ON d.customer_key = f.customer_key7WHERE f.order_ts >= DATE '2021-01-01'8GROUP BY 1, 29ORDER BY 1, 2;Five things this query does not have: a primary key predicate, a selective filter, a LIMIT, an expectation of sub-second latency, and any interest in ninety percent of the columns in fct_orders. Each absence rules out an operational technique and points at an analytical one.
Why the index does not save you
An index is a bet on selectivity. It says: I can find the matching rows without looking at the others, and the cost of the lookups will be lower than the cost of the scan. That bet pays enormously at a hundred matching rows and loses badly at a hundred million, because each lookup is a separate random access and the scan is one long sequential read.
So the planner declines it, and the interesting question becomes what else can reduce the bytes read. There are four answers, they compose, and they are worth learning in order of effect, because teams routinely spend a quarter on the smallest one.
The ordering below is relative and directional rather than measured. What transfers is not a magnitude but a sequence and a set of conditions: pruning helps only if the predicate matches the partition key, projection helps in proportion to the columns you avoid, statistics help in proportion to how sorted the data is, and encoding helps in proportion to how repetitive each column is.
Skips whole directories before a single file is opened. The largest lever available — and it does nothing unless the query's predicate matches the column the data was partitioned by.
Scales with the fraction of columns the query does not name. Four of ninety is close to the whole win; four of five is almost nothing. Requires a columnar layout to be physically possible at all.
Skips within a file. Effective in proportion to how sorted the data is on the predicate column — on randomly ordered rows every block's range covers the predicate and nothing is skipped.
Reduces bytes moved for the columns you could not avoid. Helps in proportion to how repetitive the column is; a high-cardinality identifier has little for an encoder to exploit.
Only helps a selective predicate, which analytics mostly does not have. Meanwhile it is maintained on every write, so it makes the ingestion path slower for a benefit the planner usually declines.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative ordering within one comparison for a partitioned columnar table — not measurements, and not transferable to a specific dataset or bill. Read it as a checklist in sequence: prune, then project, then skip blocks, then encode. Notice the operational answer, the index, is last.
Fast, successful, and wrong
The failure signature of this workload is the opposite of the operational one. Nothing times out, nothing errors, nothing pages. A query returns a number with the right units, the right order of magnitude and the right shape of trend, and it is wrong. It is discovered by a human who knows the business, usually weeks later, usually in a meeting.
The defence is a small portfolio of checks, each of which expresses one thing you believe about the data. What makes the portfolio a portfolio rather than a wall of assertions is the misses column: every check has a blind spot, and choosing checks is choosing which blind spots you can live with.
Read the table below by its last column. Four checks, four residual risks, and the residual risk that survives all four — that the metric does not mean what its label says — is not addressable by tests at all. It is addressed by documentation, ownership and someone who knows what "active user" is supposed to mean (Who Owns Data Quality).
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
count(*) equals count(distinct order_id) on fct_orders | The table is at the grain it claims: one row per order. | Fan-out from a join against a dimension with duplicate keys; a re-run that appended where it should have replaced. | A missing order entirely — both counts drop together and stay equal, so the assertion holds while the table is incomplete. |
Sum of net_revenue for a closed month against the operational source | Completeness and value fidelity for a period that can no longer change. | Missing partitions, a dropped status category, a unit or currency error introduced during transformation. | Anything wrong identically in both systems; anything in the current open month; every column that is not the one being summed. |
Row count per country against the same weekday four weeks ago | The distribution across the dimension still looks like itself. | A category that collapsed to null when a join key changed; a source that quietly stopped sending one region. | A slow drift across every category at once, which looks exactly like a business trend and sometimes is one. |
Every customer_key in the fact exists in dim_customer | The join will neither drop rows nor orphan them. | Late-arriving dimension rows; a surrogate key generated from the wrong natural key. | Keys that resolve to the *wrong* customer. The join succeeds, referential integrity holds, and the attribution is wrong. |
Each of these is one query and costs almost nothing to run. Their value is not that they prove the data is right — nothing does — but that they convert a class of silent failure into a loud one, which is the only kind an on-call rotation can act on.
How to build it
Most important first.
- Model for the question. A denormalised fact table with conformed dimensions exists so that the common query is a scan plus two joins rather than eleven (Star Schema, Dimension Tables).
- Declare the grain of every fact table in writing and then test it.
count(*)disagreeing withcount(distinct order_id)is the cheapest grain test there is, and it catches the single most common wrong-number cause in analytics (Grain: What Does One Row Represent?, Data Tests). - Store columnar so that projection is physically possible, and lay the files out so the common predicate can skip most of them before anything is decoded (Parquet, Partitioning, Clustering and Sort Order).
- Push the filter as far down as it will go: into the partition path first, then into file statistics, then into the row group, and only then into the scan (Source Pushdown).
- Define shared metrics once, in one layer, instead of once per dashboard. The alternative is not inconsistency you can find — it is inconsistency nobody can adjudicate (The Metrics Layer).
- Make repeated heavy aggregates incremental rather than recomputing all of history every night because the last day changed (Incremental Processing, Compute Waste).
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.
- A query returns a consistent answer over the snapshot it read. Whether that snapshot is *complete* for the period asked about is a property of the pipeline, not of the engine (Atomic Publish).
- Repeatability is not free. The same SQL re-run tomorrow returns a different answer if late data arrived, if a dimension was overwritten, or if a backfill republished a partition — and none of those is an error anywhere (Late-Arriving Data).
- Nothing guarantees the aggregate is at the grain you believe.
sum()over a fanned-out join succeeds exactly as quickly, and looks exactly as plausible, assum()over a correct one. - Ordering has no meaning at this stage. Rows reach the aggregate in whatever order the scan emitted them, which is why every analytical result must be order-independent to be correct at all.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The characteristic check is a grain assertion plus a reconciliation: assert that the fact table's declared key is unique, and compare a summed measure for a closed period against the operational source (Reconciliation).
- It misses anything wrong identically in both systems, anything in a period that is still open, and every semantic error — a correctly summed
revenuethat switched from gross to net reconciles beautifully and reports a different quantity (Semantic Changes). - It also misses the distribution. Totals can match while one category collapsed to null and another absorbed its rows, which is a failure no sum will ever see (Distribution Tests).
- This workload tolerates staleness that would be unacceptable operationally, and that tolerance is what buys everything else — batching, columnar rewrite, compaction, precomputation (Cost vs Freshness).
- What it cannot tolerate is *unknown* staleness. "Complete through midnight UTC yesterday" is a usable contract; "roughly current" is not, because no analyst can tell whether a dip is a business event or a missing partition (The Freshness SLO).
- The question to ask a consumer is never "how fresh do you want it" — the answer is always "very" — but "what decision changes if this is six hours old". Most dashboards that demand minutes are read once, in the morning.
- Analytical models are read by consumers you cannot enumerate, so a column rename here is a breaking change with an unknown blast radius. Impact analysis is the difference between knowing that and hoping (Impact Analysis).
- Adding a column is usually safe. Changing what an existing column *means* is the change that destroys trust, because every dashboard keeps working and starts lying (Semantic Changes).
- Schema evolution here is also *history* evolution: a new dimension attribute has no value for rows written before it existed, and deciding whether that is null, an explicit "unknown", or a backfill is a modelling decision with reporting consequences (Nullability & Defaults).
- Recovery is recomputation. If the inputs were retained and the transformation is deterministic, a wrong analytical table costs a bounded re-run and nothing else (Planning a Backfill).
- What makes it hard is that consumers are reading the table while you rebuild it, so the rebuild must write elsewhere and publish atomically rather than overwriting in place (Atomic Publish).
- Recomputation is only faithful if the transformation does not depend on the current time or on a dimension that has since been overwritten. Both are extremely common, and both make history quietly un-reproducible (SCD Type 2 in Practice, Idempotent Data Pipelines).
What can go wrong
- A fan-out join multiplying measures — the most common wrong-number cause in analytics, and it never raises an error (Duplicate Rows).
- A filter that excludes a status category which did not exist when the filter was written (Missing Rows).
- A partition that was never written, so the period looks quiet rather than absent (Volume Anomalies).
- One key holding most of the rows, so one worker decides the runtime of the whole query (Data Skew).
- The mitigation failing: a pre-aggregated mart that drifts from the model it derives from, so the dashboard and the ad-hoc query disagree and both are defended by someone senior (Data Marts).
- "OLAP means slow queries." It means large ones. A well-laid-out columnar scan over a pruned range answers interactively; a badly laid-out one over the same data never finishes (Partition Pruning).
- "Add an index." Indexes address selectivity, and analytics is mostly not selective. The analytical equivalent of an index is the layout — partitioning, sort order and per-block statistics (Clustering and Sort Order).
- "A warehouse is our database with more rows in it." It has a different storage layout, a different execution model, different concurrency assumptions and a different attitude to updates. That the SQL dialect looks familiar is the least important thing about it (The Data Warehouse).
- "
SELECT *is fine, the engine will optimise it." In a columnar systemSELECT *is the one construct that defeats the format's main advantage, because it forces every column chunk to be opened (Projection Pushdown).
- Analytical stores concentrate data that was scattered across operational systems, and they are read by far more people than any one of those systems was. The aggregate is often less sensitive than the row, and the row is usually still sitting there (Data Classification, Data Access Control).
- Retention here is a design decision rather than an accident: an analytical table keeps history deliberately, which is precisely the thing a retention policy exists to bound (Data Retention).
Operating it
- Bytes scanned per query, attributed to the query and to the person who ran it. It is the single most actionable analytical metric and almost nobody puts it in front of the people who move it (Scan Cost, Cost Attribution).
- Files or partitions pruned versus files read — the one ratio that tells you whether your physical layout is doing any work at all (Partition Pruning).
- Row count and a summed measure per period tracked over time, so a republish that quietly changes history becomes visible instead of being discovered in a board meeting (Volume Anomalies).
- Queue time separately from execution time. On a shared analytical cluster, waiting is often the larger half and it responds to completely different fixes (Queueing: Why Systems Get Slow Before They Get Broken).
- At 10x, the query that read the whole table becomes the query that must not. Partitioning and sort order stop being optimisations and become the difference between possible and not (Physical Data Layout).
- At 100x, single-machine execution ends and the shuffle becomes the dominant term, which changes the modelling advice: broadcast the small dimensions, avoid joins on high-cardinality keys, pre-aggregate what is asked repeatedly (Broadcast Joins).
- Consumer count scales differently again. A hundred dashboards against one model make caching, marts and concurrency limits the binding constraint long before raw scan speed is (Data Marts).
- Bytes scanned dominates, and it is set by the physical layout and by which columns are referenced — not by how elegantly the SQL is written (What Actually Drives Data Platform Cost).
- Bytes shuffled is second, set by joins and wide aggregations, and made worse by skew because one worker then moves far more than its share (The Shuffle).
- Repeated work is third and the most avoidable of the three: recomputing five years of history every night because the most recent day changed (Compute Waste).
- Everything that makes this workload fast makes single-row updates expensive. Columnar chunks, large row groups, sorted layouts and heavy encoding all assume data does not change after it is written, and each has to be partially undone to support an update (Upserts and Merges).
- Denormalisation buys scan-friendly queries and costs the ability to change one dimension value in one place (Denormalization on Purpose).
- Precomputation buys query time and costs freshness, storage, and one more artefact that can be stale while looking authoritative.
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.
- GENERALScan-plus-reduction, low selectivity, throughput as the success metric and immutability as the enabling assumption hold across every analytical engine. What differs is how each engine splits, schedules and caches the scan.
- WAREHOUSE-SPECIFICHow concurrency is bought differs architecturally: some warehouses run independent compute clusters over one shared storage layer, so queries from different teams never queue behind each other, while others allocate from a shared pool of execution slots, so a large query genuinely delays a small one. The same SQL can therefore be interactive in one and queued in the other.
- ENGINE-SPECIFICWhether an aggregate spills to disk, streams, or fails outright when it exceeds memory is an engine property, not a workload property. A
GROUP BYon a high-cardinality key is a mild slowdown in one engine and an out-of-memory failure in another with identical data.
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 happens when the scan is spread over many machines — partial failure mid-query, stragglers, and whether a retried task can be safely double-counted into an aggregate.
- — DevOps / Production Engineering owns how a change to a transformation reaches production: review, CI on the model, a staged rollout, and the rollback that puts the previous definition of a metric back.