A Stale Watermark Column Hid 48 Hours of Duplicate Rows

A Stale Watermark Column Hid 48 Hours of Duplicate Rows

A client called us on a Tuesday morning in March 2026. Their reporting team had noticed that daily revenue totals in the Gold layer of their Microsoft Fabric warehouse were roughly double what the source system reported. The numbers were not wildly off. They were almost exactly twice as large for a sliding 48-hour window. Nobody was crashing. Nobody was alerting. The pipeline was green.

What we found was a stale watermark column stuck in IST while the destination stored UTC, an append-only Gold table, and zero assertions. Here is how it happened and how we fixed it.

The Incident: 48 Hours of Silent Duplicates in a Client Gold Table

What the client reported

The client noticed the problem when a finance analyst ran a simple SUM against the Gold table for the last two weeks of transaction data. The total was 1.9x the source system total. Not 2.0x, because some rows were correct. But close enough to signal that something was structurally wrong.

The pipeline in question was a Fabric notebook that ran every hour. It read from a Bronze table, applied a watermark filter on a column called updated_at, and appended the result into a Gold Delta table. The notebook had been running smoothly for over two weeks before the duplicates started accumulating.

As Mahesh Basavaraju describes in his post on incremental load duplicates, this is a common pattern that goes undetected for weeks because the pipeline reports success every single run [1].

The first wrong diagnosis

Our first instinct was a late-arriving data problem. The source system was a MySQL instance, and we suspected that rows committed near the hourly boundary were being missed entirely. We checked the watermark logic and found that the notebook was filtering on updated_at > @lastWatermark. That looked correct on paper.

But when we pulled the raw Bronze data and compared timestamps side by side, we noticed something: the source updated_at values were stored in IST (UTC+05:30). The notebook read them as-is. The Gold table, however, stored all timestamps in UTC. No conversion was happening anywhere in the path.

Why the Watermark Column Went Stale

IST vs UTC in the source-to-landing path

The root cause sat in the gap between two clocks. The source application stamped rows with updated_at in IST. When the Fabric notebook read those values, it treated them as UTC. So a row stamped at 2026-03-10 23:58:00 IST was interpreted as 2026-03-10 23:58:00 UTC, which is actually 2026-03-11 05:28:00 IST.

This meant the watermark advanced faster than the actual data. After each hourly run, the watermark was set to the maximum updated_at seen, but that maximum was already 5 hours and 30 minutes ahead of where it should have been in real time.

The ScriptsHub analysis of watermark failures describes exactly this pattern: the moment a row becomes readable and the moment it is stamped are not the same instant, and clocks in different timezone worlds create silent gaps [2].

Append writes amplified the problem

The Gold table was append-only. Every hourly run that re-read a boundary row appended it again. The watermark was slightly ahead of reality, so boundary rows that should have been skipped were instead re-read and re-appended. This happened every hour for roughly 48 hours before anyone noticed.

As the Algoscale team documents, append writes are not idempotent. Run the same boundary record twice, get two copies. Do that on every run for two days and you have quietly doubled a sliver of every day's data [3].

What We Tried That Failed

Bumping the watermark cutoff by hand

Our first fix was crude. We opened the notebook parameters and bumped the watermark cutoff forward by 5 hours, hoping to skip the duplicate window. The notebook ran. The duplicates for that hour stopped. But the next hourly run still read from the old watermark position stored in the notebook state, and the problem resumed.

Manual cutoff adjustments do not work because the watermark is recomputed on every run from the stored state, not from the parameter value. We were treating a symptom.

A naive DELETE-and-reload script

Next we wrote a script that deleted all rows from the Gold table for the affected date range and reloaded from Bronze. It worked for that one date range. But the script took 47 minutes to run, and during that time the Gold table was empty. The reporting team flagged it.

A naive DELETE-and-reload also does not address the root cause. The next day, the same timezone mismatch would produce new duplicates. We needed a structural fix, not a cleanup script.

The Working Fix

Normalizing every timestamp to UTC at ingestion

We modified the notebook so that every timestamp column is explicitly converted to UTC the moment data lands in the Bronze layer. The conversion happens in a dedicated transformation cell before any watermark logic runs.

from pyspark.sql.functions import to_utc_timestamp

