Handling load errors in Snowflake - Step-by-Step Execution

Process Flow - Handling load errors

Start Load Data

↓

Attempt Data Load

↓

Error Occurs?

No→Load Success

Yes↓

Capture Error Details

↓

Log or Store Errors

↓

Decide: Retry or Skip

↓

Retry Load

↓

Load Success

↓

Skip Error Rows

↓

End Load Process

This flow shows how Snowflake handles data load errors by detecting errors, capturing details, logging them, and deciding whether to retry or skip problematic rows.

Execution Sample

Snowflake

COPY INTO my_table
FROM @my_stage/file.csv
ON_ERROR = 'CONTINUE';

This command loads data from a staged file into a table, continuing the load even if some rows cause errors.

Process Table

Step	Action	Error Detected	Error Details Captured	Rows Loaded	Rows Skipped
1	Start data load	No	N/A	0	0
2	Load row 1	No	N/A	1	0
3	Load row 2	Yes	Invalid date format	1	1
4	Load row 3	No	N/A	2	1
5	Load row 4	Yes	Null value in NOT NULL column	2	2
6	Load row 5	No	N/A	3	2
7	End load process	N/A	N/A	3	2

💡 All rows processed; load ended with 3 rows loaded and 2 rows skipped due to errors.

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
Rows Loaded	0	1	1	2	2	3	3
Rows Skipped	0	0	1	1	2	2	2
Error Details	[]	[]	["Invalid date format"]	["Invalid date format"]	["Invalid date format", "Null value in NOT NULL column"]	["Invalid date format", "Null value in NOT NULL column"]	["Invalid date format", "Null value in NOT NULL column"]

Key Moments - 3 Insights

Why does the load continue even after errors occur?

Because the ON_ERROR='CONTINUE' option tells Snowflake to skip error rows and keep loading the rest, as shown in steps 3 and 5 where errors are detected but loading proceeds.

How are error details tracked during the load?

Error details are captured and stored in a list each time an error occurs, as seen in the Error Details variable in steps 3 and 5 in the execution_table.

What happens to rows that cause errors during loading?

Rows with errors are skipped and not loaded into the table, increasing the Rows Skipped count, as shown in steps 3 and 5.

Visual Quiz - 3 Questions

Look at the execution table, how many rows were successfully loaded after step 4?

A 2

B 3

C 1

D 4

💡 Hint

Check the 'Rows Loaded' column at step 4 in the execution_table.

At which step does the first error occur during the load?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the first 'Yes' in the 'Error Detected' column in the execution_table.

If ON_ERROR was set to 'ABORT_STATEMENT', how would the execution_table change?

A Load would stop at first error, fewer rows loaded

B All rows would load regardless of errors

C Errors would be ignored and logged but load continues

D Rows with errors would be retried automatically

💡 Hint

Consider how 'CONTINUE' allows skipping errors; 'ABORT_STATEMENT' stops on error.

Concept Snapshot

Snowflake load errors are handled by options like ON_ERROR.
ON_ERROR='CONTINUE' skips bad rows and logs errors.
Errors are captured during load and rows skipped.
Load continues until all rows processed or aborted.
Use error handling to keep data load smooth and controlled.

Full Transcript

This visual execution shows how Snowflake handles load errors during data import. The process starts by attempting to load each row. When an error occurs, such as invalid data format or null in a required column, Snowflake captures the error details and skips the problematic row if ON_ERROR='CONTINUE' is set. The load continues with the next rows, accumulating counts of loaded and skipped rows. This approach allows partial data loading without stopping the entire process. The execution table tracks each step, showing when errors happen, how many rows load successfully, and how many are skipped. Variables like Rows Loaded, Rows Skipped, and Error Details update after each step. Key moments clarify why loading continues despite errors and how errors are tracked. The quiz tests understanding of when errors occur, how many rows load, and the effect of different ON_ERROR settings. This helps beginners see step-by-step how Snowflake manages load errors to keep data pipelines running smoothly.

Practice

1. What does the ON_ERROR option do when loading data into Snowflake?

easy

A. It controls how Snowflake handles errors during data loading.

B. It encrypts the data before loading.

C. It speeds up the data loading process.

D. It automatically deletes duplicate rows.

Solution

1. Step 1: Understand the purpose of ON_ERROR

   The ON_ERROR option is used to specify how Snowflake should behave when it encounters errors during data loading.

2. Step 2: Identify the correct behavior

   It can skip bad rows, skip files, or stop the load depending on the setting, thus controlling error handling.

