Handling load errors in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding Snowflake Load Error Handling Behavior

When loading data into a Snowflake table using the COPY INTO command, what happens if some rows in the source file violate constraints or have invalid data?

Snowflake

COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV');

A Only the valid rows are loaded; invalid rows are skipped and recorded in an error file.

B Snowflake automatically fixes invalid rows and loads all data without errors.

C The entire load operation fails and no data is loaded into the table.

D The load operation partially loads data but stops immediately after the first error.

💡 Hint

Think about how Snowflake handles bad data rows during bulk loading.

Snowflake's COPY INTO command loads all valid rows and skips invalid rows, logging errors separately. This allows partial success without failing the entire load.

❓ Configuration

intermediate

Configuring Snowflake to Capture Load Errors

Which COPY INTO option should you use to specify where Snowflake writes details about rows that failed to load?

Snowflake

COPY INTO my_table FROM @my_stage/file.csv FILE_FORMAT = (TYPE = 'CSV') ???;

A ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE

B VALIDATION_MODE = 'RETURN_ERRORS'

C PURGE = TRUE

D ON_ERROR = 'CONTINUE'

💡 Hint

Look for the option that controls error reporting without stopping the load.

The VALIDATION_MODE = 'RETURN_ERRORS' option tells Snowflake to validate data and return errors without loading, useful for error inspection.

❓ Architecture

advanced

Designing a Robust Snowflake Data Load Pipeline with Error Handling

You want to build a data pipeline that loads CSV files into Snowflake tables. The pipeline must load all valid rows, capture invalid rows separately for review, and automatically retry loading after fixing errors. Which architecture best supports this?

A Use <code>COPY INTO</code> with <code>VALIDATION_MODE = 'RETURN_ERRORS'</code> to load data and automatically fix errors in place before retrying.

B Load data using <code>COPY INTO</code> with <code>ON_ERROR = 'SKIP_FILE'</code> to skip entire files with errors and log them for later manual processing.

C Load data using Snowpipe with <code>ON_ERROR = 'ABORT_STATEMENT'</code> to stop on errors and alert the team immediately.

D Use <code>COPY INTO</code> with <code>ON_ERROR = 'CONTINUE'</code> to load data, store error files in a separate stage, and trigger a manual review and reload process.

💡 Hint

Consider how to handle partial loads and error review in an automated pipeline.

Option D allows loading valid rows while capturing errors separately for review and manual or automated retry after fixes, supporting robustness.

❓ security

advanced

Securing Error Data Generated During Snowflake Loads

When Snowflake writes error files for failed data loads, what is the best practice to ensure sensitive data in those error files is protected?

A Delete error files immediately after load to avoid storing sensitive data anywhere.

B Store error files in a public stage so all users can access and fix errors quickly.

C Encrypt error files using Snowflake-managed keys and restrict stage access to authorized users only.

D Disable error file generation to prevent sensitive data from being written.

💡 Hint

Think about protecting sensitive data while still allowing error review.

Encrypting error files and restricting access ensures sensitive data is protected while enabling authorized users to review and fix errors.

✅ Best Practice

expert

Optimizing Snowflake Load Error Handling for Large Data Sets

You are loading very large CSV files into Snowflake. To minimize load failures and maximize throughput, which approach to error handling is best?

A Set <code>ON_ERROR = 'CONTINUE'</code> to load all valid rows and skip bad rows, then analyze error files asynchronously.

B Disable error logging to speed up loading and rely on downstream data quality checks.

C Set <code>ON_ERROR = 'ABORT_STATEMENT'</code> to stop loading immediately on first error to avoid partial data loads.

D Set <code>ON_ERROR = 'SKIP_FILE'</code> to skip entire files with any error, then manually fix and reload them later.

💡 Hint

Consider how to balance load speed and error visibility for large data.

Using ON_ERROR = 'CONTINUE' allows loading all good data quickly while capturing errors for later review, which is efficient for large datasets.

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