Happens Every Day

The Daily Grind of Production Data Pipelines

Things That Happens Every Day in a Live Environment

Most people learning data engineering don't prepare for the things that actually break. They learn the theory. They build clean little Airflow DAGs on their laptop. Then something goes live and the pipeline fails at 3AM because a schema drifted or a downstream consumer changed its API, and you spend six hours on a Saturday debugging a problem that should have been caught weeks ago. I'm not telling you this to scare you. I'm telling you because the gap between textbook pipeline design and what actually runs in production is wider than most tutorials admit. Let me walk you through a specific edge case I ran into recently, and how I actually fixed it without burning a weekend. Last month I inherited a Spark job that was supposed to merge incremental updates from a Postgres source into a big Snowflake table. The documentation said it used a watermark column. It didn't. Someone had hardcoded a date filter that was two days stale, so every morning the merge picked up about 400,000 rows that were already there and then dropped roughly 80,000 rows that had been deleted from the source. No error. No alert. Just silent data rot. I wrote a comparison query against the last known state, found the delta, and patched the watermark logic with a small Python wrapper that validates the row count before committing. It added about four minutes to the job runtime but prevented the kind of damage that would have triggered an all-hands investigation.

That experience changed how I approach incremental loads. Here is what actually matters when you are building something that runs on a schedule and has real consumers watching the output.

Incremental Loads, Done Without the Headaches

The standard approach is CDC or watermark-based extraction. Both work. Both have failure modes that beginners consistently miss. The issue is not the mechanism itself. The issue is assuming the source behaves predictably. A watermark is just a column, usually a timestamp, that you use to pull only new or changed records since your last run. It sounds simple. It is simple, until the source system allows nulls in that column, or the clock jumps backward because someone reverted a deployment, or the upstream database is in a different timezone than your warehouse and you are not accounting for it. I learned this the hard way with a Kafka source where the offset tracking was off by one partition due to a replication lag that was not documented anywhere. The consumer group committed offsets ahead of the actual processing, so when the cluster restarted after a routine deploy, it skipped roughly 12 hours of data. There was no error in the logs. The job completed successfully. The numbers in the dashboard were just wrong. I ended up writing a custom offset validator that cross-references the committed offset against the actual max timestamp in the landing zone, and it flags any drift before the data gets written to the target table. That validator runs as a pre-flight check. It takes about thirty seconds.

Full refreshes are another common fallback. You truncate the target and reload everything. This works fine for small datasets. Once you are dealing with terabytes and multiple downstream consumers, the cost of a full refresh becomes real. You are paying for compute, you are blocking other jobs that depend on the table, and you are introducing a window where the data is simply unavailable. Most teams that try this approach end up switching back to incremental because the operational pain of full reloads scales linearly with dataset size.

Common Pitfalls That Nobody Talks About in Tutorials

Timezone handling is the quiet killer. If your source is in UTC and your target is in US Eastern, or if you are merging data from systems that report timestamps in mixed zones, your incremental window will either double-count records or drop them entirely. I have seen this cause a sales metrics pipeline to underreport revenue by roughly seven percent for an entire quarter. The fix is straightforward but boring: convert every timestamp to UTC at the point of extraction, before any downstream logic touches it. Do not rely on the database to do this for you. Explicit is better than implicit. Idempotency is another thing people understand in theory and mess up in practice. An idempotent operation produces the same result no matter how many times you run it. Your pipeline needs this. If a job fails halfway through and you retry it, the second run should not create duplicate rows or corrupt existing data. The typical solution is upsert logic with a unique key, but the unique key must be stable. Surrogate keys generated at load time are not stable if you are pulling from a source that does not guarantee consistency. Use natural business keys where possible, or maintain a deterministic hash column that does not change between runs. Schema drift is the third major failure mode. A column gets renamed, a type changes, a new nullable field appears. If your pipeline assumes a fixed schema, it will either crash or silently produce garbage output. The most practical defense I have found is a lightweight schema validation step that runs before the transformation phase. It compares the incoming schema against a stored version, and if anything has changed outside an allowed diff, it aborts the job and sends an alert with the specific differences. This added about two minutes of overhead to my pipelines, but it caught three schema changes in the first month alone that would have otherwise gone unnoticed.

What to Do When Your Pipeline Breaks and You Have No Time to Debug

Here is the blunt truth: sometimes things fail and you need a working system back online before you have time to do a root cause analysis. The workaround I use is a circuit breaker pattern. If a job fails three times in a row with the same error signature, the system stops retrying and falls back to a known-good snapshot. The snapshot is not ideal. It is probably hours old. But it is better than nothing, and it gives you a window to investigate without pressure. After the fallback activates, you get a alert with the error details, the failing job's state, and a suggested next step based on previous incidents of the same type. It is not a perfect solution. It introduces data staleness. But in production, a stale dataset is often easier to explain to stakeholders than a missing one. If you are starting from scratch and want something that handles these patterns without building custom infrastructure, there are tools like Airbyte, dbt, and Dagster that cover a lot of this ground. They are not magic. They will still break. But they at least give you defaults that are closer to what production actually needs than rolling your own from scratch. My recommendation if you are in a hurry is to start with dbt for the transformation layer and Airbyte for ingestion, then add the validation and circuit breaker logic on top. This combination usually cuts a fresh pipeline build from two weeks of development down to about three or four days, depending on complexity.

The Reality of Maintaining Data Systems Over Time

Pipelines are not built once and forgotten. They change. Sources change. Requirements change. The people maintaining them change. The best documentation for a pipeline is not a wiki page. It is the job logs, the error alerts, the run history, and the fallback snapshots that prove what happened when things went wrong. I keep a running incident log for every pipeline I touch, even the small ones. It is usually just a plain text file with dates, symptoms, and the fix. Two years later, when the same issue resurfaces, that log saves me forty-five minutes of searching through Slack threads and Jira tickets. The things that happen every day in production are rarely dramatic. They are small, repetitive, and boring. A timestamp is in the wrong format. A partition is missing. A downstream query returns zero rows because a join key was null. Most of the time these issues are obvious once you see the logs. The problem is that you often do not see the logs until hours after the fact. Building observability into the pipeline from the start, instead of retrofitting it after a failure, is the single most effective thing you can do to reduce the mean time to resolution.