DAA-C01 Question 123
Single answer2.2 Given a dataset, clean the data.A retail analytics team loads daily point-of-sale data into a Snowflake table named RAW_SALES. The AMOUNT column is VARCHAR because files come from multiple stores with inconsistent formatting. Sample values include '123.45', '$89.99', '1,250.00', 'N/A', '', and ' 42 '. Analysts need a cleaned numeric column for reporting, but rows with invalid values must not cause the query to fail. Which approach is the most appropriate for cleaning this column in a reusable SQL transformation?
- A
Use TRY_TO_DECIMAL(REPLACE(REPLACE(TRIM(AMOUNT), '$', ''), ',', ''), 10, 2) and keep rows where the result is NULL for downstream data quality review.
- B
Use TO_DECIMAL(AMOUNT, 10, 2) directly so Snowflake automatically ignores currency symbols, commas, and blank strings during conversion.
- C
Cast AMOUNT with AMOUNT::NUMBER(10,2); Snowflake converts any non-numeric string to 0, which preserves all rows for reporting.
- D
Use REGEXP_SUBSTR(AMOUNT, '[0-9]+')::NUMBER(10,2) so that any numeric content is extracted, regardless of where it appears in the string.
Show answer and explanation
Correct answer: A
Explanation
When cleaning inconsistent numeric text in Snowflake, the safest practice is to normalize the string first and then use a TRY_ conversion function so malformed values do not abort the query. For this scenario, removing known formatting artifacts such as '$' and ',' and trimming whitespace addresses valid but inconsistently formatted values, while TRY_TO_DECIMAL returns NULL for entries like 'N/A' or blank strings. This supports robust ELT pipelines and enables separate handling of data quality exceptions. Snowflake documentation recommends TRY_CAST and TRY_TO_* functions when conversion errors should return NULL instead of failing. Standard casts and TO_NUMBER/TO_DECIMAL without preprocessing are appropriate only when the input format is already valid.
- A. Correct.
Correct. This approach applies common cleansing steps before conversion: TRIM removes leading/trailing spaces, REPLACE removes dollar signs and thousands separators, and TRY_TO_DECIMAL attempts the numeric conversion without raising an error for invalid values such as 'N/A' or ''. Invalid inputs become NULL, which is appropriate for safe downstream handling and data quality review. This is a practical pattern in Snowflake for resilient cleaning of semi-structured or inconsistent string data.
- B. Incorrect.
Incorrect. TO_DECIMAL does not automatically ignore currency symbols, commas, or arbitrary non-numeric text. If the string is not in a valid numeric format for conversion, the statement can fail. Using TO_DECIMAL directly on values like '$89.99' or '1,250.00' without preprocessing is not a safe cleaning strategy when bad rows must not break the query.
- C. Incorrect.
Incorrect. The direct cast syntax behaves like a standard conversion and will error on invalid numeric strings rather than converting them to 0. A common misconception is that failed casts in Snowflake default to zero; they do not. To avoid query failure, TRY_CAST or TRY_TO_NUMBER/TRY_TO_DECIMAL should be used instead.
- D. Incorrect.
Incorrect. REGEXP_SUBSTR with '[0-9]+' only extracts the first contiguous sequence of digits and can corrupt the intended value. For example, '1,250.00' could become '1' and '$89.99' could become '89', losing decimal precision and producing inaccurate results. This option may look attractive because it avoids errors, but it does not reliably clean financial amounts.