Three write modes cover almost every sync you'll ever build, and picking the wrong one is how tables end up with duplicate rows or silently stale data.
No credit card. 14 days. Cancel in one click.
Quick answer: Insert always adds new rows, so running it twice duplicates everything. Update only changes rows that already exist, matched on a key column, and does nothing for rows that don't. Upsert does both: insert the row if the key isn't there yet, update it if it is. In QueryFlow's Data Sync, pick the MODE (Insert, Update, or Upsert) and, for Update or Upsert, a MATCH ON field whose values are unique per row.
Insert is the simplest: every row from the source becomes a new row in the target, no matter what's already there. Run an Insert-mode sync twice against the same source data and you'll have every row twice. It's the right mode for append-only data, like an events or logs table where every row is a genuinely new fact and duplicates would be wrong on their own terms, not because of the sync.
Update changes existing rows and skips everything else. It needs a key column, called MATCH ON in QueryFlow's Data Sync, to figure out which target row corresponds to which source row. A row in the source with no matching key in the target is simply skipped, not inserted. Update is right when you know every row you're syncing already exists in the target and you're only refreshing values.
Upsert (a blend of "update" and "insert") does both in one pass: if the MATCH ON key already exists in the target, the row is updated; if it doesn't, the row is inserted. Most "keep this table in sync with that table" jobs actually want Upsert, because new rows and changed rows both show up in the same sync run and you don't know in advance which kind any given row will be.
In Data Sync, after mapping fields you pick a MODE of Insert, Update, or Upsert. For Update or Upsert, you also choose a MATCH ON field, and it needs to be genuinely unique per row; a duplicate key value in the source for that column produces a "Duplicate key values in source for key column(s)" error rather than a silent overwrite. Against BigQuery or Databricks, Upsert runs as a MERGE statement on the key columns you picked, which is the standard SQL way both warehouses handle this exact operation.
A worked example: syncing a customers table from Postgres into a Databricks table on customer_id as the MATCH ON key, using Upsert. New customers since the last sync get inserted; customers whose email or address changed get updated in place; nothing about existing, unchanged rows is touched.
Not every sync should insert new rows. Say a nightly job refreshes a customer_status column in a target table from a source system of record, and the target table is otherwise populated by a completely different process that owns row creation. Update mode, matched on customer_id, refreshes exactly that column for existing rows and leaves row creation to whatever else owns it, rather than risking an accidental insert if a customer_id briefly doesn't match due to timing between the two systems.
Picking a MATCH ON column that isn't actually unique per row, a region column instead of an order_id, say, produces a "Duplicate key values in source" error rather than a silent bad write, which is the safer failure mode but still worth understanding in advance. The fix is almost always picking the actual primary key, or a composite of columns that together are unique, rather than a column that merely happens to look distinctive in a quick glance at the data.
Choosing Insert mode for an events or logs table only works cleanly if the upstream query itself never returns the same row twice across runs. If a scheduled Insert-mode sync's source query pulls "everything from the last 24 hours" without any tracking of what already synced, overlapping windows will duplicate rows even though Insert itself behaved exactly as designed. The discipline that keeps Insert safe lives in the source query's filter, not in the write mode.
All three modes only touch the columns you've actually mapped. An Update or Upsert leaves every other column in the target row untouched, it doesn't null them out or reset them to a default. That matters when a target table has columns owned by a different process entirely; a partial Upsert from one sync coexists fine with another sync, or a human, managing the rest of that row's columns.
See Upsert into BigQuery or Databricks for the MERGE mechanics specifically, or preview a sync before choosing a mode you're not sure about yet.
14-day free trial, no card. Try Insert, Update, and Upsert on real data.
No credit card. 14 days. Cancel in one click.