Multi-source conformance¶

Combining records of the same kind of thing from several sources into one consistent view: mapping their fields and codes, matching records that describe the same real-world entity, and deciding which values win.

Why it matters¶

Customers, products and suppliers usually exist in more than one system: two shops after an acquisition, a CRM and a billing system, regional systems for the same company. Each has its own IDs, codes and quality. Without conformance, the same person is counted twice, the same product has two names, and reports from different sources cannot be combined.

How it works¶

There are four steps, usually in the silver layer.

Mapping brings each source to one shared structure. Field names are aligned, types made consistent, and codes translated to one list: one source says "DE", another "DEU", a third "Germany"; all become the ISO code DE. Mapping tables for codes are data, kept in version control or a managed table, with an owner.

Matching decides which records describe the same entity. Deterministic matching compares a reliable identifier: the same tax number, or the same email after normalising case and whitespace. Probabilistic or fuzzy matching scores similarity across several fields, such as name, date of birth and address, and accepts pairs above a threshold. Deterministic rules are explainable and should go first; fuzzy matching catches more but makes mistakes in both directions, merging different people or missing the same person.

Identity resolution gives each matched group one ID in the platform. A cross-reference table records which source IDs belong to which platform ID, so any source record can be traced and any match can be undone.

Survivorship decides, for each attribute, which source's value the combined record shows. Rules are set per attribute: the most recently updated value, the value from the most trusted system, or the most complete one. The result is often called the golden record. The golden records are the basis of a conformed dimension, as described in Dimensional modelling: one dim_customer that every fact can share.

flowchart LR
  A[(Shop customers:<br/>c-88, ana@mail.com)] --> MAP[Map fields<br/>and codes]
  B[(Marketplace customers:<br/>m-5012, ANA@mail.com)] --> MAP
  MAP --> MATCH[Match:<br/>normalised email]
  MATCH --> XREF[(customer_xref:<br/>cust-1 = c-88, m-5012)]
  XREF --> SURV[Survivorship<br/>per attribute]
  SURV --> G[(dim_customer:<br/>one row per cust-1)]

Matches change. A customer updates an email, a new source arrives, a wrong merge is reported. The cross-reference table should keep its history, as in type 2, so that past reports can be explained and a split can be applied without losing what was known before.

Master data management (MDM) is the discipline, and often the product category, that does this as an operational service: it maintains golden records and sends them back to source systems. A data platform can conform data for analysis without a full MDM system, but if the business needs one customer ID in operational systems too, conformance belongs in MDM and the platform consumes its result.

Conformance is also an ownership question. Someone must decide which system is trusted for which attribute, and that decision is a business rule, not a technical default.

Key terms¶

• Mapping: aligning fields, types and codes of several sources to one structure.
• Deterministic matching: matching on exact, normalised identifiers.
• Probabilistic matching: matching on scored similarity across several fields.
• Identity resolution: assigning one platform ID to all records of one entity.
• Cross-reference table: the record of which source IDs belong to which platform ID.
• Survivorship: per-attribute rules for which source value wins.
• Golden record: the combined record after survivorship.

Example¶

The shop acquires a marketplace. Both have customers, and some people use both.

-- match on normalised email, deterministic
SELECT s.customer_id AS shop_id, m.user_id AS marketplace_id
FROM shop.clean_shop_customers AS s
JOIN shop.clean_marketplace_users AS m
  ON lower(trim(s.email)) = lower(trim(m.email));

Survivorship rules for dim_customer:

Ana is c-88 in the shop and m-5012 on the marketplace. customer_xref links both to cust-1, and dim_customer holds one row for her. clean_customers stays the shop's own table, keyed by c-88. Facts keep the source customer ID and reach cust-1 through customer_xref, so her orders from both now count for one customer.

Pitfalls¶

• Fuzzy matching first. Start with deterministic rules; add similarity scoring only where it is needed and reviewed.
• No cross-reference history. A wrong merge cannot be undone, and old reports cannot be explained.
• Survivorship by accident. Without explicit rules, the last source loaded wins.
• Consent and privacy merged loosely. The most restrictive choice must survive, not the most recent.
• Many-to-one matches. A shared email links several people; flag groups with more than one record per source for review.
• Conforming in every report. Do it once, in the clean layer, and reuse the result.

Learn more¶

• DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, 2nd edition (Technics Publications, 2017), chapter 10, reference and master data.
• Kimball Group, dimensional modeling techniques (conformed dimensions): https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/

Related¶

Dimensional modelling · Data products and data mesh · PII and data classification · Data quality dimensions