Handling load errors in Snowflake - Mini Project: Build & Apply

Handling Load Errors in Snowflake

📖 Scenario: You are working with Snowflake to load data from a CSV file into a table. Sometimes, the data has errors like wrong data types or missing values. You want to handle these load errors gracefully so you can see which rows failed and why.

🎯 Goal: Build a Snowflake data loading process that captures load errors into an error table for review.

📋 What You'll Learn

Create a target table called customers with columns id (integer), name (string), and age (integer).

Create a stage called my_stage to hold the CSV file.

Create an error table called load_errors to capture load errors.

Write a COPY INTO command to load data from @my_stage/customers.csv into customers with error handling that stores errors in load_errors.

💡 Why This Matters

🌍 Real World

In real projects, data often has mistakes. Handling load errors helps keep your data clean and lets you fix problems quickly.

💼 Career

Knowing how to handle load errors in Snowflake is important for data engineers and cloud architects to ensure reliable data pipelines.

1

Create the customers table

Create a table called customers with columns id as INTEGER, name as VARCHAR(100), and age as INTEGER.

Snowflake

# Create the customers table
# Your code here

Need a hint?

Use CREATE OR REPLACE TABLE customers and define the columns with their types.

2

Create the stage my_stage

Create a named internal stage called my_stage to hold your CSV files.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    age INTEGER
);

# Create the internal stage my_stage
# Your code here

Need a hint?

Use CREATE OR REPLACE STAGE my_stage with a CSV file format.

3

Create the load_errors table

Create a table called load_errors with columns error_line as VARIANT and error_message as STRING to store load error details.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    age INTEGER
);

CREATE OR REPLACE STAGE my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

# Create the load_errors table
# Your code here

Need a hint?

Use CREATE OR REPLACE TABLE load_errors with columns to hold error data.

4

Load data with error handling

Write a COPY INTO customers command to load data from @my_stage/customers.csv. Use the ON_ERROR = 'CONTINUE' option and specify LOAD_ERRORS = 'load_errors' to capture errors in the load_errors table.

Snowflake

CREATE OR REPLACE TABLE customers (
    id INTEGER,
    name VARCHAR(100),
    age INTEGER
);

CREATE OR REPLACE STAGE my_stage FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

CREATE OR REPLACE TABLE load_errors (
    error_line VARIANT,
    error_message STRING
);

# Write COPY INTO command with error handling
# Your code here

Need a hint?

Use COPY INTO customers FROM @my_stage/customers.csv with ON_ERROR = 'CONTINUE' and LOAD_ERRORS = 'load_errors'.