Deduplicating with ROW_NUMBER and QUALIFY

Description

Lesson 5 diagnosed the problem with complete evidence: raw_orders_batch has 43 rows for 40 unique combinations of order_id+product_id, with three resent orders, each with two copies identical in value. This lesson solves it: ROW_NUMBER(), a window function that numbers rows within each group, combined with QUALIFY, DuckDB's clause for filtering on a window function's result without needing a subquery. The result — verified, not assumed — is that the batch has exactly forty rows again, with the correct revenue.

Connection to the module. This lesson resolves the second of this module's two checklist rows — "explicit deduplication of repeated rows" — the first was the point-in-time join from lessons 2 through 4. The two techniques don't depend on each other — you can deduplicate without ever having joined against a historized dimension — but they coexist in the same module because both solve the same kind of question: "which row, exactly, should represent this event?", when there's more than one candidate.

An analogy: keep the latest update, not all of them

Think of a shared document several people edit at once, and every time someone saves, the system creates a new timestamped version — without deleting the earlier ones. If someone asks you for "the document," no one expects you to hand over the fifteen versions saved that day: they expect the most recent one, the one that reflects the final state after all the edits. The earlier versions aren't "wrong" — each one was, at the time, the correct version — but only one of them is the one that matters for answering "what does the document look like now?"

ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) does exactly that with Kiosko's resent orders: it groups (PARTITION BY) every copy of the same order, numbers them from most recent to oldest (ORDER BY ingested_at DESC, so the most recent gets number 1), and QUALIFY ... = 1 keeps only that one — the final version, discarding the intermediate copies without losing any information the final version doesn't already have.

Worked example: ROW_NUMBER, seen row by row, before filtering

Rebuild raw_orders_batch exactly as in lesson 5 — the same script, raw_orders_batch.py. Before filtering anything, look at what ROW_NUMBER() computes for the three resent keys, without QUALIFY yet, to understand exactly what it numbers:

# dedup_qualify.py
# (raw_orders_batch already built as in lesson 5)

print("=== ROW_NUMBER() over the 3 resent keys, WITHOUT filtering yet ===")
print(con.sql("""
    SELECT order_id, product_id, ingested_at,
           ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) AS rn
    FROM raw_orders_batch
    WHERE order_id IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    ORDER BY order_id, rn
"""))

=== ROW_NUMBER() over the 3 resent keys, WITHOUT filtering yet ===
┌──────────┬────────────┬─────────────────────┬───────┐
│ order_id │ product_id │     ingested_at     │  rn   │
│ varchar  │  varchar   │      timestamp      │ int64 │
├──────────┼────────────┼─────────────────────┼───────┤
│ ORD-1001 │ P001       │ 2026-08-03 08:50:00 │     1 │
│ ORD-1001 │ P001       │ 2026-08-03 08:15:00 │     2 │
│ ORD-3001 │ P004       │ 2026-08-05 09:05:00 │     1 │
│ ORD-3001 │ P004       │ 2026-08-05 08:11:00 │     2 │
│ ORD-6005 │ P003       │ 2026-08-08 10:00:00 │     1 │
│ ORD-6005 │ P003       │ 2026-08-08 09:11:00 │     2 │
└──────────┴────────────┴─────────────────────┴───────┘

Notice the order: for each order_id+product_id, the row with the latest ingested_at — the resent copy, the most recent to arrive — gets rn = 1; the original copy gets rn = 2. That's exactly what ORDER BY ingested_at DESC (descending) produces: the largest date first, so rn = 1 always corresponds to "the last time this data arrived."

Now, QUALIFY: it filters the window function's result, keeping only rn = 1, with no need for a subquery or a WITH to do it.

print("\n=== QUALIFY applied -- back to 40 rows ===")
print(con.sql("""
    WITH deduped AS (
        SELECT *
        FROM raw_orders_batch
        QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) = 1
    )
    SELECT COUNT(*) AS total_rows FROM deduped
"""))

=== QUALIFY applied -- back to 40 rows ===
┌────────────┐
│ total_rows │
│   int64    │
├────────────┤
│         40 │
└────────────┘

Forty rows — exactly the number of unique order lines raw_orders_batch always had, according to lesson 5. Check the grain again, with the same query as always, now on the deduplicated result, and confirm the correct revenue:

print("\n=== Grain verified again, post-dedup ===")
print(con.sql("""
    WITH deduped AS (
        SELECT *
        FROM raw_orders_batch
        QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) = 1
    )
    SELECT
        COUNT(*) AS total_rows,
        COUNT(DISTINCT order_id || '-' || product_id) AS distinct_order_product_lines,
        ROUND(SUM(revenue), 2) AS total_revenue
    FROM deduped
"""))

