DAA-C01 Question 124
Single answer2.2 Given a dataset, clean the data.A retail analytics team loads daily customer profile files from multiple regions into a Snowflake landing table. The table contains a VARIANT column named RAW_REC with fields such as "email", "phone", "signup_date", and "postal_code". Analysts report that downstream dashboards are inconsistent because signup dates appear in multiple formats, blank strings are mixed with NULLs, and phone numbers contain punctuation and spaces. The team wants to create a cleaned view for analysis without modifying the raw landing data. Which approach is the MOST appropriate in Snowflake to standardize these fields for reliable analytical use?
- A
Create a view that extracts fields from RAW_REC, converts blank strings to NULL with NULLIF, normalizes phone numbers with REGEXP_REPLACE, and uses TRY_TO_DATE with the expected formats (or COALESCE across formats) for signup_date.
- B
Update the landing table in place by replacing the VARIANT values with cleaned VARCHAR values so that all downstream users read already-standardized data from the raw table.
- C
Use SELECT DISTINCT on the extracted fields to remove formatting inconsistencies before analysts query the data.
- D
Cast signup_date directly with TO_DATE and leave blank strings unchanged so analysts can distinguish blanks from NULL values later.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to build a cleaned projection of the semi-structured data using a view or similar derived object rather than altering the raw landing table. In Snowflake, common cleaning techniques include extracting elements from VARIANT using path notation, normalizing missing values with NULLIF, standardizing text patterns with functions such as REGEXP_REPLACE, and safely converting inconsistent strings using TRY_TO_DATE or other TRY_* conversion functions. TRY_* functions are especially useful in analytical pipelines because they return NULL instead of failing the query when conversion is not possible. This supports robust downstream reporting. Snowflake best practices generally favor retaining raw ingested data and performing cleansing and standardization in downstream curated layers for transparency, reproducibility, and easier debugging.
- A. Correct.
Correct. This is the most appropriate approach because it preserves the raw landing data while presenting standardized fields for analysis in a derived layer such as a view. NULLIF can convert empty strings to NULL, which is a common data-cleaning best practice for analytics. REGEXP_REPLACE can remove non-numeric characters from phone numbers. TRY_TO_DATE avoids query failures when invalid or mixed-format date strings are encountered, and COALESCE can be used when attempting multiple date formats. This aligns well with Snowflake best practices of keeping raw data intact and applying transformations in curated layers.
- B. Incorrect.
Incorrect. Modifying the raw landing table directly is generally not the best approach when the requirement is to keep the raw ingested data unchanged. Replacing values inside the raw VARIANT payload also reduces auditability and lineage, and can make troubleshooting ingestion issues harder. While Snowflake supports updates, using them here contradicts the stated goal of creating a cleaned analytical layer without altering raw data.
- C. Incorrect.
Incorrect. SELECT DISTINCT removes duplicate rows based on selected columns; it does not standardize inconsistent formats such as '(555) 123-4567' versus '5551234567', nor does it reliably address blank strings versus NULLs or multiple date formats. A candidate might choose this if they confuse deduplication with data cleaning, but these are different tasks.
- D. Incorrect.
Incorrect. TO_DATE can fail when encountering unexpected or invalid formats, which is risky when the source contains inconsistent date representations. Leaving blank strings unchanged also creates semantic inconsistency because blank strings and NULLs are treated differently in analysis and aggregations. A cleaned analytical layer should standardize missing values, not preserve ambiguity.