Designing for retraction

Both defences live in the loader, which is the only place they can live, and that is worth saying before either of them is described. Nothing upstream will help: the chain is entitled to replace a branch and it is not going to ask. Nothing downstream will help either, because by the time a row is in a mart it has already been counted, cached and probably emailed. The loader is the boundary, and the boundary is where the decision gets made.

The first defence changes when a row is allowed through the boundary. The second changes whether a row that went through can come back. Those are different questions and they have different answers, which is why bundling them into one setting is a mistake serious enough that session 9 is about a version of this bench that did exactly that.

• Finality lag. How far behind the tip the loader stays before it writes a block's rows. Measured in blocks. Bench B sweeps it from 0 to 12.
• Held rows. Rows that exist, are correct, and have not been loaded yet because the lag has not elapsed. The freshness bill, and the only cost a lag has.
• Orphaned rows. Rows already loaded whose blocks were subsequently replaced. Wrong, unflagged, and counted by nobody.
• Retraction. A loader that deletes already-loaded rows when their blocks are replaced. Bench B calls the rows it removes corrected rather than orphaned, because they no longer sit in the warehouse being wrong.
• Reorganisation depth. How many blocks deep a replacement goes. Bench B uses 3 throughout. The value is the parameter nobody writes down and everything depends on.
• Late arriving. A record describing an earlier period that shows up now. Additive. Handled by patterns that already exist in learn-data-engineering-with-phoebe.
• Withdrawn. A record that was loaded and is now gone. Subtractive. Not the same thing, and Part 2 is largely about the difference.

A finality lag is not a dial that trades safety against freshness smoothly. It is a threshold, and the threshold is the reorganisation depth. Bench B runs a depth 3 reorganisation four times across 200 blocks, and the wrong-row count goes 511, 346, 172, 0 as the lag goes 0, 1, 2, 3. The first three of those are all failures. The last one is the answer.

Read the middle of that sequence carefully, because it is where the damage gets done in practice. A lag of 1 leaves 346 wrong rows, which is 3.99 percent of the warehouse. A lag of 2 leaves 172, which is 2.03 percent. Both of those configurations feel like caution. Both of them were chosen deliberately by somebody who was thinking about this problem. And both of them leave the warehouse wrong while removing the pressure to look again, which is a worse position than having no lag at all and knowing it.

The arithmetic is not subtle once you draw it: a reorganisation replaces the last three blocks, so any row from those three blocks that has already been written is a row that has to be unwritten. A lag of 1 writes two of those three blocks before the replacement lands. A lag of 2 writes one of them. A lag of 3 writes none.

The other end of the sweep is the part people get wrong in the opposite direction. At lag 3 the wrong rows are 0. At lag 4 they are 0. At lag 6 they are 0. At lag 12 they are 0. The defence is complete at 3 and every block of lag past that is pure cost with no corresponding benefit.

The cost is not trivial either. Lag 3 holds 125 rows back from the report. Lag 6 holds 249. Lag 12 holds 497, which is about 6 percent of the loaded volume sitting in a queue because somebody picked a round number that sounded safe. A team that cannot state the depth will reach for a big lag to cover its ignorance, and it will pay for that ignorance in freshness forever, on every report, with no way to tell whether it is overpaying by a factor of two or four.

Which leads to the uncomfortable requirement the whole defence rests on. You cannot choose a lag without knowing the depth, and the depth is not a column in your data. It is a property of the thing producing your data, it varies, and it is the input that nobody writes down.

A retracting loader does not need to know the depth. When the blocks a set of rows came from are replaced, it removes those rows, whatever depth the replacement went to. At lag 0 it corrects all 511. At lag 1 it corrects 346, at lag 2 it corrects 172, and in every one of those cases the warehouse ends up with 0 wrong rows.

That is a genuinely stronger property than the lag has, and it is worth saying plainly before the costs arrive. The lag is a bet on a number you do not control. Retraction is a mechanism that responds to what actually happened. If the depth doubles next month, the lag silently stops working and the retracting loader carries on being correct.

