What to Fix First When Your Migrated Pipeline Copies Data Twice (and Burns Cloud Credits)

You just migrated a legacy ETL pipeline to a modern stack. First run: costs triple. Second run: five times. Somewhere, data got copied twice — or three times. And your cloud credits are burning like a forgotten stove.

Why Duplicate Data After Migration Is a Burning Deck

Cloud costs spiral: storage and compute

You've just finished migrating your ETL pipeline to Xenoforge.xyz. The dashboard glows green. Then the cloud bill arrives — and it's 40% higher than your cost model predicted. Duplicate data doesn't sit silently; it burns credits on both ends. Every extra row you write to your data warehouse costs storage, yes, but also compute for transformation jobs that reprocess the same record. Worse: your storage layer starts accumulating massive staging tables that never get cleaned. I have seen teams burn $12,000 in a single week because an append-only sink swallowed three identical copies of a customer transaction table. The math is brutal: if your migration doubled a 500 GB pipeline and you're paying $0.02 per GB for storage plus $5 per TB scanned, you're not just wasting pennies — you're funding a silent credit fire.

Downstream chaos: reports double, alerts misfire

Duplicate records hit reporting like a wrecking ball. A revenue dashboard that shows $2M instead of $1M because every order row appears twice? That's not a minor rounding error — that's a boardroom credibility gap. When your sales team sees deals double-counted, they stop trusting the data. And then the alerts go haywire. Anomaly detection systems flag spikes that never happened. Fraud models start blocking legitimate transactions because the same payment hash appears multiple times. The tricky bit is that no single person notices until the damage compounds. Most teams don't catch duplication until the third week — by then, downstream consumers have already built workarounds, manual spreadsheets, and quiet resentment.

'A duplicate row is like a ghost in the machine — it looks real until you try to reconcile the total.'

— Senior data engineer reflecting on a post-migration incident that cost a fintech client 72 hours of manual cleanup

Trust erosion: business users stop believing data

Nothing kills a migration faster than lost faith. When your finance team runs a month-end close and discovers that the customer count jumped 30% overnight — with no new signups — they don't debug your pipeline. They email your VP. And they start keeping their own shadow records. That hurts. A single double-load event can poison six months of migration goodwill. We fixed this once by triaging the sink before fixing the source — that cut the burn rate in half, but the trust damage was already done. The catch is that business users don't care about idempotency keys or load semantics. They care that the dashboard lied to them on Tuesday. Once that happens, every number you present gets questioned. You'll spend more time defending your data than analyzing it.

The Core Problem: Idempotency Was an Afterthought

What Idempotency Actually Means for ETL

Idempotency sounds like academic jargon until you're staring at a $12,000 overage bill. In plain terms: an idempotent pipeline can run twice — or ten times — and land exactly the same data state. Run it once, you get one copy. Run it again, you get one copy. No duplicates, no inflation. Most legacy pipelines never bothered with this property because they ran on a strict schedule with human babysitters. The batch window was Tuesday at 3 AM, and if it failed, someone manually kicked it Wednesday. That tolerance for slop dies the moment you migrate to cloud-native execution where retries are automatic, parallel workers spawn freely, and nobody watches the logs at 3 AM.

Why Legacy Systems Tolerated Duplicates

Your old on-prem ETL had a secret advantage: it was slow and brittle. A full load took six hours, the database locked, and retries were rare because failures were catastrophic — you'd page a DBA before attempting a second run. The architecture assumed single-shot execution. Migrate that same logic to a cloud platform with auto-scaling and spot instances, and suddenly the pipeline runs three times in parallel because of a transient network blip. The old code has no guardrails for this. It just appends. That's the core problem: the migration preserved the transformation logic but not the execution constraints that accidentally prevented duplicates.

'We migrated everything exactly as it was — including the bugs we never knew were bugs because they never had the chance to fire.'

— Lead engineer from a FinTech migration post-mortem, reflecting on why their sink table swelled 3× in the first week

Three Types of Idempotency Failure

