Scenario

You work at a financial services company that receives transactional data from various clients, but both tables contain invalid and duplicate primary keys that must be cleaned before joining.

Task

Write a Snowflake SQL query that:

1. Filters {{ ref("clients") }} to keep only rows where client_id > 0, then deduplicates by client_id (keep the first occurrence using ROW_NUMBER)
2. Filters {{ ref("transactions") }} to keep only rows where transaction_id > 0, then removes any transaction_id that appears more than once (drop all copies, not just extras)
3. Joins the cleaned transactions with the cleaned clients on client_id using INNER JOIN
4. Returns all six columns, casting amount to INTEGER

Schema

transactions

clients

Example

transactions:

clients:

Expected Output:

Note: Transaction 102 is dropped because two rows share that ID (both copies removed). Transaction -104 is dropped for having a negative ID. Client 10 is duplicated, so only the first occurrence (Nexora) is kept. Client -50 is dropped for having a negative ID.

WITH clean_clients AS (
    SELECT client_id, client_name, industry
    FROM {{ ref("clients") }}
    WHERE client_id > 0
    QUALIFY ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY client_id) = 1
),
clean_transactions AS (
    SELECT transaction_id, client_id, date, amount
    FROM {{ ref("transactions") }}
    WHERE transaction_id > 0
    QUALIFY COUNT(*) OVER (PARTITION BY transaction_id) = 1
),
joined AS (
    SELECT
        t.transaction_id,
        t.client_id,
        t.date,
        CAST(t.amount AS INTEGER) AS amount,
        c.client_name,
        c.industry
    FROM clean_transactions t
    INNER JOIN clean_clients c
        ON t.client_id = c.client_id
)
SELECT * FROM joined

Explanation

Step 1: Clean the clients table

SELECT client_id, client_name, industry
FROM {{ ref("clients") }}
WHERE client_id > 0
QUALIFY ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY client_id) = 1

First filter out invalid rows where client_id <= 0. Then use Snowflake's QUALIFY clause with ROW_NUMBER() to keep only the first occurrence of each client_id. The QUALIFY clause filters the result of window functions the same way HAVING filters aggregations, eliminating the need for a subquery.

Step 2: Clean the transactions table

SELECT transaction_id, client_id, date, amount
FROM {{ ref("transactions") }}
WHERE transaction_id > 0
QUALIFY COUNT(*) OVER (PARTITION BY transaction_id) = 1

After filtering invalid IDs, use QUALIFY COUNT(*) OVER (PARTITION BY transaction_id) = 1 to drop all rows where the transaction_id appears more than once. This is different from ROW_NUMBER (which would keep one copy). Here the requirement is to discard every copy of a duplicated ID, so COUNT(*) = 1 is the correct approach.

Step 3: Join and cast

SELECT
    t.transaction_id,
    t.client_id,
    t.date,
    CAST(t.amount AS INTEGER) AS amount,
    c.client_name,
    c.industry
FROM clean_transactions t
INNER JOIN clean_clients c
    ON t.client_id = c.client_id

The INNER JOIN automatically excludes transactions whose client_id does not exist in the cleaned clients table. CAST(t.amount AS INTEGER) truncates the decimal portion. An alternative is t.amount::INTEGER AS amount, which is Snowflake shorthand for CAST. Use one or the other, not both together.