Deduplicate by rank

You meet it when#

A source delivers the same entity more than once — a snapshot table, a change log, a replay after an incident — and you want the latest version of each.

The version that looks right#

select customer_id, max(updated_at) as updated_at
from customer_snapshots
group by customer_id;

This gets you the right timestamp and none of the other columns. The usual next step is joining back on (customer_id, updated_at), which reintroduces duplicates whenever two rows share a timestamp.

The pattern#

select * from (
    select *,
           row_number() over (
             partition by customer_id
             order by updated_at desc, _loaded_at desc
           ) as rn
    from customer_snapshots
) ranked
where rn = 1;

On Snowflake, BigQuery or DuckDB, QUALIFY removes the wrapper:

select *
from customer_snapshots
qualify row_number() over (partition by customer_id
                           order by updated_at desc, _loaded_at desc) = 1;

The detail everyone skips#

Two columns in the order by. With a single tiebreaker, rows that tie are ordered arbitrarily, so the query returns a different row on each run. Nobody notices until a reconciliation disagrees with itself and a day disappears into it.

Pick a second column that is genuinely unique — a load timestamp, a sequence number, or the primary key as a last resort. Determinism is worth more than elegance here.

Choosing the ranking function#

• row_number() — 1, 2, 3. No ties. This is what you want for deduplication.
• rank() — 1, 2, 2, 4. "Top 3" can return five rows.
• dense_rank() — 1, 2, 2, 3.

For dedupe it is always row_number(). The other two exist for leaderboards, where ties are meaningful.