Etl Error Handling

Production engineering guide for etl error handling covering patterns, implementation strategies, and operational best practices.

What this is and when it matters

This page covers error handling in ETL workflows—specifically how to detect, log, and recover from failures during data extraction, transformation, and loading processes. It applies to batch ETL systems that process large volumes of data with structured inputs and outputs, where a single failure can cascade into downstream processing issues or corrupt entire datasets.

Error Detection and Logging

Log all failures at the row level

In ETL systems, track individual record failures during transformation by logging them to a dedicated error table or file. Use structured logging formats like JSON or CSV with columns for:

Example schema for error tracking table:

CREATE TABLE etl_errors (
  id SERIAL PRIMARY KEY,
  source_id TEXT NOT NULL,
  error_type TEXT NOT NULL,
  error_message TEXT NOT NULL,
  timestamp TIMESTAMP DEFAULT NOW(),
  payload JSONB
);

Capture runtime exceptions in transformation steps

When writing transformation logic, wrap operations that may fail in try/catch blocks and log exceptions with full stack traces. For Python-based ETLs, use logging.exception() instead of logging.error() to include traceback information.

Example Python code:

import logging

try:
    transformed_row = transform_function(row)
except ValueError as e:
    logging.exception(f"Failed to transform row {row['id']}: {str(e)}")
    # Optionally write to error table here

Use structured exit codes for process-level failures

ETL processes should return specific exit codes that indicate the type of failure. Common values:

Example shell script:

#!/bin/bash
python etl_script.py || case $? in
  1) echo "Unrecoverable error" >&2; exit 1 ;;
  2) echo "Data validation issue" >&2; exit 2 ;;
  3) echo "Connection timeout" >&2; exit 3 ;;
esac

Recovery Strategies

Retry mechanisms with exponential backoff

Implement retry logic for transient failures using exponential backoff. For each retry attempt, double the wait time between attempts up to a maximum interval.

Example retry loop in shell:

retry_count=0
max_retries=3
delay=1

while [ $retry_count -lt $max_retries ]; do
  if your_etl_command; then
    exit 0
  else
    retry_count=$((retry_count + 1))
    sleep $delay
    delay=$((delay * 2))
  fi
done

echo "All retries exhausted" >&2
exit 1

Fail-fast for unrecoverable errors

Do not retry operations that are known to be permanently broken. For example, if a data format changes in the source system and no transformation can handle it, immediately terminate the process rather than continuing with bad data.

Use explicit error detection:

if ! grep -q "expected_field" input.csv; then
  echo "Missing required field in input data" >&2
  exit 2
fi

Implement checkpointing for long-running jobs

Break ETL processes into smaller units that can be resumed from their last successful point. Store progress in a database or file.

Example approach:


if [ -f checkpoint.txt ]; then
  last_processed=$(cat checkpoint.txt)
else
  last_processed=0
fi

# Process from last checkpoint forward
for record in $(seq $((last_processed + 1)) 1000); do
  process_record $record || {
    echo $record > checkpoint.txt
    exit 1
  }
done

Error Classification and Routing

Separate transient from permanent errors

Classify errors by nature to determine whether they are likely to resolve themselves or require manual intervention. Transient errors include:

Permanent errors include:

Example classification function:

def classify_error(error):
  if isinstance(error, TimeoutError) or 'timeout' in str(error).lower():
    return 'transient'
  elif isinstance(error, ValueError) or 'invalid' in str(error).lower():
    return 'permanent'
  else:
    return 'unknown'

Route different error types to appropriate handlers

Send different categories of errors to different destinations based on severity. For example:

Use conditional logic:

if [ "$error_type" = "permanent" ]; then
  send_to_alerting_system "$error_message"
elif [ "$error_type" = "transient" ]; then
  log_to_file "$error_message"
fi

Configuration for Error Handling

Configure error thresholds per job

Set maximum acceptable error rates for each ETL job. For example, allow no more than 1% of records to fail during processing before marking the job as failed.

Example configuration:

job_config:
  max_error_rate: 0.01
  error_table: etl_errors

Define error recovery policies

Specify how errors should be handled for different types of jobs:

Example policy definition:

recovery_policy:
  job_type: batch
  max_retries: 3
  retry_delay_seconds: 10
  fail_on_error_rate: true

Production-Specific Considerations

Handle silent failures in data loading

Many ETL tools silently ignore certain types of errors during load operations. For example, when inserting data into a table with mismatched column counts, some systems insert null values instead of failing.

Always validate row counts after loading:

expected_rows=$(wc -l < input.csv)
actual_rows=$(psql -t -c "SELECT COUNT(*) FROM target_table;" | tr -d ' ')
if [ "$expected_rows" -ne "$actual_rows" ]; then
  echo "Row count mismatch: expected $expected_rows, got $actual_rows" >&2
  exit 1
fi

Monitor for missing data

Detect when expected data is not present in source systems. This often happens when files are missing or partitions are skipped.

Example check:

if [ ! -f "$source_file" ]; then
  echo "Source file missing: $source_file" >&2
  exit 3
fi

Validate schema changes

When ETL processes encounter unexpected schema changes, they should fail immediately. Use checksums or versioned metadata to detect such events.

Example schema validation:

current_checksum=$(md5sum schema.json | cut -d' ' -f1)
stored_checksum=$(cat schema_checksum.txt)

if [ "$current_checksum" != "$stored_checksum" ]; then
  echo "Schema has changed, job must be updated" >&2
  exit 2
fi

Common Silent Failures

Ignored column mismatches

Some ETL tools silently truncate or drop data when column types don't match. Always validate that all expected columns are present and correctly typed.

Example validation:

expected_columns="id,name,email,created_at"
actual_columns=$(psql -t -c "SELECT column_name FROM information_schema.columns WHERE table_name = 'target_table';" | tr '\n' ',' | sed 's/,$//')

if [ "$expected_columns" != "$actual_columns" ]; then
  echo "Column mismatch detected" >&2
  exit 1
fi

Unreported data loss in transformations

Transformations that silently drop rows or convert nulls to defaults can cause silent data loss. Log every transformation step's input and output counts.

Example:

input_count=$(wc -l < input.txt)
# ... processing ...
output_count=$(wc -l < output.txt)
if [ "$input_count" -ne "$output_count" ]; then
  echo "Data loss detected: $input_count -> $output_count records" >&2
fi

Incorrect error handling in external systems

When ETL calls external APIs or services, ensure that HTTP errors are properly handled and logged. Some systems return 200 OK even when the request failed internally.

Example API call:

response=$(curl -s -w "%{http_code}" http://api.example.com/endpoint)
status_code=$(echo "$response" | tail -c 3)
body=$(echo "$response" | head -c -3)

if [ "$status_code" -ne 200 ]; then
  echo "API error: $status_code - $body" >&2
  exit 1
fi

This page was rewritten on 16 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.