One customer, five systems: entity resolution and survivorship for finance data

TL;DR

• CRM, contracts, ERP, billing and KYC each count customers at a different grain. Pick one by accident and revenue by customer and concentration risk quietly depend on it.
• Model the legal entity as dim_party, point every operational account at exactly one party with its match evidence, and keep group hierarchy in an effective-dated bridge.
• Match in passes: hard identifiers first, then the cross-references systems already store, then fuzzy scores that go to a review queue and never merge on their own.
• Survivorship is decided per attribute, with source precedence first and recency only as a tie-breaker, and the winning source stored next to every value.
• One test catches fan-out, dropped accounts and broken mappings at once: revenue by party must reconcile to revenue by billing account every period.

Ask five systems at a financial firm how many customers you have and you will get five answers, all of them defensible. The CRM counts relationships. The contract repository counts signatures. The ERP counts bill-to records. The billing platform counts whoever generated activity. KYC, the know-your-customer onboarding system, counts verified legal persons. Nobody is wrong. They are each answering a different question with the same word.

That is fine until someone asks for revenue by customer, net revenue retention, or top-ten client concentration. Those numbers quietly depend on which definition you picked, and most teams pick by accident: whichever table the first analyst joined to. This post is how I build a canonical customer instead, and how I keep it honest once it exists.

Five definitions of “customer”

System	What it calls a customer	Grain	What it is good at
CRM	An account the sales team manages	Relationship	Segment, owner, pipeline
Contract repository	A counterparty that signed something	Legal entity	Legal name, signed terms
ERP	A bill-to record	Billing account	Billing address, payment terms
Billing / usage	Whoever generated activity	Platform account	What was actually charged
KYC / onboarding	A verified legal person or entity	Legal entity	Identity, jurisdiction, ownership

Two of these are legal entities, one is a relationship, and two are operational accounts. The mistake is to pick one of them and declare it the customer. The relationship grain is too coarse for revenue recognition, and the account grain is too fine for concentration risk. You need both, connected.

The model

I separate the real-world counterparty from the accounts that point at it, and keep the hierarchy in its own effective-dated table.

dim_party                    one row per real-world legal entity (stable party_id)
   ▲
   │  many-to-one
dim_customer_account         one row per account in each source system
   │                         (source_system, source_account_id) → party_id
   │                         + match_method, match_confidence, matched_at
   │
bridge_party_relationship    parent/subsidiary, managed-by, fund-of
                             effective_from / effective_to

dim_party is the legal entity, because that is the grain that signs contracts, passes KYC and carries credit and concentration risk. Every operational account in every system lands in dim_customer_account with exactly one party_id, plus the evidence for why. Groups, fund families and “this is really the same relationship” live in bridge_party_relationship, effective-dated, because corporate structures change and you will be asked what the group looked like last year.

One rule I hold firmly: every account gets a party, even before it is resolved. An unmatched account becomes a provisional singleton party with match_status = 'provisional'. That keeps the “exactly one party” invariant true at all times, so downstream joins never fan out or drop rows while a reviewer is still thinking.

Matching: deterministic first, fuzzy last

Matching runs in passes, and each pass only touches what the previous pass left unresolved.

The first pass uses hard keys: KYC identifiers, legal entity identifiers, tax or registration numbers. If two records share one of those, they are the same party, full stop. The second pass uses cross-references the systems already store about each other: the ERP customer ID on the CRM account, the KYC ID on the billing account, the contract ID on the ERP record. These are human-entered and occasionally wrong, but they were entered by someone who knew the client.

Only then does fuzzy matching run: normalized names, addresses, domains, with a confidence score. In finance, fuzzy output goes to a review queue. It never merges on its own. A false merge combines two clients’ revenue, two clients’ exposure, and occasionally two clients’ statements. A false split just leaves a duplicate for a reviewer to catch. The costs are not symmetric, so the thresholds should not be either.

A worked example: Harbor Lane

Here is a toy client at a fictional custodian. Seven records across five systems:

Source	ID	Name as recorded	Evidence
KYC	kyc_7731	Harbor Lane Capital Management LP	Registration no. on file
KYC	kyc_9014	Harbor Lane Fund II LP	Parent = kyc_7731
Contracts	ctr_0192	Harbor Lane Capital Management, L.P.	kyc_id = kyc_7731
Billing	plat_201	harborlane-ops	kyc_id = kyc_7731, erp_id = cust_4410
Billing	plat_202	HL Fund II	kyc_id = kyc_9014
ERP	cust_4410	Harbor Lane Cap Mgmt	none
CRM	acc_88A	Harbor Lane Capital	erp_id = cust_4410

Pass one creates two parties from the KYC records: P1 for the management company and P2 for the fund. The contract and plat_201 attach to P1 on kyc_7731; plat_202 attaches to P2 on kyc_9014. Pass two follows cross-references: plat_201 carries cust_4410, so the ERP record joins P1, and the CRM account carries the same ERP ID, so it joins P1 too. The KYC parent pointer becomes a row in bridge_party_relationship: P2 is a fund of P1.

Now say the CRM also has a stray account, acc_93C, named “Harbor Lane Fund 2”, with no IDs at all. Fuzzy matching scores it 0.86 against P2. It goes to the review queue, not into P2.

Here is why this matters. The fictional custodian billed plat_201 $42,000 and plat_202 $18,000 last quarter, out of $1.2M total revenue. Reported by CRM account, the old way, Harbor Lane shows $42,000, and the fund’s $18,000 lands in an “unmapped” bucket that nobody looks at. Reported by party and rolled up through the bridge, the Harbor Lane group is $60,000: 5.0% of revenue instead of 3.5%. Same data, a different answer to “who are our largest clients”, and the concentration report is the one the board reads.

