Your data pipeline breaks every week. Here is the usual cause.

The failures that page you, and the ones that don't

The pattern is familiar enough to be a genre. Something fails overnight, someone re-runs it in the morning, and it works. A ticket gets written, it says "transient," and nobody believes that word but nobody has time to disprove it. Three weeks later a different table breaks, and the cycle restarts. The reframe worth making: a pipeline that breaks weekly does not have fifty-two problems a year. It has three or four structural weaknesses producing new symptoms. Fixing symptoms is why the work never ends.

Say this plainly first: the failures that wake you up are the good ones. A job that crashes told you the truth, immediately, where you were already looking. The expensive failures finish green and write numbers that are quietly wrong — found by a person months later, in a meeting, usually while someone is presenting. The real cost is not engineering hours; it is that everyone downstream stops believing the data, builds shadow spreadsheets, and the platform you funded becomes a thing people route around. The goal is not fewer alerts. It is moving failures out of the silent category into the loud one, then reducing the loud ones.

Schema drift: someone else owns your schema

An upstream team adds a column. Renames one. Widens an integer to a string because a partner started sending "N/A" instead of a number. None of that is unreasonable — it is their system, and they usually have no idea who reads it. You do not own the schema. You own the consequences. Drift is expensive because most tools default to being helpful rather than loud, and helpful is exactly wrong here.

Take dbt. The documented behavior of on_schema_change defaults to ignore: add a column to your incremental model and it "will not appear in your target table"; remove a column and "dbt run will fail." Read that asymmetry twice — additions vanish in silence, removals explode, which is backwards from what most teams assume. The alternatives are fail, append_new_columns, and sync_all_columns, which syncs in both directions including data type changes.

Delta Lake takes the opposite default. Per the Delta Lake batch documentation, it "automatically validates that the schema of the DataFrame being written is compatible with the schema of the table" — every column must exist in the target, types must match, names cannot differ only by case. Good, except for the escape hatches: mergeSchema and overwriteSchema are what get pasted into a job at 2 a.m. to make it go green, then live in the code forever, quietly widening the table with columns nobody defined.

On the streaming side, Confluent Schema Registry makes the policy explicit: BACKWARD (the default — consumers on the new schema can read data written with the previous one), FORWARD (old consumers can read new data), FULL (both, permitting only optional additions and removals), the TRANSITIVE variants that check against all previous versions rather than only the most recent, and NONE. That transitive distinction matters: a chain of individually compatible changes can still leave version five unable to read version one.

The trade-off nobody names. Strict compatibility slows the producing team down. That is why loose settings win arguments — but loose settings do not remove the cost, they move it downstream, to another team, at 4 a.m., unbudgeted. Where that cost lands is a management decision engineers usually end up making by accident.

Silent nulls: the wrong number, delivered on time

This category damages trust rather than uptime, and it pays to understand it at the level of SQL semantics rather than folklore. The PostgreSQL documentation on aggregate functions states it directly: aggregates ignore null values in their input. count(*) "computes the number of input rows"; count(expression) counts rows "in which the input value is not null." And except for count, these functions return null when no rows are selected — "sum of no rows returns null, not zero as one might expect."

Now put a real failure on top. A join condition changes upstream and a meaningful fraction of rows start carrying a null amount. Nothing errors. But AVG(amount) silently drops those rows from both numerator and denominator while COUNT(*) keeps counting them. Two dashboards built by two different people — one dividing SUM(amount) by COUNT(*), one calling AVG(amount) — now disagree, both green, and the argument that follows will consume a week.

Second mechanism: null never equals null, so a null join key matches nothing — rows do not error, they leave, and an inner join quietly becomes a filter. Third, and nastiest: if a feed stops entirely, SUM over the empty set returns null, and BI layers very often render null as 0. An outage and a genuine zero look identical on the chart, which is how a feed goes dark for three weeks without one alert.

None of this is fixed with cleverer SQL. It is fixed by asserting invariants where data enters the system. dbt ships four generic data tests out of the box — unique, not_null, accepted_values, relationships — declared in YAML against a column; each is a select statement returning offending rows, and zero rows back means the assertion held. Great Expectations and Soda do the same job differently.

