Beyond DISTINCT: The Smart Deduplication Pattern Using Window Functions

Deduplicating data is one of the most common tasks in data engineering. Most of the time, slapping a DISTINCT or a GROUP BY onto your SQL query gets the job done.

But what happens when your duplicates aren’t exact duplicates?

Recently, we encountered a tricky “Double Identity” edge case in our Silver layer that completely broke our standard reconciliation checks. Here is how we abandoned DISTINCT and engineered a “Smart Deduplication” pattern using Window Functions.

The “Double Identity” Edge Case

We were processing a stream of physical mining locations (stockpiles and pits). To clean the data, our initial query looked something like this:

SELECT DISTINCT
    COALESCE(Alias, Name) AS LocationName,
    IS_ACTIVE
FROM raw_locations

This logic ran flawlessly for months. If a system sent the exact same location three times, the DISTINCT keyword correctly collapsed them into a single row.

Then, the data pipeline broke.

We investigated the raw Silver data and found the culprit: A single physical location (let’s call it PIT-WASTE) was acting as both an open Standalone Base Pit, and a closed Waste Override for a completely different pit.

The source system had generated two rows:

1. LocationName: PIT-WASTE, IS_ACTIVE: 1
2. LocationName: PIT-WASTE, IS_ACTIVE: 0

Because the IS_ACTIVE flags were different, the DISTINCT keyword treated them as two entirely separate entities! Both rows flowed downstream, completely bypassing the filter and causing primary key violations in the Gold layer.

1. The Raw Data (The Input)

LocationName	IS_ACTIVE
PIT-NORMAL	1
PIT-NORMAL	1
PIT-WASTE	1
PIT-WASTE	0

2. Standard DISTINCT (The Failure)

LocationName	IS_ACTIVE
PIT-WASTE	1
PIT-WASTE	0
PIT-NORMAL	1

Result: Both PIT-WASTE rows survive! Deduplication failed.

The Fix: Smart Distinct with ROW_NUMBER()

We needed a way to tell our database engine: “If you see multiple rows with the same LocationName, don’t just keep both. Evaluate them, prioritize the Active one, and throw the rest away.”

Standard aggregations (MIN, MAX, GROUP BY) are often too clumsy for this, as they can accidentally mix columns from different rows.

The most robust solution is using the ROW_NUMBER() Window Function. By partitioning the data by the unique identifier, we can enforce a strict sorting rule (a tie-breaker) to rank the duplicates.

Here is the refactored logic:

WITH RankedLocations AS (
    SELECT
        COALESCE(Alias, Name) AS LocationName,
        IS_ACTIVE,
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(Alias, Name)
            ORDER BY IS_ACTIVE DESC
        ) as rn
    FROM raw_locations
)
SELECT
    LocationName,
    IS_ACTIVE
FROM RankedLocations
WHERE rn = 1

How it works:

1. PARTITION BY: This groups the data by LocationName. Both PIT-WASTE records are thrown into the same bucket.
2. ORDER BY IS_ACTIVE DESC: This is the magic. Within the bucket, it sorts the rows so that IS_ACTIVE: 1 is placed at the top, and IS_ACTIVE: 0 is placed below it.
3. ROW_NUMBER(): It assigns an ascending integer (1, 2, 3…) to each row in the bucket based on the sort order.
4. ROW_NUMBER(): It assigns an ascending integer (1, 2, 3…) to each row in the bucket based on the sort order.
5. WHERE rn = 1: In the outer query, we simply filter for rn = 1. This guarantees we only ever output exactly one row per LocationName, and it will always be the active record if one exists.

3. Smart Distinct with ROW_NUMBER() (The Fix)

LocationName	IS_ACTIVE
PIT-NORMAL	1
PIT-WASTE	1

Result: Only the active PIT-WASTE record survives!

Conclusion

The DISTINCT keyword is a blunt instrument. It requires the entire row to be identical to collapse it.

When you start dealing with complex, real-world edge cases like slowly changing dimensions, competing timestamps, or conflicting status flags, you need a scalpel. The ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) pattern allows you to write intelligent, deterministic rules for exactly which version of a duplicate record survives.

Whenever you are building a robust deduplication pipeline, leave DISTINCT behind and embrace Window Functions.