Common data pipeline mistakes: best practices explained

Data pipelines that worked fine in development can fail silently in production, corrupting months of analytics and breaking downstream systems. We've all inherited codebases where late-night fixes and tight deadlines left technical debt that eventually surfaces as data inconsistency or job failures. This article covers common data pipeline mistakes: best practices explained—four habits that cause bad data and how to fix them.

Non-idempotent jobs

An idempotent operation produces the same result regardless of how many times it runs. If your pipeline crashes halfway through and restarts, an idempotent job will produce correct data; a non-idempotent one will duplicate records or leave your warehouse in an inconsistent state.

We've seen this play out in production: a Spark job that reads from Kafka, transforms events, and writes to S3 crashes after processing 50 million records. When ops reruns it, the same 50 million records get written again, and downstream SQL aggregations now count those events twice. The job succeeded technically, but the data is wrong.

The root cause is usually one of these patterns:

• Appending to a table without deduplication logic
• Using incremental state that doesn't account for replay scenarios
• Relying on external side effects (like third-party API calls) that can't be safely retried
• Writing directly to a destination without upsert capability

Here's a non-idempotent approach:

# BAD: Append-only write, no deduplication
df = spark.read.kafka(...)
result = df.groupBy("user_id").count()
result.write.mode("append").save("s3://data/user_counts/")

If this job runs twice, you'll have duplicate rows. Instead, use idempotent patterns:

# GOOD: Partition by date and overwrite
df = spark.read.kafka(...)
result = df.groupBy("user_id").count()
result.write \
  .partitionBy("date") \
  .mode("overwrite") \
  .option("path", "s3://data/user_counts/") \
  .save()

Better yet, use a data warehouse like Redshift or Snowflake with upsert semantics:

# GOOD: Delta Lake merge for true idempotence
result.write \
  .format("delta") \
  .mode("overwrite") \
  .option("mergeSchema", "true") \
  .saveAsTable("user_counts")

For streaming pipelines, use checkpoints and ensure your sink supports exactly-once semantics. Kafka-to-Delta via Structured Streaming with checkpoints gives you strong guarantees:

df = spark \
  .readStream \
  .format("kafka") \
  .option("kafka.bootstrap.servers", "...") \
  .load()

df.writeStream \
  .format("delta") \
  .option("checkpointLocation", "s3://checkpoints/pipeline/") \
  .outputMode("append") \
  .start()

The checkpoint prevents duplicate processing even if the job restarts.

No data quality checks

Bad data flows downstream silently. A null value in a price column becomes 0, making revenue reports wrong. A timestamp in the wrong timezone shifts all time-series queries. A user ID that suddenly changes format breaks joins. None of these errors surface until an analyst notices the KPI is off by 40%.

Real scenario: a pipeline ingesting third-party attribution data had no validation. One day, the vendor changed their event schema. The pipeline still ran successfully—Spark just set missing columns to null. Four months of attribution data was corrupt before anyone noticed.

Implement quality checks at three stages:

• Input validation: Assert schema, check row counts, validate ranges
• Transformation validation: Verify no unexpected nulls, check arithmetic correctness
• Output validation: Assert row counts match expectations, check for duplicates, validate referential integrity

Here's a practical approach using Great Expectations or custom checks:

from pyspark.sql.functions import col, count, when

# Input validation
raw_df = spark.read.parquet("s3://raw/events/")
input_count = raw_df.count()
assert input_count > 0, "Raw data is empty"

schema_fields = {f.name for f in raw_df.schema.fields}
required_fields = {"user_id", "event_type", "timestamp"}
missing = required_fields - schema_fields
assert not missing, f"Missing fields: {missing}"

# Transformation validation
transformed = raw_df \
  .filter(col("event_type").isin(["click", "view", "purchase"])) \
  .filter(col("timestamp").isNotNull())

null_check = transformed.select([
  count(when(col(c).isNull(), 1)).alias(c) 
  for c in transformed.columns
])
null_check.show()

# Output validation
final_count = transformed.count()
loss_ratio = (input_count - final_count) / input_count
assert loss_ratio < 0.05, f"Data loss {loss_ratio*100}% exceeds threshold"

# Check for duplicates
duplicate_count = transformed.groupBy("user_id", "timestamp").count() \
  .filter(col("count") > 1).count()
