Handling load errors in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the error file location in the COPY INTO command.

Snowflake

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

Drag options to blanks, or click blank then click option'

A'@my_stage/data_files/'

B'@my_stage/error_files/'

C'@my_stage/load_files/'

D'@my_stage/archive/'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the data files folder instead of the error files folder.

Omitting quotes around the path.

Specifying a non-existent folder.

The ERROR_FILE parameter should point to the location where error files are stored, typically a dedicated error folder in the stage.

2fill in blank

medium

Complete the code to set the ON_ERROR behavior to continue loading despite errors.

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = '[1]';

Drag options to blanks, or click blank then click option'

Acontinue

Babort_statement

Cskip_file

Dskip_file_num

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'abort_statement' which stops the load on error.

Confusing 'skip_file' with 'continue'.

Using an invalid ON_ERROR value.

The ON_ERROR = 'continue' option tells Snowflake to continue loading data even if some rows cause errors.

3fill in blank

hard

Fix the error in the COPY INTO command to properly handle load errors by skipping bad rows.

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = '[1]';

Drag options to blanks, or click blank then click option'

Askip_row

Bskip_file

Ccontinue

Dskip_file_num

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'skip_file' which skips entire files instead of rows.

Using 'continue' which ignores errors but does not limit skipped rows.

Using a non-existent option like 'skip_row'.

The skip_file_num option allows skipping a specified number of bad rows before aborting the load.

4fill in blank

hard

Fill both blanks to configure the COPY INTO command to log errors and skip the first 5 bad rows.

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = '[1]' ERROR_LIMIT = [2];

Drag options to blanks, or click blank then click option'

Askip_file_num

B10

C5

Dcontinue

Attempts:

3 left

💡 Hint

Common Mistakes

Setting ERROR_LIMIT to a string instead of a number.

Using 'continue' instead of 'skip_file_num'.

Confusing ERROR_LIMIT with a file path.

Using ON_ERROR = 'skip_file_num' with ERROR_LIMIT = 5 skips the first 5 bad rows before aborting.

5fill in blank

hard

Fill all three blanks to create a COPY INTO command that skips bad rows, logs errors to a specific path, and limits errors to 3.

Snowflake

COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV') ON_ERROR = '[1]' ERROR_FILE = '[2]' ERROR_LIMIT = [3];

Drag options to blanks, or click blank then click option'

Askip_file_num

B'@my_stage/error_logs/'

C3

Dcontinue

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'continue' instead of 'skip_file_num'.

Not quoting the ERROR_FILE path.

Setting ERROR_LIMIT to a string instead of a number.

This configuration skips bad rows up to 3 errors, logs errors to '@my_stage/error_logs/', and stops after the limit.

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