DAA-C01 Question 130
Single answerHandle duplicationsA retail analytics team loads clickstream events into a Snowflake table named RAW_EVENTS. Because upstream retries can resend the same event, the table can contain duplicate rows for the same EVENT_ID. Analysts need a reusable view that returns only the latest record for each EVENT_ID based on INGESTED_AT, while keeping all original rows in RAW_EVENTS for audit purposes. Which approach best meets this requirement?
- A
Create a view that uses ROW_NUMBER() OVER (PARTITION BY EVENT_ID ORDER BY INGESTED_AT DESC) and filters to QUALIFY ROW_NUMBER() = 1.
- B
Create a materialized view with SELECT DISTINCT * FROM RAW_EVENTS to remove duplicates automatically as new rows arrive.
- C
Replace the table with a transient table and define EVENT_ID as a primary key so Snowflake rejects duplicate inserts.
- D
Create a task that truncates RAW_EVENTS daily and reloads only distinct rows from the previous day.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to deduplicate at query time by exposing a view over the raw table. In Snowflake, a common best practice for handling duplicates is to use window functions such as ROW_NUMBER() with PARTITION BY the business key and ORDER BY a recency column, then filter with QUALIFY. This pattern is especially appropriate when the raw table must remain unchanged for lineage, replay, or audit requirements. DISTINCT is often a misconception because it only removes identical rows across all projected columns and does not express business rules like 'keep the latest row per EVENT_ID.' Another common misconception is relying on primary key constraints for enforcement; in Snowflake, standard constraints are generally metadata only and are not enforced. Snowflake documentation for QUALIFY and window functions supports this approach, and Snowflake best practices commonly recommend preserving raw data while creating curated query layers for deduplicated analytical consumption.
- A. Correct.
Correct. Using ROW_NUMBER() partitioned by EVENT_ID and ordered by INGESTED_AT DESC is the standard Snowflake pattern for deduplicating while preserving the source data. QUALIFY lets you filter the results of the window function in the same query, making it ideal for a reusable deduplicated view. This returns the most recent row per EVENT_ID without deleting or altering RAW_EVENTS.
- B. Incorrect.
Incorrect. SELECT DISTINCT * only removes rows that are identical across all selected columns. In this scenario, duplicate business events may differ in INGESTED_AT or other metadata, so DISTINCT * would not reliably keep just the latest row per EVENT_ID. Also, materialized views have restrictions and are not the right answer for this requirement-focused deduplication logic.
- C. Incorrect.
Incorrect. In Snowflake, primary key constraints are informational by default and are not enforced on standard tables. Declaring EVENT_ID as a primary key would not prevent duplicate inserts. Changing the table to transient also only affects fail-safe/cost characteristics, not deduplication behavior.
- D. Incorrect.
Incorrect. Truncating and reloading the source table conflicts with the requirement to keep all original rows for audit purposes. It also introduces unnecessary operational risk and complexity. The requirement is for a reusable query layer that presents deduplicated results, not a destructive data management process.