COPY INTO command in Snowflake - Step-by-Step Execution

Process Flow - COPY INTO command

Start COPY INTO command

↓

Connect to source data

↓

Read data files

↓

Parse and transform data

↓

Load data into target table

↓

Handle errors or skip bad records

↓

Finish and report status

The COPY INTO command reads data files from a source, parses them, loads the data into a target table, and handles errors before finishing.

Execution Sample

Snowflake

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

This command copies CSV data from a stage into a table, skipping the header and continuing on errors.

Process Table

Step	Action	Details	Result
1	Start COPY INTO	Target: my_table, Source: @my_stage/data/	Ready to load data
2	Connect to source	Access files in @my_stage/data/	Files located
3	Read data files	Read CSV files, skip first line	Data rows ready
4	Parse data	Split fields by comma	Parsed rows
5	Load data	Insert rows into my_table	Rows inserted
6	Handle errors	On error: CONTINUE	Bad rows skipped, process continues
7	Finish	Report number of rows loaded and skipped	Load complete

💡 All files processed; errors handled by skipping bad rows

Status Tracker

Variable	Start	After Step 3	After Step 5	Final
Files to load	List of files in stage	Files read	Files loaded	All files processed
Rows read	0	Data rows read	Rows inserted	Total rows loaded
Errors	0	Errors found	Errors skipped	Total errors skipped

Key Moments - 3 Insights

Why does the COPY INTO command skip the first line of the CSV?

Because the FILE_FORMAT option SKIP_HEADER=1 tells Snowflake to ignore the header row, as shown in step 3 of the execution_table.

What happens when a bad row is encountered during loading?

The ON_ERROR='CONTINUE' option causes the command to skip the bad row and continue loading, as explained in step 6 of the execution_table.

How does Snowflake know where to find the data files?

The FROM clause specifies the stage location (@my_stage/data/), which Snowflake accesses in step 2 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does Snowflake parse the CSV data?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Refer to the 'Parse data' action in step 4 of the execution_table.

According to variable_tracker, what happens to the 'Errors' variable after step 6?

A It increases and bad rows are skipped

B It resets to zero

C It increases and stops the load

D It decreases

💡 Hint

Check the 'Errors' row in variable_tracker after step 6 and the 'Handle errors' action in execution_table.

If we remove SKIP_HEADER=1 from FILE_FORMAT, what changes in the execution?

A No data will be loaded

B The command will fail immediately

C The header row will be loaded as data

D Errors will be ignored

💡 Hint

Consider step 3 in execution_table where SKIP_HEADER=1 causes the first line to be skipped.

Concept Snapshot

COPY INTO command loads data files into a Snowflake table.
Specify source location with FROM and file format with FILE_FORMAT.
Use ON_ERROR to control error handling (e.g., CONTINUE to skip bad rows).
SKIP_HEADER option skips header lines in files.
The command reads, parses, loads data, and reports results.

Full Transcript

The COPY INTO command in Snowflake loads data from files into a table. It starts by connecting to the source location, reading the files, parsing the data according to the file format, and then loading the data into the target table. If errors occur, the command can skip bad rows and continue loading based on the ON_ERROR setting. The process finishes by reporting how many rows were loaded and how many were skipped. For example, using SKIP_HEADER=1 skips the first line of CSV files, which is usually the header. This step-by-step flow helps beginners understand how data moves from files into Snowflake tables safely and efficiently.

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