I have seen three distinct flavors of this failure, and they rarely arrive alone. First: upsert logic that only works on primary keys — your merge statement checks for matching IDs, but the source system changed the key structure during migration, so every incoming row looks new. Suddenly your daily load writes 500,000 fresh records when it should have updated 500,000 existing ones. Second: offset-based extraction that resets on failure — the source database restarts its change-tracking cursor after a timeout, re-emitting yesterday's data alongside today's. The pipeline treats both as legitimate new rows. Third: retry amplification — a single data chunk fails, the scheduler retries the entire batch, and your sink receives the first 90% of records twice. The catch is that all three failures look identical in the final table: more rows than expected, no error logs, and a steadily climbing cost curve.

Most teams skip this diagnosis step. They see duplicates and immediately blame the sink — wrong order. The sink is usually innocent; it's doing exactly what you told it to do. The real fault lives upstream in assumptions about how many times a record can arrive. Worth flagging — fixing idempotency after migration almost always requires touching the extraction or transformation layer, not just the write destination. That means more code changes, more testing, and a harder conversation with stakeholders who want a quick S3 dedup bandage. That bandage won't stick. You'll be burning credits again next month.

Step 1: Check Your Sink — Is It Append-Only?

Append vs. Upsert Sinks — You Paid for Both, But Only One Works

Most teams don't think about sink semantics until the cloud bill arrives. You've got a target — BigQuery, Snowflake, Redshift — and your pipeline writer is set to INSERT. That's append-only. Every micro-batch, every retry, every backfill lands new rows. No merge logic, no dedup key. Just endless stacking. I've walked into war rooms where the engineering lead swore they had upsert configured — turns out they were using a connector that only does insert under the hood. The sink looked like it supported merge; the pipeline never called it. That mismatch is where duplicate data breeds. You can scream at the pipeline all day, but if the sink treats every record as a new guest at the door, you're paying for the party twice.

The catch is that append-only isn't inherently wrong. Good use cases exist: immutable event logs, raw ingestion for reprocessing. But if your pipeline migrated from a legacy system that used staging tables with truncate-and-reload, and your new sink is append-only without a dedup step? You're burning credits monthly. Wrong order. The sink should match the design intent. Check your connector documentation — not the marketing page, the actual sink.properties or YAML config. Look for mode: append or writeDisposition: WRITE_APPEND. That's the smoking gun.

Flag this for data: shortcuts cost a day.

Flag this for data: shortcuts cost a day.

How Missing Merge Logic Doubles Rows on Every Retry

Here's a concrete scenario I helped untangle last quarter. A team migrated from a nightly batch job to a streaming pipeline. The old system used temp tables — drop, create, insert, swap. Clean. The new system used Kafka Connect with a JDBC sink. Sink mode: insert. Every time the pipeline restarted after a transient failure — and it restarted four times one Tuesday — it pushed the same 200K rows into the target. No merge key, no upsert. The result: 800K rows instead of 200K. That hurts. Missing merge logic doesn't just double rows; it multiplies them by the number of runs. A merge clause on a unique key (or a MERGE INTO if your warehouse supports it) turns that explosion into a single row per unique record. Without it, your retry policy becomes a copier instead of a fixer.

Most teams skip this: verifying that the sink actually executes upsert statements. Many connectors claim "upsert support" but default to insert on conflict — silently. Worth flagging — I've seen a Snowpipe configuration that looked like merge in the UI but was actually replaying files because the dedup logic lived upstream, not in the sink. That upstream logic got lost during migration. Oops. You don't need a PhD in data engineering to catch this. Just run a count-distinct on your target's natural key versus total row count. If they diverge, your merge logic is a ghost.

Quick Audit: Query Your Target Table

Stop guessing. Run this immediately on your target table:

'SELECT key_column, COUNT(*) as dup_count FROM your_table GROUP BY key_column HAVING COUNT(*) > 1 ORDER BY dup_count DESC LIMIT 10;'

— standard dedup query, adapt key to your business identifier

If that returns any rows, your sink is append-only or your merge condition is broken. The duplicate count tells you how many times the pipeline has run while thinking it was idempotent. I've seen teams waste a week optimizing shuffle partitions before running that one query. Premature. The root cause was staring at them from a 30-second SQL scan. What usually breaks first is the assumption that migration preserved merge logic — it often doesn't. The new system might use different connector libraries, different offset management, or a different dialect for upsert statements. Legacy systems sometimes relied on database triggers or stored procedures that didn't survive the move. Your audit isn't done until you also check whether the sink's upsertKey matches your actual business key — not a surrogate column that shifts between runs. That's a subtle killer: a pipeline merging on row_hash but writing append-only because the connector treats row_hash as an output column, not a merge key. That'll look like dedup works until you inspect the rows. Duplicates with different hash values. You'll chase that for days. Don't.

