Deduplication by Business Priority
When “latest record wins” is not enough.
Evidence tier describes my hands-on relationship with the content. Official sources describe platform contracts/documentation. Production observations should not automatically be treated as official limits.
A Golden Record rule often needs explicit source trust, status priority or permission precedence before recency. Put those rules in the window ORDER BY so the surviving row reflects the business contract.
Encode priority explicitly with CASE.
Keep priority rules readable enough to audit.
Use recency after trust/permission rules when appropriate.
Document why each source outranks another.
Priority-first ranking
This pattern prefers an authoritative source before comparing timestamps. It is useful when a late-arriving low-trust row must not override a trusted master record.
SELECT
CustomerIdentity,
EmailAddress,
SourceSystem,
ModifiedDate,
RecordID
FROM (
SELECT
s.CustomerIdentity,
s.EmailAddress,
s.SourceSystem,
s.ModifiedDate,
s.RecordID,
ROW_NUMBER() OVER (
PARTITION BY s.CustomerIdentity
ORDER BY
CASE s.SourceSystem
WHEN 'CRM' THEN 1
WHEN 'ECOMMERCE' THEN 2
WHEN 'LEGACY' THEN 3
ELSE 9
END,
s.ModifiedDate DESC,
s.RecordID DESC
) AS rn
FROM [Customer_Staging] s
) ranked
WHERE rn = 1Permissions are not just another attribute
Do not let a generic “newest row wins” rule accidentally overwrite a stricter consent state. Permission and compliance logic deserves its own explicit precedence model.
Identity resolution and consent resolution are related problems, but they are not the same problem.