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

Question

You are tasked with loading data from an external stage containing gzipped CSV files into a Snowflake table. The CSV files have a header row that you want to skip during the load process. Additionally, you need to transform the data during the load, specifically concatenating two columns (FIRST NAME and LAST NAME) into a new column called FULL NAME. Which combination of the following COPY INTO options will accomplish this task? Choose all that apply.

Options

• AFILE_FORMAT = (TYPE = CSV, SKIP_HEADER = 1 , COMPRESSION = 'GZIP')
• BTRANSFORM = (FULL_NAME = FIRST_NAME II ' ' II LAST_NAME)
• CON ERROR = 'SKIP FILE'
• DTRANSFORM AS SELECT $1, $2, $3 II ' II $4 FROM @mystage/data.csv
• EFILE FORMAT = (TYPE = CSV, SKIP_HEADER = 1, COMPRESSION = 'GZIP'), TRANSFORM

Explanation

Option A sets the FILE FORMAT correctly specifying the file type as CSV, skipping the header row (SKIP HEADER = 1), and specifying the compression type as GZIP. Option D correctly transforms the data by selecting all columns and concatenating the columns. This option assumes the file format is already defined in the COPY INTO statement. The @mystage/data.csv after FROM should only include name of the stage name and not the filename. However, that is not part of copy options but part of FROM Statement. The TRANSFORMS parameters that are part of copy options are deprecated.

No community discussion yet for this question.