The partition key that cost $60,000 a month

The job didn't fail. That was the problem.

A pipeline I owned ran fine for about a year, and then it started taking longer. Not dramatically. A few minutes more one month, a few more the next. Nothing alerted, because nothing was broken. Nobody complained, because the numbers still landed before anyone needed them.

The compute bill had been climbing that whole time. Working out why took an afternoon. Working through it took a few months — and by the end we had taken $60,000 a month off the bill.

It was one column — the one we had picked to partition on.

The decision that looked right

We were loading more than a thousand tables into Delta. Some of it historical, some of it ongoing change feeds. Ordinary warehouse work, at a scale where nothing about it can be done by hand.

Every table was partitioned by processing time: the moment our pipeline wrote the row. Not the moment anything happened in the business.

I want to defend that choice properly, because it wasn't careless and it wasn't lazy. Processing time is always there. It's never null, never malformed, never needs cleaning, and it doesn't vary by source. When you're onboarding a thousand tables through one framework, a key that behaves identically everywhere is worth a great deal. Nobody was going to sit down and choose a partition column a thousand times.

There was also no obvious column to choose instead. Several teams consumed this platform, and they queried the same tables in different ways — different filters, different grains, different questions. There was no single read pattern to optimise for. Pick any business date and you'd be picking a winner among consumers, and the losers would still scan everything.

So we optimised the merge instead.

It's worth being precise about what a merge is, because the name makes it sound like a write. It isn't. Applying a batch of changes to a Delta table is a read, followed by rewriting whatever that read touched. The engine has to work out, for every incoming row, whether a matching row already exists in the target, which existing rows are unaffected, and which need deleting. That classification is a scan. The writing at the end is cheap. The scan is the bill.

So the only lever that matters is how much of the target the engine has to scan, and ours didn't scan all of it. For each incoming batch we first queried the table to find which partitions those keys already lived in, then put those partition values into the merge condition alongside the primary keys. The scan pruned to a handful of directories instead of the whole table.

And processing time made that pruning tight, because of one assumption about how the data behaved: rows that get updated are usually rows that arrived recently. Partition by arrival, and an update batch resolves to the newest few partitions — which are small. Steady state, this was correct. Merges were fast, and they were fast because of the key.

On top of that, a few dozen tables had no usable date in them at all — no order date, no event date, nothing. Whatever key we picked had to work for those too, and processing time was the only column guaranteed to exist on every row of every table. So the awkward tables closed off the alternatives, and the framework had no way to say except for this one.

Put together, the decision was reasonable. What nobody wrote down was the assumption holding it up: that partitions stay small and roughly the same size. The merge optimisation only pays off while that's true. Nothing in the platform enforced it, nothing measured it, and nothing would complain when it stopped being true.

What a historical load does to it

When you process a large slice of history in one run, all of those rows get written at roughly the same moment. So they all get the same processing timestamp. So they all land in the same partition.

Years of data, in one directory.

Everything after that behaved as designed — each incremental run wrote its own small partition. So the table ended up as one enormous partition holding most of the history, trailed by a long line of small ones holding everything since.

And now the optimisation turned around and pointed the other way. The merge still looked up which partitions the incoming keys lived in. It still pruned. But for any key that had come in with the historical load, the answer to which partition is this row in was the giant one. The pruning worked perfectly and returned most of the table.

Why that costs real money

Two things go wrong, and they feed each other.

Every merge now reads the history. A batch of updates that used to scan a few small directories scans the largest object in the table instead — not because more data changed, but because the engine still has to classify the incoming rows against whatever the pruning hands it. The work stopped being proportional to what changed and became proportional to everything that had ever arrived. That happens on every load, on every table shaped like this.

And the work stops spreading out. Distributed processing is only fast because many workers each take a slice. When one partition holds most of the rows, one task gets most of the scan and the rest of the cluster finishes early and waits. You're paying for a large cluster and getting roughly one worker's throughput on the critical path.

