Derive Once, Run Many

The question that decides the next two years

Your table needs to refresh. Two options:

Full rebuild — drop everything, recompute from source, replace. Simple, self-healing, and always consistent with the source. Cost scales with total history, so a decade of fault events gets recomputed every Monday to add one week.

Incremental — compute only what changed, append or merge into what exists. Cost scales with the change, not the history. It is also where every hard bug in this training lives.

The default should be full rebuild until it hurts. Rebuilding 3.2 million rows weekly is nothing on modern query engines, and it makes an entire category of problem impossible: no drift, no gaps, no double-counting, and a bug fix applies to all history the moment you deploy it. Reach for incremental when the rebuild genuinely stops fitting — the run takes longer than the window, or the cost shows up on a bill somebody asks about.

Prompt first: rebuild, and say when it stops working

Make this monthly table refreshable.

Start with a FULL REBUILD implementation - do not write incremental
logic unless I ask. Then tell me:
- roughly what the rebuild scans, and at what point it stops being
  the right choice
- what the incremental version would need to handle that the rebuild
  does not

For the rebuild, make the write idempotent by partition replacement,
and give me a test that runs the load twice and proves the result is
identical.

Asking for the rebuild first is deliberate. You get the simple thing that works, plus the agent’s own account of when it stops working — which is a better basis for the incremental decision than making it up front.

Stop and escalate when a late arrival lands outside the lookback window: the restate-or-leave call belongs to the metric owner, in writing, before any reprocessing runs — because it changes numbers people have already reported.

The watermark, returning in a new role

If you took Cloud Modernization, you met the watermark as a migration device: a recorded cut-point so the team knew exactly which rows had moved and nothing was copied twice during a one-time cutover. Its rule there was that the seam is exact and nothing is orphaned.

Same mechanic, different lifecycle. Here it is not a one-time seam — it is a boundary that moves every run, and the difference introduces problems a migration never had to face. A migration’s source stops changing behind you. Yours does not.

-- The naive version, and why it leaks
SELECT * FROM circuit_faults
WHERE event_ts > (SELECT last_watermark FROM load_state);

Three ways this loses data, all of them silent:

1. Late arrivals. A fault at 14:00 that reaches the lake at 17:30, after a 17:00 run whose watermark is now 17:00. It is never picked up. Nobody finds out.
2. Ties at the boundary. > drops rows exactly at the watermark; >= reprocesses them. One loses data, the other duplicates it — unless the write is idempotent, which is the next section.
3. Event time versus arrival time. Watermarking on event_ts when records arrive late is the bug in item 1. Watermarking on ingested_at fixes the leak, but then a reprocessed old record reappears as new.

The workable pattern for most cases:

-- Watermark on ARRIVAL, reprocess a lookback window, write idempotently
WHERE ingested_at > (SELECT last_watermark FROM load_state)
   OR event_ts >= CURRENT_DATE - INTERVAL '7 days'

The lookback window is sized by how late your data actually arrives — which your profile from Lesson 2.2 can tell you, by measuring the distribution of ingested_at - event_ts. Do not guess seven days because it sounds safe; measure it, and write the measured number into the definition.

Idempotent means you can run it twice

A load is idempotent if running it twice produces the same result as running it once. This is not an elegance concern. It is what decides whether a failed run at 3am is a retry or a person.

Non-idempotent:

INSERT INTO circuit_monthly SELECT ... ;  -- runs twice, doubles everything

Idempotent, by replacing whole partitions:

DELETE FROM circuit_monthly WHERE month IN (SELECT DISTINCT month FROM staging);
INSERT INTO circuit_monthly SELECT * FROM staging;

Idempotent, by merging on the key:

MERGE INTO circuit_monthly t
USING staging s ON t.circuit_id = s.circuit_id AND t.month = s.month
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Partition replacement is usually the better choice for this kind of table: it is simpler to reason about, it handles a row that should no longer exist (a fault later voided), and a MERGE will happily leave deleted source rows behind forever.

Prove it rather than assuming it. Run the load twice against the same input and compare a checksum of the result. That is a two-minute test that converts a belief into a fact, and it belongs in your acceptance test from Lesson 1.4.

Late arrivals and the restatement question

When a fault from three months ago finally arrives, your lookback window will not reach it. That leaves a real decision, and it is the same one Module 1 assigned to the metric owner:

• Restate. Reprocess the affected month. History becomes accurate; a number somebody already reported changes underneath them.
• Leave it. History stays as published; your table permanently disagrees with the source by a small amount.

Either is defensible. What is not defensible is doing one of them by accident, which is what happens when nobody decides and the lookback window silently makes the choice. Write the rule down:

LATE ARRIVALS   records arriving more than 7 days after event time are
                loaded into the current period and flagged, NOT restated
                into their original month. Monthly totals are frozen at
                month-end + 7 days. Owner: Distribution Planning Supervisor.

Key takeaway

Full rebuild until it hurts: it is self-healing, applies bug fixes to all history, and makes drift impossible, and an agent will reach past it for incremental unless you say otherwise. When incremental is genuinely necessary, watermark on arrival time with a lookback window sized from measured arrival lag, and make the write idempotent by replacing whole partitions — then prove it by running twice and comparing checksums. Late arrivals force a restate-or-leave decision that belongs to the metric owner and belongs in writing, because the alternative is a lookback window making the choice silently. Lesson 3.3 hands the building over to an agent, with the checks that make that safe.