9 Common ETL Pipeline Mistakes That Break in Production
Nine ETL reliability checks covering idempotency, schema drift, data quality, credentials, alerting, incremental loads, and recovery.
Production data can expose gaps that development samples miss: duplicate records, schema changes, partial loads, and missing values. The checks below focus on detecting those failures and recovering without corrupting downstream data.
1. Not Building Idempotent Pipelines
An idempotent pipeline produces the same output whether it runs once or ten times against the same input. If your pipeline is not idempotent, every retry after a failure risks duplicating data, creating partial loads, or silently corrupting downstream tables.
How to fix it: Use upserts instead of appends where possible. Partition data by a time key and delete-then-insert within that partition before writing. Never write with unbounded INSERT unless the destination already enforces deduplication.
2. Ignoring Schema Drift
Source systems change. Columns get added, renamed, retyped, or dropped. If your pipeline does not handle schema drift explicitly, it either breaks on the new schema or silently drops or misinterprets changed fields.
How to fix it: Validate incoming schema on every run. Use a schema registry or at minimum compare against a known-good schema snapshot. Alert on schema changes rather than failing silently or ignoring them.
3. Skipping Data Quality Checks
Pipelines that move data without validating it propagate bad data downstream. By the time a business stakeholder catches a data quality issue in a dashboard, it may have affected weeks of reports.
How to fix it: Add row-count checks, null checks on critical fields, range checks on numeric columns, and referential integrity checks at the boundary between stages. dbt tests, Great Expectations, and Soda are all viable options. Run checks before data is promoted to production tables.
4. Hardcoding Connection Strings and Credentials
Credentials embedded in pipeline code make secrets impossible to rotate without a code change, create security exposure when code is shared, and block environment-specific deployments.
How to fix it: Pull credentials from environment variables or a secrets manager at runtime. Never commit credentials to source control. Use separate credentials per environment.
5. No Error Handling or Alerting
Many early-stage pipelines fail silently. The scheduler marks the job complete, no alert fires, and nobody notices until a downstream consumer complains about stale or missing data.
How to fix it: Treat pipeline failures the same as application errors. Connect failures to an alerting channel (PagerDuty, Slack, email). Log structured output — task name, source, row counts, duration, error message — so failures are diagnosable without re-running the pipeline.
6. Monolithic Pipeline Design
A single pipeline that handles ingestion, transformation, validation, and load in one long script is a single point of failure. A failure partway through gives you partial results and no clean recovery path.
How to fix it: Break pipelines into distinct stages with clear handoff points. Each stage should be independently retriable. If the transform step fails, you should be able to rerun only the transform step against the already-ingested raw data.
7. Using Full Loads When Incremental Loads Are Feasible
Scanning and reloading an entire source table every run is wasteful, slow, and puts unnecessary load on source systems. It also makes pipelines harder to scale as data volume grows.
How to fix it: Identify an incremental key — typically a created_at or updated_at timestamp, or a monotonically increasing ID. Load only rows changed since the last successful run. Track the high-water mark in a metadata table or pipeline state store.
8. Not Testing Pipelines Before Production
ETL pipelines often get deployed directly to production because they "work on my machine." Differences in data volume, schema, encoding, or timezone between environments cause failures that could have been caught earlier.
How to fix it: Maintain a staging environment with a representative sample of production data. Run the full pipeline against staging before promoting changes. Automate this in CI so it is not a manual step that gets skipped.
9. Ignoring Pipeline Run History and Lineage
Without a record of which runs succeeded, which rows were processed, and what the pipeline state was at each point, debugging failures becomes archaeology.
How to fix it: Log pipeline run metadata — start time, end time, rows read, rows written, status, errors — to a dedicated runs table. Most orchestrators (Airflow, Dagster, Prefect) give you this out of the box. Make sure you are using it, not just relying on orchestrator UI logs.
Include Recovery in the Pipeline Design
Pipelines run unattended and supply downstream systems. Include testing, monitoring, versioning, and documented recovery procedures in their design.
Test reruns, partial failures, and schema changes before relying on a pipeline for production reporting.