Where Do Quarantined Rows Go

One-line summary

Quarantine is a device for not throwing rows away and is not a solution in itself. Without a loop that fixes and reinserts and eyes that watch age, quarantine becomes a drawer nobody opens.

Why this was needed

You attached a quality check. You do not discard unusable rows; you leave them in a quarantine table with their reasons. Most people do up to this point.

Half a year later, when you open that table, it holds 40,000 rows. There are six reasons, and one of them accounts for 30,000 rows, and all 30,000 came about because on one day upstream moved a field to a different place. Since that day nobody has looked at that table. The sales aggregate had been going out with 30,000 rows missing the whole time, and the dashboard stayed green.

What this reveals is the fact that run state and data state are different axes. The pipeline succeeded every day. Having succeeded means "finished without errors," not "put in everything that should have been put in." If you lump the two into one field and report them, you see neither.

Where this course and fde-data diverge

The CSV lab in fde-data goes as far as separating unusable rows from the chunk of a file a customer gave and leaving them as a rejection file. That file comes once, and the rejection list ends when you send it to the customer. This course is about a pipeline that runs every day, so it does not end there — quarantine piles up again on every run, a row quarantined yesterday comes again today, and if someone fixes it and puts it in, that row can be counted twice. Taking something apart once and keeping something running are different designs.

Expectations as a file, not as code

An expectation is a written statement of "if this data is correct, it should look like this." If you scatter them around the code as conditionals, nobody knows what is being looked at. If you write them as a list in one file, three things change — you can talk with upstream over that file, the code does not change when rules are added, and the name of the violated rule becomes the quarantine reason.

{"rules": [
  {"name": "order_id_format", "column": "order_id", "type": "regex",
   "pattern": "^ORD-[0-9]{6}$"},
  {"name": "qty_range", "column": "qty", "type": "range", "min": 1, "max": 50},
  {"name": "status_enum", "column": "status", "type": "enum",
   "values": ["NEW", "PAID", "CANCELLED"]}
]}

If you make the reason the rule name, "why was it quarantined" becomes not a free sentence but a countable value. Only when it is countable can you report how many rows there are per reason and see which reason is growing.

What to base the threshold on

One row being wrong and a whole file being wrong are different events. The former is handled with row-level quarantine, and the latter with a run-level stop (circuit break). If upstream sends the column order changed, nearly every row is off, and if you quarantine at the row level at that point, ten thousand rows go into the quarantine table and the aggregate goes out empty. For such a file, it is better not to put in a single row.

The criterion for splitting is a ratio. And that ratio should be set from the usual value. If you first set a round number like "5%," it becomes one of two things — for data whose usual is 7%, it gets cut off every day, and for data whose usual is 0.1%, it does not get cut off even when things get 20 times worse. You measure the failure ratio of recent runs and set it generously above that. The threshold differs per dataset, and so it must be written down per dataset.

When you stop, you put in nothing. If you put in half and stop, the next person has to judge how far it got in, and that judgment will be wrong sooner or later.

The reinsertion loop and double counting

A quarantined row is finally done only when it is fixed and put back in. Two things tend to break here.

First, it goes into the fact table twice. If you just INSERT the fixed row, both the original row and the fixed row remain. If you apply a unique constraint to the natural key and put it in as an update, this problem disappears.

Second, closing a quarantine twice. If you run the same fix twice, it closes an already closed quarantine again and "resolved 3 this time" gets reported twice. If you close only those still open when closing, the second run gives 0. Reinsertion will certainly be run several times — because it is something people run by hand.

And there are quarantines that cannot be fixed. A row whose key itself is broken is one. If it was quarantined because the voucher number did not follow the format, the moment you fix the number it becomes a different row. Such a thing must be sent back to upstream, not reinserted.

Put an alert on aged quarantines

It is normal for quarantine to pile up. The problem is what does not shrink. So you look not at "how many were quarantined" but at "which run's quarantine is the oldest."

It is easier to handle age by number of runs than by time. In a pipeline that runs once a day, "a quarantine older than 3 runs" means three days, and if deployment stops and the pipeline does not run, the age does not advance either — that matches human intuition better.

What it looks like in the field

• The quarantine table has ten thousand rows and nobody knows. If you export the maximum age of open quarantines as a metric, you see it that very day.
• Sales doubled after reprocessing. This is the case where fixed rows were put in by insert rather than by update.
• A pipeline that gets cut off every day. This is the case where the threshold was set to a round number rather than the usual value. People soon turn the check off.
• "It succeeded yesterday." This is the case where you looked only at run state and not at data state. If you split the report into two fields, this conversation goes away.

What really matters in practice

• Write expectations as a file, not as code, and use the name of the violated rule as the quarantine reason.
• Separate row-level quarantine from run-level stop, and set the stop threshold from the usual failure ratio.
• Reinsert as an update and close only the open quarantines. The numbers must not change even if you run it twice.
• Export the age of quarantines as a metric. With counts alone you cannot see what does not shrink.
• Report in two fields whether the run succeeded and whether the data is correct.

What to do in the next lab

You write expectations as a file and build up the gatekeeper qgate.py step by step. Starting with check, which only counts, you pile facts and quarantine separately into sqlite, cut off a run that is wrong too much in its entirety, put the fixed rows back in while keeping the numbers from changing even if you put them in twice, catch aged quarantines, and report run state and data state separately. The grader sets up its own drops and expectations each time with different thresholds and different shop names, actually runs your gatekeeper, and directly opens the database to count whether facts and quarantine went in correctly.