Incremental SQL Loads: Replay, Checkpoints, and Deletes

For a reliable incremental load, capture a complete committed change feed, keep the latest source version for every key, retain delete markers, and commit target changes with their checkpoint. Replaying a batch should produce the same state. An older update should never overwrite a newer update or bring back a deleted row.

This PostgreSQL 16+ tutorial gives you a complete, read-only test of that apply stage. It calculates candidate rows and a candidate checkpoint from fictional data; it creates no tables and writes nothing. It does not configure change data capture (CDC), discover changes from a source table, or implement a production writer.

Agree on the source contract before writing SQL

The example assumes one ordered feed for one scope, consumed by one writer. Start from a target snapshot coordinated with that feed at checkpoint 5. A checkpoint means every relevant committed change through that position has been represented in the target.

• Each change has a stable, unique, totally ordered position. The source delivers only committed changes and certifies a complete prefix ending at a committed transaction boundary.
• Every insertion, update, and hard deletion is included. U supplies a complete after-image; D supplies a key and a delete marker. A key change requires deleting the old key and creating the new one.
• Re-delivery preserves the position and payload exactly. Multiple positions may affect one key. Positions need not be consecutive.
• The target stores one latest version per key, including deleted keys. Feed identity, scope, and schema remain compatible across retries.

The integer positions below are a teaching representation. A real adapter might need a transaction log position plus an event ordinal to order individual changes. A raw log position shared by several row changes is insufficient as this example's unique event position. Multiple partitions require their own checkpoint design.

BIGSERIAL or an identity column does not establish commit order: one transaction can obtain an ID and remain uncommitted while another commits. PostgreSQL also documents that sequence allocations are not rolled back. See the sequence functions documentation.

Understand what a timestamp filter cannot prove

Imagine a transaction starts at 10:00 and commits at 10:05. A loader runs at 10:03 and advances a timestamp watermark to 10:03. If the late commit carries updated_at = 10:00, the next updated_at > watermark filter misses it. PostgreSQL's now() and CURRENT_TIMESTAMP use the transaction start time, as documented under date and time functions.

A lookback reduces risk only within its assumptions; it does not guarantee capture of arbitrarily late commits. An upsert applies captured rows and cannot discover a hard-deleted source row. If you have only read access and no trustworthy change feed, use a validated complete snapshot comparison instead. Do not label an ordinary timestamp or increasing ID a committed feed position.

Run the complete candidate-state example

Copy the whole query into a PostgreSQL 16+ query editor. The initial state contains products 100, 200, and 300, with an active amount total of 60. The supplied receipt asserts completeness through position 9. That assertion is an external source obligation: setting prefix_complete to true cannot make an incomplete extract safe.

The delivery contains five rows but four distinct changes: update 100, delete 200, insert 400, repeat the identical insertion, and update 300. NULL, empty text, and zero remain different values. A delete must have a null payload; an update's null field is a real stored null, not an omitted patch field.

WITH
receipt(previous_checkpoint, certified_end, prefix_complete) AS (
  VALUES (5::bigint, 9::bigint, true)
),
current_state(product_id, last_position, is_deleted, amount, note) AS (
  VALUES (100, 2::bigint, false, 10::numeric, NULL::text),
         (200, 3::bigint, false, 20::numeric, 'ok'),
         (300, 5::bigint, false, 30::numeric, 'keep')
),
delivery(position, product_id, operation, amount, note) AS (
  VALUES (6::bigint, 100, 'U'::text, 12::numeric, ''::text),
         (7::bigint, 200, 'D', NULL::numeric, NULL::text),
         (8::bigint, 400, 'U', 0::numeric, 'new'),
         (8::bigint, 400, 'U', 0::numeric, 'new'),
         (9::bigint, 300, 'U', 35::numeric, NULL::text)
),
all_versions AS (
  SELECT last_position AS position, product_id,
         CASE WHEN is_deleted THEN 'D' ELSE 'U' END AS operation,
         amount, note
  FROM current_state
  UNION ALL
  SELECT position, product_id, operation, amount, note FROM delivery
),
distinct_versions AS (
  SELECT DISTINCT position, product_id, operation, amount, note
  FROM all_versions
),
errors(issue) AS (
  SELECT 'invalid_receipt'
  WHERE (SELECT count(*) FROM receipt) <> 1 OR EXISTS (
    SELECT 1 FROM receipt WHERE previous_checkpoint IS NULL
      OR certified_end IS NULL OR prefix_complete IS DISTINCT FROM true
      OR previous_checkpoint < 0 OR certified_end < 0
  )
  UNION ALL
  SELECT 'invalid_target_key_or_state' WHERE EXISTS (
    SELECT 1 FROM current_state
    WHERE product_id IS NULL OR last_position IS NULL OR last_position < 1
       OR is_deleted IS NULL
       OR (is_deleted AND (amount IS NOT NULL OR note IS NOT NULL))
  ) OR EXISTS (
    SELECT 1 FROM current_state GROUP BY product_id HAVING count(*) > 1
  )
  UNION ALL
  SELECT 'target_ahead_of_checkpoint' WHERE EXISTS (
    SELECT 1 FROM current_state CROSS JOIN receipt
    WHERE last_position > previous_checkpoint
  )
  UNION ALL
  SELECT 'invalid_delivery' WHERE EXISTS (
    SELECT 1 FROM delivery CROSS JOIN receipt
    WHERE position IS NULL OR product_id IS NULL OR operation IS NULL
       OR position < 1 OR operation NOT IN ('U', 'D')
       OR position > certified_end
       OR (operation = 'D' AND (amount IS NOT NULL OR note IS NOT NULL))
  )
  UNION ALL
  SELECT 'conflicting_position_payload' WHERE EXISTS (
    SELECT 1 FROM distinct_versions GROUP BY position HAVING count(*) > 1
  )
  UNION ALL
  SELECT 'unseen_change_behind_checkpoint' WHERE EXISTS (
    SELECT 1 FROM delivery d CROSS JOIN receipt r
    LEFT JOIN current_state s ON s.product_id = d.product_id
    WHERE d.position <= r.previous_checkpoint
      AND (s.product_id IS NULL OR d.position > s.last_position)
  )
),
ranked AS (
  SELECT *, row_number() OVER (
    PARTITION BY product_id ORDER BY position DESC
  ) AS rn FROM distinct_versions
),
output AS (
  SELECT 'validation_error'::text AS row_kind, issue,
         NULL::integer AS product_id, NULL::bigint AS last_position,
         NULL::boolean AS is_deleted, NULL::numeric AS amount,
         NULL::text AS note, NULL::bigint AS candidate_checkpoint
  FROM errors
  UNION ALL
  SELECT 'batch', NULL::text, NULL::integer, NULL::bigint,
         NULL::boolean, NULL::numeric, NULL::text,
         greatest(previous_checkpoint, certified_end)
  FROM receipt WHERE NOT EXISTS (SELECT 1 FROM errors)
  UNION ALL
  SELECT 'state', NULL::text, product_id, position,
         operation = 'D', amount, note, NULL::bigint
  FROM ranked WHERE rn = 1 AND NOT EXISTS (SELECT 1 FROM errors)
)
SELECT * FROM output ORDER BY row_kind, product_id NULLS LAST, issue;