=== Grain verified again, post-dedup ===
┌────────────┬──────────────────────────────┬────────────────┐
│ total_rows │ distinct_order_product_lines │ total_revenue │
│   int64    │            int64             │     double     │
├────────────┼──────────────────────────────┼────────────────┤
│         40 │                            40 │        106.15 │
└────────────┴──────────────────────────────┴────────────────┘

total_rows and distinct_order_product_lines match again — the grain is restored — and total_revenue is back to 106.15, the familiar reference number, not the inflated 119.05 lesson 5 measured on the batch before deduplication.

Diagram: what each piece of the query does

flowchart TD
    A["raw_orders_batch: 43 rows"] --> B["PARTITION BY order_id, product_id\ngroups the copies of the same order line"]
    B --> C["ORDER BY ingested_at DESC\nwithin each group, most recent first"]
    C --> D["ROW_NUMBER()\nnumbers 1, 2, 3... within each group"]
    D --> E{"QUALIFY rn = 1"}
    E -->|"rn = 1\n(the most recent copy)"| F["Kept: 40 rows"]
    E -->|"rn > 1\n(older copies)"| G["Discarded: 3 rows"]

Going deeper: why QUALIFY, and what it replaces exactly

Before QUALIFY existed as a clause, filtering on a window function's result required wrapping the query in a subquery or a WITH — exactly what this lesson's deduped CTE does internally — because window functions, unlike WHERE, get computed after the rows have already been selected, not before. QUALIFY exists, in the words of DuckDB's official documentation, with the same relationship to window functions that HAVING has to GROUP BY: just as HAVING filters an aggregate function's result without needing a subquery, QUALIFY filters a window function's result without needing one either. QUALIFY's exact position within a complete SELECT, per that same documentation, is after WINDOW and before ORDER BY:

SELECT select_list FROM tables WHERE condition GROUP BY groups
HAVING group_filter WINDOW window_expression QUALIFY qualify_filter
ORDER BY order_expression LIMIT n

Here's the equivalent form, without QUALIFY, that you would have had to write on an engine that doesn't support it:

-- Equivalent WITHOUT QUALIFY, with an explicit subquery
SELECT order_id, store_id, product_id, quantity, unit_price, revenue, order_ts, ingested_at
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) AS rn
    FROM raw_orders_batch
) t
WHERE rn = 1

Both forms produce exactly the same result — QUALIFY doesn't change the logic, it just avoids having to name and wrap a subquery solely to be able to filter on rn. It's worth noting a DuckDB restriction that shows up if you try to combine QUALIFY with an aggregate function with no GROUP BY in the same query — for example, SELECT COUNT(*) FROM t QUALIFY ROW_NUMBER() ... = 1, with no CTE in between: DuckDB requires the columns used inside the window function to also appear in a GROUP BY, because it treats the whole query as an aggregation. The correct form, the one this lesson uses, avoids that conflict by separating the filtering (QUALIFY, inside the deduped CTE) from the final aggregation (COUNT(*), outside it) — two distinct steps, each with its own responsibility.

Why ORDER BY ingested_at DESC, and not ASC

Notice the ORDER BY's direction: DESC, descending, not ASC. The choice isn't arbitrary — it depends on which copy represents "the truth" when there's more than one. In this lesson's scenario, the copies are identical in value (lesson 5 confirmed it), so either one would technically be correct to keep. But the convention of "keep the most recent record" — ORDER BY <timestamp> DESC, rn = 1 — is the one that generalizes correctly to the more common production case, where a resend can bring a real correction (not just an exact copy): if a source system fixes an error and resends the same order with a different value, keeping the most recent copy is, almost always, the right call — it's the most up-to-date version of the truth, according to the source. Using ASC instead would deliberately keep the oldest copy, a valid choice only if your business rule is "the first record that arrives is the one that counts, regardless of later resends" — a different rule, with its own use cases, but not the one this lesson adopts.

Common mistakes

Using PARTITION BY order_id alone, without product_id. What happens: someone writes ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ingested_at DESC), without including product_id in the PARTITION BY, repeating the exact incomplete-key mistake module 1 and this module's lesson 5 already warned about for fact_orders's grain. Why it happens: with Kiosko's current data — where each order has a single product — order_id alone produces the same result as the composite key, so the error is invisible with this dataset. How to spot it: if your PARTITION BY doesn't include every column that makes up the table's real grain (here, order_id and product_id), and some day a Kiosko order had more than one product, ROW_NUMBER() would group completely different order lines as if they were duplicates of the same thing — discarding, by mistake, a real product line, not a duplicate. How to fix it: a deduplication's PARTITION BY must use exactly the same key the grain query uses for COUNT(DISTINCT ...) — in Kiosko, always order_id and product_id together, never one alone.

