Design an incremental SQL extract with late-arriving records

An incremental extract reads newly available records instead of repeatedly copying the entire source. Its cursor must track availability under a documented source contract. Using business event time as the only cursor can miss an event that occurred earlier but arrived after the previous run.

Reliable incremental loading also needs stable batch boundaries, unique record identity, replay handling and a rule for advancing the checkpoint only after successful persistence.

Observe the late record

The advanced SQL dataset includes event E08: a customer view at 11:50 that arrives at 12:10. A first extract ending at 12:05 receives seven events. The second extract, ending at 12:15, must receive E08 even though its event time precedes the first cutoff.

SELECT event_id, event_at, ingested_at
FROM events
WHERE ingested_at >= '2026-01-01T12:05:00'
  AND ingested_at < '2026-01-01T12:15:00'
ORDER BY ingested_at, event_id;

Expect E08, with event_at 11:50 and ingested_at 12:10. Filtering event_at over the same interval returns no rows and misses the late arrival.

This example assumes immutable ingestion timestamps assigned on receipt, no later visibility of rows backdated before an already completed ingestion cutoff, and immutable event payloads. Those assumptions are part of the exercise, not automatic properties of every source database.

Fix the upper boundary for each run

At the start of a batch, choose an upper cutoff and persist it with the run metadata. Read the half-open interval from the previous successful cutoff inclusive to the new cutoff exclusive. A record exactly at the upper boundary belongs to the next batch.

Do not continually move the upper bound while paginating. Otherwise the batch population changes during extraction, complicating completeness and restart behavior. For pagination within a fixed interval, use a stable ordering and a cursor that includes a unique tie-breaker when timestamps repeat.

An ingestion timestamp is suitable only if its relationship to transaction visibility is reliable. A source can assign an earlier timestamp and commit later, causing a naive timestamp cursor to skip the row. A committed change sequence, source-supported change feed or overlapping reread strategy may be necessary, depending on the source contract.

Make retries preserve the same result

Run operational_checks.py in the lab directory. It copies the two batches into an in-memory destination and deliberately delivers each batch twice. The destination has event_id as its primary key. Because the fixture's events are immutable and replays identical, duplicate IDs can be ignored without changing the result.

The saved operational results show seven destination rows after the first batch and eight after the second. Replaying either batch does not increase those counts. The test also confirms that the event-time cursor would miss E08.

If payloads can change under an existing event ID, ignoring duplicates is unsafe. Define revision handling or flag conflicting payloads. The deduplication lesson separates identical replays from contradictory updates.

Advance the checkpoint after durable success

If the destination write fails, retain the old checkpoint so the same batch can be retried. If writing succeeds but checkpoint persistence fails, replay safety allows the batch to run again without duplicating business records.

Where practical, commit destination changes and checkpoint state atomically. Across systems that cannot share a transaction, use durable run state and a replay-safe protocol. The teaching script uses a local destination transaction and in-memory checkpoint; it demonstrates ordering, not a complete crash-recovery system.

Python's sqlite3 transaction documentation explains the connection behavior used by the local example.

Recompute the affected analytical periods

Capturing E08 is only the ingestion step. The event belongs to an earlier business-time window, so its arrival may change a previously calculated session or funnel. Identify affected partitions from the new records' event times and refresh those results according to the correction policy.

A fixed lookback can reduce missed late data, but it cannot guarantee completeness if arrivals can be arbitrarily late. Track delay distributions and exceptional arrivals, and provide a backfill route beyond the normal refresh window.

Exercise: simulate failure before checkpoint advancement and rerun the batch. Then change E08's payload under the same ID. Explain why the fixture's immutable-event policy must now reject the conflict or use a version-aware update.

NeuraPath's Data Analytics with Generative AI course connects SQL and Python workflows with reporting correctness. An incremental-extract project should demonstrate retries and late-data correction alongside its faster routine refresh.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the preceding task in Parameterize Python SQL queries without string interpolation.
• Return to the cluster foundation in ROW_NUMBER versus RANK versus DENSE_RANK on tied sales.