How do Hightouch's Upsert, Update, Insert and Mirror sync modes differ?
answer
- Four modes, one of them deletes
- Create only, change only, or both
- What happens to rows that left the model?
- Leaving the query is not being deleted
- Availability depends on the destination API
basics
~20 sInsert only creates new destination records, Update only changes records that already exist, Upsert does both, and Mirror additionally removes destination records for rows that dropped out of the Hightouch model. Which modes are offered depends on the destination.
solid answer
~50 sHightouch sync modes decide what a run is allowed to do to the destination. **Insert** creates records for new rows and never touches existing ones. **Update** writes changed fields onto records that already match, and does nothing when there is no match — so it can never create. **Upsert** is the common choice: create if unmatched, update if matched. **Mirror** goes one step further and makes the destination reflect the model exactly, which means rows that no longer appear in the model are removed or deleted downstream. That removal behaviour is the one to be careful with: a model that silently returns fewer rows — a broken join, a filter on a stale table — becomes a mass deletion in your CRM or ad audience. Mode availability is per-destination, since it depends on what that API supports.
code
text · 9 linesModel primary key = customer_id
run 1 result: 101, 102, 103
run 2 result: 101, 102 (103 no longer matches the query)
row 102 changed a mapped column
Insert -> nothing sent (no new rows); 103 untouched
Update -> writes 102 only; 101 unchanged, 103 untouched
Upsert -> writes 102; creates 101/102 if missing; 103 untouched
Mirror -> writes 102 AND removes 103 in the destinationgo deeper
Learn the four names and the one-line behaviour of each, especially that only one of them ever removes anything in the destination.
Explain how each mode maps onto the added, changed and removed classes of the run's diff, and why record creation ownership decides between Update and Upsert.
Show the operational instinct: a shrinking model plus a removal-capable mode is a mass-deletion incident, so you gate it with row-count checks and prefer a reversible status flag.
Own the policy question of which destinations are ever allowed a destructive mode, who reviews that choice, and how downstream state that lives only in the SaaS tool is protected from warehouse mistakes.
## The four modes A Hightouch sync's mode is the contract between the diff Hightouch computed and the writes it is allowed to make. - **Insert** — only rows that are new relative to the previous run are sent, and they are created as new destination records. Existing records are never modified. Useful when the destination object is append-only by nature, such as writing activity or event-like records where updating history would be wrong. - **Update** — only rows that matched an existing destination record are written, and only their mapped fields change. If no record matches the identifier, nothing is created. This is the mode you pick when another system owns record creation and you are only enriching what it created — a CRM where sales reps create the contacts and the warehouse supplies the scores. - **Upsert** — create when there is no match, update when there is. The default choice for most customer-attribute syncs, because you want the destination to end up with a record per row regardless of who got there first. - **Mirror** — make the destination match the model exactly, including removals. Rows present in the destination but absent from the current model result are removed: deleted, archived or dropped from the list, depending on what the destination supports. ## Why Mirror is the mode that bites Mirror is the only mode whose failure mode is destructive. "Absent from the model" is not the same as "deleted in the source system"; it only means the row stopped satisfying the model's query. A join that starts producing no matches because an upstream table failed to load, a filter on `is_active` after a column rename, a partially-loaded staging table — any of these shrink the result set, and Mirror faithfully removes the corresponding downstream records. Recovering means re-adding records that may have lost destination-side state that never lived in your warehouse: CRM activity history, message engagement, list membership dates. Defences are: validate model row counts before activating a Mirror sync, alert on unexpectedly large removal counts in a run, and prefer Upsert plus an explicit `is_active` or `status` column that the destination can filter on. Setting a status field is reversible; deleting a record often is not. ## Removal semantics differ by destination What "remove" means is a property of the destination API, not of Hightouch. In a list or audience destination, removal means dropping the member from the list, which is cheap and reversible. In a CRM object, it may mean deleting or archiving a record with related child data. In an ad platform's customer list, it means the user stops being targeted. Read what the destination does before choosing the mode. For the same reason, not every mode is offered for every destination. Some APIs expose only an upsert-shaped endpoint; some cannot delete at all; some support membership add and remove but not field updates. Hightouch surfaces the modes the destination can actually honour, so "which mode should I use?" is always partly answered by "which does this destination support?". ## How modes interact with the diff The diff and the mode are separate concerns. Every run classifies model rows as added, changed or removed relative to the previous run; the mode decides which of those classes turn into API calls. - Added rows → sent by Insert, Upsert and Mirror; ignored by Update (unless a record already exists, in which case Update writes to it). - Changed rows → sent by Update, Upsert and Mirror; ignored by Insert. - Removed rows → acted on **only** by Mirror; every other mode leaves the downstream record exactly as it was. That last line is the single most tested fact here. In Upsert, a customer who churns out of your "active" model simply stops receiving updates; their CRM record keeps whatever values the last run wrote. If you need downstream state to reflect the exit, you must either use Mirror or, more safely, keep the row in the model with a changed status column. ## Choosing a mode Ask three questions. Who owns record creation in the destination — if it is not you, choose Update. Does the destination need to forget records that leave the model — if yes, and the destination's removal is reversible, choose Mirror; if removal is destructive, choose Upsert plus a status flag. Is the object append-only — then Insert. When in doubt, start with Upsert, watch a few runs, and only add removal behaviour once you trust the model's row counts.
- A customer churns and drops out of the model, but the sync uses Upsert. What does the CRM record look like?Unchanged from the last run that included them. Upsert never acts on removals, so the record keeps its final synced values and will look permanently active. If downstream systems must see the exit, keep the row in the model with a status column set to churned, or move to Mirror only if the destination's removal is reversible.
- What safeguards would you put around a Mirror sync before enabling it?Assert the model's row count and non-null key coverage before the sync runs, alert on any run whose removal count exceeds a small threshold, and dry-run against a sandbox destination first. Prefer a reversible removal — dropping list membership — over destructive deletion of records that carry downstream state you cannot rebuild.
saying these in an interview costs you the question
- Thinks Upsert deletes rows that left the model
- Assumes every destination supports all four modes
- Chooses Mirror without knowing it removes downstream records
- Believes Update will create a record when none matches
- Treats a row leaving the model as a deleted source row