You are using Snowsight to load data into a Snowflake table 'PRODUCT DATA' from a CSV file stored in an internal stage. The CSV file contains a column named 'PRICE' which represents product prices…

Question

Options

• AUse the 'VALIDATE function to identify invalid rows before loading and then manually correct the
• BDefine a file format with 'ON_ERROR = SKIP_FILE' to skip files containing invalid price values.
• CUse the 'TO NUMBER function with the DEFAULT option in the `COPY INTO' statement's
• DUse the 'TRY TO NUMBER function in the COPY INTO' statement's 'SELECT' clause to convert
• EDefine a file format with 'ON_ERROR = ABORT_STATEMENT to abort the COPY INTO statement

• A
  22% (14)
• B
  3% (2)
• C
  5% (3)
• D
  61% (39)
• E
  9% (6)

Explanation

TRY_TO_NUMBER is correct because it gracefully handles conversion failures - when it encounters a non-numeric or empty value, it returns NULL instead of throwing an error, and wrapping it with IFNULL(TRY_TO_NUMBER($col), 0) in the SELECT clause of COPY INTO replaces those NULLs with 0, achieving exactly the required cleansing inline during load.

Why the distractors fail:

• A - VALIDATE only previews errors; it doesn't load data or replace values, so you'd still need to handle the bad rows separately.
• B - ON_ERROR = SKIP_FILE discards the entire file if any error exists, losing all valid rows in that file - far too aggressive.
• C - TO_NUMBER (without the TRY_ prefix) raises a hard error on invalid input; it has no DEFAULT option that silently substitutes a value.
• E - ON_ERROR = ABORT_STATEMENT halts the entire load on the first bad value, which is the opposite of tolerant loading.

Memory tip: Think of the TRY_ prefix in Snowflake as a "safe mode" for type conversion functions - TRY_TO_NUMBER, TRY_TO_DATE, etc. always return NULL on failure instead of crashing, making them the go-to choice whenever you need fault-tolerant casting inside a COPY INTO or SELECT.

No community discussion yet for this question.