The trade-off. Tests that run after a load commits tell you the truth late, when bad rows are already downstream. Tests that gate a load cost freshness during an incident. Choose per table: if it feeds a number that leaves the building, gate it; if it feeds an exploratory dashboard, test after and alert. Deciding what "clean" even means before writing the rule is the subject of our guide to data cleaning.

Late-arriving data: two clocks, one table

Every pipeline has two clocks and most code acknowledges one: when an event happened, and when your system found out. The Dataflow Model paper (Akidau et al., Proceedings of the VLDB Endowment, 2015) is the standard reference for treating that split as a design decision rather than an annoyance, and it supplied the vocabulary current engines implement.

Spark states the operational semantics precisely. Per the Structured Streaming documentation, for a window ending at time T, "the engine will maintain state and allow late data to update the state until (max event time seen by the engine - late threshold > T). In other words, late data within the threshold will be aggregated, but data later than the threshold will start getting dropped."

Then the sentence worth memorizing, one-sided in a way people consistently misread. A watermark delay of two hours "guarantees that the engine will never drop any data that is less than 2 hours delayed." But "data delayed by more than 2 hours is not guaranteed to be dropped; it may or may not get aggregated." A watermark is not a rule about what gets thrown away. It is a promise about what gets kept. Past it, behavior is undefined, and two re-runs can legitimately give two answers.

Batch has the same problem in different clothing. Your job runs at 00:15 for "yesterday." A source system posts a backdated correction three days later. That partition was correct when written and is wrong now — and a pipeline that never revisits a closed partition stays wrong permanently without ever failing.

The trade-off. A short threshold gives fresh results, small state, and more data silently never counted. A long one gives more correctness, more state, and a longer wait before any number is final. No setting gives both — there is only a threshold chosen deliberately and one inherited from a tutorial.

Two moves follow. Store event time and ingest time as separate columns, so "how late is our data, really" becomes a query instead of an argument. Then reprocess a rolling window of recent partitions nightly, as normal operation. Both only work if the pipeline is idempotent, which is the next problem.

No idempotency: the retry is the second failure

Orchestrators retry. That is what they are for — so every task will eventually run twice on the same interval, and the only real question is whether that is safe. Apache Airflow's best-practices documentation is blunt: "You should treat tasks in Airflow equivalent to transactions in a database. This implies that you should never produce incomplete results from your tasks." Three rules follow, each mapping to a failure people hit constantly:

• Do not INSERT on a re-run. An INSERT "might lead to duplicate rows in your database. Replace it with UPSERT." Duplicate rows are the classic Monday discovery after a Sunday retry storm.
• Read and write a specific partition. "Never read the latest available data in a task," because someone may modify the input between the first run and the re-run. The docs suggest the data interval start as a partition key.
• Never call now() inside a task. It "leads to different outcomes on each run."

That last one is the sleeper. A task filters WHERE updated_at > now() - interval '1 day'. On Thursday you re-run it to repair Tuesday. You do not get Tuesday — you get a window ending Thursday, with a hole exactly where Tuesday was. The backfill reports success, row counts look plausible, and the gap stays invisible until someone runs a year-over-year comparison.

A rule of thumb that removes most of this: a task should take its time window as an input, write only inside that window, and be safe to run a hundred times. If the simplest route is deleting and rewriting a whole partition, do that — a partition overwrite is idempotent by construction and explainable in one sentence, which beats a merge statement nobody dares touch.

Streaming adds a transport-layer version. Confluent's producer configuration reference notes that allowing retries while enable.idempotence is false and more than one request is in flight per connection "will potentially change the ordering of records," because a failed-and-retried batch can land after a later one that succeeded. Deduplication downstream needs a key and a bounded window — Spark exposes dropDuplicatesWithinWatermark for the case where two copies of one record carry slightly different event times.

No contract: why the same four keep coming back

Step back: every failure above is a communication failure with a technical symptom. The producer changed something and had no obligation to tell anyone. The consumer assumed something and never wrote it down. The pipeline is where those two omissions meet.

A data contract is that assumption, written down and versioned. The Open Data Contract Standard is maintained by the Bitol project, a Linux Foundation AI & Data project, which describes a contract as "an agreement between a data producer and its consumers" — a YAML file covering structure, semantics, quality rules, service levels, and ownership.

