Master Data Synchronization Across Three Systems
A company maintains customer master data in Salesforce (system of record for new customers), SAP (billing address and credit terms), and a legacy DB2 table (historical account numbers). Every night, a Boomi process pulls all three sources, maps them to a canonical Customer object, and upserts to a data warehouse. The process currently uses a full outer join in a Map shape with no survivorship rules; the last source processed wins. Data quality reports show 12% of records have conflicting addresses, and 4% have duplicate customer IDs because DB2 uses a different key format. The warehouse team needs a single golden record per customer with lineage. What data management patterns would you apply, and how would you handle the key mismatch?
1. Define survivorship and identity resolution rules for the golden record, specifying source precedence and conflict handling.
Read the worked answer
Establish explicit survivorship by attribute, not by source order. For legal name and new-customer creation, Salesforce is authoritative. For billing address and credit terms, SAP is authoritative. For historical account numbers, DB2 is authoritative but only as a cross-reference attribute, never as the primary key. For attributes with no clear owner (e.g., phone), use a most-recently-updated rule with a timestamp comparison, and if timestamps are within a tolerance, flag for manual review rather than silently picking one. Implement identity resolution in a dedicated step: normalize the DB2 key (e.g., strip leading zeros, apply a deterministic hash or a cross-reference table) and match to the canonical ID via a match key such as tax ID plus normalized name. Persist lineage by storing source system, source key, and last-updated timestamp for each attribute in the warehouse.
2. How would you implement the key mismatch and lineage in the Boomi process without breaking the nightly SLA?
Read the worked answer
Add a reference-data lookup step before the main join: load a DB2-to-canonical cross-reference table (or a cached lookup) into the process, then map DB2 records to canonical IDs. For unmatched DB2 keys, route to an exception queue rather than dropping or guessing. For lineage, extend the canonical schema with per-attribute metadata (source, source_key, updated_at) and populate it in the Map shape using a combination of source-specific branches and a merge that preserves metadata. To protect the SLA, process each source independently, cache the cross-reference table once per run, and use batch upserts to the warehouse. Measure the added lookup cost; if the cross-reference is large, use a database join in the source query instead of an in-memory lookup.