Step 2: Audit Your Incremental Load Keys

Off-by-One Errors in Watermark Columns

The most common incremental load key bug I see looks dead simple on paper. Your source table has a last_updated timestamp. Your pipeline reads rows where last_updated >= last_run_timestamp. That >= is the trap. Say your last run finished at 2024-11-15 23:59:59.000. The next run picks up everything from that same millisecond onward. If the previous run wrote records exactly at 23:59:59.000 and the sink committed them a few milliseconds late — bam, you re-ingest the same rows. The sink commits, the watermark advances, but the window overlaps.

We fixed this by switching to > (strictly greater) and storing the watermark as the maximum last_updated value from the previous successful extraction — not the pipeline start time. That sounds obvious. Most teams don't do it. They store the runtime timestamp, which drifts relative to the source data's clock domain. One server's 23:59:59.000 is another's 00:00:00.001. You lose a day to duplicates before anyone notices the bill.

Duplicate Windows Due to Timestamp Precision

Here's the one that burned 400 dollars in a single weekend for a client. Their source database stored timestamps with microsecond precision. Their extraction tool truncated to milliseconds. A batch of orders landed at 2024-11-16 14:22:33.456789. The pipeline read it as 14:22:33.456, processed it, and advanced the watermark to 14:22:33.456. Next run picked up rows where last_updated >= '14:22:33.456'. That .456789 row? Still greater than the watermark. Every run re-read that order until a human noticed the storage costs climbing. The catch is — your sink might deduplicate within a single run, but across runs it sees a "new" row because the source timestamp shifted precision.

'We lost three percent of our monthly cloud budget to a truncation operator we didn't know existed.'

— Data engineer, mid-market retail analytics team, after a post-mortem I attended

The fix isn't beautiful: store watermarks at the source's native precision, or pad the window with a safety offset of 1 second. That said, padding introduces its own risk — you might skip genuinely new rows if the offset is too tight. Trade-off: precision overhead versus cloud credit hemorrhage. I'd take the overhead.

Example: Fixing a Date-Based Offset

Most teams skip this: they use WHERE load_date >= CURRENT_DATE - 1 and call it done. Wrong order. That loads yesterday and today simultaneously, and if the pipeline runs twice in a day — which retry logic encourages — you double-insert every row from 00:00:00 onward. Instead, bind the offset to the last successful closed partition. If you process daily batches, set your watermark to the last fully-committed date minus one second. Not the current time. Not the last row's timestamp. The last closed partition boundary. That hurts because it forces you to track pipeline state beyond a simple number — you need to know which partitions actually landed without error. But that's the difference between a pipeline that burns credits and one that doesn't.

Field note: data plans crack at handoff.

Field note: data plans crack at handoff.

Your first action after reading this: open your extraction query. Look for >= in the watermark filter. If you see it, you likely have duplicates waiting to surface. Replace it with > and set the value to the max timestamp from the last successful batch, not the scheduler's clock. Test it on a copy first — one wrong offset and you'll skip rows. That's better than double-paying for them.

When Retry Logic Backfires: The Duplicate Run

Retries Without Dedup at the Pipeline Level

Most teams slap retry logic onto their ETL jobs believing it's safety net — in reality, it's often a duplicate factory. I've watched a well-meaning Airflow DAG retry a failed sink three times, each rerun landing the same 40,000 rows into BigQuery. The original failure? A transient network blip. The result? 120,000 rows where 40,000 should live. The retry handler never checked whether any records from that batch already existed. It just replayed the entire extract. That's the core sin: treating retries as pure replay instead of state-aware recovery. You need a dedup check before your sink commit, or at minimum a transaction boundary that knows "this batch ID already succeeded." Without it, your retry logic isn't resilience — it's a credit-burning loop.

Checkpointing Failures and Partial Writes

