Deduplication Methods

In Snowflake, I want to have a way to day these steps. I will have multiple subqueries. For each subquery there will do these steps logically: 1. Create the result set for the subquery with its where clause criteria 2. Remove records in this result set where they are any duplicate records from the previous results sets, using one or more keys to match. If it is the first query, then there will be no previous result sets to remove from. 3. Choose the number of records in this result set that will be include (using a limit if it makes the most sense)

ChatGPT said:

Thought for 27s

You can do this in Snowflake in (at least) two clean ways. I’ll show the one that most literally matches your step-by-step logic first, then a more compact “priority + window function” pattern.

1. “Step-by-Step” with CTEs + `NOT EXISTS` (most literal)

This follows your 3 steps for each subquery:

Build the result set
Remove rows that already appeared before (by key)
Apply a limit

Assume:

You have 3 subqueries q1, q2, q3
Your de-dupe keys are key1, key2
You want different limits for each query.

WITH q1 AS (
    -- 1. base result set for subquery 1
    SELECT key1,
           key2,
           other_cols
    FROM   table1
    WHERE  ... -- criteria for subquery 1
),
q1_final AS (
    -- 2+3. nothing to exclude yet (first query), just limit
    SELECT *
    FROM   q1
    ORDER BY some_order_col   -- whatever ordering you want
    LIMIT  100                -- limit for subquery 1
),

q2 AS (
    -- 1. base result set for subquery 2
    SELECT key1,
           key2,
           other_cols
    FROM   table2
    WHERE  ... -- criteria for subquery 2
),
q2_final AS (
    -- 2. remove records already in q1_final
    SELECT q2.*
    FROM   q2
    WHERE  NOT EXISTS (
        SELECT 1
        FROM   q1_final p
        WHERE  p.key1 = q2.key1
        AND    p.key2 = q2.key2
    )
    -- 3. choose number of records to keep
    ORDER BY some_order_col
    LIMIT  50                   -- limit for subquery 2
),

q3 AS (
    -- 1. base result set for subquery 3
    SELECT key1,
           key2,
           other_cols
    FROM   table3
    WHERE  ... -- criteria for subquery 3
),
q3_final AS (
    -- 2. remove records already in q1_final or q2_final
    SELECT q3.*
    FROM   q3
    WHERE  NOT EXISTS (
        SELECT 1
        FROM   q1_final p
        WHERE  p.key1 = q3.key1
        AND    p.key2 = q3.key2
    )
    AND    NOT EXISTS (
        SELECT 1
        FROM   q2_final p
        WHERE  p.key1 = q3.key1
        AND    p.key2 = q3.key2
    )
    -- 3. limit
    ORDER BY some_order_col
    LIMIT  25                   -- limit for subquery 3
)

SELECT * FROM q1_final
UNION ALL
SELECT * FROM q2_final
UNION ALL
SELECT * FROM q3_final;

This pattern:

Mirrors your step order exactly.
Lets you use different limits, sorts, or filters per subquery.
Scales to more subqueries; you just keep adding NOT EXISTS against the previous _final CTEs.

If you have many subqueries, you can also combine the anti-joins (e.g., one NOT EXISTS on a UNION ALL of previous results), but the idea is the same.

2. Compact “Priority + Window Function” Pattern

If you’re okay defining a priority order for the subqueries (q1 wins over q2 wins over q3 when keys collide), you can do it in one structure:

UNION all subqueries with a priority column.
Pick the earliest priority per key.
Then apply a per-priority limit.

WITH q1 AS (
    SELECT key1, key2, other_cols
    FROM   table1
    WHERE  ...
),
q2 AS (
    SELECT key1, key2, other_cols
    FROM   table2
    WHERE  ...
),
q3 AS (
    SELECT key1, key2, other_cols
    FROM   table3
    WHERE  ...
),

all_rows AS (
    SELECT 1 AS priority, q1.*
    FROM   q1
    UNION ALL
    SELECT 2 AS priority, q2.*
    FROM   q2
    UNION ALL
    SELECT 3 AS priority, q3.*
    FROM   q3
),

-- assign the winning priority per key
marked AS (
    SELECT a.*,
           MIN(priority) OVER (PARTITION BY key1, key2) AS owner_priority
    FROM   all_rows a
),

dedup AS (
    -- keep only the row from the earliest priority per key
    SELECT *
    FROM   marked
    WHERE  priority = owner_priority
),

ranked AS (
    -- rank within each priority to apply limits
    SELECT d.*,
           ROW_NUMBER() OVER (
               PARTITION BY priority
               ORDER BY some_order_col
           ) AS rn
    FROM   dedup d
)

SELECT *
FROM   ranked
WHERE  (priority = 1 AND rn <= 100)
    OR (priority = 2 AND rn <= 50)
    OR (priority = 3 AND rn <= 25);

This:

Ensures each key appears at most once (in the highest-priority subquery that produced it).
Applies per-subquery limits after deduplication.

Which should you use?

If you want to mirror the mental model of “do q1, then q2 minus q1, then q3 minus q1 & q2…” → use approach 1 with NOT EXISTS.
If you’re comfortable with a priority-based model and want something more compact, especially as the number of subqueries grows → use approach 2 with window functions.

If you share your actual subqueries / keys, I can plug them into one of these patterns so you can drop it straight into Snowflake.

Deduplication Methods

ChatGPT said:

1. “Step-by-Step” with CTEs + NOT EXISTS (most literal)

2. Compact “Priority + Window Function” Pattern

Which should you use?

1. “Step-by-Step” with CTEs + `NOT EXISTS` (most literal)