Hospital Procurement: Vendor Master Duplicates and Key Design
A hospital procurement office keeps vendor records in a 15-year-old relational database. Vendor names are free text; 4,200 active vendor rows include 'Acme Supply', 'ACME SUPPLY CO.', and 'Acme Supply Co'. Purchase orders reference vendor_id, but payments are matched on tax ID. Last month 63 payments were held because the tax ID field was blank or malformed. A nightly ETL loads from three source systems and aborts when duplicate tax IDs appear. The CFO wants a 90-day cleanup and a rule preventing recurrence. The DBA proposes adding an auto-incrementing surrogate key and making it the primary key; the AP manager worries this will break downstream reports and wants vendor name to remain the key. No data steward has been assigned, and the three source systems use different vendor identifiers.
1. Evaluate the surrogate-key proposal against the AP manager's objection. What should be the primary key, and what must be true before changing it?
Read the worked answer
A surrogate key is stable and avoids cascading updates when a vendor name changes, but it does not solve duplicate entities - it would let 'Acme Supply' and 'ACME SUPPLY CO.' persist with different IDs. Vendor name is a poor natural key because it is unstable and already duplicated. Tax ID is a better business identifier but is blank or malformed in 63 payment cases and may not exist for all vendors.
Use the surrogate vendor_id as the primary key for referential integrity, and add a separate uniqueness rule on normalized tax ID where present, plus a match/merge process for duplicate entities. Keep a crosswalk table and build compatibility views so downstream reports retain stable identifiers. Before changing the key: profile and deduplicate the 4,200 rows, define survivorship rules (which name/address wins), map the three source identifiers, and freeze new vendor creation except through a controlled workflow. The key identifies a row; entity resolution is a separate business process.
2. Design a 90-day data-quality plan with measurable gates. Include how you would prevent recurrence rather than only cleaning.
Read the worked answer
Days 1–15 - profile and assign ownership: count duplicates, blank/malformed tax IDs, and source overlaps; name a data steward. Days 16–45 - standardize and resolve: normalize names and addresses, validate tax ID format, and match/merge using blocking on tax ID plus name similarity; preserve a crosswalk. Days 46–75 - migrate: implement the surrogate key, create report views, run parallel reconciliation, and change the ETL to quarantine duplicates instead of aborting. Days 76–90 - certify: gate on duplicate rate below 1%, tax ID completeness above 98%, payment holds below 5 per month, and ETL success at 100%.
Prevention: require a validated tax ID at vendor creation, run a duplicate check before insert, maintain a source-system identifier map, and review exceptions weekly. Do not change the primary key before entity resolution is stable; otherwise the new key simply hides duplicates. Publish the gates so the CFO sees progress rather than a single cleanup event.