9 Common ETL Pipeline Mistakes That Break in Production
The most frequent ETL pipeline mistakes data engineering teams make — from skipping idempotency to ignoring schema drift — and how to fix each one.
ETL pipelines have a way of working flawlessly in development, then failing in ways nobody anticipated once they hit production. The failures are rarely exotic. Most trace back to a small set of well-known mistakes that are easy to skip when moving fast and expensive to fix later. Here are the ones that appear most often in real pipelines.
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.
The Pattern Behind the Mistakes
Most of these mistakes share a root cause: treating the pipeline as a script rather than as infrastructure. Scripts are disposable. Pipelines carry data that downstream systems depend on, and they run unattended. They deserve the same engineering discipline as your application code — testing, monitoring, versioning, and documented failure modes.
Getting these fundamentals right upfront is far cheaper than retrofitting reliability into a pipeline that has been running in production for six months.
If you are building data pipelines that need to be reliable from day one, Clixo's engineering team designs and ships production-grade data infrastructure for product and growth teams.