Snowflake QUALIFY Interview Questions: Window Filters and Unexpected Duplicates

You write QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1 and expect one current row per customer. The query returns one row today, a different row tomorrow, and two rows after a downstream join. Which part of QUALIFY failed?

Probably none. Snowflake's QUALIFY reference says the clause filters after window functions are computed. It does not define the business key, break timestamp ties, or guarantee that a later join remains one-to-one. The ledger and interview prompts below are original practice exercises, not candidate reports or claims about a particular employer. No verified same-cycle candidate account establishes this exact question set. For a related key-grain drill, Deduplicate events and rank products with SQL asks you to defend the duplicate rule rather than merely use a syntax trick.

Question 1: Where does QUALIFY sit in the query?

Snowflake documents a typical logical evaluation order: FROM, WHERE, GROUP BY, HAVING, window evaluation, QUALIFY, DISTINCT, ORDER BY, then LIMIT. That means WHERE filters source rows before a window sees them, while QUALIFY filters rows after a window result exists. This distinction is more important than memorizing the list. If you restrict events to one day in WHERE, a “latest event per customer” window only ranks that day. If you rank all history and then select a day from the surviving rows, you answer a different question.

HAVING operates on groups after aggregation; QUALIFY operates on window-function results. Snowflake requires at least one window function in the SELECT list or QUALIFY clause. It allows a SELECT alias for a window expression to be referenced in QUALIFY. The clause is Snowflake SQL and is not ANSI-standard syntax, so portability needs a subquery or CTE. These are documented product facts, not universal SQL rules.

An interviewer may ask whether QUALIFY is “the same as WHERE on a window alias.” The safe answer is that it is similar in intent to filtering the result of an inner query, but the stage matters. A direct WHERE rn=1 in the same select block cannot use a window result calculated later. A CTE with ROW_NUMBER() and an outer WHERE rn=1 usually expresses the same row-selection logic as the corresponding QUALIFY, provided the input, grouping, and later operations are kept equivalent. Do not claim they are always interchangeable after arbitrarily moving joins or filters.

Question 2: What is the actual duplicate key?

Use this invented change-event ledger. The business wants the latest accepted status per account. event_id is stable and unique; ingest_seq is a monotonic tie-breaker within this fixture. Two events for A have the same updated_at.

event_id	account_id	updated_at UTC	ingest_seq	status
E10	A	2026-09-01 09:00	10	pending
E11	A	2026-09-01 10:00	11	approved
E12	A	2026-09-01 10:00	12	rejected
E20	B	2026-09-01 08:00	20	approved

PARTITION BY account_id means one winning row per account. PARTITION BY account_id, status would keep one row per status per account, leaving A with multiple rows. That is not a syntax failure; it is a different key. If the business needs latest status per account and region, region belongs in the partition only if the same account can genuinely have independent regional states. Define the entity and grain before writing SQL.

For the stated rule, a deliberate query is:

SELECT account_id, event_id, updated_at, ingest_seq, status
FROM accepted_events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY account_id
  ORDER BY updated_at DESC, ingest_seq DESC, event_id DESC
) = 1;

The fixture yields E12 for A and E20 for B. event_id supplies a final stable order if a future load ties on time and sequence. In a real system, verify that the declared tie-breaker reflects the business definition of “latest.” A load timestamp may choose the last arrival rather than the latest actual change. The ROW_NUMBER reference defines partition and ordering; using a stable final key to make a tied survivor deterministic is an engineering inference reinforced by Snowflake's migration note on tied ranking.

Question 3: Why can the survivor change across runs?

If the ORDER BY contains only updated_at DESC, E11 and E12 tie. ROW_NUMBER still assigns them different row numbers, but the specification has not told the engine which tied row deserves 1. Do not rely on table load order, physical storage order, or a previous query's observed result as a hidden tie-breaker. Add columns that create a total, reproducible ordering and explain why that order matches the question.

Compare ranking functions. ROW_NUMBER() = 1 yields exactly one row per nonempty partition. RANK() = 1 or DENSE_RANK() = 1 can yield multiple top rows when ordering values tie. That is appropriate if the business wants all equally latest records, but inappropriate for a strict one-row current-state table. Conversely, forcing one arbitrary record with ROW_NUMBER may hide a genuine conflict between two equally valid source updates. A strong answer offers a quarantine or conflict flag if no authoritative tie-breaker exists.

Sorting the final result with ORDER BY updated_at DESC does not decide the window's winning row. The ORDER BY inside OVER (...) determines rank within each partition; the outer ORDER BY orders rows after QUALIFY. This is a common interview trap because both clauses use similar words but operate at different stages. Show both in a small trace if the interviewer wants proof.

Question 4: Why are duplicates still present after QUALIFY?

