Destination Match Key

A reverse-ETL sync writes modelled rows into an operational tool, and almost always as an upsert: find the destination record matching this row and update it, otherwise create it. The field used to find the record is the entire correctness surface of the sync, and it is usually chosen in the first hour of setup from whatever both systems happen to hold - an email address, a company domain, a phone number.

Those fields are maintained by humans and they change. When one does, the sync looks for the new value, finds nothing, and does the only other thing an upsert can do: it inserts. The old record survives, holding the last values it was given, still inside every segment and campaign filter built on them.

Nothing errors. The run reports rows written, and an insert and an update are both rows written. The symptom arrives weeks later as two records for one customer with conflicting numbers, and the duplicate arrival rate is simply the churn rate of the field: a 3% monthly change rate across 180000 accounts produces roughly 5400 orphans a month, each able to go stale again.

Why it matters

This is the most common way a reverse-ETL integration damages an operational system, and it damages it in the place that is hardest to repair. Deduplicating a CRM is manual, lossy and political, because each duplicate has activity history attached and somebody has to decide which survives.

It also matters because the warehouse is now an upstream dependency of a system with its own ideas about identity. A match key is an interface, and it has to be as stable as a primary key because that is exactly what it is being used as - the same requirement as an idempotency key on a payment API, failing the same way when the key is derived from mutable content.

Implementation patterns

• Generate the key in the warehouse - a stable identifier for the entity, independent of any attribute - and write it once into a dedicated external-id field on the destination record. Match on that field from then on.
• Keep the mapping in the warehouse: warehouse key to destination record id, written back from the sync's response. It makes every run idempotent and lets an engineer answer "which record is this row" without searching the destination.
• Alert on the insert-to-update ratio per run. In a steady state it is near zero; anything above a few percent of the synced population means the key is breaking. This is the single most valuable metric a sync emits.
• Reconcile counts between the warehouse entity count and the destination record count on a schedule, because the divergence is otherwise invisible.
• Make the sync update-only where the destination has no external-id field. A sync that cannot insert cannot create duplicates; route unmatched rows to a human queue with the warehouse key attached.
• Bound the blast radius: a cap on rows created or cleared per run, so a key change affecting everything fails loudly at 500 records instead of succeeding at 500000.

Industry example

The pattern is visible in the design of the destinations themselves. CRMs and marketing tools ship a dedicated external-identifier field precisely so an external system can own identity, and their upsert endpoints accept it as the lookup field. The field exists because the failure it prevents is universal, and the teams hitting it in production are the ones that used a human-facing field instead because it was already populated on both sides.

The adjacent failure is the same key problem in reverse: a full-sync mode treating absence from the source query as an instruction to clear the field, so a model returning fewer rows empties data across a live operational system. Both come from trusting a shape of the source nobody verified.

Failure scenarios

• Duplicate records at the churn rate of the match field, with campaigns firing against the stale copy.
• Two systems disagreeing about the same customer, where support sees one record and the dashboard counts both.
• A key change applied in bulk - a domain migration, a rebrand - orphaning thousands of records in one run.
• Dedup plus a uniqueness constraint as the "fix", which clears the backlog, leaves the mechanism intact and rejects legitimate shared mailboxes at the worst moment.
• A mapping held only in the sync tool's own state, lost on reconfiguration, so the next run re-inserts everything it can no longer match.

Trade-offs

Gained by a warehouse-owned immutable key: idempotent runs · duplicates structurally impossible · a mapping that makes investigation and merges mechanical · the ability to change any business attribute without consequence.

Paid: a first-sync bootstrap to populate the external id on existing records, which is the one-off migration nobody enjoys · a custom field to create and protect in the destination · a mapping table to maintain · and a dependency on the destination's API returning record ids, which some do only in a batch result file.

When not to use it

If the destination genuinely has no custom field and no merge API - some small tools and some regional systems - then there is no immutable key to use, and the answer is to constrain the sync rather than to pretend the key is stable: update-only, a human queue for unmatched rows, and a cap on changes per run.

Do not build the mapping machinery for a one-way push of non-identifying data either. A sync that writes a metric onto records it never creates - no inserts, no identity decisions - does not need an external id, and adding one is ceremony. The pattern is required the moment the sync can create a record.

Interview question

Q: A nightly sync pushes a health score into the CRM matching on email. Over six weeks the CRM has grown by 34000 accounts and sales is seeing conflicting scores on the same customer. Tell me the mechanism, the metric that would have caught it in week one, and how you would clean up without the problem returning.

What a strong answer covers: an upsert on a mutable attribute inserts when the attribute changes, so duplicates arrive at the field's churn rate; insert-to-update ratio per run as the missing signal, because success counts cannot distinguish the two; the fix as a warehouse-generated immutable key written into an external-id field with the mapping held in the warehouse; cleanup as a mapping-driven merge keeping the record with activity history; and why deduplication plus an email uniqueness rule is not a fix.

Quick check

Quiz: Why does a reverse-ETL sync keyed on email create duplicates with no error? - When the email changes the upsert finds no match and inserts, leaving the old record stale but live; the rate is the field's churn rate and the signal is the insert-to-update ratio.

Flashcard: What property must a reverse-ETL match key have and where should it come from? - Immutable and warehouse-owned - a generated identifier written once into the destination's external-id field, with the key-to-record-id mapping kept in the warehouse so every run is idempotent.