Adopting that standard matters far less than getting four questions answered in a file that lives in a repository: who owns this dataset, what is its schema and how may it change, what quality rules must always hold, and how fresh is it promised to be. Once those sit next to the code, a breaking change becomes a pull request someone reviews rather than a page at 4 a.m.

The honest boundary: a contract does not stop an upstream team from breaking it. It converts breakage from an argument into a diff and gives the on-call engineer something to point at. That is valuable, and it is less than the word "contract" suggests. If nobody upstream will sign, what you have is documentation — useful, but do not budget for it as a fix.

The practices that stop most of it

None of these are exotic. They are rare because each costs something visible today to prevent something invisible later — the hardest trade to get funded.

1. Set schema-change behavior explicitly, in every tool. Never inherit a default you have not read. Write down which tables fail closed and which may absorb new columns.
2. Assert invariants at the boundary, not on the dashboard. Not-null, uniqueness, referential integrity, and accepted values belong where data enters, not where a human finally notices.
3. Take the time window as an input and make every write idempotent over it. Partition overwrite or upsert. No now(), no "latest available."
4. Store event time and ingest time as separate columns. Lateness becomes measurable, and measurable is the precondition for choosing a threshold on purpose.
5. Reprocess a rolling window as routine, not as incident response. Re-running the last N days nightly is cheap once step 3 is done, and it absorbs backdated corrections silently.
6. Quarantine bad rows — do not drop them, do not let them through. A rejects table with a reason code turns "the numbers look off" into a five-minute query.
7. Alert on freshness and row volume, not just exit codes. A green pipeline delivering zero rows is the most common silent failure there is, and no orchestrator will mention it.
8. Keep column-level lineage. The expensive question is never "what broke" but "what else did that touch," and lineage is the difference between minutes and days.

Do the first three and most weekly breakage stops. Do all eight and the failures that remain are genuinely novel — the only kind worth an engineer's Monday. If you are earlier in the stack, still choosing formats, orchestration, and warehouse shape, our data pipeline guide and data modeling guide cover the ground beneath this article.

Sources: dbt — incremental models and on_schema_change; dbt — data tests; Delta Lake — table batch reads and writes (schema validation, mergeSchema); Confluent — Schema Registry compatibility types; PostgreSQL — aggregate functions and null handling; Apache Airflow — best practices (idempotency); Apache Spark — Structured Streaming watermarking; Confluent — producer configuration reference; Bitol — Open Data Contract Standard; Akidau et al., "The Dataflow Model," PVLDB 8(12), 2015. Analysis and framing by Precision AI Academy.

Common questions

Why does my data pipeline break every week? Weekly breakage is usually a small number of structural causes producing many different symptoms: schema drift from upstream systems you do not control, nulls that change aggregate results without raising an error, data that arrives after the window it belongs to has closed, and tasks that are not safe to re-run. Each has a documented remedy, and none of them are fixed by restarting the job.

What is schema drift and how do I stop it from breaking downstream tables? Schema drift is any change to an upstream dataset's columns, types, or semantics that you did not agree to. The remedy is to make the drift policy explicit rather than inherited. dbt's on_schema_change config accepts ignore, fail, append_new_columns, and sync_all_columns; Delta Lake validates the written schema against the table unless mergeSchema is set; Confluent Schema Registry enforces BACKWARD, FORWARD, FULL, or NONE compatibility, with BACKWARD as the default.

What does it mean for a pipeline task to be idempotent? An idempotent task produces the same result whether it runs once or a hundred times on the same input window. Apache Airflow's best-practices documentation advises replacing INSERT with UPSERT so re-runs do not create duplicate rows, reading and writing a specific partition rather than the latest available data, and never calling now() inside a task because it produces a different outcome on every run.

How should I handle late-arriving data? Separate event time from ingest time and store both, then choose an explicit lateness threshold. In Spark Structured Streaming, withWatermark tells the engine how late data is expected to be; per the Spark documentation, late data within the threshold is aggregated and data later than the threshold starts getting dropped, and the guarantee is one-sided — data less than the threshold behind is never dropped, but data beyond it may or may not be processed. In batch pipelines, the equivalent is deliberately reprocessing a rolling window of recent partitions.