Why nightly jobs cost more than they should

Correct output says nothing about cost: a nightly job should cost in proportion to what changed since yesterday, not to everything that exists.

What it means for the business

Measure the gap first. For each nightly job, compare the rows it writes with the rows that actually change. The difference is roughly how much writing the job does for nothing. What that costs depends on the platform: in some it is a bill, in others a database that grows and slows down the queries everyone else runs. Making a job cheaper costs engineering time, so start with the biggest gap, not with every job.

The first fix is often one condition. A common version of the problem needs no theory at all: a job that writes a value that is already there. Asking "did this row actually change?" before writing it removes that work and leaves the result exactly as it was. In a database that is the saving; in a warehouse billed by what a job reads, the saving comes from narrowing what the job covers, such as the days that can have changed.

Some questions are expensive by nature. Latest status per customer, top ten, biggest anything. They are reasonable questions, but they stay cheap to keep current only by keeping more than the answer: the rows behind it, or a reserve of runners-up that is refilled now and then. They should be few, and asked knowingly.

Corrections have to go somewhere. A sale moved from March to April has to leave March. If the pipeline cannot record a removal, it either recomputes every total the correction touches or stays wrong about last month. Ask which.

Prove the fast version against the slow one. A result updated from changes alone must equal a full rebuild. Run both side by side for a while, through corrections and re-runs, and treat every row where they differ as a bug. The typical one is double counting: a change applied twice, because a sender retried or a failed job was re-run after applying half its changes. The totals still look plausible when that happens.

Three questions to ask your data team:

1. For each nightly job, how many rows does it write, and how many actually change?
2. Do our updates skip rows that already have the new value?
3. Where a job updates only from changes, how does it handle corrections and re-runs, and when was its result last compared with a full rebuild?

The theory, for the technical reader

Why an unchanged row still costs

In a database such as PostgreSQL, an update writes a new version of the row even when no value in it changes; the old version stays until a cleanup process (vacuum) removes it. Every unnecessary update is a write, a log entry, often index work and later cleanup, and a table that takes billions of them grows and slows the queries that read it. PostgreSQL even ships a trigger whose only purpose is to skip such updates. Cloud warehouses price the same mistake differently. BigQuery's on-demand pricing bills an update by the data scanned plus the size of the table it modifies, so a condition that skips unchanged rows saves little on the bill there; the saving comes from narrowing what the update covers, for example to the partitions that can have changed. Snowflake bills the time a warehouse runs. The question "how much does the job write for nothing?" is the same everywhere; the answer is priced in a different currency.

Changing only what changed

Skipping unchanged rows is the easy case. The harder one is a report, a calculation on source data, that is rebuilt from scratch every night although only a little of the source changed. Ideally the work to update it would follow the change rather than the size of all the data. Whether that is possible depends on the calculation. Database researchers have studied the question, called incremental view maintenance, since at least the 1980s. In 2023 Budiu and colleagues published DBSP, a general recipe: if a calculation is built from a small set of standard steps, a computer can work out on its own how to update it from changes alone, and how much it has to store to do so. It won a best paper award at VLDB, one of the field's two main conferences, that year.

Three kinds of calculation

For practical purposes, the calculations in a report fall into three kinds.

The cheap kind can be updated from the changed rows alone: sums, counts, filters, and appending one list to another. If ten sales were added, the total goes up by those ten; nothing from yesterday has to be read again.

The middle kind is matching two tables against each other, say sales against customers. A changed sale has to be looked up in the customer table, and a changed customer in the sales table, so both tables have to be kept, with a fast way to look rows up. The work follows the change and the rows it matches, not the whole table; a changed customer with a million sales still touches a million results.

The expensive kind depends on how the rows in a group compare with each other: the biggest deal per region, the top ten, the latest status per customer. Adding is cheap: a new biggest deal simply replaces the old one. The problem is removal. If the biggest deal is withdrawn or corrected, the report needs the second biggest, and only a system that kept more than the winner can find it quickly. So these calculations need more stored than the result itself: either every row in the group, or a reserve of the next few candidates that is refilled from the full data when removals empty it (Yi and colleagues, 2003). The median is harder still: even additions move it, so every value has to be kept.

Corrections as negative rows

A correction, say a sale recorded in the wrong month and moved, is best handled as two changes: minus one in the old month, plus one in the new. DBSP represents the minus as a row with a negative count, so a removal flows through the same steps as an addition; bookkeeping has always worked this way, with reversing entries. For sums and counts that is the whole fix. For the expensive kind it is not enough on its own: removing the biggest deal still needs the runners-up.

Exactly once

A system that updates from changes has to apply each change exactly once, which takes either changes that carry an identity so a repeat can be recognised, or updates that give the same result when applied twice. A full rebuild does not have this problem, which is a fair point in its favour, and it is why the comparison against a rebuild is the test that matters most. It proves the fast version for the inputs it was run on, so it should run through at least one round of corrections and re-runs before the rebuild is switched off.

Sources

• Blakeley, J. A., Larson, P.-Å. and Tompa, F. W. (1986). Efficiently updating materialized views. SIGMOD, 61–71.
• Gupta, A. and Mumick, I. S. (1995). Maintenance of materialized views: problems, techniques, and applications. IEEE Data Engineering Bulletin 18(2), 3–18.
• Budiu, M., Chajed, T., McSherry, F., Ryzhyk, L. and Tannen, V. (2023). DBSP: automatic incremental view maintenance for rich query languages. PVLDB 16(7), 1601–1614.
• Yi, K., Yu, H., Yang, J., Xia, G. and Chen, Y. (2003). Efficient maintenance of materialized top-k views. ICDE.
• PostgreSQL documentation: Routine vacuuming; suppress_redundant_updates_trigger.
• Google Cloud documentation: BigQuery DML pricing. Snowflake documentation: understanding compute cost.