When three systems disagree, the winner should be a row in a table

A per-field survivorship rules table for golden records, with SQL that picks each winning value and records the source row it came from.

Sources Match and cluster Builder Rules table Golden record Publish back Steward queue CRM, billing, support per field, versioned with lineage columns CRM and warehouse exceptions
Sources are matched, the builder applies the rules table, and the golden record is published back or sent to a steward.
On this page
  1. Quiet failures in the merge script
  2. One rule per field, with a start date
  3. The query that picks a winner and remembers why
  4. Pendle Travel, three ways
  5. Where the record goes next
  6. Costs I’d accept

Matching a customer across systems is the step that gets the meetings. Picking which of their names, addresses and tax IDs survives gets a merge script, written once and almost never readable by anyone outside the data team.

Master data is back in CRM conversations this year. Salesforce agreed in May to buy Informatica, and identity resolution in Data 360 (Data Cloud until last month) already pairs match rules with reconciliation rules that pick a value per field by last updated, most frequent, or a source order. So there’s plenty of tooling talk. My claim is narrower, and it holds whether you buy a product or build this in the warehouse: survivorship rules are configuration, and they belong in a table that gets versioned and reviewed the way code does.

Quiet failures in the merge script

Match rules get argued about because their failures are loud. Merge two different companies and somebody notices within a week.

Survivorship fails quietly. An invoice goes to an office the customer left two years ago, because a rep edited the CRM account last month and the merge script says newest wins. Nobody chose that outcome. A line like coalesce(crm.address, billing.address) chose it, in a file that finance has never seen.

“Which system is the source of truth for customers” is the wrong grain for the question. Finance owns the legal name, since it’s what goes on the invoice. Sales owns the name people say on calls. Support knows which mailbox answers. The answer changes per field, so the rule has to be stored per field.

One rule per field, with a start date

Each row names a field, ranks the sources allowed to supply it, and says how to break a tie when one source holds several rows for the same customer. null_handling decides whether a blank from a higher-ranked source falls through to the next one (skip) or stands as the answer (keep). I’d keep a blank tax ID from billing over one borrowed from a support ticket.

max_age_months demotes old values without deleting them. A stale value from the top source loses to a fresh one from a lower source, and still wins if it’s all there is.

create table survivorship_rules (
  field            text   not null,
  source_priority  text[] not null,  -- best source first
  tie_break        text   not null
                   check (tie_break in ('most_recent', 'most_complete')),
  null_handling    text   not null
                   check (null_handling in ('skip', 'keep')),
  max_age_months   int,              -- older values are demoted, not dropped
  effective_from   date   not null,
  primary key (field, effective_from)
);

insert into survivorship_rules values
  ('legal_name',      '{billing,crm,support}', 'most_recent',   'skip', null, '2025-09-01'),
  ('trading_name',    '{crm,support,billing}', 'most_recent',   'skip', null, '2025-09-01'),
  ('billing_address', '{billing,crm}',         'most_recent',   'skip', 18,   '2025-09-01'),
  ('tax_id',          '{billing}',             'most_recent',   'keep', null, '2025-09-01'),
  ('payment_terms',   '{billing,crm}',         'most_recent',   'skip', null, '2025-09-01'),
  ('account_owner',   '{crm}',                 'most_recent',   'keep', null, '2025-09-01'),
  ('industry',        '{crm,billing}',         'most_complete', 'skip', null, '2025-09-01'),
  ('support_email',   '{support,crm}',         'most_recent',   'skip', 24,   '2025-09-01');

A rule change is a new row with a later effective_from, in a pull request the field’s owner approves. The old row stays, so you can rebuild last quarter’s golden record with last quarter’s rules.

The query that picks a winner and remembers why

The builder reads source values in long form, one row per cluster, field and source row. The cluster id comes from the matching step. This is Postgres; the same window function works in any warehouse, with its own array lookup.

