Incremental snapshot with deletes, trigger bypass and a reset watermark

Pattern. A Scheduler snapshot job on last_modified_ts, writing to Snowflake with an idempotent MERGE on the booking primary key. Idempotency is the foundation: once re-reading a range is harmless, everything else becomes a tuning choice rather than a correctness risk.

Watermark. Take the high-water mark from the source, not from the target. At the start of the run, capture the source's MAX(last_modified_ts) (or the source-side current timestamp in UTC) and persist it only after the load succeeds. If you instead derive the next start from MAX(last_modified_ts) in the Snowflake table, you silently skip rows committed with an older timestamp after the run began - a long-running job over a busy table guarantees this. Persisting after success also means a failed run retries the same window rather than advancing past it.

Overlap. Use >= (not >) against the stored watermark and subtract an overlap window, e.g. re-read the last 60 minutes. Rows can be committed out of timestamp order by long transactions and clock skew; the MERGE makes the overlap free of duplicates.

Time zones. The watermark must be stored and compared as an absolute instant (UTC timestamptz), never as a local-time string. A CET/CEST mismatch at the DST switch shifts the window by an hour, which either re-reads an hour or, worse, skips one. Normalise at the view, not in the job parameter.

Initial run. Seed the watermark deliberately (e.g. 12 months back) and run the first load as a bounded backfill, not a 260-million-row single query.

Neither of these is solved by the watermark; both are data-quality defects in the pipeline design and must be named as such.

Deletes. The source keeps no tombstone, so a purge is invisible to any timestamp-based read. Options, in order:

1. Change the source: have the purge set a deleted_flag and update last_modified_ts, or maintain a purge audit table. Cheapest and most reliable; the source is under the same organisation.
2. CDC: logical replication or Debezium into a stream Denodo can read. Heavier, but it also solves the COPY problem and gives ordering.
3. Key-set reconciliation: monthly (or weekly) pull only booking_id from the source and MINUS/EXCEPT it against the landing table to find keys that no longer exist, then delete them in Snowflake. Feasible because it is a narrow column, but it must be scheduled, not remembered.

COPY load bypassing the trigger. Do not paper over this. Either set the timestamp inside the COPY itself, or run a reconciliation over the affected key range each month. If the corrections are confined to a known key set (e.g. a route or a date band), a targeted re-read is far cheaper than a full compare.

What finance is told. The mart is not guaranteed correct between reconciliations. State the exposure honestly: up to 30,000 removed bookings and 400,000 stale rows per month until the source or CDC fix lands, with a documented reconciliation run and sign-off at month end. A silently wrong finance mart is a far worse outcome than a 6-hour job. Also add a monthly control that compares row counts and a checksum of key financial columns between source and landing.

What happened. The Scheduler stores the incremental field's last value in its own metadata database. Reinitializing it destroyed that state, so the job fell back to no watermark and behaved like a first run - a 260-million-row read. That is correct behaviour in the sense that the job had no other way to know where it was, but it is an outage if it happens at 02:00 without warning.

Controls:

• Protect and back up the metadata database. Treat it as a stateful production component, not an installation artefact. Restore rather than reinitialize when possible.
• Make the watermark reseedable. After any reset, set the incremental start value explicitly from the landing table's own MAX(last_modified_ts) or from a control table before re-enabling the job, and have that written into the runbook. This is the step that turns a six-hour surprise into a one-minute action.
• Alert on volume anomaly. A run that touches orders of magnitude more rows than the typical 150,000 is the signature of a lost watermark. Alert on row count per run, not just on job failure - this job succeeded, which is why it went unnoticed.
• Prevent overlap. If a run overruns its schedule, the next invocation must not start concurrently; enforce single-instance execution and alert on runs approaching the SLA, rather than discovering it at 04:00.
• Ordering. Add a job dependency on the source purge and correction jobs so the snapshot does not read mid-purge, and notify on failure.
• SLA arithmetic. Test the incremental path against a full reload: if incremental ever needs to fall back, the 6h15m full run no longer fits the window, so the fallback itself needs a plan.