CloudGuild · Blog · Cheat sheets · Lessons · Certifications
Understanding Data Transformation in Snowflake: COF-C02 Exam Question Walkthrough
Master the SnowPro Core Certification exam with this detailed walkthrough of a data transformation question. Learn why option A is the right choice.
A common decision point in the SnowPro Core Certification exam is how to effectively load and transform JSON data from a Snowflake stage into a structured table. Candidates often second-guess themselves, especially when multiple SQL commands seem plausible. This question tests your understanding of the correct command for data loading and transformation.
The question
A data engineer is tasked with transforming raw JSON data stored in a Snowflake stage into a structured table format. Which of the following SQL commands should they use to effectively load the data into a table while transforming it?
A. COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'JSON')
B. INSERT INTO table_name SELECT * FROM @stage_name FILE_FORMAT = (TYPE = 'JSON')
C. CREATE TABLE table_name AS SELECT * FROM @stage_name FILE_FORMAT = (TYPE = 'JSON')
D. SELECT * FROM @stage_name INTO table_name FILE_FORMAT = (TYPE = 'JSON')
Think before you scroll
When approaching this question, consider the specific functions of each SQL command in relation to Snowflake's capabilities. Focus on which command is designed for loading data from an external stage and allows for transformation through file formats.
The answer
The correct option is A: COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'JSON'). This command is tailored for loading data from external stages into tables, and it accommodates JSON data transformation.
Why the other options lose
B: INSERT INTO table_name SELECT * FROM @stage_name FILE_FORMAT = (TYPE = 'JSON') is incorrect because the INSERT INTO command does not support loading directly from stages. It requires existing data to insert into a table, but does not handle the stage-to-table load process.
C: CREATE TABLE table_name AS SELECT * FROM @stage_name FILE_FORMAT = (TYPE = 'JSON') misuses the CREATE TABLE AS SELECT syntax. This command is not valid for loading from external stages directly and is instead meant for creating tables from existing data already loaded in Snowflake.
D: SELECT * FROM @stage_name INTO table_name FILE_FORMAT = (TYPE = 'JSON') is incorrect due to the invalid syntax. SELECT INTO is not recognized in Snowflake for loading data from external stages; it is typically used for creating new tables from existing data.
The concept behind it
The principle here is understanding how Snowflake handles data loading and transformation. The COPY INTO command is specifically designed for this purpose, allowing for effective transitions from raw formats like JSON to structured tables. Always remember that loading data from external stages requires commands that directly support that functionality.
Exam trap to remember
Remember, when loading data from external stages, always opt for COPY INTO when you're transforming data formats. This rule can save you from making common mistakes in the exam.