# Convert source IST timestamps to UTC at ingestion
bronze_df = bronze_df.withColumn(
    "updated_at_utc",
    to_utc_timestamp("updated_at", "Asia/Kolkata")
)

The watermark filter now references updated_at_utc instead of the raw updated_at. This eliminated the 5-hour 30-minute drift between source stamps and destination interpretation.

Switching to an idempotent MERGE on the business key

We replaced the append write with a MERGE operation keyed on the composite business key (order_id, line_item_id). This follows the guidance from the Algoscale analysis: idempotent writes stop new duplicates, and re-running a window becomes safe [3].

MERGE INTO Gold.transactions AS target
USING Bronze.staging AS source
ON target.order_id = source.order_id
   AND target.line_item_id = source.line_item_id
WHEN MATCHED THEN
    UPDATE SET *
WHEN NOT MATCHED THEN
    INSERT (order_id, line_item_id, amount, updated_at_utc, warehouse_loaded_at)
    VALUES (source.order_id, source.line_item_id, source.amount, source.updated_at_utc, current_timestamp());

Now whether a boundary record arrives once or ten times across reruns, the result is identical: exactly one row per business key holding the latest version.

Adding a post-load duplicate assertion in the Fabric notebook

The most important addition was a post-load assertion cell at the end of the notebook. It runs a SQL query that checks for duplicate business keys in the Gold table after every load:

SELECT order_id, line_item_id, COUNT(*) AS cnt
FROM Gold.transactions
WHERE warehouse_loaded_at >= DATEADD(hour, -2, CURRENT_TIMESTAMP())
GROUP BY order_id, line_item_id
HAVING COUNT(*) > 1;

If this query returns any rows, the notebook throws an error and fails the run. As the Algoscale team emphasizes, assertions turn silent contamination into a red pipeline [3]. The whole reason the original bug survived for three months in similar pipelines is that nothing ever said no.

We also added a check using Spark's dropDuplicatesWithinWatermark for our streaming validation path, which removes duplicate events within a configurable delay threshold [4][5].

Pitfalls We Would Warn an Intern About

Never trust the source timestamp as a load clock

The source updated_at is when the application wrote the row. It is not when the row landed in your warehouse. These are different instants, and treating them as the same is the single most common watermark bug we encounter.

As Preethi Kaluva documents, the watermark column is a timestamp from the source application, not a load clock, and that distinction kills you in multiple situations including backfills and timezone mismatches [6].

Blind spots in append-only destination tables

Append-only tables give you no protection against duplicates. If your write logic is not idempotent, every rerun or boundary overlap produces additional rows with no warning. Always prefer MERGE or upsert patterns over append when the destination must remain queryable for downstream consumers.

What We Would Do Differently Next Time

Enforce a warehouse_loaded_at column in the ingestion layer

We now stamp every row with warehouse_loaded_at at the moment it lands in the Bronze layer. The watermark filter uses this column instead of any source timestamp. This eliminates timezone drift entirely because the load clock is generated by the destination system in a single timezone.

As Preethi Kaluva recommends, a warehouse_loaded_at column catches records regardless of their source timestamp and removes clock drift from the equation [6].

Build a scheduled reconciliation query against the source

We added a daily scheduled query that compares row counts and aggregate totals between the source system and the Gold table for the previous 24 hours. If the delta exceeds zero, the pipeline sends an alert. This catches any silent duplication or data loss that the post-load assertion might miss in a narrow window.

The original bug survived 48 hours because the pipeline was green and nobody was asking the right question. A reconciliation query forces that question on a schedule.

The fix is always the same pattern: make the write idempotent, then make the pipeline prove its own output before it trusts it.

---

Sources:

[1] Mahesh Basavaraju, "The Incremental Load That Duplicated Records," LinkedIn. Link

[2] ScriptsHub, "Incremental Loads: Why Watermarks Miss Late-Arriving Data." Link

[3] Algoscale, "Watermark Bugs in Fabric Incremental Loads." Link

[4] Preethi Kaluva, "SQL Boo-Boos #8: Why My Incremental Model Missed Records," Towards Data Engineering, Medium, September 2026. Link

[5] Databricks, "dropDuplicatesWithinWatermark." Link

[6] Dibyaranjan Jena, "The Timestamp Trap: Lessons from Moving MySQL Replica Data to Snowflake via S3," Medium, June 2026. Link