Pipelines you can safely re-run, which is most of pipeline design

Pipelines fail constantly and for boring reasons: a source is briefly unreachable, a credential expires, a schema changes, a machine is reclaimed. None of that is preventable. What is designable is what happens on the re-run, and that single property determines whether the data can be trusted.

Idempotent means the second run changes nothing

The definition to hold onto: running the job twice for the same input window produces exactly the same result as running it once. Most pipelines fail this test on the write, not the read.

-- Not idempotent. Re-running doubles the window.
INSERT INTO daily_orders
SELECT order_date, count(*), sum(total)
FROM orders
WHERE order_date = '2026-09-19'
GROUP BY order_date;

-- Idempotent: delete the partition, then write it. The pair must be
-- one transaction, or a failure between them leaves a hole.
BEGIN;
  DELETE FROM daily_orders WHERE order_date = '2026-09-19';
  INSERT INTO daily_orders
  SELECT order_date, count(*), sum(total)
  FROM orders
  WHERE order_date = '2026-09-19'
  GROUP BY order_date;
COMMIT;

-- Or, where the grain has a natural key, let the write resolve it.
INSERT INTO daily_orders (order_date, order_count, revenue)
SELECT order_date, count(*), sum(total)
FROM orders
WHERE order_date = '2026-09-19'
GROUP BY order_date
ON CONFLICT (order_date) DO UPDATE
  SET order_count = excluded.order_count,
      revenue     = excluded.revenue;

• Delete-then-insert per partition, in one transaction, is the general answer and works for any grain
• Upsert on a natural key is cleaner where one exists and the grain is genuinely unique
• Never a bare append for a derived table, unless something downstream deduplicates — and "something downstream" is usually nobody
• Truncate-and-reload is idempotent and fine for small tables; it stops being viable at scale, not in principle

Partition by time, and make the window a parameter

A job that computes "yesterday" from the current date cannot be asked to recompute last Tuesday. Making the window an explicit input is what turns a scheduled job into something that can be backfilled, tested and reasoned about.

The first row contains a subtler problem than it appears. A job whose output depends on the clock is not reproducible, so two people investigating the same discrepancy get different answers depending on when they looked — and neither can tell which is correct.

Backfills are the test of the design

Sooner or later a bug means six months of derived data is wrong. Whether that is an afternoon or a fortnight is decided entirely by choices made when the pipeline was written.

1. 01Backfill one partition first and verify it against the known-correct figure before running the rest.
2. 02Run oldest to newest if anything downstream depends on order, and note that windowed calculations often do.
3. 03Bound the concurrency. A backfill of 180 partitions launched in parallel is a denial-of-service attack on your own warehouse.
4. 04Write to the same tables the scheduled job writes, not to a parallel set someone then has to reconcile.
5. 05Verify a sample after completion, including the boundary partitions, which is where off-by-one errors live.

Late-arriving data, which is normal rather than exceptional

Records arrive after the window they belong to has been processed: a mobile client was offline, a payment settled two days later, a third-party export lagged. A pipeline that computes each day exactly once and never revisits it will silently under-report forever.

• Decide which timestamp defines the partition. Event time is usually correct and arrives late; ingestion time is always available and answers a different question
• Reprocess a trailing window. Recomputing the last three or seven days on each run picks up late arrivals automatically — and is only affordable because the job is idempotent
• Set a cut-off and state it. "Figures for the last three days may change" is a documented property; silent drift is a data-quality incident
• Never let late data create duplicates. Reprocessing a window must replace it, which is the idempotence requirement again

The trailing-window approach is the pragmatic answer for most analytics, and it is worth noticing that it is only available at all because the write is idempotent. The two properties are the same design decision viewed twice.

Design for the failure that happens midway

The failure that hurts is not the job that dies at the start. It is the one that wrote half its output and then stopped, leaving a state that looks complete.

The five questions for any pipeline

1. 01If this runs twice for the same window, is the result identical?
2. 02Can I recompute an arbitrary past window with the same code and no manual steps?
3. 03If it dies halfway, is the partial state invisible to anything reading downstream?
4. 04What happens to a record that arrives three days late, and is that stated somewhere?
5. 05If the source adds, removes or retypes a column, does the job stop or silently continue?

Pipelines answering all five are unremarkable to operate. Pipelines failing two or three are the reason data teams spend their time reconciling numbers rather than producing them — and the difference is design decisions that cost nothing extra at the point they are made.