nerdexam
Snowflake

SOL-C01 · Question #121

You are tasked with loading data into a Snowflake table 'SALES DATA' that contains columns 'SALE ID (NUMBER)' , 'PRODUCT ID (NUMBERY, 'SALE DATE (DATE), and 'SALE AMOUNT (NUMBER)'. You have a CSV file

The correct answer is C. Use in the INSERT statement. Rows with invalid date formats will result in NULL values for the. Using TRY_TO_DATE() (or TRY_CAST) in the INSERT statement is the correct approach because it converts values to the target date type where possible and returns NULL for any values that fail conversion - allowing the rest of the row to load successfully without halting the entire

Data Loading and Unloading

Question

You are tasked with loading data into a Snowflake table 'SALES DATA' that contains columns 'SALE ID (NUMBER)' , 'PRODUCT ID (NUMBERY, 'SALE DATE (DATE), and 'SALE AMOUNT (NUMBER)'. You have a CSV file with the same fields. However, some rows in the CSV file contain invalid date formats (e.g., '2024/01/01' instead of 'YYYY-MM-DD'). You want to handle these errors during the data loading process without aborting the entire load operation. Which of the following options BEST describes how to achieve this using the 'INSERT command with data from a stage?

Options

  • AUse before the insert statement to identify invalid rows, then filter those rows out using a WHERE
  • BCreate a file format with `ON ERROR = 'SKIP_FILE", and Snowflake will automatically skip the file
  • CUse in the INSERT statement. Rows with invalid date formats will result in NULL values for the
  • DUse the function with a custom date format. If TO_DATE fails, it will throw an error and abort the
  • ECreate an error table and specify 'ON_ERROR = 'CONTINUE" file format option, then use

How the community answered

(32 responses)
  • A
    9% (3)
  • B
    3% (1)
  • C
    66% (21)
  • D
    3% (1)
  • E
    19% (6)

Explanation

Using TRY_TO_DATE() (or TRY_CAST) in the INSERT statement is the correct approach because it converts values to the target date type where possible and returns NULL for any values that fail conversion - allowing the rest of the row to load successfully without halting the entire operation. This gives you per-row error tolerance rather than all-or-nothing behavior.

Why the distractors fail:

  • A is vague and incomplete - there's no standard pre-INSERT identification mechanism described, and filtering before load doesn't address in-flight error handling during the INSERT itself.
  • B is wrong because ON_ERROR = 'SKIP_FILE' skips the entire file when any error is encountered, not individual rows - the opposite of what's needed.
  • D is wrong because plain TO_DATE() with a custom format still throws an error and aborts on invalid values; it has no tolerance for bad data.
  • E conflates two concepts incorrectly - ON_ERROR = 'CONTINUE' is a COPY INTO option (not INSERT), and there's no built-in mechanism to route bad rows to an error table via a file format option alone.

Memory tip: Think of the TRY_ prefix as a "safe mode" for conversion functions in Snowflake - TRY_TO_DATE, TRY_TO_NUMBER, TRY_CAST all follow the same pattern: fail gracefully with NULL instead of raising an exception, making them your go-to for dirty data during loads.

Topics

#Data Loading#Error Handling#Date Format Conversion#NULL Values

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full SOL-C01 Practice