All posts

Missing or duplicate data in incremental ETL: CDC, retries, and quality checks

October 27, 2026 · 10 min read

The job succeeded, but revenue does not reconcile. Follow an order analytics pipeline through timestamp cursors and late commits, CDC snapshots and deletes, retry-safe publication, JOIN grain, and freshness versus completeness, drawing on five related tips.

What is incremental ETL? A successful job with revenue that does not reconcile

An incremental load captures data changed since a previous run and applies it to a destination. ETL extracts, transforms, and loads; ELT transforms after loading. Either approach needs explicit extraction boundaries, recovery positions, and publication units to handle missing and duplicate data.

Start by asking whether the source orders arrived, whether retries published them twice, and whether aggregation preserved order grain. A job's exit code cannot answer those questions. Five related tips follow the path from the order database to the revenue dashboard.

Why updated_at misses changes: update time is not commit order

Consider reading only rows newer than the previous maximum update time. Under PostgreSQL 18 Read Committed, an ordinary SELECT reads data committed before the statement began. From that visibility rule, we can construct the following missed-change scenario when timestamps are assigned before commit.

Adding an ID does not change A's older timestamp. Making an ordering unique and capturing changes that become visible later are separate problems. An increasing ID alone needs the same scrutiny: allocation order does not necessarily match commit order.

SELECT id, updated_at, amount_yen, status
FROM orders
WHERE (updated_at, id) > ($1, $2)
  AND updated_at < $3
ORDER BY updated_at, id
LIMIT 1000;

Assume updated_at is NOT NULL and id is unique and NOT NULL. Bind $1 and $2 to the last timestamp and ID on the previous page, and $3 to a fixed upper timestamp for this extraction. The row comparison paginates tied timestamps. This explanation assumes a fixed visible dataset; the query alone does not guarantee loss-free extraction while rows change or transactions commit late.

To keep timestamp polling, verify that every update path maintains the timestamp, then reread an overlapping range. Handle duplicates by primary key and a trustworthy version so stale values cannot overwrite newer ones. A finite overlap cannot cover delays beyond that range. Hard-deleted rows also disappear from SELECT results, requiring a deletion journal, periodic reconciliation, or another path.

Choosing timestamp polling, CDC, or full replacement

Decide whether you need current state or every intermediate change. If an order changes twice, polling may observe only its final state. A current-state aggregate and an audit of every transition cannot adopt the same method without checking that requirement. The following comparison is a design aid for this fictional service.

Starting CDC: do not start the log at the copy's completion time

CDC, or Change Data Capture, extracts changes. The Debezium PostgreSQL connector takes an initial consistent snapshot and transitions to changes at its associated position in the write-ahead log, or WAL. The boundary must cover changes during copying; an arbitrary wall-clock completion time is not the starting position.

CDC still needs a safe destination. Recovery can replay changes, so simply adding each event to revenue can double count it. Here, apply changes to current state by order ID, managing order and versions and explicitly applying deletes. Consistent aggregation across tables also needs an applied boundary that accounts for completed source transactions. Advance the checkpoint after destination application is durable. If checkpoints live in another system, design for a crash between those steps to cause harmless replay.

PostgreSQL replication slots can retain WAL needed by a consumer. Monitor retained volume and free disk during outages, checking retention limits and slot validity. If required history is gone, jumping to the newest position leaves a gap. Define resnapshot and reconciliation procedures, and keep the dashboard from presenting refreshed figures as confirmed until validation finishes.

Avoid duplicate retries: stage, validate, then publish

Suppose an October 1 aggregate writes 100 rows but loses its completion response. Appending the same 100 rows on retry produces 200. A fresh run ID does not remove the duplicate business data. Airflow's best practices recommend repeatable task results, fixed input ranges, and techniques such as UPSERT.

Here, rebuild the interval in staging that readers cannot access, validate it, and publish it together. Reproducibility needs more than an interval: pin the version of source data through a defined cutoff and the transformation code. Reading whatever is latest on every attempt allows inputs to change between retries.

expected_generation = read_current_generation(interval)
input = pin_input_version(interval, source_cut)
output = build_in_staging(input, transform_version)
validate(output, expected_scope)

with destination_transaction():
    lock_publication_record(interval)
    if committed(publication_id):
        result = recorded_result(publication_id)
    else:
        require current_generation(interval) == expected_generation
        replace_interval(interval, output)
        record_publication(publication_id, input.version, transform_version)
        advance_generation(interval)
        result = publication_id
return result