This is a validation gate: any validation_error row suppresses both candidate state and checkpoint output. Exact duplicates collapse with DISTINCT. Conflicting records at one position fail validation before a winner can be released. The remaining positions provide a deterministic order for row_number() within each key.

Check the entire expected result

Expect one batch row with candidate_checkpoint = 9, followed by these four state rows. There should be no validation errors. The other columns on the batch row and the candidate-checkpoint column on state rows are null.

Reconcile keys and values: four retained keys, three active keys, one tombstone, and active amount 12 + 35 + 0 = 47. A business-facing query filters is_deleted = false. Do not physically remove product 200 from the version store: its position 7 is what prevents an old update from reviving it.

A matching total alone is insufficient. Confirm every key, version, deletion flag, and payload, including product 100's null-to-empty transition and product 300's text-to-null transition. Unknown amounts would need an explicit completeness check before publishing a total; treating them as zero could conceal missing data.

Make replay and failure behavior observable

Use the query's three input CTEs as a small acceptance-test fixture. For tests starting after success, put the four expected state rows in current_state and set previous_checkpoint to 9.

For an empty input, replace its VALUES with SELECT NULL::bigint, NULL::integer, NULL::text, NULL::numeric, NULL::text WHERE false. An empty batch may advance a checkpoint only when the source certifies that no relevant changes were omitted through the new boundary. Neither an empty result nor MAX(position) supplies that proof.

The query and failure variants were checked with PostgreSQL 16.14 using read-only literal queries, alongside an independent in-memory Python reducer. This verifies the illustrated state transitions, not an actual connector, writer, or database crash-recovery implementation.

Keep durable state and checkpoint together

Before implementing writes, define a transaction owned by one writer for this feed. Acquire its checkpoint lock, verify the expected previous checkpoint and source identity, validate the staged delivery, apply only strictly newer versions, and store candidate_checkpoint = greatest(previous_checkpoint, certified_end) in the same target transaction. Never rewind the durable checkpoint when replaying an older certified prefix. A version at the same position must match the retained payload; disagreement is an error.

If that transaction fails, both target changes and checkpoint must roll back. If it commits but the client loses the acknowledgment, reading the durable checkpoint and safely replaying must produce the same result. A checkpoint committed before target changes can skip data. Separately committed partial target writes make the simple recovery contract harder to satisfy.

When source acknowledgment belongs to another system, acknowledge only after the target commit and design for re-delivery. PostgreSQL's logical decoding documentation explicitly discusses replay after crashes and the client's responsibility to handle it. A destination upsert does not establish an end-to-end exactly-once guarantee.

This fixture combines all retained state for clarity. It is not a throughput recipe for repeatedly sorting a large target. A production implementation needs indexed keys, bounded staging, suitable locks, and measured batches. See the recurring SQL report guide for release checks and operational recovery.

Troubleshoot blocked batches without weakening the gate

Invalid receipt: require exactly one control row, valid boundaries, and a genuinely certified prefix. Invalid delivery: inspect null keys or positions, unknown operations, positions beyond the certified boundary, and nonnull delete payloads. Preserve rejected input for investigation.

Conflicting position: investigate unstable event identity or scope mixing; do not break ties using arrival time. Target ahead of checkpoint or unseen change behind checkpoint: investigate a mismatched snapshot, missing tombstones, out-of-band writes, or an incorrectly advanced checkpoint. Repair through an explicit reconciliation or coordinated reinitialization, rather than hiding the error.

FAQ

Can I discard tombstones after a week?

Only with a documented retention protocol proving older events can no longer be replayed and that initialization cannot reintroduce stale versions. Elapsed wall-clock time alone is insufficient. Otherwise retain the key and last position, even if you archive other deletion details.

Does a SQL MERGE or upsert solve this by itself?

It can implement part of applying a validated batch. It still needs source capture, version comparisons, deletion signals, concurrency control, and a durable checkpoint. The read-only candidate query deliberately leaves those implementation choices to the target system.

Will this preserve every intermediate change?

No. The result is current state per product. If you need audit history, financial movements, or every intermediate status transition, retain an event history and define its processing separately. Reducing to the latest version intentionally discards intermediate states.