The trickier scenario: your job checkpointed the source read but not the sink write. Partial failure midway through a batch leaves 5,000 rows written and the job retries from the saved checkpoint — which re-reads and re-writes those same 5,000 rows plus the remaining 15,000. Now you're double-counting. I fixed a Spark streaming job once where the checkpoint location was pointing at a staging path that got cleaned nightly. The retry simply created a fresh checkpoint offset, replayed the entire micro-batch, and doubled a week's worth of clickstream data. The fix was brutal: enforce that checkpoint and sink commit happen atomically, or build a dedup key into the target table that rejects re-insertion of the same batch_id. Worth flagging — partial writes hurt worse than full failures, because you get silence until the cloud bill arrives.

“Retry without dedup is just a more expensive way to be wrong.”

— senior data engineer after untangling a $12k duplicate run on Redshift

Case: Spark Job Retry Creates 2x Rows

Here's one I personally debugged. A Spark structured streaming job reading from Kafka lost its driver mid-batch. On restart, the checkpoint remembered the last committed offset — but the sink (a JDBC table) had already received half the batch before the crash. The retry re-delivered the full Kafka offset range. No primary key checks, no upsert logic — just blind appends. That table jumped from 2 million to 4 million rows overnight. The pipeline's SLA was 15-minute latency; the fix took three days of dedup queries and refund negotiations. What usually breaks first is the assumption that checkpointing guarantees exactly-once semantics. It doesn't. Checkpointing guarantees source exactly-once. The sink is your problem. You want idempotent writes at the destination — merge statements, upsert keys, or a version column that rejects older runs. Without that, one Spark failure can double your storage costs before anyone notices. That hurts.

One rhetorical question worth asking: would your retry logic survive a mid-batch power loss? Most can't. Test that scenario before you need it.

What Not to Fix First: Premature Optimization Traps

Don't rewrite the whole pipeline

Your first instinct after discovering duplicate rows—and the cloud bill climbing—is to torch the entire migration and start over. I get it. I have seen teams burn two weeks rebuilding a Spark job that was actually fine, except for one missing dedup key. The trap is emotional: you think the architecture is broken, but usually it's a single configuration flag or a sink setting that's wrong. Rewriting buys you nothing but a fresh set of bugs and a delay that lets the duplicate problem fester. Instead, isolate the sink behaviour. Check if your target table is append-only or if it supports upsert. Nine times out of ten, the fix is simpler than a rewrite—and way cheaper.

Don't add complex dedup logic at query time

Another common overreaction: slapping ROW_NUMBER() OVER (PARTITION BY … ORDER BY ingestion_ts DESC) onto every downstream query. That sounds fine until your BI team complains that dashboards load three minutes slower and your data warehouse compute costs double. Query-time dedup is a bandage, not a fix. It hides the root cause—your pipeline is still emitting duplicates—and punishes every read with extra work. Worth flagging: I once watched a team add a five-table join just to collapse duplicates. They never fixed the load key. The seam blew out three weeks later when a backfill ran. The smarter path is to stop duplicates at the source or at the sink, not in every SQL statement your analysts write.

Focus on source-level dedup first

Most teams skip this: look at what your source is sending. Is your CDC connector emitting the same event twice because of a network retry? Is your API extract pulling overlapping date ranges? The catch is—if you fix dedup at the source, every downstream consumer benefits automatically. No schema changes, no query rewrites, no midnight calls about "why are my counts off by 12%." Start by checking your incremental load keys: are they truly unique per record? Does your watermark column tick forward monotonically? If not, that's your first fix, not adding a distributed lock or a Redis dedup cache. Those are premature optimizations that mask a broken key design.

“We spent three days building a bloom filter to catch duplicates. Turned out our source timestamp had millisecond precision loss.”

— engineer who wishes they'd checked the schema first

That hurts. But it's the pattern: complexity first, fundamentals last. Don't be that team. Your first three actions after reading this? Audit your sink mode, validate your load key uniqueness, and disable any retry logic until you confirm idempotency. Everything else—caching layers, materialized views, fancy window functions—can wait. They're traps until the basics hold.

Frequently Asked Questions About Duplicate Data

Can I deduplicate after ingestion?