assert duplicate_count == 0, f"Found {duplicate_count} duplicates"

Fail fast and loudly. Log these metrics and set up alerts. If duplicate count suddenly spikes, page someone immediately rather than letting bad data accumulate.

Hard-coded dates

Hard-coded dates are a sneaky source of pipeline debt. A date value hard-coded for "yesterday" works once, then the job fails the next day because nobody updates it. Or it succeeds, but processes the wrong data retroactively.

Common patterns we see:

• Partition paths: s3://data/year=2024/month=01/day=15/ hard-coded for a specific run
• Lookback windows: WHERE created_at > '2024-01-01' to backfill history
• Date filters in production jobs that should be dynamic

Here's the problem:

# BAD: Hard-coded date
query = "SELECT * FROM events WHERE date = '2024-01-15'"
df = spark.sql(query)

When this job runs on Jan 16, it processes the same data again or fails when run outside a specific window.

Use dynamic dates based on execution time or a parameter:

from datetime import datetime, timedelta

# GOOD: Dynamic date from execution context
run_date = datetime.utcnow().date()
yesterday = run_date - timedelta(days=1)

query = f"SELECT * FROM events WHERE date = '{yesterday}'"
df = spark.sql(query)

For Airflow or Databricks Jobs, pass the execution date as a parameter:

# Airflow DAG passes {{ ds }} (execution date)
# Databricks job receives it as a widget

dbutils.widgets.get("execution_date")  # e.g., "2024-01-15"
run_date = datetime.strptime(execution_date, "%Y-%m-%d").date()

If you need to backfill historical data, use a separate backfill job with an explicit parameter, not embedded dates:

# Backfill script with explicit start/end
start_date = "2023-01-01"
end_date = "2024-01-15"

current = datetime.strptime(start_date, "%Y-%m-%d").date()
end = datetime.strptime(end_date, "%Y-%m-%d").date()

while current <= end:
  process_day(current)
  current += timedelta(days=1)

Always derive dates from the job execution context or passed parameters, never embed them in code.

No ownership

A pipeline with no clear owner becomes a zombie. When it breaks, everyone assumes someone else will fix it. The data degrades silently. Alerts go unread because on-call has no context.

We've inherited pipelines where the original engineer left years ago, and the entire team treats it like a black box. When something goes wrong, nobody feels responsible for investigating root cause or implementing a proper fix.

Establish ownership practices:

• Document the pipeline owner and on-call rotation in code comments and runbooks
• Create a Slack channel for pipeline alerts and discussions
• Add the owner to alerting rules, not a generic "data-team" channel
• Require code review for any pipeline changes, with the owner as a reviewer
• Schedule regular audits of pipeline health and data freshness

In your repository, add an OWNERS file or document ownership in the code itself:

"""
User Events Pipeline

Owner: analytics-platform-team
On-call: @analytics-oncall
Last reviewed: 2024-01-10

This pipeline:
- Ingests user events from Kafka
- Aggregates to user_summary table daily
- SLA: 6am UTC each day

Alert contacts: See OWNERS file
"""

from datetime import datetime, timedelta
# ... pipeline code

Set up monitoring that feeds into an incident response process:

• Alert on job failure, data staleness, and row count anomalies
• Route to the owner's email and Slack
• Include a runbook link so they know how to debug
• Track MTTR (mean time to resolution) by pipeline

When a pipeline owner changes roles, onboard the new owner and update documentation. Treat this as seriously as code ownership in application teams.

Common mistakes

These four issues often compound. A pipeline with no ownership won't have data quality checks. A job that's never been tested for idempotence will corrupt data on the second run. Hard-coded dates combined with no alerts means bad data sits in your warehouse for weeks.

Key takeaways

• Build idempotency into every pipeline. Use partition-based overwrites or merge semantics. Test restarts in staging.
• Implement data quality checks at input, transformation, and output stages. Fail fast and alert on anomalies.
• Derive dates dynamically from execution context or parameters. Never hard-code dates in production pipelines.
• Establish clear ownership with runbooks, alert routing, and code review. Treat data pipeline ownership like application ownership.
• Automate validation and monitoring. A pipeline that isn't monitored is a pipeline that's already broken.