Idempotent loads.

The plain definition

Idempotent is a math and programming term before it's an ETL one: an operation is idempotent if applying it multiple times has the same effect as applying it once. Pressing an elevator button you've already pressed doesn't call the elevator twice. In data loading, the same idea answers a very practical question: if a scheduled job fails halfway through, or a network hiccup causes a scheduler to fire the same job twice, does re-running it produce correct data, or does it duplicate or corrupt what's already there?

Idempotency isn't a property of a data source or a schedule, it's a property of how a load writes. The same source query can back an idempotent load or a non-idempotent one, depending entirely on the write mode used to land it.

Why Insert alone isn't idempotent

An Insert-mode sync appends every row from the source to the target, with no awareness of what's already there. Run it once against a given source snapshot and you get one copy of every row. Run it again against an overlapping snapshot, say, a scheduled job that failed after loading half the data and gets retried from the start, and you get the overlapping rows twice. Insert is the right mode for genuinely append-only data, like an events table where every row is a new fact by definition, but it needs the source query itself to guarantee non-overlapping windows across runs; the write mode alone provides no protection against duplicates from a retry.

How Upsert delivers it

Upsert, matched on a key column, is idempotent in the sense that matters for retries: if a row with a given key already exists in the target, running the load again updates that same row rather than adding a second copy. In QueryFlow's Data Sync, Upsert against BigQuery or Databricks runs as a genuine MERGE statement on the key columns you pick, which is the standard SQL mechanism both warehouses provide for exactly this: match on a key, update if found, insert if not.

MERGE INTO `project.dataset.orders` AS target
USING staging_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET target.status = source.status, target.total = source.total
WHEN NOT MATCHED THEN
  INSERT (order_id, status, total) VALUES (source.order_id, source.status, source.total);

Run that MERGE once, or run it five times against the same staging data, and orders ends up in the same state either way, the definition of idempotent, delivered by the write mode rather than by anything special about the source query.

A worked example: a job that gets retried

Say a scheduled job syncing Salesforce opportunities into a Snowflake table fails midway through, after writing half the batch, and QueryFlow's retry logic re-runs it from the start. With Insert mode, the opportunities written before the failure now exist twice in Snowflake once the retry completes. With Upsert mode, matched on opportunity_id, the retry updates those same rows back to their correct values instead of duplicating them, the retry is safe precisely because the load is idempotent.

When Insert's lack of idempotency is fine

Not every load needs this property. A true append-only events table, where the source query is scoped to a window that never overlaps between runs (an incremental load keyed on a monotonically increasing timestamp or ID, tracked so each run only pulls genuinely new rows), can use Insert safely without needing Upsert's key-matching overhead. The discipline just has to live in the source query's filter instead of in the write mode.

Related: Upsert vs. Insert vs. Update for the full mode comparison, upserting into BigQuery or Databricks, and incremental loads for the append-only pattern.