DAA-C01 Question 54
Single answerCleanse, conform, and enrich dataA retail company ingests daily customer records from three source systems into Snowflake. Each source uses different country representations (for example, "US", "USA", and "United States"), inconsistent email casing, and occasionally sends duplicate customer rows for the same business key. Analysts need a curated customer dimension that is standardized, deduplicated, and enriched with a region value from a reference mapping table. The pipeline must be repeatable and should avoid rewriting logic across downstream queries. Which approach is the MOST appropriate?
- A
Create a transformation layer that standardizes fields with SQL functions, deduplicates with window functions and QUALIFY, and enriches records by joining to a country-to-region reference table before publishing the curated dimension.
- B
Ask analysts to standardize country values and email casing in each reporting query so the raw data remains unchanged and flexible for all use cases.
- C
Use a materialized view directly on the raw ingestion table to handle all standardization, deduplication, and enrichment logic, including the join to the reference table.
- D
Replace the source-system business key with a newly generated random identifier in the curated table so duplicate rows are no longer visible to analysts.
Show answer and explanation
Correct answer: A
Explanation
The most appropriate solution is to build a curated transformation layer that standardizes attributes, deduplicates records, and enriches them from trusted reference data before exposing the final dimension to analysts. In Snowflake, this is commonly implemented using SQL transformations in views, dynamic tables, tasks/stored procedures, or ELT pipelines. For deduplication, ROW_NUMBER() with QUALIFY is a standard Snowflake pattern to retain the latest or otherwise preferred row per business key. For conformance, using centralized mapping logic or reference tables ensures that values such as country names are represented consistently across sources. For enrichment, joining to reference dimensions or lookup tables is a standard best practice. Snowflake documentation supports QUALIFY for filtering window-function results and emphasizes SQL-based transformation patterns for preparing analytics-ready data. Centralizing these steps improves consistency, maintainability, and trust in downstream reporting.
- A. Correct.
Correct. This is the best practice for cleansing, conforming, and enriching data in Snowflake. A dedicated transformation layer centralizes reusable business logic, making the process repeatable and easier to govern. Standardization can be handled with SQL functions such as UPPER, LOWER, TRIM, and CASE or lookup joins for conformed values. Deduplication is commonly implemented with ROW_NUMBER() over the business key and QUALIFY to keep the desired surviving record. Enrichment from a reference table via a join is a practical and common dimensional modeling pattern. Publishing a curated dimension ensures downstream users consume trusted, consistent data without re-implementing logic.
- B. Incorrect.
Incorrect. Although raw data should generally be preserved, pushing cleansing and conformance logic into every reporting query creates inconsistent results, duplicated effort, and higher maintenance risk. Different analysts may implement slightly different standardization logic, which defeats the goal of a trusted curated layer. This option reflects a common misconception that flexibility is more important than governed reuse for analytics-ready data.
- C. Incorrect.
Incorrect. Materialized views in Snowflake have important limitations and are not the best fit for this full pattern, especially when complex transformation requirements and enrichment joins are involved. Even aside from feature constraints, using a materialized view as the primary place for all data quality and conformance logic is less flexible than a managed transformation layer with tables or views designed for curated consumption. This distractor targets the misconception that materialized views are a universal solution for any transformation workload.
- D. Incorrect.
Incorrect. Generating a new random identifier does not actually resolve duplicate business entities; it only masks the duplicate problem by assigning different surrogate values. The curated dimension still needs deterministic business-key-based deduplication logic to identify the correct surviving record. Surrogate keys can be useful in dimensional models, but they do not replace proper cleansing and conformance steps.