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.