SCD2 Orphans After Customer Merges and Late-Arriving Orders
An e-commerce company builds a customer dimension using SCD Type 2 from a CRM and an orders fact table from a transactional database. About 4.2 million order rows arrive daily. A referential-integrity check reports 0.6–0.9% of orders whose customer_id has no matching dimension row on the order date. The CRM team merges duplicate customer records weekly, and merged customer IDs are retired; orders placed before the merge keep the old ID. Some orders also arrive two days late due to a payment retry queue. Customer service reports that repeat-purchase rates for merged accounts look understated. The dimension currently has no row for retired IDs after the merge, and the merge mapping is stored in a CSV that is emailed weekly. The data team wants to close the gap before the quarterly board pack.
1. Explain the mechanisms that produce the 0.6–0.9% orphan rate and why a simple left join check understates the problem.
Read the worked answer
There are at least three mechanisms. First, weekly CRM merges retire duplicate customer IDs; orders written before the merge retain the retired ID, and the dimension only has the surviving ID. Second, orders arrive two days late through the payment retry queue, so an order can reference a customer version that was current when the order was placed but has since been superseded; a check against the current dimension row rather than the as-of order date will misclassify it. Third, the merge mapping lives in a weekly emailed CSV, so the pipeline may apply a merge before the mapping is loaded, creating a window of orphans. A simple left join against the dimension's current rows conflates the second mechanism with true orphans and misses the first if the retired ID was never loaded. The correct check is an as-of join: for each order, find the dimension version where order_date is between effective_start and effective_end, and separately check whether the customer_id exists in the merge bridge. That separates late-arriving-version issues from genuinely unmapped IDs.
2. Design the dimension and mapping changes to resolve orphans and preserve history.
Read the worked answer
Keep SCD2 rows for both the surviving and retired customer IDs, and add a merge bridge table with retired_id, surviving_id, merge_effective_date, and load_timestamp. When a merge is applied, do not delete the retired row; close its SCD2 version at the merge date and insert a new version under the surviving ID, then write the bridge entry. For order facts, resolve customer_id through the bridge as of the order date, not as of load date, and keep the original ID as an attribute for audit. For late-arriving orders, use the order date to select the correct SCD2 version; if the order predates the merge, it should map to the retired ID's version even though the current dimension no longer presents that ID as active. Load the merge file through a governed feed with a contract and a daily freshness check, not email. Add a referential-integrity check that counts orders with no as-of match and orders whose bridge entry is missing separately, and reconcile repeat-purchase rates before and after the change so the board pack uses a restated, explainable figure.
3. Which quality metrics would you report to show the gap is closing, and what reconciliation would you do before the board pack?
Read the worked answer
Report three metrics daily: orphan rate by mechanism (no as-of dimension match, missing bridge entry, late-arriving order), merge-mapping freshness (hours since the latest merge file was loaded versus the CRM merge timestamp), and repeat-purchase rate computed on the resolved customer key. The first two are leading indicators; the third is the business outcome. Before the board pack, reconcile the resolved repeat-purchase rate against a manual sample of 200 merged accounts, stratified by merge week, and against the CRM's own account-level report. Explain any residual difference rather than smoothing it. Restate prior quarters using the bridge so the trend is comparable, and disclose the restatement separately from the operational fix. Set thresholds: orphan rate below 0.1%, mapping freshness below 24 hours, and sample reconciliation within 1 percentage point. If a threshold is missed, report the known affected rows and the direction of bias rather than a single blended number.