DAA-C01 Question 52
Single answer1.5 Implement data processing solutions.A retail analytics team receives semi-structured clickstream files in an internal stage every 5 minutes. Analysts need a curated table that is continuously updated with flattened event attributes and deduplicated by EVENT_ID so downstream dashboards query a relational structure instead of raw VARIANT data. The team wants to minimize custom orchestration and avoid reprocessing unchanged files. Which solution best meets these requirements?
- A
Create a dynamic table that selects from the staged files directly, flattens the JSON, and uses QUALIFY ROW_NUMBER() to keep the latest record per EVENT_ID.
- B
Use Snowpipe to load raw files into a landing table, then create a stream on that table and a triggered task that MERGEs transformed, flattened, and deduplicated rows into the curated table.
- C
Schedule a task to run COPY INTO the curated table from the stage every 5 minutes using MATCH_BY_COLUMN_NAME so only new JSON attributes are processed.
- D
Create a materialized view on top of the staged files and enable automatic clustering on EVENT_ID to maintain a deduplicated relational table.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to separate ingestion from transformation using native incremental processing features. Snowpipe is designed for continuous file ingestion from stages into Snowflake tables and avoids reloading unchanged files by tracking load metadata. A stream on the landing table captures CDC-style changes at the table level, and a triggered task can run SQL only when new stream data is available. Inside that task, the team can parse VARIANT columns, use LATERAL FLATTEN for nested attributes, and MERGE into a curated table keyed by EVENT_ID to deduplicate/upsert records. This approach is practical for implementing data processing solutions because it reduces external orchestration, supports near-real-time processing, and provides a maintainable raw-to-curated pattern. Relevant Snowflake documentation includes Snowpipe for continuous data loading, Streams and Tasks for incremental ELT orchestration, MERGE for upserts, and FLATTEN for transforming semi-structured data.
- A. Incorrect.
Incorrect. Dynamic tables can automate incremental refresh of query results over supported sources, but they do not read directly from staged files as a source in the way described here. Staged files must first be loaded into tables. In addition, deduplication logic for curated persistence is more appropriately handled after ingestion into a landing/raw table. This option reflects a common misconception that dynamic tables can replace ingestion from stages.
- B. Correct.
Correct. This design aligns with Snowflake best practices for near-real-time ingestion and downstream transformation. Snowpipe continuously loads new files from the stage into a raw landing table without reprocessing unchanged files. A stream tracks row-level changes in that landing table, and a triggered task can execute when the stream has data, applying FLATTEN and MERGE logic to upsert into the curated relational table while deduplicating by EVENT_ID. This minimizes custom orchestration and supports incremental processing.
- C. Incorrect.
Incorrect. COPY INTO can load files from a stage into a table, but scheduling it every 5 minutes does not inherently provide the same event-driven, low-orchestration behavior as Snowpipe. More importantly, MATCH_BY_COLUMN_NAME is for loading into table columns by matching source fields to target columns and does not solve deduplication or selective processing of only changed JSON attributes. This option confuses schema evolution/loading behavior with incremental transformation design.
- D. Incorrect.
Incorrect. Materialized views are created on base tables, not directly on staged files. Automatic clustering also does not deduplicate records; it only improves micro-partition organization for query performance. This option combines valid Snowflake features in an invalid architecture for the stated requirement.