Survivorship, per attribute

Once records are matched, each party still has several candidate values for every attribute. Survivorship is the rule for which value wins. Picking one source to win everything is the usual shortcut, and it is wrong: KYC is the authority on legal name and jurisdiction, but it knows nothing about sales segment. So I declare precedence per attribute:

# Which source wins, attribute by attribute. First listed = most trusted.
legal_name:      [kyc, contracts, erp, crm]
billing_address: [erp, contracts, crm]
segment:         [crm]
industry:        [crm, kyc]
country_of_risk: [kyc]

The tie-breakers are precedence, then recency, then completeness. Sources not listed for an attribute are not allowed to contribute at all, which is deliberate: I do not want a CRM free-text field deciding a client’s country of risk.

For Harbor Lane, legal_name comes from KYC (“Harbor Lane Capital Management LP”), not the CRM’s friendlier “Harbor Lane Capital”. billing_address comes from the ERP, updated in February, even though the contract still has the address from signing. segment comes from the CRM because nothing else has an opinion.

In dbt, the rules become a seed with one row per attribute and source, and the golden record (the single winning version of each party) is a ranked pick:

-- One winning value per party × attribute, with the evidence kept.
with candidates as (
  select
    c.party_id,
    c.attribute,
    c.value,
    c.source_system,
    c.source_updated_at,
    r.precedence                      -- 1 = most trusted
  from {{ ref('int_party_attribute_candidates') }} c
  join {{ ref('survivorship_rules') }} r
    on r.attribute = c.attribute
   and r.source_system = c.source_system
  where c.value is not null
    and trim(c.value) != ''
)
select
  party_id,
  attribute,
  value,
  source_system      as won_from,
  source_updated_at  as won_at
from candidates
qualify row_number() over (
  partition by party_id, attribute
  order by precedence, source_updated_at desc, length(value) desc
) = 1

The won_from and won_at columns are the point. When someone asks why a client’s name looks odd on a report, the answer is a lookup, not an investigation. The empty-string filter matters too: a blank field in the most trusted source should not beat a real value from the next one.

What goes wrong in practice

Transitive blobs. If A matches B on address and B matches C on phone number, clustering puts A and C together. In finance, many unrelated entities share a registered agent’s address or a fund administrator’s phone line. One shared address can collapse dozens of funds into one party. Hard keys can be transitive; fuzzy evidence should never be.

Party ID churn. If you re-cluster from scratch every night, IDs shift, and every downstream snapshot, saved report and CRM sync breaks. Party IDs must be stable. When two parties merge, keep the survivor and record the retired ID in a merge map so old references still resolve.

Fan-out in revenue. If an account ever maps to two parties, every join through it double-counts. This is the most common way a well-intentioned customer model makes revenue go up.

Flapping attributes. Recency as a primary rule lets a noisy, low-trust source overwrite a good value every time it syncs. Recency is a tie-breaker inside a precedence level, not a replacement for it.

Hierarchy without dates. When a client acquires another, last year’s concentration report should not silently change. Effective-dating the bridge lets you answer both “what was the group then” and “what is it now”, which is the same as-of versus as-was distinction I wrote about in bitemporal modeling.

Tests that keep it honest

The model is only as good as the invariants you enforce on it every run. Every operational account maps to exactly one party. No party is orphaned without at least one account. No party exists without a legal name from an allowed source. And the one I care about most: revenue by party reconciles to revenue by billing account, in total, for every period.

-- Fails (returns rows) if any period's revenue changes when re-keyed by party.
with by_account as (
  select period_month, sum(revenue_usd) as usd
  from {{ ref('fct_revenue') }}
  group by 1
),
by_party as (
  select r.period_month, sum(r.revenue_usd) as usd
  from {{ ref('fct_revenue') }} r
  join {{ ref('dim_customer_account') }} a
    on a.source_system = 'billing'
   and a.source_account_id = r.billing_account_id
  group by 1
)
select a.period_month, a.usd as account_usd, p.usd as party_usd
from by_account a
left join by_party p using (period_month)
where p.usd is null
   or abs(a.usd - p.usd) > 0.01

This one test catches fan-out, dropped accounts and broken mappings at once. It is the same idea as in a ledger-first architecture: reconciliation is a test, not a quarterly project.

Beyond pass/fail tests, I monitor match rate by source and the age of the review backlog as metrics with owners. A backlog that grows quietly is how a clean model becomes a stale one.

Where AI helps, and where it must not

Language models are good at the tedious middle of this work. Given two candidate records, a model can propose a match and explain its reasoning in a sentence: “same registration number format, fund name matches the parent’s naming pattern, different address because one is the administrator.” A reviewer can then accept or reject it in seconds. That turns a review queue from a chore into a skim.

What it must not do is merge parties on its own. A merge changes revenue attribution, exposure and client statements. Those decisions need a named human and an audit trail, and the model’s suggestion should be stored as evidence alongside the decision, not as the decision.

The customer is a decision

There is no customer table waiting to be discovered in your source systems. There are five partial views and a set of choices about how to combine them: the grain, the matching evidence you trust, which source wins each attribute, and how history is kept. Make those choices explicit, store the evidence next to every value, and test the reconciliation every run. Then “revenue by customer” becomes a number you can defend line by line, and the next time two reports disagree, the argument is about a rule rather than a guess.