09:00. Yesterday's mart is empty, the day before is doubled

Ticket #01. "The report is lying"

09:00, 15 March 2184. You come on shift on the intake level of the Vault-9 station — a dim cargo tier where the intake belt hauls in archive snapshots all night and stacks them across the vault. In the morning the Academy reads its work.

One ticket in the queue, opened at 08:41 by the archive warden:

"The daily report is lying. Yesterday is empty — as if the belt had been standing still. The day before, revenue is twice what the snapshots hold. Which of those is true?"

The answer is unpleasant: there is no truth in the report at all. Yesterday the belt was running, and the day before the snapshots hold exactly half of what the mart shows. Both lines of the ticket concern the same stretch of belt, but two different nights.

The belt was your predecessor's. From nine in the morning it is yours.

QUERY: Good morning, archivist. Your predecessor left you a run log and two unfinished checks. I dozed all night in a background process — and saw everything. An empty night and a doubled night are different failures; start by telling them apart.

The mart dm_daily_revenue is the end of the belt, the last shelf: one row per day and city. Separate loading layers stand before it. We will examine their structure in the next lesson; for now, all you need to know is that the mart holds the result.

Today the job is simpler: read the instruments and call the failure by its name.

Two different failures in one table

The instruments have been read — now let's name what they show.

There is no data for yesterday in the mart — not "zeroes" but exactly an absence of rows. The day before is written twice: twice as many rows as normal, and written by two different run_ids.

From the outside it looks like one broken report. In fact there are two breakages, and they are repaired differently:

The difference is fundamental. In the first case a repeat is the cure; in the second it is another helping of the disease: it will put a third set of rows into the mart.

The second case requires idempotency: the property of a step giving the same result however many times it is run.

For now remember the symptom: two run_ids on one day is evidence of a repeated load that appended instead of replacing.

But a symptom is not yet a story. What went on during the night is shown by the run log etl_runs. It is not kept for reporting: it is the only place where a trace of every attempt survives, including the ones launched automatically.

QUERY: Unrolling the log for both nights. People remember what they launched. The log remembers what launched.

What happened during the night

The log shows the whole story — attempt by attempt, not by the final outcome.

13 March — three attempts of one task instead of one.

• 990001, 02:31–02:33, up_for_retry, rows_out = 0 — the attempt never made it and wrote nothing;
• 990002, 02:41–02:44, success, rows_out = 22 — the repeat counted the day and put it into the mart: twenty-two cities, twenty-two rows;
• 8812, 03:24–03:28, failed, rows_out = 1552 — one more attempt at the same day, which fell over but had already reached the writing stage.

There is the explanation of the forty-four rows: the day was put into the mart more than once, because the loader appended to what was already there on a repeat.

Nobody noticed, for a simple reason: there is a successful attempt in the list, and a green attempt looks like "all is well". The failure hid not behind a missing signal but behind a success signal standing next to somebody else's write.

About rows_out on unsuccessful attempts. 1552 is not "that many rows in the mart". The counter is updated as the work proceeds, and on an attempt that never reached the end it confirms exactly one thing: the attempt reached the writing stage.

It cannot tell you how many rows reached the mart, or whether any reached it at all. That is why you look at the state first and only then at the numbers.

14 March. The same task is still in state running, finished_at is empty, and its rows_out is not zero either — but that does not prove that rows have reached the mart: the attempt has not finished, and the day is absent there.

So the task did not fall over, it hung. Yesterday's data is missing because the load has not brought it in yet; the mart says nothing about where it is on the preceding layers.

What the log does NOT say: which attempt put which rows in. The mart has its own load run_id, the log has its own attempt number, and the link between them appears in chapter de2.

But it answered the main question of the shift: there were several attempts, and one of them fell over after reaching the writing stage.

Hence the shift rule the course will come back to for ten chapters running:

Ask the run log what was going on first, and only then ask the data what it holds. Data shows you the effect, the log shows you the cause.

A bare INSERT is not a load

The cause of the doubling is one verb. The mart loader appended: it counted the day and added the result to whatever was already in the table.

Appending is right only when an event happens once. A load for a date is not such an event: it happens as many times as it is launched, and it will certainly be launched more than once — by a retry at night or by hand in the morning.

The property the step needs is called idempotency: however many times it is executed with the same inputs, the state comes out the same. Not "the step will not fall over on a repeat", but exactly "after the second run the table holds precisely what it held after the first".

To get there, the loader changes its unit of writing. It works not with individual rows but with the range of one date — the slice of the mart that belongs wholly to that day. We will call such a slice the day's range.

The step then turns from "count and append" into a pair:

1. DELETE everything belonging to this date;
2. INSERT … SELECT what has just been counted.

The point of the pair shows on repeats. The first run deletes zero rows and puts down twenty-two. The second deletes twenty-two and puts down twenty-two. The tenth does the same.

Neighbouring dates do not suffer for it: what gets deleted is ITS OWN range, not the whole table.

One caveat about production: without a transaction the mart is empty for the day between DELETE and INSERT, and a report that falls into that gap will show a zero. In a transactional database the two operations are therefore usually combined. In the course sandbox you cannot control the transaction — the validator will not let BEGIN through — so below the pair is simply two statements one after the other.

Let us build such a loader and run it twice in a row, exactly as the night-time retry did.