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

Question

You are loading data from a CSV file stored in an Amazon S3 bucket into a Snowflake table named `CUSTOMER DATA. The CSV file contains a header row, and the data is comma- separated. The 'CUSTOMER DATA table has columns `customer_id', 'first_name', and 'email'. You want to use a named file format object called `CSV FORMAT which you have already created. You also want to skip the header row and only load data where the column is not null. Which of the following `COPY INTO' statement achieves this most efficiently and correctly? A. B. C. D. E.

Options

• ACOPY INTO CUSTOMER_DATA FROM @my_stage/customer.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FORMAT') ON_ERROR = 'SKIP_FILE';
• BCOPY INTO CUSTOMER_DATA FROM @my_stage/customer.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FORMAT') SKIP_HEADER = 1 WHERE email IS NOT NULL;
• CCOPY INTO CUSTOMER_DATA FROM @my_stage/customer.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FORMAT') SKIP_HEADER = 1;
• DCOPY INTO CUSTOMER_DATA FROM @my_stage/customer.csv FILE_FORMAT = (FORMAT_NAME = 'CSV_FORMAT') PURGE = TRUE WHERE email IS NOT NULL;

Explanation

Option D is the most correct because it efficiently copies the data from the stage, using a named file format, skips the header and sets error handling. The WHERE clause to filter null emails cannot be directly used within COPY INTO. Options A, B and C, is incorrect, as 'WHERE clause and 'SKIP_HEADER = 1' is not applicable as the way written in the option. E is incorrect as its using select statement and it cannot be used in 'COPY INTO'

No community discussion yet for this question.