The analytical queries never benefited from the layout, and we knew that going in. With several teams filtering different ways, the partition column was never going to match anyone's predicates, so their scans read everything regardless. That was the accepted trade — the one partition key we had went to the operation that ran constantly rather than the ones that ran occasionally.

Which is exactly why the historical load hurt as much as it did. It didn't introduce a new kind of cost. It took the one operation whose reads were bounded and unbounded them.

Have a look at what the historical load does. Flip the layout and watch the merge.

Why it survived a year

Every property of this failure works against being noticed.

It degrades smoothly. There's no threshold where something breaks — each month is slightly worse than the one before, and slightly worse never looks like a problem.

It doesn't fail, so no alert covers it. I have never seen a monitoring rule for correct, but increasingly wasteful.

And the cost is diffuse. It doesn't show up as one expensive job. It shows up as a slightly bigger number across a lot of jobs, which is precisely what you would expect from a business handling more data each year. That was the real trap: the bill going up had an innocent explanation, and the innocent explanation was even partly true. We were processing more data. Just not $60,000 a month more.

How it was actually found

Not by a cost alert. The job got slow enough to irritate me, so I went looking.

Three steps, and none of them took long:

Read the query plan. That's where it stopped being a hunch. The plan showed the scan pulling far more data than the query needed, with no partition filter applied at all. If the engine can't prune, the plan says so plainly.

Look at the physical layout. Bytes and file counts per partition, not the partition count. A healthy-looking number of partitions tells you nothing. One partition holding most of the table is obvious the moment you sort by size.

Read the transaction log. Delta records every write. The log showed one commit, from the historical run, that had written the overwhelming majority of the table under a single partition value.

What we actually did

We didn't re-architect anything, and we didn't migrate a thousand tables.

We added a configuration field. The framework already onboarded every source through a row of config, so the partition column became one more thing that row could name. The default stayed processing time. Nothing changed for the vast majority of tables, nothing needed re-testing, and the field only mattered when somebody set it.

Then we worked a list. Rank every table by what it was costing. Take the top ten. For each one: confirm the layout is actually the problem, find a column in that table worth partitioning by, set it in config, rerun. Then the next ten.

Each batch took another slice off the monthly bill, and we kept going down the list until the total reached around $60,000 a month.

Two things about that approach are worth copying.

Rank by cost, not by size. The biggest table isn't automatically the expensive one. What matters is size multiplied by how often something scans it. A huge table nobody merges into is cheap. A middling one rebuilt every hour is not. The bill already knows which is which, so start there rather than with a list of row counts.

Leave the default alone. The tables that had no good column simply kept processing time and stayed exactly as they were — which is fine, because they were never the ones costing money. Every change was one row of config, one table, reversible.

That's also the honest answer to the design flaw. The framework wasn't wrong to have a default. It was wrong to have a default nobody could escape.

One thing to know if you follow the same path: a business date needs cleaning in a way processing time never does. Business dates go null, arrive in three formats, and occasionally claim to be from 1970. Have a rule for bad values before you switch a table over, not after.

Write it down, then measure it

Know which operation your partitioning is for, and write down the assumption that makes it pay off.

Ours was for the merge, and it earned its place — updates arrive for recent rows, recent rows sit in small partitions, merges stay cheap. Every word of that is true and none of it was written anywhere. An assumption nobody records is an assumption nobody checks, and this one stopped being true on a Tuesday afternoon when somebody ran a backfill.

So: write it down, then measure the thing it depends on. In our case a single number — bytes in the largest partition over the median — would have gone from boring to alarming and stayed there for a year, waiting for someone to look.

Twenty minutes on your own tables is enough to find out. I had a thousand of them and never touched most; the money was always in the top few.

This one was worth $60,000 a month across the tables we got to. What kept it alive for a year wasn't subtlety — the query plan gave it up on the first read. It survived because everything was working, and nobody goes looking at a system that works.