Compute Waste
Rebuilding history that did not change, refreshing models nobody reads, holding capacity nobody uses, and shuffling data that did not need to move.
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.
Your platform spends most of its compute recomputing things that have not changed. How would you prove that, and which of the four shapes of waste is dominant?
The consumers of a wasteful pipeline are usually satisfied — the data is correct and it arrives on time, which is what they asked for. That is the difficulty. Nobody downstream is going to raise this, so the audience for this lesson is the team that owns the pipeline and the person who will eventually ask why the platform costs what it does (Data Platform Engineering).
The unit is one execution of one transformation over one range of input. Waste is measured by comparing that range against the range that actually changed: a run that reads three years to produce one day has a ratio of about a thousand to one, and the number that matters is that ratio rather than the runtime.
Rebuild the model from scratch on every run: CREATE OR REPLACE TABLE fct_orders AS SELECT … FROM stg_orders. This is the right default and it should be defended, not apologised for. A full rebuild is idempotent by construction, has no watermark to get wrong, no late-data window to tune, no merge semantics to reason about, and it heals silently from any upstream correction. Most teams should start here and most models should stay here.
History grows monotonically and the daily change does not, so the ratio of work done to work needed grows without bound. Nothing about the job changes; it simply reads more each night than it did the night before (Full Refresh vs Incremental).
- History grows monotonically and the daily change does not, so the ratio of work done to work needed grows without bound. Nothing about the job changes; it simply reads more each night than it did the night before (Full Refresh vs Incremental).
- Someone shortens the schedule from daily to hourly because a stakeholder wanted fresher numbers. The full rebuild is now performed twenty-four times a day over the same history, and the code review showed a one-line change to a cron expression (Cost vs Freshness).
- The rebuild runs past its window and overlaps the next one. Two instances now contend for the same warehouse, both slow down, and the platform pays for both while a consumer sees stale data (When a Task Fails Mid-DAG).
- A model is built for a project that ends. Nothing retires it, so it refreshes every night for three years, feeding a dashboard whose last viewer left the company (Data Discovery).
- A cluster is provisioned for the largest job in the platform and stays up between runs, so the platform is charged for capacity during the twenty hours a day it is idle (Idle Capacity: Headroom or Waste?).
- A join between two large tables shuffles both sides across the network because nobody noticed the smaller side would fit in memory and could have been broadcast (Broadcast Joins).
- A
SELECT DISTINCTat the end of a model, added years ago to paper over a duplicate that has since been fixed upstream, forces a full shuffle on every run for no remaining reason (Deduplication).
What is actually happening
- Compute waste is always the same shape: work performed whose output is identical to work already performed. There are four ways to arrive at it — recomputing unchanged history, refreshing output nobody consumes, holding capacity that is not executing, and moving data between machines unnecessarily — and they have different fixes, so naming which one you have is the first step.
- Recomputed history is the largest and the least visible, because its cost is proportional to the age of the platform rather than to anything anyone did recently. Every night the job does slightly more work than the night before, and there is no day on which that becomes noticeable (Incremental Processing).
- Unused output is invisible for a structurally different reason: the work is genuinely necessary *if* someone reads the result, and nothing in the platform records whether anyone does. Creation is self-service, retirement requires proving a negative, so the population of models only grows (Data Ownership).
- Idle capacity is a mismatch between how compute is provisioned and how work arrives. Data workloads are bursty by nature — a schedule concentrates them — so a fixed cluster sized for the peak is idle for most of the interval, and this driver is entirely absent on platforms that charge per query rather than per hour (Fixed vs Variable Cost).
- Shuffle is the one shape that is not repetition. It is data crossing the network between stages because the operation requires re-partitioning by a key, and it is expensive because it is written to disk, read back, and transferred — three physical operations for one logical one (The Shuffle, Narrow and Wide Transformations).
- Skew makes shuffle worse in a way that adding capacity cannot fix. If one key holds most of the rows, one task holds most of the work, and the job finishes when that task does regardless of how many workers are idle waiting for it (Data Skew, Straggler Tasks).
The largest line item is history being recomputed for no reason
On most batch platforms, the single biggest consumer of compute is a set of models that rebuild themselves completely on every run. This is not because anyone chose it; it is because a full rebuild is the obviously correct thing to write, it works perfectly on day one, and its cost grows so smoothly that there is no day on which it becomes a problem.
The arithmetic is worth doing explicitly, because it is the argument that convinces people. A model over three years of history that rebuilds nightly performs, over the course of a month, roughly thirty passes over three years. The information content added during that month is thirty days. The ratio between those two numbers is the whole lesson, and it is a ratio that gets worse every single night without anything changing.
The counter-argument deserves respect. Full rebuilds are idempotent by construction. They heal from any upstream correction with no intervention. They have no watermark, no late-data window, no merge semantics and no state to lose. An incremental model is a strictly harder thing to be correct about, and teams that convert everything on principle end up with quiet row loss in exchange for compute they did not need to save. The recommendation is therefore narrow: convert the heaviest models, validate them against the rebuild, and keep the rebuild path working.
- The condition for conversion is all three of: history is large, history is unchanged, and the model runs frequently. Any model missing one of those should stay a rebuild.
- Validate by running both versions over the same range and comparing row counts and a monetary sum. Anything short of that is a hope (Validating a Backfill Before You Publish).
- Schedule a periodic full rebuild anyway — weekly or monthly — to re-converge from any drift the incremental path accumulated (Reprocessing vs Retrying).
`CREATE OR REPLACE TABLE fct_orders AS SELECT … FROM stg_orders` reads every row of history, recomputes every derived column, re-runs every join, writes the entire table and republishes it. Cost is proportional to all of history. The output differs from yesterday's by one day of rows.
Read source rows whose watermark column exceeds the stored high-water mark, minus an overlap window sized for late arrivals. Transform only those. Merge into the target on the business key so a re-run replaces rather than appends. Advance the watermark only after the merge commits. Cost is proportional to what changed, plus the overlap.
The cost of a rebuild is a function of history and the value it produces is a function of the change, and those two quantities diverge for as long as the platform exists. Incrementality realigns them — at the price of three new things that can be wrong: the watermark can advance past rows not yet committed, the overlap can be too short for genuinely late data, and the merge key can fail to be unique. Each of those is a silent row-level failure, which is why the rebuild path stays in the repository as the recovery mechanism (The High-Water Mark, Upserts and Merges).
Four shapes of waste, and why they need different fixes
It is tempting to treat compute cost as one thing and attack it with one lever, usually cluster size. That fails because the four shapes respond to completely different interventions, and applying the wrong one produces a change that looks like progress and moves nothing.
Repeated work is fixed by changing what a job reads. Unused output is fixed by retirement, which is a governance problem rather than an engineering one. Idle capacity is fixed by provisioning, and is entirely absent on some pricing models. Unnecessary data movement is fixed in the query plan, by broadcasting, pre-bucketing or filtering earlier — and is the only shape where adding capacity can make things actively worse.
The weights below give the ordering that holds most often on a scheduled batch platform. The notes are the operative part: each names the intervention, and the intervention differs in kind every time. A cost programme that does not distinguish these will spend its effort on the one that is easiest to measure.
Fixed by changing the input range a job reads: watermark, overlap window, merge. Grows with the age of the platform and with schedule frequency, and is not affected by cluster size at all.
Fixed by retirement, which requires read-recency evidence and an owner willing to make the call. An engineering fix cannot touch this one; it is a decision problem wearing a cost costume.
Fixed by right-sizing, scale-to-zero and batching small jobs together. Dominant on time-counted platforms and exactly zero on scan-counted ones, which is why this bar moves more than any other between platforms.
Fixed in the plan: broadcast the small side, pre-bucket a repeated join key, filter before joining. The one shape where adding workers can increase cost rather than reduce it.
Fixed by salting the hot key or by splitting it out and handling it separately. Presents as a long job rather than a large one, so it is usually diagnosed as a performance problem and never reaches a cost report.
Fixed upstream, by making the job fit its window and its retries idempotent. A job that fails halfway and retries from the start pays for its own failure twice, and a job that overlaps its successor pays for contention as well.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights on a modelled scheduled-batch platform. The ordering is the teaching; so is the fact that each bar has a different kind of fix attached, which is why "reduce compute cost" is not an actionable instruction.
Finding waste you cannot see
Every shape of waste above is invisible at the observation points a normal platform has. The job succeeded. It finished inside its window. Its output is correct. No consumer complained. There is nothing to detect, in the ordinary sense, because nothing has gone wrong — the platform is doing exactly what it was told, and what it was told is more than was needed.
So the checks here are not failure detectors; they are ratio detectors. Each one compares two quantities that ought to be related and flags when they are not: work done against information produced, capacity held against capacity used, data moved against data needed, output produced against output consumed. A ratio that drifts is the only signal available.
As always, the misses column carries most of the value. Every one of these checks scores a different kind of waste perfectly while being completely blind to the others, so running one and declaring the platform audited is worse than running none — it establishes a false all-clear that survives for a year.
| Check | Expresses | Catches | Still misses |
|---|---|---|---|
| Rows read divided by rows written, per model, trended over months. | The job reads roughly what it needs to produce its output. | Full rebuilds of growing history, a schedule shortened without an incrementality change, and a filter that stopped being selective after an upstream change. | Idle capacity, which reads no rows and scores perfectly; shuffle-heavy jobs, which can have an ideal ratio while moving an enormous amount between stages; and anything whose engine does not report row counts. |
| Worker-seconds held divided by worker-seconds executing, per cluster. | Capacity you are paying for is doing work. | Clusters kept warm between scheduled runs, autoscaling that never scales down, and a cluster sized for one nightly peak and used for nothing else. | Everything about whether the work being executed was necessary. A cluster busy for twenty hours recomputing 2019 scores as fully utilised, which is the most expensive possible way to pass this check. |
| Days since last read, per output table, retained longer than the longest-period consumer. | The thing this pipeline produces is being consumed. | Abandoned models, dashboards nobody opens, experiment outputs that were never retired, and tables kept alive only by another unused model reading them. | Quarterly and annual consumers if the retention window is shorter than their interval — the failure that makes a retirement programme break a regulatory report. It also misses value: a table read daily by a job that produces nothing anyone uses still scores well (Data Lineage). |
| Shuffle bytes per run, with the largest task compared against the median task. | Data moves between workers only as much as the operation requires, and the work is spread evenly. | A join that shuffles both sides where one could have been broadcast, an unnecessary distinct or sort, and a hot key concentrating a stage into a single straggler. | Repeated work entirely — a perfectly balanced, minimally shuffling job that recomputes three years of history every night passes this check without qualification (Data Skew). |
| Job runtime against daily input volume, trended. | The work grows with the data, not with the calendar. | The slow accumulation of rebuild cost, and layout degradation as appends erode clustering. | Anything that got more expensive without getting slower, which includes every increase caused by adding parallelism, and every increase in a job that was already bounded by a straggler (Straggler Tasks). |
Run at least the first three. Each covers one shape and is confidently blind to the others, so the portfolio is the point — a single ratio check produces an all-clear that nobody revisits.
How to build it
Most important first.
- Convert the heaviest models to incremental processing, heaviest first, and stop. Incrementality has a real correctness cost and it is not worth paying on a model whose full rebuild reads a small amount (Incremental Processing).
- Make the incremental boundary explicit and durable: a high-water mark stored outside the job, an overlap window for late arrivals, and a merge that is idempotent under re-run. Without all three, an incremental model is a full rebuild that occasionally loses rows (The High-Water Mark, Upserts and Merges).
- Keep the ability to do a full rebuild. Incremental models drift — a bug, a missed window, a late correction upstream — and the periodic full rebuild is what re-converges them. Deleting that path to save the compute is a false economy (Reprocessing vs Retrying).
- Record days-since-last-read per model and retire anything unread past your longest-period consumer's interval. The interval matters: a quarterly model looks abandoned to any thirty-day report (The Data Catalog).
- Right-size before you re-architect. Most overprovisioning is a cluster sized once for the worst job and never revisited, and the fix is a configuration change with a rollback (Autoscaling Signals).
- Attack shuffle structurally rather than by adding workers: broadcast the small side of a join, pre-bucket on the join key if the join is repeated, filter before the join rather than after, and salt keys that are genuinely skewed (Broadcast Joins, Bucketing, Salting a Skewed Key).
- Batch small scheduled jobs onto shared capacity rather than starting an engine for each. On time-counted platforms the start-up is charged; on all platforms it is latency the consumer feels (Orchestration).
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 full rebuild guarantees that the output is a pure function of the current input. That is a genuinely strong property and it is what makes rebuilds so hard to argue against on correctness grounds (Full Refresh vs Incremental).
- An incremental model guarantees nothing about rows outside the window it processed. Anything corrected upstream in an older period stays wrong until something reprocesses it, and no error is raised (Late-Arriving Data).
- Idempotency is not automatic on either side. A rebuild is idempotent because it replaces; an incremental append is idempotent only if the write is a merge on a stable key (Idempotent Data Pipelines).
- Nothing guarantees that removing a model removes its cost. If it fed another model that is still refreshed, the upstream work continues, and the saving is only realised when the whole branch is retired (Data Lineage).
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 that exposes recomputed history is the ratio of rows read to rows written per run. For an incremental model it is roughly flat over time; for a full rebuild it grows in step with history, and the trend line makes the argument without anyone having to read the SQL.
- It misses waste that is not repetition — an idle cluster reads no rows at all and scores perfectly, and a shuffle-heavy job can have an excellent ratio while moving an enormous amount of data between stages.
- Pair it with a read-recency check per output table and a shuffle-bytes-per-run metric. The three together cover the four shapes; each alone covers one and gives false confidence about the others (Pipeline Metrics).
- None of these say anything about whether the output is right. A model can be perfectly incremental, cheap, fresh and producing a number that no longer means what its consumers think it means (Semantic Changes).
- Incrementality usually improves freshness as a side effect: a job that reads one day finishes sooner than a job that reads three years, so the same schedule delivers results earlier in the window.
- It also introduces a new staleness that the rebuild did not have. Corrections that arrive for old periods are not picked up until something explicitly reprocesses them, so the model is fresh at the head and can be quietly wrong in the tail (Backfills).
- Retiring an unused model has no freshness cost by definition, which is what makes it the only unambiguous win in the lesson — provided the "unused" determination looked back far enough (Impact Analysis).
- A schema change usually forces a full reprocess of the affected model, which is a one-off cost that has to be budgeted rather than avoided. Trying to avoid it by patching forward produces a table with two meanings in one column (Schema Evolution).
- When a model becomes incremental, its evolution story changes: adding a column now requires backfilling it for history, where a rebuild would have populated it everywhere on the next run. This is a real ongoing cost of incrementality and it is rarely stated up front (Planning a Backfill).
- Retiring a model is a schema change from every consumer's point of view, and the consumers you do not know about are exactly the ones that make retirement risky. Announce, then break, then delete (Contract Enforcement).
- The safest way to make a model incremental is to run both versions in parallel over the same period and compare, rather than to cut over and hope. The comparison is the only evidence that the watermark logic is correct (Validating a Backfill Before You Publish).
- Keep the full-rebuild path in the repository even after incrementality lands. It is the recovery mechanism for every incremental bug, and a path that has not been run in a year does not work (Reprocessing vs Retrying).
- Retirement should be reversible for a period: stop the schedule, keep the table, wait longer than your longest-period consumer, then delete. Deleting on the day you stop refreshing removes your ability to undo a wrong call (Data Retention).
- Right-sizing is trivially reversible and should be treated as such — change the size, watch the runtime and the spill, change it back if the job starts spilling to disk (Partitions: the Unit of Parallelism).
What can go wrong
- An incremental model that silently misses rows whose transaction committed after their timestamp was assigned, so the window that looked closed was not (Incremental Extraction).
- A watermark stored inside the job rather than outside it, so a re-run from a clean environment reprocesses everything or nothing.
- Right-sizing taken too far: a cluster small enough that the shuffle spills to disk, which costs more hours than the larger cluster did (The Shuffle).
- A model retired that fed a quarterly regulatory report, discovered at quarter end (Impact Analysis).
- The mitigation failing: a rows-read-to-rows-written metric computed only for jobs that already report row counts, which excludes exactly the opaque ones most likely to be wasteful.
- Autoscaling configured to scale up but never meaningfully down, which converts a fixed cost into a ratchet (Autoscaling).
- "Full rebuilds are an anti-pattern." They are the correct default for most models and the correct recovery path for all of them. The anti-pattern is rebuilding history that is large, unchanged and recomputed frequently — three conditions, not one (Full Refresh vs Incremental).
- "The job takes four minutes, it cannot be significant." Multiply by the schedule and by the number of similar jobs. Frequency is the multiplier that turns unremarkable jobs into the largest line in the platform.
- "Adding workers will make it cheaper because it finishes sooner." On time-counted platforms this is sometimes true and sometimes exactly inverted; if the job is waiting on one straggler, extra workers are idle capacity you are paying for (Data Skew).
- "We deleted the dashboard, so the cost is gone." The model feeding it still refreshes. Retirement has to walk the lineage upstream or it removes the symptom and none of the work (Data Lineage).
- "Autoscaling handles this." Autoscaling handles capacity matching demand. It has no opinion at all about whether the demand was necessary, and a wasteful job that autoscales is a wasteful job that finishes faster (Autoscaling Signals).
Operating it
- Rows read divided by rows written, per model, per run, trended. The single most informative cost metric in a batch platform (Pipeline Metrics).
- Worker-seconds held versus worker-seconds executing, per cluster. The gap is idle capacity, and on time-counted platforms it is a direct line item (Idle Capacity: Headroom or Waste?).
- Shuffle bytes per stage, with the largest task compared against the median. A wide gap is skew and no amount of extra capacity will close it (Data Skew).
- Days since last read per output table, retained for longer than your longest-period consumer (The Data Catalog).
- Job runtime against input size over time. A runtime that grows while daily input is flat is a rebuild announcing itself (Regression or Tuesday? Telling a Real Change from Noise).
- At 10x history, full rebuilds cost ten times as much to produce the same amount of new information. Incrementality stops being an optimisation and becomes the only schedulable option (Incremental Processing).
- At 10x models, unused output becomes the dominant waste, because the population grows faster than anyone's ability to remember which ones matter (Data Discovery).
- At 10x concurrency, idle capacity falls on its own — a busier platform wastes less of what it holds — while shuffle contention rises, so the dominant shape flips (Workload Isolation).
- Skew does not improve with scale. Ten times the data with the same key distribution gives ten times the straggler, and the job is still waiting for one task (Straggler Tasks).
- Work repeated over unchanged history: the input range read per run multiplied by the number of runs, where the numerator grows with the age of the platform and the denominator is set by a cron expression.
- Output produced for no consumer: the full production cost of a model, charged in perpetuity, with zero offsetting value and no signal that this is the case.
- Capacity held but not executing, which is charged on time-counted platforms and is exactly zero on scan-counted ones.
- Bytes moved between workers by wide transformations, written and read at both ends of the exchange, and made worse by skew in a way that additional workers cannot offset.
- Incrementality trades a large, permanent compute saving for a permanent increase in the complexity of being correct. Watermarks, late data, merge semantics and backfill paths are all new surface, and every one of them is a way to lose rows silently (Full Refresh vs Incremental).
- Retiring models trades cost against optionality. The dataset that turns out to be needed after all costs a rebuild and a delay, and occasionally costs history that cannot be reconstructed.
- Right-sizing trades headroom for utilisation. A cluster sized for the median job is cheaper and fails on the tail, and the failure mode — spilling, retrying, overlapping the next run — can cost more than the headroom did (Retries in Pipelines).
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 four shapes — repeated work, unused output, idle capacity, unnecessary data movement — are properties of scheduled batch computation and appear on every platform. Which of them you can see depends on what your engine reports, not on whether it is present.
- ENGINE-SPECIFICShuffle behaviour, spill thresholds and broadcast decisions differ sharply between engines, and some optimisers will convert a join to a broadcast automatically while others require a hint. The rule that survives is to look at the stage boundaries the engine reports rather than to assume a plan.
- WAREHOUSE-SPECIFICIdle capacity is a real and often dominant driver on platforms that charge for a running cluster, and exactly zero on platforms that charge per query. Advice to shut down warehouses between runs is meaningless on the second kind and important on the first.
- SCALE-SPECIFICBelow a certain history size, a full rebuild is cheaper in total cost of ownership than an incremental model, because the engineering time and incident risk of watermarks and merges exceeds the compute saved. That crossover is real and teams cross it in the wrong direction routinely.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the capacity and autoscaling policy that decides whether idle compute is charged at all, and the change process that makes a right-sizing revertible.
- — Distributed Systems owns why re-partitioning data by key across machines is an expensive operation with its own failure modes rather than an implementation detail.