Case Study 1: The Rerun That Overwrote March

"They cleared four failed tasks from last spring. Airflow re-ran them, all four went green, and March now contains August."

Executive Summary

Kestrel's kestrel_daily DAG had four failed task instances from March 2026 that had never been cleaned up — a stalled extract during a source migration, left red in the UI because the data had been loaded by hand.

Eleven weeks later, a new engineer tidying the DAG list cleared them. All four re-ran and succeeded.

Each one wrote August's data into March's partition, because the extract derived its window from datetime.now() rather than from data_interval_start. The dt partition column was correct — it came from the task's logical date — and the rows inside it were August's.

fct_order_item gained 71,012 August order lines dated March, and lost nothing, so every row count went up and no test failed. The March monthly close, already filed, was overstated by $1,994,727 in a re-query nine days later.

Skills applied: the date model (§24.3); datetime.now() and rerun safety (§24.3's ⚠️ callout); idempotency under clearing (§24.5); testing with airflow tasks test (§24.11).

Background

The DAG. kestrel_daily, schedule="0 3 * * *", running since January 2025.

The task, written in 2025 and reviewed by two people:

@task
def extract_orders():
    # Pull everything modified since yesterday.
    since = datetime.now() - timedelta(days=1)
    rows = source.query(
        "SELECT * FROM orders WHERE updated_at >= %s", (since,))
    path = f"{LAKE}/bronze/orders/dt={context['ds']}/orders.parquet"
    write_parquet(rows, path)
    return path

Read that carefully, because everything about it looks fine.

  • The window is "since yesterday," which for a nightly job is right.
  • The partition path uses {{ ds }} — the logical date — which is right.
  • It returns a path rather than data (§24.9), which is right.
  • It is idempotent by partition overwrite (Chapter 20 Strategy 1), which is right.

The defect is that two different clocks decide two different things. The rows come from the wall clock; the partition they land in comes from the logical date. On a normal run those agree, and they agreed for 434 consecutive nights.

The four failures. On 2026-03-14 through 03-17, the source database was migrating and the extract timed out. The data was loaded by a manual script, the incident was closed, and the four red squares were left in the UI — because clearing them would have re-run the extract, and at the time everyone correctly understood that re-running it was unnecessary.

Nobody wrote down why they were left red.

The Problem

On 2026-08-02, an engineer doing housekeeping found four failed tasks from March in a DAG that was otherwise green. They cleared them, which is the documented way to resolve a failed task instance and is what the UI's most prominent button does.

Cleared: kestrel_daily.extract_orders  2026-03-14  →  queued → running → success
Cleared: kestrel_daily.extract_orders  2026-03-15  →  queued → running → success
Cleared: kestrel_daily.extract_orders  2026-03-16  →  queued → running → success
Cleared: kestrel_daily.extract_orders  2026-03-17  →  queued → running → success

Four green squares. The DAG list was tidy. The engineer moved on, correctly believing they had resolved a stale failure.

What each run actually did:

run labelled 2026-03-14
  since   = datetime.now() - 1 day   =  2026-08-01     ← August data
  path    = .../dt=2026-03-14/                          ← March partition
  result  = 17,766 August order lines written to March

And because the write is a partition overwrite, March's correct rows were deleted first.

The Analysis

Nothing detected it for nine days.

  • Every task succeeded. Chapter 23 §23.3's distinction, in the orchestrator.
  • The grain test passed. August rows are unique on (order_id, line_number).
  • The volume floor passed. March's partitions had rows — more of them, in fact.
  • not_null, accepted_values, relationships — all passed. August data is valid data.
  • The freshness check passed. It looks at the most recent partition, which was August's and was fine.

Every one of Chapter 23 §23.4's six assertions passed, and that is the most uncomfortable sentence in this case study. The six are correct and they are all statements about whether the rows are well-formed, and these rows were.

⚠️ Failure Mode — none of the six assertions asks whether a row is in the right partition

The gap is specific and worth naming precisely: the six check the rows against themselves. They do not check the rows against the partition that contains them.

The missing assertion is one line and nobody writes it:

sql -- Every row in a date partition must belong to that date. SELECT dt, MIN(ordered_at), MAX(ordered_at), COUNT(*) FROM bronze.orders WHERE ordered_at::date NOT BETWEEN dt - 3 AND dt + 1 GROUP BY dt; -- expect zero rows

The tolerance is not decoration. A three-day lookback (Chapter 20 §20.7) legitimately puts slightly older rows in a partition, and late arrivals put rows in the following one. A partition assertion with no tolerance fires on correct behaviour and is disabled within a fortnight — Chapter 23 §23.7 again.

The general form, and it is worth adding to the register: assert the relationship between a record's content and its location. Partition versus content date, filename versus contents, dt= prefix versus the timestamps inside. Every partitioned system can be wrong in this way and almost none of them check.

How it was found. Not by a test. A finance analyst re-ran March's close in early August for an audit query and got a different number from the filed one:

$$\$1{,}994{,}727\ \text{more than the number filed in April}$$

Step 1: which rows are new? The _ingested_at column — present on every bronze table for exactly this reason — made it immediate:

SELECT dt, COUNT(*), MIN(_ingested_at), MAX(_ingested_at)
  FROM bronze.orders WHERE dt BETWEEN '2026-03-14' AND '2026-03-17'
 GROUP BY dt;

dt            n        first_ingested         last_ingested
2026-03-14   17,766   2026-08-02 11:04:22   2026-08-02 11:04:22
2026-03-15   17,801   2026-08-02 11:06:41   2026-08-02 11:06:41
2026-03-16   17,702   2026-08-02 11:09:03   2026-08-02 11:09:03
2026-03-17   17,743   2026-08-02 11:11:28   2026-08-02 11:11:28
                      ^^^^^^^^^^^^^^^^^^^ every row loaded on one August morning

71,012 rows, all ingested within eight minutes, all in March partitions.

🔎 Read the Plan — _ingested_at is the column that makes this a ten-minute investigation

Kestrel's bronze tables carry a _ingested_at audit column on every row. It costs 8 bytes and it is the difference between the query above and an afternoon of guessing.

Four questions it answers that nothing else can, all of which come up during an incident:

  • "When did these rows arrive?" — which is a different question from "what date are they about," and the gap between the two answers is this incident.
  • "Which rows came from the re-run?" — a WHERE _ingested_at > '2026-08-02' selects exactly the damage.
  • "How long has this been happening?" — the earliest _ingested_at for a bad row.
  • "Did the backfill actually run?" — without it, an idempotent re-run is invisible.

Add _ingested_at, _source, and the run identifier to every bronze table. Three columns, negligible storage, and each of them turns one class of incident from archaeology into a query.

The run identifier is the one people omit and it is the most useful of the three: run_id written into the row lets you join warehouse rows to orchestrator history, which answers "what produced this?" directly rather than by inference.

Step 2: what was destroyed? March's original rows had been deleted by the partition overwrite. The recovery came from three places, and the fact that there were three is what made this a nine-day incident instead of a permanent one:

  • Delta time travel on bronze.orders (Chapter 10 §10.6) — but the vacuum retention is 30 days and the write was in March. Gone.
  • The source database, which retains 18 months. Available, and the actual recovery path.
  • The manual load script's output, still on the engineer's machine from March. Available, and the confirmation that the re-derived data matched.

The Decision

Five changes.

One: fix the task. Four lines, and the diff is almost invisible:

@task
def extract_orders(data_interval_start=None, data_interval_end=None):
    rows = source.query(
        "SELECT * FROM orders WHERE updated_at >= %s AND updated_at < %s",
        (data_interval_start - timedelta(days=3),   # Ch. 20 §20.7's lookback
         data_interval_end))
    path = f"{LAKE}/bronze/orders/dt={data_interval_start.date()}/orders.parquet"
    write_parquet(rows, path)
    return path

Both the rows and the partition now come from the same source of truth, which is the property that was missing.

Two: lint for it. dag_lint.py --rule wall-clock runs in CI and fails the build.

Three: the partition-content assertion from the ⚠️ callout above, on every partitioned bronze table, with a tolerance matching that table's lookback.

Four: airflow tasks test twice, in CI, for every task that writes. §24.5's check would have caught this in 2025 — the second run against a historical interval would have produced different data.

Five: a rule about red squares, which is the organizational half.

📐 Design Decision — a failed task instance is an obligation, not a decoration

The four red squares sat for eleven weeks. That was the real precondition, and the fix that matters is not the code.

Two options were considered:

"Never clear old tasks." Rejected. It makes the UI permanently untrustworthy, it gives new engineers no safe action, and it converts a real signal — a red square — into wallpaper. A rule that requires everyone to remember an exception is not a rule.

"A failed task is resolved within one week: cleared, or marked success with a reason." Adopted. Airflow's mark_success exists precisely for "this was handled outside the pipeline," and it leaves a note.

text Marked SUCCESS by @engineer 2026-03-18: "Source migration; loaded manually via scripts/manual_load_2026-03.py. Do NOT clear -- the extract is not safe to re-run for a past date. Tracked in DATA-2211."

That note is the whole fix. The engineer in August would have read it, and it names both the hazard and the ticket.

The general principle: an unresolved state in an operational tool decays into noise, and noise is where the next incident hides. It is the same shape as Chapter 23's mutes and Chapter 19's known-issues page — anything that can be left in an ambiguous state needs a maximum age and a required explanation.

What Happened

Before After
Rows misplaced 71,012 0
Detection a finance re-query, 9 days partition assertion, same night
datetime.now() in tasks 6 occurrences 0, linted
Unresolved failed tasks 4, eleven weeks old max age 7 days
Recovery source the source DB (18 months) unchanged

The lint found five more. One in extract_customers, one in export_reports, and three in a DAG that computes a filename from datetime.now().strftime(...) — which is the least alarming-looking instance and produces a file whose name does not match its contents on any re-run.

Two of the six had comments explaining why they were fine. Neither explanation survived contact with the question "what does this do when someone clears a task from March?"

March was restated. The four partitions were rebuilt from the source, the downstream models re-run for the range, and the close re-filed. The restatement was $1,994,727 downward — back to the originally filed number, which had been correct all along.

And a smaller finding with a longer tail. The Delta vacuum retention on bronze.orders was 30 days, which meant time travel could not recover a March write in August. Chapter 10 §10.6 says to check that the retention window covers your realistic detection time; this incident's detection time was nine days, which fits — but the write was in March and the damage was in August, so what mattered was the retention relative to the age of the data being overwritten.

That is a subtler requirement than the one Chapter 10 stated. The write was on 2026-03-14 and the damage was on 2026-08-02 — 141 days — so recovering it by time travel would have needed 141 days of retention, not the nine days of detection time. The retention was raised to 90 days with the reasoning written down, and the honest note recorded alongside it: 90 would not have covered this one either, and the source database is the real recovery path.

Lessons

  1. A task that derives its window from the wall clock and its partition from the logical date has two clocks. They agree on every normal run — 434 consecutive nights — and diverge on the first rerun.

  2. Clearing a task is the UI's most prominent action and it re-runs the code. For a task with any wall-clock dependency, that is destructive, and nothing warns you.

  3. All six of Chapter 23's assertions passed. They check rows against themselves. None asks whether a row is in the right partition.

  4. Assert the relationship between a record's content and its location — partition date versus content date, with a tolerance matching the lookback. Every partitioned system can be wrong this way and almost none check.

  5. _ingested_at, _source, and run_id on every bronze row turn this class of incident from archaeology into a query. The run identifier is the one people omit and the most useful.

  6. airflow tasks test run twice against a HISTORICAL interval would have caught this in 2025. Twice against today's interval would not have.

  7. The four red squares were the precondition, and they sat for eleven weeks. The code fix is four lines; the fix that matters is a maximum age and a required explanation.

  8. mark_success with a note is the right resolution for "handled outside the pipeline." The note would have stopped this.

  9. An unresolved state in an operational tool decays into noise — red squares, mutes, known-issues entries — and noise is where the next incident hides.

  10. Two of the six wall-clock uses had comments saying why they were fine. Neither survived the question "what does this do when someone clears a task from March?"

  11. Time-travel retention must cover the age of the data you might overwrite, not just your detection time. A March write damaged in August needed 141 days, not 9 — and the honest conclusion is that time travel is not the recovery path for this class at all.

Questions for Discussion

  1. The task was reviewed by two people and every individual line was defensible. What review question would have caught the two-clocks problem?

  2. Clearing a failed task is the UI's most prominent action. Should an orchestrator warn before re-running a historical interval, and what would it check to decide?

  3. Chapter 23's six assertions all passed. Should the partition-content check be a seventh? What does adding it cost, and what else might belong?

  4. The four red squares were left deliberately, by people who understood the hazard, and nobody wrote it down. What makes a team write things like that down?

  5. Three of the six wall-clock uses computed a filename. Argue that this is harmless. Then argue that it is worse than the others.

  6. Recovery depended on the source retaining 18 months. What would this incident have cost against a seven-day CDC retention, and what does that imply for Chapter 20 §20.1's "incremental by necessity" case?

  7. The retention lesson here is subtler than Chapter 10's version. State the general rule for how long time travel must cover, in one sentence.