Technically yes — practically it's a bandage, not a fix. Running a batch dedup after every load works if your data volume is small and your team has spare cycles. But here's the catch: dedup after ingestion doesn't stop the cost burn. You still pay for storing, scanning, and transforming those duplicate rows. I have seen teams slap a nightly dedup job on a Snowflake table and call it done. Two weeks later, their warehouse bill jumped 40% — because the dedup itself consumed credits. The better path is stopping duplicates at the sink, not cleaning up the mess after. If you absolutely must post-process, use a MERGE or INSERT OVERWRITE with a unique key — but set a budget alert first. That hurts less.

Odd bit about data: the dull step fails first.

Odd bit about data: the dull step fails first.

Should I use DISTINCT in downstream queries?

Only if you hate your analysts. DISTINCT in a SELECT masks the root problem: your pipeline is broken. It also adds a full table scan or shuffle on every query — which burns credits faster than the original duplicate writes. Most teams skip this: DISTINCT doesn't fix inserts, it hides them. Your BI tool still shows correct numbers, but your cost-per-query triples. Worse, if your dedup logic uses ROW_NUMBER() OVER (PARTITION BY ...) in a view, you're now paying compute on every read. The real fix is upstream — make the pipeline write once. A quick em-dash aside: DISTINCT is fine for ad-hoc exploration, not production. Not yet.

How do I set up idempotency checks?

Start with your incremental load keys — the column or combination that defines "this row already exists." Common candidates are: business date + record ID, source system transaction hash, or a monotonically increasing sequence number. The tricky bit is testing them under retry scenarios. I fixed this once by adding a pre-check query before the sink write: SELECT COUNT(*) FROM target WHERE load_key IN (SELECT load_key FROM staging). If the count matches the staging row count, skip the write entirely. That sounds fine until your retry logic runs the entire batch again — then you need a MERGE with an upsert condition, not a blind INSERT. Wrong order. Set the idempotency key as a composite unique constraint in the database first, then validate it in the pipeline code. One concrete anecdote: a client burned $6k in a weekend because their Airflow retry loop replayed a 2-hour window three times. The sink table had no unique constraint. Three inserts of the same 12M rows. The seam blows out fast.

Idempotency isn't a feature you bolt on later — it's the only thing between your pipeline and an infinite cost loop.

— paraphrase from a production ops engineer who learned the hard way

Your First Three Actions After Reading This

Audit sink table for duplicates — right now

Pull a simple SELECT COUNT(*) FROM sink WHERE … grouped by your natural key columns. If any group returns more than one row, you’ve confirmed the bleed. I’ve seen teams waste a week tuning memory settings when the real fix was a DISTINCT on the write side. That hurts.

The catch is that counting alone doesn't tell you why. Was it a retry? A missing watermark? Or is your sink genuinely append-only with no de-dupe logic? Most cloud warehouses charge per scan — so run this query once, not every hour. Worth flagging: if your sink is a blob store like S3, duplicate rows are invisible until you read them. You're burning credits on storage and compute.

Verify incremental load keys — they're probably wrong

Open your pipeline config. What timestamp or sequence column drives the WHERE updated_at > last_run filter? If that column allows NULL or gets backfilled after the run, you’ll re-ingest old rows every cycle. One client had a modified_at field that the source team updated at batch time — not at row time — so every load grabbed yesterday's data plus today's.

Fix this: add a last_modified > (SELECT MAX(last_modified) FROM sink) or, better, a monotonically increasing offset that survives pipeline restarts. A rhetorical question here — how many duplicates have you accepted because 'the source timestamp is trustworthy'?

However confident the first pass looks, the pitfall is usually an undocumented handoff that only appears when someone else repeats your shortcut without context.

Most aren't. Test with a three-day window: if counts drift upward, your key is leaky.

Add a simple idempotency check — before the write

Don't architect a full dedup service. Start with a MERGE or INSERT … ON CONFLICT DO NOTHING at the sink. That single statement stops duplicate rows from landing, even if your upstream fires twice. We did this for a fintech pipeline that re-ran nightly after timeouts — credits dropped 40% in one week.

‘Idempotency isn’t a feature. It’s a contract the pipeline owes your wallet.’

— senior data engineer, post-mortem on a $12k overrun

The trade-off: MERGE can slow down high-volume writes if your natural key lacks an index. Benchmark with representative data — not a 100-row test. If latency spikes, fall back to a pre-insert dedup check using a sorted temp table. That said, a half-second delay per batch beats a month of unexplained charges. Your first action