SOL-C01 Question #121: Real Exam Question with Answer & Explanation

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

Explanation

Using allows Snowflake to attempt to convert the date, and if it fails, it returns NULL. This allows the INSERT statement to complete without errors. The data can then be filtered using a WHERE clause to exclude these invalid dates. Option A involves a separate validation step, which adds complexity and doesn't directly handle the error during the INSERT operation. Option B skips the entire file, which is not desirable as only some rows are bad. Option D aborts the whole operation which we want to avoid. Option E works, but it doesn't use INSERT statement.

No community discussion yet for this question.