It is also the only one of the two that can recover from a mistake. A lag that turned out to be too shallow has already written the wrong rows and has no way to find them again. A retracting loader that was running the whole time has already taken them out.

Bench B fixes the depth at 3 because a bench has to fix something, and every number on this page is conditional on that choice. What the bench cannot tell you, and what no generated data could tell you, is what the depth is for whatever Larkfield is actually reading. That question has to be answered outside the warehouse, and the answer is not a constant.

What a practitioner can do is measure it rather than assume it. The loader already sees every block it loads. Recording, for each replacement it observes, how many blocks were replaced, gives an empirical distribution of depths over time rather than a folklore number. That distribution is the input to the lag decision, and it is the only honest basis for one. A team that has been running for six months and cannot produce that histogram has been guessing for six months.

Note also what the distribution does to the framing. A lag is chosen against a percentile of it, not against a maximum, because the maximum is unbounded. So a lag is a statement about how often you are willing to be wrong, and that is a decision for whoever owns the report rather than whoever owns the loader.

Every warehouse has machinery for data that shows up after the period it belongs to. Idempotent loads, merge keys, restatement windows, slowly changing dimensions: the general craft is well developed and it is taught properly in learn-data-engineering-with-phoebe, which is where it should be learned rather than here. What matters tonight is a distinction that craft does not have to make, because it never has to.

Those patterns handle data that arrives late. This is data that is withdrawn. Late data is additive: a total goes up, a previously missing row appears, and nothing that was already correct becomes wrong. Withdrawn data is subtractive: a total goes down, a row that was counted is gone, and a figure that was right yesterday cannot be reproduced today. Those two are not variations of the same event and the second one almost never appears in a downstream consumer's mental model.

This is why the retraction bill is hard to size. You are not paying for the delete statement. You are paying for the first time a monthly extract that was already sent to a partner no longer matches the source, the first time a cached dashboard has a higher number than the table it reads from, and the first time somebody reruns last week's query and gets a different answer with no code change in between.

Turn both on and set the lag to 3 and the bench reports 0 orphaned rows and 0 corrected rows. The retraction code is present, wired in, and never fires, because the gate stopped every row that would have needed retracting. It looks like waste. It is the only configuration in the sweep that is both correct and honest about why.

The reason to build it that way is that the lag and the retraction fail in different circumstances. The lag fails when the depth is deeper than you assumed, silently and completely. Retraction fails when a downstream consumer cannot cope with a deletion, loudly and locally. Running both means the common case costs you nothing beyond the freshness you already agreed to, and the day the depth exceeds your lag, the second mechanism catches what the first one let through and you find out because the correction count stops being 0.

That last point is the useful one for an on-call engineer. In a both-on pipeline, a non-zero corrected count is a signal that your lag assumption has just been violated. In a lag-only pipeline, the same event produces no signal whatsoever.

One camp says: hold the rows. A warehouse's contract with everyone downstream is that a row, once present, stays present, and that contract is worth more than a few hours of freshness. Break it and you have not built a fresher warehouse, you have built a cache with a warehouse's interface, and every consumer who trusted the interface is now wrong in a way they cannot detect. On this view the freshness cost is the cheapest thing in the entire design and the argument about it is a distraction. Pick a lag at a high percentile of the observed depth, publish it as a documented latency, and be done.

The other camp says: that contract was never true, it was only never tested. Any warehouse whose upstream can restate is already breaking it and nobody noticed because restatements are rare and usually additive. What a lag actually does is hide the problem behind a delay, which means the one time the depth exceeds the lag, the warehouse is wrong and has no mechanism to recover. On this view a retracting loader is not the expensive option, it is the only option that tells the truth, and the engineering cost downstream is the real cost of the data being what it is. Paying it once is cheaper than discovering it during an incident.

Both camps are staffed by people who have run production pipelines against upstreams that restate. They disagree about which failure is worse, and that is a judgement about your consumers rather than about your loader. The tell is what your downstream looks like. If it is three internal marts and a dashboard, retraction is affordable and the second camp is right. If it includes extracts that leave the building, contractual reports, or anything a regulator has already received, the first camp is right and the argument is over before it starts.