SnowPro Associate: Platform Question 209
Single answer○ Structured dataA retail company loads daily sales data from CSV files into a Snowflake staging table. One column, SALE_AMOUNT, is defined as VARCHAR because the source system sometimes sends values such as "125.50", "99", or invalid strings like "N/A". Analysts want a curated table with SALE_AMOUNT stored as a numeric type so they can aggregate revenue reliably, but the load process must continue even when some rows contain invalid values. Which approach should the data engineer use?
- A
Insert into the curated table by casting SALE_AMOUNT with TO_NUMBER(SALE_AMOUNT), because Snowflake converts invalid numeric strings to 0 automatically.
- B
Insert into the curated table by using TRY_TO_NUMBER(SALE_AMOUNT), which converts valid values to a numeric type and returns NULL for invalid values.
- C
Change the curated column to VARIANT so both valid numbers and invalid strings can be stored without transformation, and Snowflake will still treat them as numeric in aggregations.
- D
Use PARSE_JSON(SALE_AMOUNT) during insert so numeric-looking strings become structured numeric values and invalid strings are skipped.
Show answer and explanation
Correct answer: B
Explanation
The best solution is to use TRY_TO_NUMBER when transforming the VARCHAR source column into a numeric column in the curated table. In Snowflake, structured data benefits from explicit column typing, such as NUMBER for monetary values, because it enables consistent validation, storage, and aggregation behavior. Unlike TO_NUMBER, which raises an error on invalid input, TRY_TO_NUMBER returns NULL for non-convertible values and allows the statement to continue. This is a common best practice when loading imperfect source data into a strongly typed structured schema. Snowflake documentation for conversion functions distinguishes standard casts and conversion functions from TRY_ variants, which are specifically intended for tolerant parsing.
- A. Incorrect.
Incorrect. TO_NUMBER attempts an explicit conversion, but invalid strings such as "N/A" cause a conversion error rather than being converted to 0. Using TO_NUMBER directly would risk failing the insert statement when bad source values are encountered. A common misconception is assuming Snowflake silently coerces invalid strings to zero, but it does not.
- B. Correct.
Correct. TRY_TO_NUMBER is designed for this scenario. It converts valid numeric strings to a NUMBER value and returns NULL when conversion is not possible, allowing the load to continue. This supports a curated structured schema while handling bad source data gracefully. Analysts can then aggregate the numeric column and decide how to treat NULLs.
- C. Incorrect.
Incorrect. VARIANT is a semi-structured data type, not the best choice for a curated structured column intended for reliable numeric aggregation. While Snowflake can store mixed content in VARIANT, using it here would weaken schema enforcement and complicate downstream analytics. The requirement is specifically to store the curated value as a numeric type.
- D. Incorrect.
Incorrect. PARSE_JSON is used to parse JSON-formatted text into VARIANT. A plain string like "125.50" is not the right input pattern for this requirement, and invalid values are not automatically skipped by PARSE_JSON in a way that solves the problem. This option reflects confusion between structured data typing and semi-structured JSON parsing.