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

Question

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. However, some rows in the CSV file contain invalid price values (e.g., non- numeric characters, empty strings). You want to ensure that only valid numeric price values are loaded into the 'PRODUCT DATA' table, and invalid values should be replaced with a default value of 0. Which of the following combinations of options within the 'COPY INTO' statement, including functions and file format parameters, would BEST achieve this data cleansing and loading requirement?

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

Explanation

Option D provides the most robust and efficient solution. attempts to convert the `PRICE to a number and returns NULL if the conversion fails. 'COALESCE then replaces the NULL values with O, ensuring that the table always contains valid numeric prices, or defaults to 0 for invalid entries. Using "VALIDATE (Option A) requires manual intervention. 'ON_ERROR = SKIP_FILE (Option B) skips the entire file if a single error exists. Option E is similar, but aborts the statement. Option C is viable however lacks handling of NULL values.

No community discussion yet for this question.