Make It Run Again

The lesson that decides whether this becomes somebody’s 3am

Your transform is correct. This lesson is about the fact that it now runs without you, roughly fifty times a year, and about half of the operational pain in data work comes from what happens when one of those runs goes wrong.

Prompt first: the operational wrapper

My transform is correct and now needs to run weekly, Monday before 7am.
Source data lands between 2am and 4am, occasionally as late as 6am.

Design the operational wrapper:
- readiness check that FAILS if the source has not loaded the expected
  period, rather than computing on partial data
- atomic write, so a mid-run failure leaves the previous data intact
- a separate backfill entry point that REQUIRES an explicit period range
- failure notification to a person
- a data-as-of value the consumer surface can display

For each, tell me what breaks if I skip it, and which single one you
would implement first if I only had time for one.

The last clause is worth including habitually. It forces a ranking rather than a list, and the answer is almost always the readiness check — because it is the one that prevents the failure nobody detects.

Stop and escalate when the readiness check needs a completion signal the source does not emit — that is a platform-team request, and a lookback guess is not a substitute for it.

Schedule on readiness, not on the clock

The reflex is to pick a time: “runs at 5am Monday.” The problem is that 5am is a fact about your calendar and not about whether the data is there.

Meridian’s fault data lands from the outage system between 2am and 4am, usually. Occasionally the source job is delayed and it lands at 6am. A 5am schedule therefore produces, a few times a year, a Monday morning list built from incomplete data — with no error, because an empty week is a valid query result.

Two better options:

• Trigger on upstream completion — the load runs when the source signals it finished. Best when your platform supports it.
• Check readiness, then decide — the job starts at 5am, verifies the source has the expected period, and fails loudly if not, rather than proceeding on partial data.

-- Readiness gate: fail rather than compute on partial input
SELECT CASE
  WHEN MAX(event_ts) < CURRENT_DATE - INTERVAL '1 day'
  THEN ERROR('source stale: newest event is ' || MAX(event_ts)::text)
END
FROM circuit_faults;

What a half-finished run leaves behind

Answer this before the first failure, not during it. If the job dies partway, what is on disk?

• Partition replacement (Lesson 3.2) — the delete and insert should be one atomic operation. If your platform cannot do that, write to a staging table and swap, so a failure leaves the old data intact rather than a table with March deleted and nothing written back.
• Multiple outputs — a job writing three tables that fails after the second leaves an inconsistent set. Either make it one transaction or make each output independently valid.
• Anything downstream that already read the partial result — the reason the swap matters. A dashboard that refreshed between your delete and your insert showed a month of zeros to whoever was looking.

The goal is that a failed run leaves exactly the state that existed before it started. Then recovery is “run it again,” which is a retry rather than an incident.

Backfill is a different job

Reprocessing history is not the same operation as the nightly run, and treating it as one causes trouble in both directions.

• Volume — a backfill of a decade may be 500 times a normal run. It will contend with production queries, and it may exceed limits nobody has hit since the table was built.
• Ordering — if periods depend on each other, they must be processed in order. If they do not, run them in parallel and finish in a tenth of the time.
• Blast radius — a backfill rewrites numbers people have already seen. That is Lesson 3.2’s restatement decision, and it needs the owner’s sign-off before it runs, not after somebody notices last quarter changed.

Give backfill its own entry point with an explicit period range, and make the range a required argument. A backfill script that defaults to “everything” will eventually be run by someone who meant to fix one month.

Who finds out first

Rank the outcomes:

1. Best — the job fails, you are notified, you fix it before Monday 7am. The consumer never knows.
2. Acceptable — the job fails, the consumer sees a clearly stale-marked table with the date of the data on it, and knows not to trust it as current.
3. Bad — the job fails, the table silently holds last week’s numbers, and the supervisor assigns crews from stale data believing it is fresh.
4. Worst — the job succeeds on partial data, and the numbers are wrong in a way nothing marks.

Outcomes 3 and 4 are what you are designing against, and the defenses are cheap:

• Notification on failure, to a person, not to a log nobody reads.
• A visible data-as-of timestamp on every surface the consumer sees. This one line converts outcome 3 into outcome 2 for nearly no effort.
• A freshness assertion that fails the run when the source is stale, which converts outcome 4 into outcome 1.

Key takeaway

Schedule on upstream readiness rather than clock convenience, and make “no data” an explicit failure — because an empty result is a successful query, and that is how a stale source becomes an unusually good week on somebody’s dashboard. Design the write so a mid-run failure leaves exactly the prior state, making recovery a retry instead of an incident. Give backfill its own entry point with a required period range and the owner’s sign-off, since it rewrites numbers people have already seen. Then rank who finds out first: failure notification and a visible data-as-of timestamp are the two cheapest reliability features available. Module 4 makes correctness itself something that runs on every load.