Forgetting the ORDER BY inside ROW_NUMBER(), or using a column that doesn't distinguish the copies. What happens: someone writes ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY order_ts) — using order_ts instead of ingested_at — exactly what lesson 5 warned against. Since both copies of each resent order share the same order_ts (the sale happened once), DuckDB numbers the tied rows in an order that isn't guaranteed to be deterministic without a tiebreaker column. Why it happens: order_ts is the more familiar column, and it isn't obvious, without thinking about it, that both copies share that exact value. How to spot it: if you run the same deduplication query twice and the result (which specific row ends up with rn = 1) isn't always the same, your ORDER BY has an untied tiebreaker. How to fix it: a deduplication ROW_NUMBER()'s ORDER BY must use a column that does uniquely distinguish the copies from each other — ingested_at, in this lesson's case, because each resend arrived at a different instant.

Assuming QUALIFY replaces WHERE, instead of complementing it. What happens: someone tries to write WHERE ROW_NUMBER() OVER (...) = 1 directly, without QUALIFY, and DuckDB rejects the query with a syntax error. Why it happens: WHERE filters rows before window functions get computed, so at the moment WHERE is evaluated, ROW_NUMBER() doesn't exist yet as an available value — it's impossible to filter on something that hasn't been computed yet. How to spot it: if DuckDB reports a "column not found" error or a syntax error when trying to use a window function inside a WHERE, that's exactly the symptom. How to fix it: any filter that depends on a window function's result — ROW_NUMBER(), RANK(), or any other — needs QUALIFY (or the equivalent subquery/WITH), never WHERE directly.

Exercises

Exercise 1 — Confirm that the copy kept for each resent key is the one with the most recent ingested_at. Using the deduplicated result, write a query that shows, for the three resent keys, the ingested_at that remained after applying QUALIFY.

print(con.sql("""
    SELECT order_id, ingested_at
    FROM raw_orders_batch
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) = 1
    AND order_id IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    ORDER BY order_id
"""))

┌──────────┬─────────────────────┐
│ order_id │     ingested_at     │
│ varchar  │      timestamp      │
├──────────┼─────────────────────┤
│ ORD-1001 │ 2026-08-03 08:50:00 │
│ ORD-3001 │ 2026-08-05 09:05:00 │
│ ORD-6005 │ 2026-08-08 10:00:00 │
└──────────┴─────────────────────┘

Exercise 2 — Deduplicate using ASC instead of DESC, and explain the difference. Run the same deduplication query, but with ORDER BY ingested_at ASC (ascending), and confirm which ingested_at remains for the three resent keys.

print(con.sql("""
    SELECT order_id, ingested_at
    FROM raw_orders_batch
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at ASC) = 1
    AND order_id IN ('ORD-1001', 'ORD-3001', 'ORD-6005')
    ORDER BY order_id
"""))

┌──────────┬─────────────────────┐
│ order_id │     ingested_at     │
│ varchar  │      timestamp      │
├──────────┼─────────────────────┤
│ ORD-1001 │ 2026-08-03 08:15:00 │
│ ORD-3001 │ 2026-08-05 08:11:00 │
│ ORD-6005 │ 2026-08-08 09:11:00 │
└──────────┴─────────────────────┘

Exercise 3 — Explain why total_rows = 40 after QUALIFY isn't, by itself, sufficient proof the deduplication was correct. In 2-3 sentences, describe what other check you would need to run to confirm the rows kept are the correct ones, not just that the count is correct.

Summary and next step

This lesson deduplicated lesson 5's batch with ROW_NUMBER() OVER (PARTITION BY order_id, product_id ORDER BY ingested_at DESC) combined with QUALIFY ... = 1, and confirmed, with the same familiar grain query, that the result has exactly forty rows — the correct number — with total revenue back at 106.15. You also saw why QUALIFY exists (avoiding the subquery a filter on a window function would otherwise need) and why the ORDER BY's direction (DESC, "the most recent wins") is a business decision, not an arbitrary technical detail.

Before moving on you should be able to: write from memory the structure ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) ... QUALIFY = 1; explain why PARTITION BY must use the same composite key as the grain query; and explain the difference between ORDER BY ... DESC (keeps the most recent) and ASC (keeps the oldest) in a deduplication context.

Lesson 7 introduces a related but different tool: ANTI JOIN/SEMI JOIN, two native DuckDB JOIN types that serve both for finding fact rows with no dimension version to cover them (picking back up lesson 4's problem) and for precisely detecting which rows in a new batch represent a real change against what's already loaded.

Resources

• DuckDB — official QUALIFY clause documentation — the exact syntax and position reference within a SELECT, including the explicit comparison with HAVING. duckdb.org/docs/current/sql/query_syntax/qualify. In English.
• DuckDB — window functions documentation, including ROW_NUMBER() — the complete syntax reference for PARTITION BY and ORDER BY inside OVER (...). duckdb.org/docs/current/sql/functions/window_functions. In English.
• DuckDB — official Python client documentation, used to build and deduplicate raw_orders_batch in this lesson. duckdb.org/docs/current/clients/python/overview. In English.