create table source_values (
  cluster_id     text        not null,  -- from match and cluster
  field          text        not null,
  source_system  text        not null,
  source_row_id  text        not null,
  value          text,
  updated_at     timestamptz not null
);

with current_rules as (
  select *
  from (
    select r.*,
           row_number() over (
             partition by field order by effective_from desc
           ) as rn
    from survivorship_rules r
    where r.effective_from <= current_date
  ) versions
  where rn = 1
),
candidates as (
  select v.*,
         r.tie_break,
         r.effective_from as rule_version,
         array_position(r.source_priority, v.source_system) as source_rank,
         coalesce(
           v.updated_at < now() - make_interval(months => r.max_age_months),
           false
         ) as is_stale
  from source_values v
  join current_rules r on r.field = v.field
  where v.source_system = any (r.source_priority)
    and (r.null_handling = 'keep' or nullif(btrim(v.value), '') is not null)
),
ranked as (
  select c.*,
         row_number() over (
           partition by cluster_id, field
           order by is_stale,
                    source_rank,
                    case when tie_break = 'most_complete'
                         then length(value) end desc nulls last,
                    updated_at desc,
                    source_row_id
         ) as pick
  from candidates c
)
select cluster_id,
       field,
       value          as golden_value,
       source_system  as won_by,
       source_row_id,
       updated_at     as source_updated_at,
       is_stale,
       rule_version
from ranked
where pick = 1;

The columns after golden_value are the lineage. Every golden value can name the system, the row and the rule version that produced it. Pivot to one wide row per customer afterwards if you like, but keep this long table. It’s the one you’ll query when someone asks why.

Pendle Travel, three ways

Run it on an invented hotel group, Kittiwake Hotels, and one of its trade customers: a travel agency the CRM calls “Pendle Travel Ltd”, billing calls “PENDLE TRAVEL LIMITED” and the support desk calls “Pendle Travel (UK)”. Billing holds three account rows for it, opened over several years, each with a different address. Two were last touched before May 2024. One was updated this June.

Field Golden value Won by Source row
legal_name PENDLE TRAVEL LIMITED billing bil-2290
trading_name Pendle Travel Ltd crm acc-0417
billing_address 14 Weavers Row, Clitheroe billing bil-2290
tax_id GB000000000 billing bil-2290
payment_terms Net 30 billing bil-2290
account_owner UK North team crm acc-0417
industry Travel agencies crm acc-0417
support_email [email protected] support org-5521

Four fields from billing, three from the CRM, one from support. The address row is where the age limit earns its column. The two older billing addresses are past 18 months, so they sort behind everything fresh, and the June row wins on recency. If all three had been stale, a fresh address from the CRM would have won instead. Support’s version of the name loses twice, which is right: nobody there owns it.

Where the record goes next

The golden record gets published in two directions. The warehouse gets the long table with its lineage. The CRM gets the wide row through reverse ETL, as an upsert keyed on an external ID field that holds the cluster id (a PATCH to /sobjects/Account/Golden_Id__c/<cluster id> in Salesforce’s REST API). I’d only send fields where won_by isn’t the CRM. Writing the CRM’s own values back to it is how sync loops start.

Some rows shouldn’t publish without a person looking. I’d queue a field for a data steward when the winner is stale, when the top source has two fresh rows that disagree, or when a field marked keep came out blank. The steward fixes the source record or proposes a rule change. They never edit the golden record itself, because the next rebuild would undo it.

Costs I’d accept

Long form means every value is text, so dates and numbers need a cast on the way out. most_complete by string length is crude, and for addresses I’d count filled components instead. And a single changed row in the rules table can rewrite a field for every customer at once.

That last one is also why I want the table. Before merging a rule change, run the query with the proposed row and compare it to yesterday’s output: how many golden values move, in which field, toward which source. A merge script gives you no such number. With the table, the review has one, and when an invoice lands at the wrong office, the trail from that address back to bil-2290 is a single lookup.