Skip to main content

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_MODE for 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 NumberColumn NameError
2last_nameExpected string but got 'abc'
3first_nameNULL value in required field
4hire_dateDate format error: 'not-a-date'
  • βœ… Validation Options:
    • VALIDATION_MODE = 'RETURN_ERRORS' – Show first error found
    • VALIDATION_MODE = 'RETURN_ALL_ERRORS' – Show up to 1000 errors
    • VALIDATION_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 = TRUE only 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 MERGE for 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_MODE first to inspect format and field-level issues
  • Review COPY_HISTORY and QUERY_HISTORY for failure details
  • Validate staging data before moving it into production
πŸ” Click to expand: troubleshooting flow
  1. Confirm file availability
    • LIST @aws_s3_si_bucket_stage_new or LIST_STAGE output
  2. Validate the file format
    • Use VALIDATION_MODE = 'RETURN_ERRORS'
  3. Inspect staging results
    • Check nulls, invalid dates, duplicate keys
  4. Review Snowflake load history
    • Query INFORMATION_SCHEMA.COPY_HISTORY
  5. Apply corrective actions
    • Fix source data, update file format, rerun with FORCE = TRUE only when intended

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​

PracticeWhy It MattersHow to Implement
Use ON_ERROR='CONTINUE'Prevents complete load failures, ensures partial data availabilityLoad valid rows while logging errors for review
Staging + ValidationProtects production data integrity, enables data quality checksLoad to staging first, validate, then move clean data
VALIDATION_MODEPreview issues before production impactUse RETURN_ERRORS to see problems without loading
INFORMATION_SCHEMA MonitoringTrack load performance and costs for optimizationQuery COPY_HISTORY and QUERY_HISTORY views
Automated TasksEnsures consistent, timely data refreshesSchedule loads with Snowflake Tasks
Error LoggingEnables root cause analysis and prevents repeating mistakesStore detailed error information for troubleshooting
Data Quality MetricsMaintains trust in analytics and reportingRegular 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 = TRUE when 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