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.