DAA-C01 Question 53
Single answerCleanse, conform, and enrich dataA retail analytics team loads raw customer records from multiple regional systems into Snowflake. The data contains inconsistent country values such as 'US', 'USA', 'United States', and NULL, and analysts frequently join these records to a reference table that stores the standardized country name and ISO code. The team wants a repeatable transformation that both conforms the country values to a standard and enriches the customer dataset with the ISO code for downstream reporting. Which approach is the MOST appropriate?
- A
Create a transformation using a CASE expression to standardize the country values, then join the transformed result to the country reference table to add the ISO code.
- B
Use CLUSTER BY on the raw customer table by COUNTRY so Snowflake automatically groups equivalent country values and makes them join correctly to the reference table.
- C
Create a materialized view on the raw customer table and rely on automatic refresh to normalize country values and populate missing ISO codes from the reference table.
- D
Convert the COUNTRY column to VARIANT so semi-structured processing can infer equivalent country values such as 'US' and 'United States' during query execution.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to explicitly cleanse and conform the source values before enrichment. In Snowflake, this is commonly implemented with SQL transformations in views, dynamic tables, tasks/stored procedures, or ELT pipelines. Standardizing country values with expressions such as TRIM, UPPER, NULL handling, and CASE or a mapping table ensures that multiple source representations map to a single canonical form. Once conformed, joining to a reference or dimension table is the appropriate way to enrich the data with attributes like ISO code. This aligns with data warehousing best practices for conformed dimensions and with Snowflake's SQL-based transformation model. By contrast, physical design features like clustering improve performance but do not perform cleansing; materialized views can cache transformed query results but do not replace the need for explicit business logic; and VARIANT is intended for semi-structured data, not semantic standardization of known relational attributes.
- A. Correct.
Correct. This is the standard data cleansing and conformance pattern in Snowflake: explicitly standardize inconsistent source values with SQL logic such as CASE, COALESCE, TRIM, UPPER, or lookup-based mappings, then enrich the conformed dataset by joining to a trusted reference table that contains the canonical attributes, such as ISO code. This approach is transparent, repeatable, and appropriate for downstream analytics.
- B. Incorrect.
Incorrect. CLUSTER BY affects micro-partition organization and can improve pruning for some query patterns, but it does not cleanse data or make distinct textual values equivalent. 'US' and 'United States' remain different values unless explicitly transformed.
- C. Incorrect.
Incorrect. A materialized view can persist results of a query for performance, but it does not inherently normalize values or infer business mappings. You would still need explicit transformation logic. Also, using a materialized view is primarily a performance optimization, not the core cleansing strategy.
- D. Incorrect.
Incorrect. Converting a structured text column to VARIANT does not provide semantic standardization. Snowflake does not infer that different country strings represent the same business value simply because they are stored in a semi-structured type. This adds complexity without solving the conformance problem.