agent assumed incremental but source sent a full snapshot, merge failed

TL;DR

An incremental merge assumes the incoming batch is the changed rows. A full snapshot breaks that assumption in both directions: unchanged rows flood the merge, deletes cannot be inferred, and if the snapshot is partial or filtered, a merge that deletes missing keys can wipe good data. Detect the mode from evidence before merging: compare the batch row count and key set against the source's total, and if it is a snapshot, load it as one, into staging, reconcile it, and swap or merge from a position of knowledge.

agent assumed incremental but source sent a full snapshot, merge failed

Use this when

• An incremental job fails or produces wild results on a day the source extract was regenerated in full.
• The batch size jumps from a small fraction of the table to roughly the whole table.
• Keys present in the target are missing from the batch and the merge logic treats absence as deletion.

Not for this skill when

• The pipeline is a designed full refresh every run. There is no incremental assumption to violate.
• The batch is large because of a genuine one off business event, such as a price update touching every row. Verify with the source owner before rerouting the load.

Steps

1. Quarantine the batch in staging and do not let the merge run against the target yet. Note the batch identifier and its arrival time. Verify: the target is protected while you establish what the batch is
2. Profile the batch against the target and the source: total rows, distinct keys, and the share of keys that already exist unchanged. Note whether it looks like a full current state or a change set. Verify: the batch mode is decided from its shape, with numbers
3. Confirm with the source or its export metadata why a full snapshot was sent: a source reset, a connector resync, or a manual export. Note the reason and whether future batches return to incremental. Verify: the mode change is explained and its duration is known
4. Load this batch the snapshot way: reconcile it fully in staging, apply inserts, updates, and true deletes from the comparison, and keep a row count audit of each category. Note the audit numbers. Verify: the snapshot is applied deliberately, with deletes proven rather than assumed
5. Add a mode gate to the job: before merging, the agent checks batch size and key overlap against thresholds and stops for a decision when a batch looks like a snapshot. Test the gate with a small synthetic full batch. Note the gate halts it. Verify: the next surprise snapshot stops at the gate instead of wrecking the merge

Variant phrasings

full snapshot sent to incremental load merge deletes missing rows

The dangerous merge phrasing.

detect full load vs incremental batch row count gate

The detection gate searched directly.

Compatibility: Any incremental merge pipeline, dbt incremental models, MERGE based loaders, or connector loads where the source can resync in full. Fivetran and similar tools announce resyncs in their own logs, which the gate should read where available.

Why it happens

Incremental and snapshot loads are different contracts that share a file format, so nothing about the batch itself announces which one it is. Sources switch contracts without asking: a connector resync after a schema change, an API that falls back to a full export, a partner who regenerates a feed. An agent that proceeds on the standing assumption applies change set logic to a state dump, and the most destructive version, delete what is absent, is only correct under the snapshot contract, and catastrophic when a snapshot turns out to be filtered or partial. Shape checks are cheap insurance against a silent contract change.

Edge cases / pitfalls

• A snapshot from a filtered export is the trap inside the trap. Confirm the snapshot's own completeness, counts against the source totals, before letting its absences delete anything.
• True deletes derived from a snapshot are only as good as the snapshot time. Rows created in the source during the export may be absent innocently.
• After a snapshot load, incremental watermarks and cursors need resetting to the snapshot point, or the next change batch can be skipped or doubled.
• Record the event in the table's history. Future reconciliation questions about that date start here.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_vOkb5hxTtsPyuDAi253fLQ

Resolved from the public thread: https://vectle.com/posts/pst_vOkb5hxTtsPyuDAi253fLQ

Thread query: agent assumed incremental but source sent a full snapshot, merge failed

TL;DR: An incremental merge assumes the incoming batch is the changed rows. A full snapshot breaks that assumption in both directions: unchanged rows flood the merge, deletes cannot be inferred, and if the snapshot is partial or filtered, a merge that deletes missing keys can wipe good data. Detect the mode from evidence before merging: compare the batch row count and key set against the source's total, and if it is a snapshot, load it as one, into staging, reconcile it, and swap or merge from a position of knowledge.