Two Customer IDs, Both Correct
I used to approach a mismatched customer ID as a lookup problem. Find the missing key, repair the mapping, rerun the join. That works often enough to become instinct.
Then you run into a transaction that identifies one customer, while the customer master points to another. Both are active. Both have sensible names and valid external identifiers. Neither is obviously junk.
The join isn’t broken. The question is wrong.
Which customer do you mean?
Take a fictional distributor called Cedar Supply. Its sales feed says an order belongs to C-104, a regional reseller. The current customer register maps the external account reference on that order to C-220, the reseller’s successor after an account consolidation.
There are at least three reasonable questions:
- Who did the source identify when the transaction happened?
- Who owns that account relationship now?
- Which customer should receive credit in this particular report?
They aren’t interchangeable. A financial reconciliation may need the first. A current account-management dashboard may need the second. A commission or channel report may have an explicit rule for the third.
The lookup can resolve an identity. It cannot decide which business question the report is asking.
A technically successful join can make things worse
The tempting SQL is a fallback chain:
COALESCE(master.customer_key, source.customer_key, 0)It looks defensive. Prefer the curated master, use the source when the master is absent, and send the rest to unknown.
But it quietly encodes an authority rule: the master always wins when it has a match. That could rewrite old transactions under a new identity after an account merger. Reverse the order and you get the opposite policy. Neither COALESCE is neutral.
The same problem appears when an upstream source carries a reseller ID and a customer-reference lookup produces a different reseller ID. It’s tempting to treat the conflict as an error and force the values to agree. Yet that disagreement might represent a real transfer, a stale account mapping, an exception in the source, or a historical relation the current master no longer models.
I found it more useful to classify conflicts before fixing them:
| Condition | What it tells us |
|---|---|
| Source and reference agree | Straightforward resolution |
| Source exists, reference missing | Missing coverage, not automatically a bad source ID |
| Both exist and disagree | Competing authorities; investigate the policy |
| Both missing | Unresolved identity |
| One identity maps to several active records | Ambiguous reference data |
The table doesn’t choose the winner. It stops five different problems from being collapsed into an unknown_customer bucket.
Keep the evidence that lets you change your mind
My preference is to retain the incoming identifier, the matched reference identifier, and the resolved reporting identifier separately where the model permits it. The reporting key can remain simple for consumers. The path used to choose it should be explainable.
That doesn’t require dumping every lookup candidate into the fact table. A rule version, source identity, stable lookup evidence, and exception audit can be enough. The goal is to reconstruct why a row got a particular reporting identity without asking someone to remember a rule from six months ago.
There is a cost. Maintaining historical relationship tables and policy versions is real work. If the business only cares about current ownership, a current-state mapping may be entirely appropriate. But that should be a chosen reporting definition, not an accident of join order.
The lesson I kept was simple: an identity mismatch isn’t always evidence of dirty data. Sometimes it’s evidence that two systems describe different relationships. Before changing the SQL, ask which relationship the report is supposed to preserve.
That question also leads to a broader problem: reference data can change the meaning of history even when no transaction is edited.
Next in “Microsoft Fabric migration and data correctness” The ETL Passed. The Business Meaning Didn't →