Incremental Load Design AI Prompt

Design a robust incremental data load pattern for this source table.

Source: {{source_table}} in {{source_db}}
Target: {{target_table}} in {{target_db}}
Update pattern: {{update_pattern}} (append-only / append-and-update / full CRUD)

1. Watermark management:
   - Store the last successful watermark (max updated_at or max id) in a dedicated metadata table
   - Watermark must be committed only after successful write to target — never before
   - Handle clock skew: use watermark = max(updated_at) - safety_margin (e.g. 5 minutes) to catch late-arriving rows

2. Incremental query:
   - SELECT * FROM source WHERE updated_at > {{last_watermark}} AND updated_at <= {{current_run_time}}
   - Use a closed upper bound (current run time) to make the window deterministic and replayable
   - Add an ORDER BY to ensure consistent extraction order

3. Merge/upsert to target:
   - Use MERGE statement (or equivalent) matching on primary key
   - Handle inserts (new rows), updates (changed rows), and optionally soft deletes (is_deleted flag)
   - Never use INSERT blindly — always upsert to maintain idempotency

4. Hard delete handling:
   - If source supports deletes and CDC is not available: run a full key scan periodically (e.g. daily) and soft-delete rows absent from source
   - Add deleted_at and is_current columns to target table

5. Backfill procedure:
   - To re-process a date range: set watermark back to range start and re-run
   - Ensure the merge logic is idempotent so backfill does not create duplicates

6. Schema change handling:
   - Before each run, compare source schema to last known schema
   - Alert on new, removed, or type-changed columns before proceeding

Return: watermark table DDL, incremental query, merge statement, delete handling, and backfill procedure.