BytesByBytes
    Data Engineering

    Incremental Pipelines That Can Be Run Twice

    Idempotency is the property that turns a fragile nightly job into something you can retry without thinking. Here is how to build it into a pipeline from the start.

    4 min read

    Every data pipeline is eventually run twice. A task times out and the scheduler retries it. Someone backfills a week. A deploy replays yesterday because the deploy before it was wrong.

    If running the same job twice produces different results, all of those are incidents. If it does not, they are Tuesdays.

    The property, stated precisely

    A pipeline step is idempotent when running it n times with the same inputs leaves the destination in the same state as running it once.

    Note what that does not say. It does not say the step produces no output on the second run, and it does not say it is fast. It says the end state matches. That distinction is what makes idempotency achievable in practice - you are allowed to redo work, you are just not allowed to accumulate it.

    Append is the enemy

    The default shape of an incremental load is an append:

    SQL
    INSERT INTO events_daily
SELECT * FROM staging_events
WHERE event_date = '2026-07-30';

    Run this twice and you have every row twice. Every downstream count is wrong, and nothing errors. The fix is not a deduplication step further down the graph; it is to make the write itself replace a well-defined region.

    SQL
    BEGIN;

DELETE FROM events_daily
WHERE event_date = '2026-07-30';

INSERT INTO events_daily
SELECT * FROM staging_events
WHERE event_date = '2026-07-30';

COMMIT;

    Delete-then-insert inside a transaction is unglamorous and it is correct. The partition is the unit of work, and the job owns it completely.

    On engines with partition-level atomicity you get the same guarantee more cheaply:

    SQL
    INSERT OVERWRITE TABLE events_daily
PARTITION (event_date = '2026-07-30')
SELECT * FROM staging_events
WHERE event_date = '2026-07-30';

    Choose a watermark you control

    The other half of incrementality is deciding which rows are new. The tempting choice is the source system’s updated_at, and it will hurt you: clocks drift, transactions commit out of order, and a row updated during your read window can be committed after your high-water mark has moved past it.

    Three options, in increasing order of reliability:

    1. Source timestamp with a lag window. Read up to now() - 15 minutes and re-read the last hour each time. Combined with an idempotent write, the overlap costs nothing.
    2. Monotonic sequence from the source. A log sequence number or change-stream position is not subject to clock skew.
    3. Change data capture. The source tells you exactly what changed, in commit order.

    Option one is fine for most pipelines and costs a day of work. Option three is right when correctness is non-negotiable and costs a quarter.

    The reason to re-read an overlapping window is not that you expect late rows. It is that you cannot prove you did not miss any, and re-reading is cheaper than proving it.

    Keys, not row identity

    Idempotent writes need a key that means the same thing on both runs. Surrogate keys generated at load time - a UUID, an autoincrement, a row number - break this immediately, because run two generates different ones.

    Use a deterministic key derived from the source:

    SQL
    SELECT
  md5(source_system || ':' || source_id || ':' || event_version) AS event_key,
  ...
FROM staging_events;

    If a natural key genuinely does not exist, hash the full business payload. It is not elegant, but a hash of the data is stable across runs and a nextval() is not.

    Make the pipeline say what it did

    A retryable pipeline needs a record of what has already been produced, or the retry logic becomes folklore. A single audit table covers most of it:

    Column Purpose
    job_name Which step
    partition_key What region of the destination it owns
    status running, succeeded, failed
    row_count What it wrote
    started_at / ended_at Duration, and detection of stuck runs

    With this table, “has 2026-07-30 been loaded?” is a query rather than a conversation. It also gives you the row count needed to catch the failure mode idempotency does not prevent: a run that succeeds and writes nothing.

    The checks that pay for themselves

    • Row count against the source, per partition. Cheap and catches truncated reads.
    • Uniqueness of the business key. Catches the day someone changes the grain upstream.
    • Non-null on the partition column. A null partition key silently routes rows into a bucket no downstream query reads.

    Run them in the same transaction as the write where the engine allows it. A failed check that leaves bad data in place is a check you will learn to ignore.

    What this buys you

    Once every step owns a partition and every write replaces rather than appends, the operational model collapses to one rule: if a run failed, run it again. No manual cleanup, no “check whether it got halfway”, no deduplication script that someone runs from their laptop.

    That rule is worth more than any amount of pipeline monitoring.