Keep publication_id fixed across retries of the same publication. Lock the interval's publication record and return the recorded result if already committed. Otherwise, allow publication only when the current generation matches expected_generation recorded before staging was built. Change the output, publication record, and generation in the same transaction. On a conflict, roll back, inspect the current version, and rebuild or stop. The pseudocode uses with as an abstraction that commits on normal exit and rolls back on exceptions. Enforce publication ID uniqueness at the destination as well.

If the commit response is lost, reconnect and query the record using the same publication_id. This design commits output and publication records in one destination database. PostgreSQL transactions hide intermediate changes from other transactions. A dashboard reading through multiple queries also needs a contract to pin one version. This database transaction does not automatically include writes to external files.

For object storage, consider immutable completed files referenced by a manifest, with a conditional switch of the published reference. Readers must follow that publication contract. Moving several files one after another does not atomically publish the entire dataset. Enforce destination-side conditions so an older run finishing late cannot overwrite a newer publication.

Matching counts, wrong totals: check the grain after a JOIN

Correct ingestion and publication do not prevent a transformation from inflating amounts. Grain means what one row represents. JOIN a ¥3,000 order to three line items and each row can carry ¥3,000. A naive SUM returns 3,000 × 3 = ¥9,000. Three rows can be correct while revenue at order grain remains ¥3,000.

SUM(DISTINCT amount_yen) is not a general repair. Two different ¥3,000 orders should total ¥6,000, but that expression collapses them to ¥3,000. Aggregate at order grain, or define line-item amounts using business rules before summing. Counts can also match when one missing row offsets one duplicate.

dbt provides unique, not_null, accepted_values, relationships, and custom data tests. Your team still defines revenue semantics and reconciliation scope. Align cutoffs rather than comparing the source's current values directly with yesterday's finalized version. If each run checks changed intervals and full reconciliation runs periodically, account for delayed detection outside the changed scope.

Freshness and completeness: the newest row does not establish complete coverage

A job succeeds at 09:45, and its newest row is timestamped 09:44. If contiguous data is confirmed complete and published only through 09:00, confirmed coverage lags by 45 minutes. That exceeds our illustrative 15-minute target by 30 minutes. The one-minute lag calculated from the newest row measures something different.

dbt freshness checks use the configured timestamp field, supported metadata, or other configured signals. That check alone does not establish complete coverage of every interval. This design records contiguous completion per source and allows publication only through the scope covered by every required input. Distinguish a legitimate empty interval from a stopped source using business calendars, heartbeats, and completion notifications.

Validate the design with stops, replay, and partial gaps

One successful run does not demonstrate safe recovery. Inject failures appropriate to the chosen method, inspecting destination data and the version readers can access. These are proposed tests for this pipeline, not product validation performed for this article.

1. Commit a change after extraction with updated_at behind the cursor. Test delays inside and outside the overlap window, checking detection and repair.
2. Update and delete orders during the initial copy. After CDC handoff, reconcile source and destination at the same cutoff.
3. Stop after applying destination data but before saving the recovery position. Replay the change and verify that revenue is not duplicated and deleted rows stay deleted.
4. Stop before publication and just before the commit response. Verify that readers cannot access an unvalidated version and that the same publication ID supports lookup and retry. Finish an older run late and confirm its generation conflict blocks publication.
5. Include orders with multiple line items, different orders with equal amounts, and inputs whose missing and duplicate rows cancel in the count. Verify checks beyond counts detect the errors.
6. Stop one input while another supplies a recent row. Verify the completeness alert remains active and legitimate empty input is distinguishable.

FAQ: are shorter intervals, UPSERT, or CDC sufficient?

• Does more frequent extraction prevent loss? It can reduce observation delay but does not fix commits appearing later with older update timestamps.
• Does UPSERT make retries safe? A unique key can prevent duplicate rows, but stale overwrites, deletes, double-added aggregates, and partial publication need separate handling.
• Does CDC provide end-to-end exactly-once processing? Check the guarantee across capture, delivery, destination, aggregation, and publication. Where replay can occur, the receiver must apply the same change safely.

Primary sources

The official documentation below was checked on October 27, 2026. Product behavior is distinguished from the illustrative failures and designs constructed from it. Before adoption, verify the versions and settings of the connector, destination, and adapters you select.

• PostgreSQL 18 — Transaction Isolation: Read Committed
• PostgreSQL 18 — Row Constructor Comparison
• Debezium — PostgreSQL connector: snapshots and recovery
• PostgreSQL 18 — Logical Decoding Concepts: replication slots
• Apache Airflow — Best Practices: creating a task
• PostgreSQL 18 — Transactions
• dbt — Data tests
• dbt — Freshness configuration