3. Final Answer:

   It controls how Snowflake handles errors during data loading. -> Option A

4. Quick Check:

   ON_ERROR controls error handling [OK]

Hint: ON_ERROR sets error handling behavior during load [OK]

Common Mistakes:

• Confusing ON_ERROR with encryption settings
• Thinking ON_ERROR speeds up loading
• Assuming ON_ERROR deletes duplicates

2. Which of the following is the correct syntax to skip bad rows during a Snowflake COPY INTO command?

easy

A. COPY INTO table_name FROM @stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'ignore_error';

B. COPY INTO table_name FROM @stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'skip_file';

C. COPY INTO table_name FROM @stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'abort_load';

D. COPY INTO table_name FROM @stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = 'skip_row';

Solution

1. Step 1: Recall valid ON_ERROR options

   Snowflake supports options like 'skip_file', 'skip_row', and 'abort_load' for ON_ERROR.

2. Step 2: Identify option to skip bad rows

   'skip_row' tells Snowflake to skip only the bad rows, not the entire file.

3. Final Answer:

   ON_ERROR = 'skip_row' -> Option D

4. Quick Check:

   Skip bad rows = skip_row [OK]

Hint: Use 'skip_row' to skip bad rows in ON_ERROR [OK]

Common Mistakes:

• Using 'skip_file' to skip rows instead of files
• Using invalid ON_ERROR values like 'ignore_error'
• Confusing 'abort_load' with skipping errors

3. Given this COPY command:

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

If one file has 5 bad rows, what happens?

medium

A. The entire file with bad rows is skipped, other files load normally.

B. Only the 5 bad rows are skipped, rest of the file loads.

C. The load stops immediately with an error.

D. All files are skipped regardless of errors.

Solution

1. Step 1: Understand ON_ERROR = 'skip_file'

   This option skips the entire file if any error occurs in it.

2. Step 2: Apply to scenario

   Since one file has 5 bad rows, Snowflake skips that whole file but continues loading other files.

3. Final Answer:

   The entire file with bad rows is skipped, other files load normally. -> Option A

4. Quick Check:

   skip_file skips whole file on error [OK]

Hint: 'skip_file' skips whole file if any error found [OK]

Common Mistakes:

• Thinking only bad rows are skipped with 'skip_file'
• Assuming load stops on first error
• Believing all files skip on one bad file

4. You run a COPY INTO command with ON_ERROR = 'skip_row' but still see the load failing. What is a likely cause?

medium

A. ON_ERROR only works for JSON files, not CSV.

B. The file format is incorrect causing parsing errors.

C. You must set ON_ERROR to 'skip_file' to avoid failures.

D. The target table does not exist.

Solution

1. Step 1: Understand ON_ERROR limitations

   ON_ERROR skips bad rows but cannot fix fundamental file format or parsing errors.

2. Step 2: Identify cause of failure

   If file format is wrong, Snowflake cannot parse data, causing load failure despite ON_ERROR.

3. Final Answer:

   The file format is incorrect causing parsing errors. -> Option B

4. Quick Check:

   Wrong file format causes failure despite ON_ERROR [OK]

Hint: Check file format if ON_ERROR skip_row still fails [OK]

Common Mistakes:

• Thinking ON_ERROR fixes all errors
• Believing ON_ERROR only works for JSON
• Ignoring table existence errors

5. You want to load multiple CSV files but skip any file with more than 10 bad rows, while loading others fully. Which ON_ERROR setting should you use?

hard

A. ON_ERROR = 'continue' with MAX_ERROR = 10

B. ON_ERROR = 'skip_row' with MAX_ERROR = 10

C. ON_ERROR = 'skip_file' with MAX_ERROR = 10

D. ON_ERROR = 'abort_load' with MAX_ERROR = 10

Solution

1. Step 1: Understand requirement

   Skip entire files only if bad rows exceed 10, otherwise load fully.

2. Step 2: Match ON_ERROR and MAX_ERROR

   Using ON_ERROR = 'skip_file' with MAX_ERROR = 10 skips files exceeding 10 errors, loads others fully.

3. Final Answer:

   ON_ERROR = 'skip_file' with MAX_ERROR = 10 -> Option C

4. Quick Check:

   Skip files over 10 errors = skip_file + MAX_ERROR [OK]

Hint: Use skip_file with MAX_ERROR to limit bad rows per file [OK]

Common Mistakes:

• Using skip_row which skips rows, not files
• Assuming 'continue' skips files
• Thinking abort_load allows skipping