DAA-C01 Question 129
Single answerHandle duplicationsA retail analytics team loads clickstream events into a Snowflake table named RAW_EVENTS. Due to upstream retries, the table can contain duplicate rows for the same EVENT_ID, with different INGESTED_AT timestamps. Analysts want a reusable object that exposes only the latest row for each EVENT_ID while preserving all source rows in RAW_EVENTS for audit purposes. The solution should be simple for BI users to query and should avoid rewriting the source table each time new data arrives. Which approach best meets these requirements?
- A
Create a view that uses ROW_NUMBER() OVER (PARTITION BY EVENT_ID ORDER BY INGESTED_AT DESC) and QUALIFY ROW_NUMBER() = 1.
- B
Create a materialized view with SELECT DISTINCT * FROM RAW_EVENTS to automatically remove all duplicates.
- C
Periodically run INSERT OVERWRITE on RAW_EVENTS using SELECT DISTINCT EVENT_ID, INGESTED_AT, ... to keep only one row per event.
- D
Create a standard view that groups by EVENT_ID and uses MAX(INGESTED_AT), then join BI queries to RAW_EVENTS manually when other columns are needed.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a standard view that deduplicates with a window function and QUALIFY. In Snowflake, QUALIFY filters the results of window functions such as ROW_NUMBER(), making it ideal for patterns like "latest row per key." This approach preserves RAW_EVENTS for audit, avoids repeatedly rewriting the base table, and provides a clean object for analysts and BI tools. A typical implementation would be: SELECT * FROM RAW_EVENTS QUALIFY ROW_NUMBER() OVER (PARTITION BY EVENT_ID ORDER BY INGESTED_AT DESC) = 1. This is generally preferred over SELECT DISTINCT when duplicates are defined by a business key and recency rather than exact row equality. It also aligns with Snowflake SQL best practices for deduplication and analytic query design.
- A. Correct.
Correct. A standard view using ROW_NUMBER with PARTITION BY EVENT_ID and ORDER BY INGESTED_AT DESC, filtered with QUALIFY = 1, is a common Snowflake pattern for deduplicating while keeping the original table unchanged. It returns the latest record per business key and is easy for BI users to query directly. QUALIFY is specifically designed in Snowflake to filter results of window functions without requiring an extra subquery.
- B. Incorrect.
Incorrect. Although SELECT DISTINCT can remove exact duplicate rows, it does not solve the stated problem of keeping the latest row per EVENT_ID when duplicate business keys may differ in INGESTED_AT or other attributes. Also, Snowflake materialized views have restrictions and are not the right generic answer for this latest-record-per-key deduplication pattern.
- C. Incorrect.
Incorrect. This rewrites the source table, which conflicts with the audit requirement to preserve all original rows in RAW_EVENTS. In addition, using DISTINCT across selected columns does not reliably choose the latest version per EVENT_ID unless combined with a ranking or aggregation strategy, and it adds operational complexity the scenario says to avoid.
- D. Incorrect.
Incorrect. Grouping by EVENT_ID with MAX(INGESTED_AT) identifies the latest timestamp, but by itself it does not expose the full latest row safely and conveniently for BI users. Requiring downstream users to manually join back to RAW_EVENTS increases complexity and can introduce duplicate matches if multiple rows share the same maximum timestamp. The ROW_NUMBER plus QUALIFY pattern is simpler and more reusable.