There are several meanings of “duplicate.” You might have one row per account after ranking, but two rows with the same visible display name. Or you may have one event per (account_id, region) because that was the partition. Or a later join may multiply the winner. Put counts at each stage and ask which invariant failed.

Suppose E12 is A's winner, then you join it to account_contact on account_id. A has two active contacts. The join returns two rows for A, each carrying E12. QUALIFY successfully reduced events to one per account; the join is one-to-many. If you want one contact, define its selection rule in a separate ranked CTE or aggregate contacts explicitly. If the report wants all contacts, the two output rows are legitimate and an account-level measure must avoid double counting. A late DISTINCT may hide identical projections but cannot decide which contact or event is correct.

The opposite query order matters too. If you join raw events to the two contacts before ranking, E12 appears twice in A's window partition. ROW_NUMBER() = 1 keeps just one joined row, potentially throwing away an intended contact. The safe pattern for current account status plus contact data is to rank the event stream at event grain, validate one event per account, and then join to a contact relation whose desired grain is explicit. That is a design inference from relational cardinality, not a promise that every Snowflake optimizer will execute CTEs physically in that order.

Stage	A rows in the fixture	What to test
Raw accepted events	3	Are all three valid events?
After ROW_NUMBER() = 1	1	Is E12 the declared survivor?
Contacts relation	2	Is two contacts per account allowed?
After winner-to-contact join	2	Is the output intended to be account or account-contact grain?

This cardinality ledger is a more useful debugging asset than a blanket “add DISTINCT” recommendation. SELECT DISTINCT occurs after QUALIFY in Snowflake's documented order and only removes rows that are identical over the selected columns. If the two contacts have different email fields, it will not reduce them. If you omit contact fields, it may collapse the two projections while masking the join defect from an aggregate that already multiplied revenue.

Question 5: How would you prove the query is safe to publish?

Test business invariants on a bounded sample and the full expected population. Count raw rows, distinct business keys, ranked winners, null keys, tied maximum timestamps, and keys with more than one result after every join. For an account-current table, a basic assertion is COUNT(*) = COUNT(DISTINCT account_id) with an explicit decision about null account IDs. Compare a sample of selected event IDs against the upstream change log. If status is filtered to “accepted” before ranking, verify that rejected events are intentionally invisible to the current-state definition.

Separate correctness from performance. Snowflake's dynamic-table optimization documentation discusses favorable incremental behavior for a top-level QUALIFY ROW_NUMBER() ... = 1 pattern in that particular feature. Do not generalize it into “QUALIFY is always faster than a subquery” or “the optimizer always materializes a ranking CTE.” Evaluate a representative query profile, partition sizes, and clustering/access patterns. A logically wrong partition can run quickly and still be unfit for production.

Make the review inspectable by recording the intended key, tie rule, and expected winner in the model or pull-request description. A reviewer can then check that a later edit does not replace ingest_seq with an unstable presentation field. Add a counterexample fixture in which two events share the maximum timestamp and differ in status; a test with only unique timestamps would never exercise the difficult choice. If the upstream source can resend an identical event_id, deduplicate delivery identities before deciding current business state, or specify clearly that repeated deliveries do not change the winner. These are two different forms of deduplication: delivery retry recovery and one-current-row selection. Conflating them can make a dashboard appear unique while event counts and audit history remain wrong.

A good interview follow-up asks about late-arriving updates. If an event arrives today with an earlier updated_at, the latest-by-business-time rule still chooses the newer business timestamp. If the intended rule is “last received,” order by an ingestion sequence instead. If two sources disagree on the same effective timestamp, define priority or surface a conflict. These are business semantics attached to the ranking, not behaviors supplied by QUALIFY.

A concise answer and three traps

“QUALIFY filters the windowed result. I would first define one row per account, then rank events by business update time and a stable tie-breaker. In the fixture, E12 wins for A and E20 for B. If the output still has two A rows, I would count rows before and after each join; A's two contacts can re-expand a correctly deduplicated event. I would test key uniqueness and tied timestamps before treating DISTINCT as a fix.”

Three traps are worth rehearsing. First, WHERE updated_at >= ... before the window changes the candidate population; it does not just improve speed. Second, RANK() = 1 returns every top tie, while ROW_NUMBER() = 1 chooses one. Third, an outer ORDER BY cannot repair an incomplete window ordering. If you can explain each with the four-row ledger, you understand the SQL rather than merely remembering the clause spelling.

Sources and further reading

• Snowflake: QUALIFY clause — logical evaluation order, aliases, and required window function.
• Snowflake: ROW_NUMBER function — partitioning and in-window ordering.
• Snowflake: migration note on tied ranks — stable tie-breakers for reproducible survivors.
• Snowflake: Dynamic-table refresh optimization — the scoped incremental QUALIFY recommendation.