skip to content

A Census sync keeps overwriting edits sales reps make in Salesforce. How do you fix it?

level: seniorimportance: should knowfreq 52%

answer

  1. two writers, one field
  2. nothing merges, something just wins
  3. the field you never map is the field that survives
  4. decide ownership before touching config
  5. ingest the edit back if it is authoritative

basics

~20 s

Decide ownership field by field. A reverse-ETL sync is a blind last-writer-wins job with no merge, so the fix is to unmap fields humans own, keep warehouse-owned fields separate from human-editable ones, and ingest CRM edits back into the warehouse if the model must respect them.

solid answer

~50 s

There is no conflict detection to turn on — the sync writes the warehouse value on every run where the model differs from what it last sent, and the human edit loses. The real fix is governance, expressed in configuration. **Unmap** every field a human is supposed to own: a field that is not in the mapping is never written. Where both sides have a claim, split them — the warehouse writes `mrr_from_billing` and the rep owns `negotiated_rate`, rather than both fighting over one column. If the model genuinely should respect manual input, close the loop: pull the CRM field back into the warehouse with an inbound connector, incorporate it in the model's logic, and let the model emit the merged answer. Then make the arrangement visible: mark warehouse-owned fields read-only in the destination's own permissions so reps see they cannot edit them, instead of discovering it when their work disappears overnight.

code

sql · 11 lines
sql
-- close the loop: manual CRM input wins, warehouse fills the rest
select
  c.salesforce_id,
  c.email,
  coalesce(c.industry_manual, m.industry_predicted) as industry,
  m.churn_score,                       -- warehouse owns, always written
  m.product_usage_tier                 -- warehouse owns, always written
  -- account_owner_notes deliberately NOT selected: reps own it,
  -- so it is never mapped and never overwritten
from raw_salesforce.contact c          -- ingested back from the CRM
left join analytics.dim_contact_scores m on m.email = c.email

go deeper

for a junior

Know that a field the sync does not map is a field it never writes, and that a scheduled push has no awareness of manual edits made between runs.

for a middle

Explain the mechanics of last-writer-wins in a diff-based sync, including why an edit can appear to survive until the model's own value changes and reasserts itself.

for a senior

Show the full remediation ladder and the loop design: ownership map, unmapped human fields, split contested fields, read-only field security downstream, and inbound ingestion with an explicit precedence rule in SQL.

for a principal

Own this as data governance — negotiate field-level ownership with the CRM owner, write it down, and make the sync configuration a consequence of that agreement rather than a series of ad-hoc unmappings after each complaint.

## Why this happens at all Reverse ETL makes the warehouse a writer into a system that already had writers. The sync compares the current model against the state it recorded last run and pushes the rows whose mapped values changed. It has no idea a human touched the record in between, and no merge strategy — the value in the model is the value that lands. The moment two parties can write the same field, someone loses, and on a schedule the schedule wins. Note the subtlety in diff-based syncing: if the *model's* value has not changed, the sync may have nothing to send, so a rep's edit can survive for a while. That is worse than losing immediately, because it teaches everyone that manual edits stick — right up until the model value changes and silently reasserts itself. ## Step one: an ownership map, not a setting The durable fix is a written, field-level ownership decision for the destination object: for each field, is the system of record the warehouse, the destination, or neither? This is an organizational artifact agreed with whoever runs the CRM, not something you infer alone. Once it exists, the configuration is mechanical. **Warehouse-owned fields** go in the sync's mapping. Behavioral scores, product-usage aggregates, billing-derived values, segment membership, anything computed. **Human-owned fields** are simply left out of the mapping — an unmapped field is never touched, which is the strongest and simplest guarantee available. **Contested fields** should be split into two fields rather than arbitrated. If the warehouse can compute contract value from billing and the rep knows about a side agreement, create `mrr_from_billing` (synced) and `negotiated_mrr` (manual), and let reporting decide precedence explicitly. ## Step two: make it visible in the destination A field the warehouse owns should be read-only in the destination's field-level security so the edit box is greyed out. This converts a silent data loss into an obvious rule. It also stops the support ticket that starts "the CRM is deleting my work" — reps are not wrong to file it; they were given a writable box whose contents were being replaced by a robot. ## Step three: close the loop when human input is real input Sometimes the manual edit is genuinely authoritative — a rep marks an account as a strategic logo, or corrects an industry classification the warehouse guessed. Then the answer is not to stop syncing but to bring that field back. An inbound connector loads the CRM object into the warehouse; the model reads it and applies a precedence rule in SQL (`coalesce(manual_override, computed_value)`); the sync pushes the merged result. The warehouse stays the single place where the precedence rule is written down and testable. Watch the loop's timing. Inbound load, model rebuild and outbound sync form a cycle, and if the outbound sync can fire before the inbound load has picked up a fresh edit, you will overwrite it and then read your own write back. Order the jobs — load, then transform, then sync — and trigger the sync on the transformation's completion rather than on an independent clock. ## Step four: choose a restrained sync behavior If the destination's records are created and owned by the sales team, an update-only behavior stops the warehouse creating records at all, shrinking the surface where conflicts can arise. Reserve upsert for populations the warehouse legitimately defines, and reserve mirror-style removal for membership lists that have no human authorship. ## What not to say Do not propose "detect conflicts and merge" as though a toggle exists; batch reverse ETL has no view of destination edit history. Do not propose slowing the schedule — that reduces the frequency of the overwrite, not its inevitability. Do not propose comparing timestamps in the model unless you have actually ingested the destination's modified timestamp, which is the loop described above by another name. ## How to answer Diagnose it as two writers on one field with last-writer-wins and no merge, and say that plainly. Then give the ladder: agree field-level ownership, unmap human fields, split contested ones, mark synced fields read-only downstream, and only where manual input is authoritative, ingest it back and merge in the model with an explicit precedence rule. Close by noting that this is a governance conversation with the CRM owner that happens to have a configuration expression — which is exactly the judgment the question is testing.

  • Why is lowering the sync frequency not a real fix for this problem?
    It changes how often the overwrite happens, not whether it happens. A daily sync still replaces the rep's edit the next morning, and the longer gap makes it worse: the edit appears to stick, people build trust in it, and the loss arrives later and more confusingly. Frequency is a cost and freshness lever, not a conflict-resolution mechanism.
  • How do you keep an inbound-then-outbound loop from overwriting an edit it has not yet read?
    Order and trigger the jobs explicitly: load the destination object into the warehouse, rebuild the model, then fire the sync on the transformation's success rather than on an independent schedule. If the outbound sync can run before the inbound load has captured a fresh edit, it will push the stale merged value and then read its own write back on the next cycle.
  • What is the advantage of splitting a contested field into two fields instead of picking a winner?
    Both facts survive and the precedence rule becomes explicit and reportable. The warehouse writes the computed value, the human writes theirs, and whoever consumes the record decides which to trust — in a query you can test, not in a race between a rep and a scheduler. It also removes the incentive to fight over a single box.

saying these in an interview costs you the question

  • Claiming the sync detects conflicts and merges automatically
  • Suggesting a slower schedule as the fix
  • Letting the warehouse write every field on the object by default
  • Ignoring the CRM owner and treating it as a purely technical setting
  • Comparing timestamps without ever ingesting the destination's modified time

context

resolving links…