Late-Arriving Commits and Deletes in a 15-Minute Oracle-to-Snowflake Load
An Oracle ORDERS table with 45 million rows is replicated to Snowflake every 15 minutes. The mapping filters on LAST_UPDATE_DATE > $LastRunTime, where $LastRunTime is set from the previous successful run's start time. The source system sets LAST_UPDATE_DATE when the transaction begins, and replication can deliver commits 90 minutes late; some transactions remain open for several hours. The target is loaded with INSERT, and business users report both missing recent changes and duplicate order IDs. Hard deletes are also performed in Oracle, but no delete flag or delete feed exists; the business expects the warehouse to reflect them. The current load takes 6 minutes, well inside the 15-minute window, but a full compare of 45 million keys is not acceptable every run. Analysts need near-real-time accuracy for order status and revenue.
1. Diagnose why the current watermark loses updates, and describe a changed extraction and target-load design that is idempotent under late commits.
Read the worked answer
The filter is unsafe because LAST_UPDATE_DATE is a transaction-start timestamp, not a commit timestamp. A row can be committed after $LastRunTime has advanced past its timestamp, and a long-open transaction can be committed hours later; strict > then never sees it. Replication lag adds another gap. The INSERT target also makes every overlap or rerun a duplicate.
A safer design:
- Extract by commit time using CDC or a source change table if available. If not, keep LAST_UPDATE_DATE but re-read a deliberate overlap, for example the last 3 hours on every run, and deduplicate.
- Write to a staging table keyed by ORDER_ID plus source commit/version. Deduplicate to the latest version per key using commit time and a deterministic tie-breaker.
- MERGE or UPDATE-INSERT into the target on ORDER_ID so replays are idempotent.
- Set the stored watermark to the maximum source commit time observed, and only after the target transaction succeeds; never derive it from the mapping start time.
With 15-minute runs, a 3-hour overlap is 12x the normal volume but still bounded and index-friendly. Validate by replaying a late-committed transaction and confirming no duplicate and correct final state.
2. How should hard deletes be handled without a delete flag, given the 15-minute SLA and the no-full-compare constraint?
Read the worked answer
Do not infer deletes from absence in an incremental extract; a late-arriving row would look deleted and you would remove valid data. The clean answer is a source soft-delete flag or a delete feed, ideally through CDC. If neither can be obtained, use bounded reconciliation:
- Run a periodic Oracle MINUS or anti-join between source keys and target keys, partitioned by key range or source partition, off-peak. A full 45-million-key compare every run violates the constraint, but a weekly partition-wise reconciliation may be acceptable.
- For near-real-time needs, negotiate a small delete feed or a high-water-mark table populated by the source team. Apply deletes through Update Strategy DD_DELETE or a MERGE delete, and retain history if the business needs it.
- Add a daily bounded compare for recent or high-value orders, and track key-count drift between source and target.
Test with an order deleted outside the incremental window, and document that a delete is applied only from an authoritative delete signal or reconciliation result, never from a missing incremental row.
3. What validation and monitoring would prove the revised load is correct before and after go-live?
Read the worked answer
Before go-live, run targeted tests: a late commit where the source commit time is after the stored watermark; a transaction held open for several hours; a duplicate update; a hard delete; and a rerun of the same window. Reconcile counts and sums per 15-minute window: source distinct keys, staged keys, and target current rows. Check for duplicate ORDER_ID values and compare control totals for amount and status.
After go-live, monitor watermark lag as source max commit time minus stored watermark, MERGE insert/update/delete counts, reject counts, and a recurring duplicate-key query. Run the old and new mappings in parallel to a shadow schema for several days and compare final row hashes for a sample of keys. Alert when lag exceeds the overlap window or when reconciliation shows unexplained key-count drift. Only cut over after the rerun test produces zero net changes.