Monitoring and Error Handling
Data Loading Monitoring and Error Handling in Snowflakeβ
π― Why Monitoring Mattersβ
-
Data Quality = Trust β bad data leads to wrong insights and costly mistakes
-
Cost Control β failed loads waste compute resources and increase cloud bills
-
Compliance β audit trails show data integrity for regulatory requirements
-
Operational Reliability β automation avoids manual firefighting and downtime
-
Faster issue resolution β structured monitoring helps teams find problems faster
-
Actionable visibility β know which loads failed, why, and when
-
β Key Benefits:
- Check data health before it reaches production
- Validate file loads and catch issues early
- Reduce downstream impact on models and analytics
1. Use Staging Tables for Safe Validationβ
-
β Staging tables act as a safety buffer:
- Load data here first for testing
- Validate and clean before moving to production tables
- Protect your main tables from bad data
Example with our employee file:
-- Create staging table for employee data
CREATE OR REPLACE TABLE staging_employee_data (
emp_id INTEGER,
first_name STRING,
last_name STRING,
department STRING,
hire_date DATE
);
-- Load to staging with error handling
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
ON_ERROR = 'CONTINUE'; -- Skip bad rows but keep good data
2. Validation Checks Before Productionβ
- β Check for NULLs and data quality issues:
-- Validate critical fields in staging
SELECT COUNT(*) as total_rows,
COUNT(CASE WHEN emp_id IS NULL THEN 1 END) as missing_emp_ids,
COUNT(CASE WHEN first_name IS NULL THEN 1 END) as missing_names,
COUNT(CASE WHEN hire_date IS NULL THEN 1 END) as missing_hire_dates
FROM staging_employee_data;
- β
Use
VALIDATION_MODEfor Previews Without Loading:
-- Check for errors without actually loading data
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
VALIDATION_MODE = 'RETURN_ERRORS';
π Sample Bad CSV File (si_south_indian_employees.csv):
emp_id,first_name,last_name,department,hire_date
1,Alice,Johnson,Engineering,2024-01-15
2,Bob,abc,Sales,2024-02-01 -- β Invalid last_name (should be string)
3,,Smith,Marketing,2024-03-10 -- β NULL first_name
4,Charlie,Brown,HR,not-a-date -- β Invalid hire_date format
5,Diana,Wilson,Finance,2024-04-20
π Sample Output from VALIDATION_MODE:
| Row Number | Column Name | Error |
|---|---|---|
| 2 | last_name | Expected string but got 'abc' |
| 3 | first_name | NULL value in required field |
| 4 | hire_date | Date format error: 'not-a-date' |
- β
Validation Options:
VALIDATION_MODE = 'RETURN_ERRORS'β Show first error foundVALIDATION_MODE = 'RETURN_ALL_ERRORS'β Show up to 1000 errorsVALIDATION_MODE = 'RETURN_1_ROW'β Validate format with just first row
3. Common Error Scenariosβ
Scenario 1: Malformed CSV Dataβ
Context: A source file contains bad rows, invalid types, or broken dates.
- Example file:
s3://beems-feb-2026-new/si_south_indian_employees.csv - Common issues: missing fields, invalid dates, or unexpected delimiters
- Why it matters: bad rows can fail loads or result in partial data
-- Load with error handling - skip bad rows but continue
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
ON_ERROR = 'CONTINUE';
Note: Use
FORCE = TRUEonly when you intentionally want to reprocess a corrected file.
Scenario 2: Permission Issuesβ
- Problem: IAM role lacks S3 access or storage integration permissions are incorrect.
- Symptoms:
Access Denied,File not found, or authorization failures. - Check: IAM trust policy, S3 bucket policy, and Snowflake storage integration configuration.
Scenario 3: Duplicate Employee Recordsβ
- Problem: The source file is reprocessed and the same business keys already exist in the target.
- Why Snowflake usually avoids this: Snowflake avoids reloading the same file twice by default.
- Recommended pattern: Use
MERGEfor idempotent upsert behavior.
MERGE INTO employee_data target
USING staging_employee_data source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
UPDATE SET
target.first_name = source.first_name,
target.last_name = source.last_name,
target.department = source.department,
target.hire_date = source.hire_date
WHEN NOT MATCHED THEN
INSERT (emp_id, first_name, last_name, department, hire_date)
VALUES (source.emp_id, source.first_name, source.last_name, source.department, source.hire_date);
4. Comprehensive Monitoring Dashboardβ
Daily Load Health Checkβ
-- Key metrics for employee data loads over last 7 days
SELECT
DATE_TRUNC('day', LAST_LOAD_TIME) as load_date,
COUNT(*) as total_loads,
SUM(ROWS_LOADED) as total_employees_loaded,
AVG(ROWS_LOADED) as avg_employees_per_load,
SUM(CASE WHEN STATUS = 'SUCCESS' THEN 1 ELSE 0 END) as successful_loads,
SUM(CASE WHEN STATUS = 'FAILED' THEN 1 ELSE 0 END) as failed_loads,
SUM(CASE WHEN STATUS = 'PARTIALLY_LOADED' THEN 1 ELSE 0 END) as partial_loads
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(TABLE_NAME=>'EMPLOYEE_DATA'))
WHERE LAST_LOAD_TIME >= DATEADD(days, -7, CURRENT_TIMESTAMP())
GROUP BY load_date
ORDER BY load_date DESC;
Cost Monitoring for Data Loadingβ
-- Track loading costs by day to understand resource usage
SELECT
DATE_TRUNC('day', START_TIME) as load_date,
SUM(CREDITS_USED) as total_credits_for_loads,
COUNT(*) as load_operations,
ROUND(AVG(CREDITS_USED), 4) as avg_credits_per_load
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TYPE = 'COPY'
AND START_TIME >= DATEADD(days, -30, CURRENT_TIMESTAMP())
AND QUERY_TEXT ILIKE '%employee_data%'
GROUP BY load_date
ORDER BY load_date DESC;
Data Quality Metricsβ
-- Check for data quality issues in loaded data
SELECT
COUNT(*) as total_employees,
COUNT(DISTINCT emp_id) as unique_employee_ids,
COUNT(CASE WHEN first_name IS NULL THEN 1 END) as missing_first_names,
COUNT(CASE WHEN last_name IS NULL THEN 1 END) as missing_last_names,
COUNT(CASE WHEN hire_date IS NULL THEN 1 END) as missing_hire_dates,
COUNT(CASE WHEN hire_date > CURRENT_DATE THEN 1 END) as future_hire_dates,
ROUND(AVG(DATEDIFF('year', hire_date, CURRENT_DATE)), 1) as avg_tenure_years
FROM employee_data
WHERE hire_date <= CURRENT_DATE; -- Exclude future dates for accurate metrics
5. Troubleshooting Checklistβ
This section exists to give every data engineer a clear, repeatable process when load issues happen.
When a load fails or produces unexpected resultsβ
- Confirm the stage path and file name:
@aws_s3_si_bucket_stage_new/si_south_indian_employees.csv - Check S3 permissions and Snowflake storage integration access
- Use
VALIDATION_MODEfirst to inspect format and field-level issues - Review
COPY_HISTORYandQUERY_HISTORYfor failure details - Validate staging data before moving it into production
π Click to expand: troubleshooting flow
- Confirm file availability
LIST @aws_s3_si_bucket_stage_neworLIST_STAGEoutput
- Validate the file format
- Use
VALIDATION_MODE = 'RETURN_ERRORS'
- Use
- Inspect staging results
- Check nulls, invalid dates, duplicate keys
- Review Snowflake load history
- Query
INFORMATION_SCHEMA.COPY_HISTORY
- Query
- Apply corrective actions
- Fix source data, update file format, rerun with
FORCE = TRUEonly when intended
- Fix source data, update file format, rerun with
6. Automate with Tasksβ
π§ Click to expand: Task-based load automation
Purpose: Schedule periodic loads with validation, cleanup, and auditing.
-
Load new data into
staging_employee_data -
Validate critical fields before production
-
Move only clean rows into
employee_data -
Truncate staging as part of task cleanup
-
Use task history for audit and troubleshooting
-
β Schedule periodic loads and validation for employee data:
CREATE OR REPLACE TASK load_employee_data_task
WAREHOUSE = my_warehouse
SCHEDULE = 'USING CRON 0 */4 * * * UTC' -- Every 4 hours
AS
BEGIN
-- Load to staging with error handling
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
ON_ERROR = 'CONTINUE';
-- Move only valid data to production table
INSERT INTO employee_data (emp_id, first_name, last_name, department, hire_date)
SELECT emp_id, first_name, last_name, department, hire_date
FROM staging_employee_data
WHERE emp_id IS NOT NULL
AND first_name IS NOT NULL
AND hire_date IS NOT NULL;
-- Clean up staging table for next load
TRUNCATE TABLE staging_employee_data;
END;
7. Error Logging and Notificationsβ
π Click to expand: Comprehensive error tracking setup
- β Set up comprehensive error tracking for employee data loads:
-- Create error logging table
CREATE OR REPLACE TABLE load_error_log (
load_id VARCHAR,
file_name VARCHAR,
error_message VARCHAR,
error_row_number INTEGER,
error_column_name VARCHAR,
error_value VARCHAR,
load_timestamp TIMESTAMP,
processed_by VARCHAR
);
-- Enhanced error handling in load procedure
CREATE OR REPLACE PROCEDURE load_employee_data_with_logging()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
load_id STRING := UUID_STRING();
error_count INTEGER := 0;
BEGIN
-- Log start of load
INSERT INTO load_error_log (load_id, file_name, error_message, load_timestamp, processed_by)
VALUES (load_id, 'si_south_indian_employees.csv', 'Load started', CURRENT_TIMESTAMP, CURRENT_USER);
-- Load with error capture
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
ON_ERROR = 'CONTINUE'
VALIDATION_MODE = 'RETURN_ERRORS';
-- Check for errors and log them
INSERT INTO load_error_log (load_id, file_name, error_message, error_row_number, error_column_name, error_value, load_timestamp)
SELECT
load_id,
file_name,
error_message,
error_row_number,
error_column_name,
error_value,
CURRENT_TIMESTAMP
FROM TABLE(VALIDATE(staging_employee_data, JOB_ID => (SELECT last_query_id(-1))));
-- Count errors
error_count := (SELECT COUNT(*) FROM load_error_log WHERE load_id = :load_id AND error_message != 'Load started');
-- Send notification if errors found
IF (error_count > 0) THEN
-- In production, integrate with email/SMS services
INSERT INTO load_error_log (load_id, file_name, error_message, load_timestamp)
VALUES (load_id, 'si_south_indian_employees.csv', CONCAT('ALERT: ', error_count, ' errors found in load'), CURRENT_TIMESTAMP);
END IF;
RETURN CONCAT('Load completed with ', error_count, ' errors logged');
END;
$$;
-- Execute the procedure
CALL load_employee_data_with_logging();
8. Best Practices Summaryβ
| Practice | Why It Matters | How to Implement |
|---|---|---|
Use ON_ERROR='CONTINUE' | Prevents complete load failures, ensures partial data availability | Load valid rows while logging errors for review |
| Staging + Validation | Protects production data integrity, enables data quality checks | Load to staging first, validate, then move clean data |
| VALIDATION_MODE | Preview issues before production impact | Use RETURN_ERRORS to see problems without loading |
| INFORMATION_SCHEMA Monitoring | Track load performance and costs for optimization | Query COPY_HISTORY and QUERY_HISTORY views |
| Automated Tasks | Ensures consistent, timely data refreshes | Schedule loads with Snowflake Tasks |
| Error Logging | Enables root cause analysis and prevents repeating mistakes | Store detailed error information for troubleshooting |
| Data Quality Metrics | Maintains trust in analytics and reporting | Regular checks for completeness, accuracy, timeliness |
9. Complete Workflow Exampleβ
π Click to expand: Production-ready employee data loading workflow
-- 1οΈβ£ Create staging table for safe loading
CREATE OR REPLACE TABLE staging_employee_data (
emp_id INTEGER,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
hire_date DATE,
salary DECIMAL(10,2)
);
-- 2οΈβ£ Load to staging with error handling
COPY INTO staging_employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
ON_ERROR = 'CONTINUE';
-- 3οΈβ£ Validate data quality before production
SELECT
COUNT(*) as total_rows_loaded,
COUNT(CASE WHEN emp_id IS NULL THEN 1 END) as missing_emp_ids,
COUNT(CASE WHEN first_name IS NULL THEN 1 END) as missing_first_names,
COUNT(CASE WHEN hire_date IS NULL THEN 1 END) as missing_hire_dates,
COUNT(CASE WHEN hire_date > CURRENT_DATE THEN 1 END) as future_hire_dates
FROM staging_employee_data;
-- 4οΈβ£ Move only valid data to production table
INSERT INTO employee_data (emp_id, first_name, last_name, department, hire_date)
SELECT emp_id, first_name, last_name, department, hire_date
FROM staging_employee_data
WHERE emp_id IS NOT NULL
AND first_name IS NOT NULL
AND hire_date IS NOT NULL
AND hire_date <= CURRENT_DATE;
-- 5οΈβ£ Handle duplicates using MERGE (upsert pattern)
MERGE INTO employee_data target
USING staging_employee_data source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
UPDATE SET
target.first_name = source.first_name,
target.last_name = source.last_name,
target.department = source.department,
target.hire_date = source.hire_date
WHEN NOT MATCHED AND source.emp_id IS NOT NULL
AND source.first_name IS NOT NULL THEN
INSERT (emp_id, first_name, last_name, department, hire_date)
VALUES (source.emp_id, source.first_name, source.last_name, source.department, source.hire_date);
-- 6οΈβ£ Clean up staging for next load
TRUNCATE TABLE staging_employee_data;
-- 7οΈβ£ Monitor the load results
SELECT
STATUS,
FILE_NAME,
ROWS_LOADED,
ROWS_PARSED,
ERROR_COUNT,
START_TIME,
END_TIME
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'EMPLOYEE_DATA',
START_TIME => DATEADD(hours, -1, CURRENT_TIMESTAMP())
));
- β
Key Benefits of This Workflow:
- Safety First: Staging prevents production data corruption
- Quality Assurance: Validation catches issues before they impact operations
- Duplicate Handling: MERGE ensures data consistency
- Monitoring: Built-in tracking for troubleshooting and optimization
- Scalability: Works for both small files and large datasets
π οΈ Snowflake's Built-in Duplicate File Preventionβ
Important: Snowflake automatically prevents loading the same file twice by default. This is built-in behavior - no additional setup required for basic duplicate prevention.
How Snowflake's Default Behavior Worksβ
-- First load - works normally
COPY INTO employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1);
-- Second load of same file - Snowflake automatically skips it
COPY INTO employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1);
-- Result: 0 rows loaded (file already processed)
What happens: Snowflake tracks loaded files internally and skips them automatically to prevent duplicates.
When You Need Custom File Trackingβ
For advanced scenarios like incremental loads or custom logic, you can implement file tracking:
-- Optional: Custom tracking for operational logic
CREATE OR REPLACE TABLE employee_file_tracking (
file_name STRING PRIMARY KEY,
load_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
rows_loaded INTEGER,
status STRING DEFAULT 'SUCCESS'
);
-- Check what files are available vs already loaded
WITH staged_files AS (
SELECT METADATA$FILENAME AS file_name,
METADATA$FILE_ROW_NUMBER AS row_count
FROM TABLE(INFORMATION_SCHEMA.LIST_STAGE('@aws_s3_si_bucket_stage_new'))
),
files_to_load AS (
SELECT file_name, row_count
FROM staged_files
WHERE file_name NOT IN (SELECT file_name FROM employee_file_tracking)
)
SELECT * FROM files_to_load;
Using FORCE = TRUE to Override Default Behaviorβ
When you need to reload a file (after fixing data quality issues):
-- Force reload when you intentionally want to reprocess
COPY INTO employee_data
FROM @aws_s3_si_bucket_stage_new/si_south_indian_employees_corrected.csv
FILE_FORMAT = (TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' SKIP_HEADER=1)
FORCE = TRUE; -- Override Snowflake's default duplicate prevention
Use Cases for FORCE = TRUE:
- Loading corrected data files
- Reprocessing after fixing data quality issues
- Testing with the same file multiple times
- Overriding default behavior for specific operational needs
β‘ Key Pointsβ
- Default: Snowflake prevents duplicate file loads automatically
- Override: Use
FORCE = TRUEwhen you need to reload files - Custom Tracking: Implement only when you need operation-specific logic
- Safety: Default behavior protects against accidental duplicate loads
β‘ Key Takeawayβ
Monitoring, error handling, and smart incremental load strategiesβcombined with appropriate use of options like FORCEβare essential to:
- β Maintain Data Quality
- β Prevent Duplicate Loads
- β Make Pipelines Production Ready
- β Ensure Operational Reliability