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

Question

You have a large CSV file stored in AWS S3 that you need to load into a Snowflake table called 'SALES DATA'. The CSV file is compressed using GZIP, and it contains a header row that you want to skip. The table 'SALES DATA' already exists with the correct schema. You want to optimize the data loading process by using a named file format object and explicitly specifying the virtual warehouse. The S3 bucket is properly configured for Snowflake access. Which of the following SQL statements is the MOST efficient and correct way to load the data? A. B. C. D. E.

Options

• ACOPY INTO 'SALES DATA' FROM @my_s3_stage/large_file.csv.gz FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 COMPRESSION = GZIP) WAREHOUSE = my_warehouse;
• BCOPY INTO 'SALES DATA' FROM @my_s3_stage/large_file.csv.gz FILE_FORMAT = my_csv_format WAREHOUSE = my_warehouse;
• CCOPY INTO SALES_DATA FROM @my_s3_stage/large_file.csv.gz FILE_FORMAT = my_csv_format;
• DCOPY INTO SALES_DATA FROM @my_s3_stage/large_file.csv.gz FILE_FORMAT = my_csv_format WAREHOUSE = my_warehouse;

Explanation

Option E is the most correct and efficient. First, it creates the named file format to encapsulate the CSV- specific settings (compression, header skipping). This promotes reusability and readability. Then, the COPY INTO statement uses this named format, skips files on error, and specifies the 'WAREHOUSE for explicit control, improving performance. Option A doesn't use a named file format or specify a warehouse. Option B is missing the creation of the file format object and the skip header parameter, will throw an error. Option C uses 'COMPRESSION = AUTO' , but explicitly stating the compression type (GZIP) is better for performance. Option D does not create file format and so the query would fail.

No community discussion yet for this question.