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.