# Matching Customer Records Across Source Systems in Analytics Models
Most companies hold customer data in more than one place. A billing tool, a product database, a support desk and a marketing platform can each keep their own customer table with their own ID. When someone asks how many customers the company has, those tables rarely agree. Deciding which records belong to the same customer is a modeling job, and on many data teams it lands with the analytics engineer.
Contents
# Start with the question people are asking
Before writing any SQL, find out what the matched customer will be used for. Finance may count paying accounts. Product may count people who log in. Support may care about the contact who opened a ticket. Each of these can need a different entity, and forcing them into one table without agreeing on the definition only moves the disagreement into a dashboard.
Write down the grain you are building. One row per person, one row per company account, or one row per billing entity are all reasonable choices. They just are not the same choice.
# Pick match keys you can defend
Some systems share a stable key, such as an account ID passed from the product into billing. That is the easiest case, and it should be used first. When no shared key exists, teams often fall back to a normalized email address or a company domain. Normalize before matching, so case and stray spaces do not split one customer into two.
Be careful with weak keys. Shared inboxes, personal email domains and reused test accounts can join records that should stay apart. It is better to leave a record unmatched than to merge two real customers by mistake.
# Keep a mapping table instead of rewriting sources
A common pattern is a mapping model with one row per source record, its source system, its source ID and the customer key it was assigned. The customer dimension is then built from that mapping. Source tables stay untouched, and anyone can trace a merged customer back to the records behind it.
This also makes corrections simple. When someone finds a bad merge, the fix is a change to the mapping rules, and the change can go through code review like any other model.
# Test the result
Add tests that each source record maps to at most one customer key, and that the customer key is unique in the final dimension. Track how many records are left unmatched from each source over time. A sudden jump usually means an upstream change, such as a new signup flow that stopped passing the account ID.
# Document the rules
The Analytics Engineer Jobs guide to the analytics engineer role describes the work as turning warehouse data into trusted, tested, documented datasets and shared metrics. It lists clarifying definitions with stakeholders and recording model grain, assumptions and ownership as part of a typical day. Customer matching is a clear example of why that matters, since every downstream count depends on rules that someone has to be able to read.
The role guide on Analytics Engineer Jobs describes the work of turning warehouse data into tested, documented datasets.
If you want to see how employers describe this kind of work, the open analytics engineer roles on Analytics Engineer Jobs link to each employer’s own posting, so you can read the requirements in full before you apply.