data // SQLPRODUCTION

ROW_NUMBER() with Business Priority

Choose a surviving customer row by source trust first, recency second.

sqlrow_numbercasegolden record

Trust & provenancePRODUCTION

Evidence tierPRODUCTIONOfficial sources0Other sources0Last verified2026-10-05

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.

EvidencePRODUCTION

Difficultyadvanced

Verified2026-10-05

Use when

One source is more authoritative than another.

A Golden Record needs explicit precedence rules.

SELECT
    CustomerIdentity,
    EmailAddress,
    SourceSystem,
    ModifiedDate
FROM (
    SELECT
        s.*,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerIdentity
            ORDER BY
                CASE SourceSystem
                    WHEN 'CRM' THEN 1
                    WHEN 'ECOMMERCE' THEN 2
                    WHEN 'LEGACY' THEN 3
                    ELSE 9
                END,
                ModifiedDate DESC,
                RecordID DESC
        ) AS rn
    FROM [Customer_Staging] s
) ranked
WHERE rn = 1

Why it works

CASE converts business precedence into an explicit sort order inside the window function.

Operational notes

Source priority should be documented as a business rule, not hidden inside an unexplained query.

Gotchas

Consent precedence deserves its own explicit rule; do not blindly treat it as just another attribute.

Deterministic Deduplication — Latest Record

Keep the most recent row for each logical identity using ROW_NUMBER().

→

ROW_NUMBER() with a Deterministic Tie-Breaker

Make deduplication deterministic when the main timestamp can tie.

→

ROW_NUMBER() with Business Priority

Keep following the context.