COPY INTO command in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What happens when you run this COPY INTO command in Snowflake?

Consider the following command that loads data from an external stage into a Snowflake table:

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

What is the expected behavior of this command?

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER = 1);

A It loads only the first CSV file from the stage into my_table, including the header line.

B It loads all CSV files from the stage into my_table, skipping the first line of each file as header.

C It loads all CSV files from the stage into my_table, including the header line of each file.

D It deletes all data from my_table before loading the CSV files.

💡 Hint

Think about what SKIP_HEADER = 1 does in the file format options.

The COPY INTO command loads all files from the specified stage. The FILE_FORMAT option with SKIP_HEADER = 1 tells Snowflake to skip the first line of each CSV file, which usually contains headers. It does not delete existing data unless specified with a TRUNCATE option.

❓ Configuration

intermediate

Which COPY INTO command option controls how errors are handled during loading?

You want to load data into a Snowflake table but want to limit the number of errors allowed before the load fails. Which option in the COPY INTO command controls this behavior?

A SKIP_ERRORS

B MAX_ERRORS

C ON_ERROR

D ERROR_LIMIT

💡 Hint

Look for the option that specifies what to do when errors occur.

The ON_ERROR option controls how Snowflake handles errors during loading. It can be set to CONTINUE, SKIP_FILE, SKIP_FILE_, or ABORT_STATEMENT to control error tolerance.

❓ Architecture

advanced

How does Snowflake handle parallel loading with COPY INTO from multiple files?

You have many files staged in an external location. When you run COPY INTO to load them into a table, how does Snowflake process these files?

A Snowflake loads multiple files in parallel to maximize throughput.

B Snowflake loads only the largest file and ignores the rest.

C Snowflake loads files sequentially, one after another, to maintain order.

D Snowflake merges all files into one before loading.

💡 Hint

Think about how cloud services optimize performance with many files.

Snowflake automatically loads multiple files in parallel when using COPY INTO, which speeds up data loading by using multiple compute resources.

❓ security

advanced

Which permission is required to run COPY INTO to load data into a table?

To successfully execute a COPY INTO command that loads data into a Snowflake table, which privilege must the user have?

A USAGE on the stage and INSERT on the target table

B SELECT on the target table and USAGE on the database

C OWNERSHIP on the stage only

D CREATE TABLE privilege

💡 Hint

Consider what is needed to read from the stage and write to the table.

To load data, the user needs USAGE privilege on the stage to read files and INSERT privilege on the target table to add data.

✅ Best Practice

expert

What is the best practice to ensure idempotent data loading with COPY INTO?

You want to run COPY INTO multiple times without duplicating data in your Snowflake table. Which approach best ensures idempotent loading?

A Load data directly into the target table multiple times.

B Run COPY INTO with TRUNCATE before loading to clear the table.

C Use ON_ERROR = CONTINUE to skip duplicates during load.

D Use a staging table and merge new data into the target table after loading.

💡 Hint

Think about how to avoid duplicates when loading data repeatedly.

Loading data into a staging table first allows you to merge only new or changed data into the target table, preventing duplicates and ensuring idempotency.

Practice

1. What is the main purpose of the COPY INTO command in Snowflake?

easy

A. To load data files from cloud storage into Snowflake tables

B. To export data from Snowflake tables to local files

C. To create new tables in Snowflake

D. To delete data from Snowflake tables

Solution

1. Step 1: Understand the command purpose

   The COPY INTO command is designed to load data from external files into Snowflake tables.

2. Step 2: Compare options

   Options B, C, and D describe exporting, creating, or deleting data, which are not the function of COPY INTO.

3. Final Answer:

   To load data files from cloud storage into Snowflake tables -> Option A

4. Quick Check:

   Loading data = COPY INTO [OK]

Hint: COPY INTO means loading data into tables [OK]

Common Mistakes:

• Confusing COPY INTO with export commands
• Thinking COPY INTO creates tables
• Assuming COPY INTO deletes data

2. Which of the following is the correct basic syntax to load data using COPY INTO in Snowflake?

easy

A. COPY table_name INTO @stage_name FILE_FORMAT = (TYPE = 'CSV');

B. LOAD DATA INTO table_name FROM @stage_name FORMAT = CSV;

C. COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'CSV');

D. INSERT INTO table_name COPY FROM @stage_name FILE_FORMAT = CSV;

Solution

1. Step 1: Recall correct COPY INTO syntax

   The correct syntax is COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'CSV'); to load CSV files from a stage.

2. Step 2: Identify incorrect syntax

   Options A, C, and D use wrong keywords or order that Snowflake does not accept.

3. Final Answer:

   COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'CSV'); -> Option C

4. Quick Check:

   Correct COPY INTO syntax = COPY INTO table_name FROM @stage_name FILE_FORMAT = (TYPE = 'CSV'); [OK]

Hint: COPY INTO table FROM stage with FILE_FORMAT [OK]

Common Mistakes:

• Swapping INTO and FROM keywords
• Using LOAD DATA instead of COPY INTO
• Incorrect FILE_FORMAT syntax

3. Given the command:

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',') ON_ERROR = 'skip_file';

What happens if one file in the stage has errors during loading?

medium

A. Snowflake retries loading the file indefinitely

B. The entire load fails and no data is loaded

C. Only the erroneous rows are skipped, file loads partially

D. The file with errors is skipped, other files load successfully

Solution

1. Step 1: Understand ON_ERROR = 'skip_file'

   This option tells Snowflake to skip any file that causes errors during loading instead of failing the entire load.

2. Step 2: Analyze the effect on other files

   Other files without errors will load normally, so only the bad file is skipped.

3. Final Answer:

   The file with errors is skipped, other files load successfully -> Option D

4. Quick Check:

   ON_ERROR = skip_file skips bad files [OK]

Hint: ON_ERROR = skip_file skips bad files, loads others [OK]

Common Mistakes:

• Assuming entire load fails on one bad file
• Thinking only bad rows are skipped with skip_file
• Believing Snowflake retries endlessly

4. You run this command:

COPY INTO sales FROM @data_stage FILE_FORMAT = (FORMAT_NAME = 'csv_format');

But get an error: SQL compilation error: file format not found. What is the likely cause?

medium

A. The stage @data_stage does not exist

B. The FILE_FORMAT clause references a named file format that does not exist

C. The sales table does not exist

D. The CSV files are empty

Solution

1. Step 1: Analyze the error message

   The error says file format not found, meaning Snowflake expects a named file format or a full definition.

2. Step 2: Check the FILE_FORMAT clause

   The clause FILE_FORMAT = (FORMAT_NAME = 'csv_format') references a named file format that does not exist.

3. Final Answer:

   The FILE_FORMAT clause references a named file format that does not exist -> Option B

4. Quick Check:

   File format error means missing or wrong FILE_FORMAT [OK]

Hint: File format errors mean missing or wrong FILE_FORMAT [OK]

Common Mistakes:

• Assuming stage or table missing causes file format error
• Ignoring need for file format definition
• Thinking empty files cause this error

5. You want to load JSON files from an external S3 bucket into a Snowflake table named events. Which COPY INTO command correctly handles JSON format and ignores files with errors?

hard

A. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'skip_file';

B. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'continue';

C. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'abort_statement';

D. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'XML') ON_ERROR = 'skip_file';

Solution

1. Step 1: Match file format to JSON files

   Since files are JSON, the FILE_FORMAT must specify TYPE = 'JSON'.

2. Step 2: Choose error handling to ignore bad files

   ON_ERROR = 'skip_file' skips files with errors, which matches the requirement.

3. Step 3: Eliminate incorrect options

   COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'continue'; uses CSV format, wrong for JSON. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'abort_statement'; aborts on error, not ignoring. COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'XML') ON_ERROR = 'skip_file'; uses XML format, incorrect.

4. Final Answer:

   COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'skip_file'; -> Option A

5. Quick Check:

   JSON format + skip_file error handling = COPY INTO events FROM @s3_stage FILE_FORMAT = (TYPE = 'JSON') ON_ERROR = 'skip_file'; [OK]

Hint: Match FILE_FORMAT type to file type and use skip_file to ignore errors [OK]

Common Mistakes:

• Using wrong file format type
• Choosing abort instead of skip_file for errors
• Confusing JSON with CSV or XML