Timezone Mismatch Caused Duplicate Rows in Our Client's Daily ETL

The Incident: Duplicate Rows in the Client's Daily ETL

It started on a Tuesday morning. Our client's daily ETL pipeline, which had been running clean for two weeks, suddenly started producing duplicate rows in their Snowflake target table. The duplicates were not random. They were exact copies of rows that had already been loaded the previous day. The business_id and event_time columns matched perfectly, but the row_id was different. This was a classic sign of an incremental load gone wrong.

We pulled the logs and traced the issue back to the watermark column used for incremental loads. The source system was in IST (Indian Standard Time), but the target system in Snowflake was configured to use UTC. The watermark comparison was being done against a TIMESTAMP_TZ column, but the source data was stored as TIMESTAMP_NTZ (no timezone). This mismatch caused Snowflake to apply the session timezone offset, leading to incorrect comparisons and duplicate inserts.

The client's data engineering team had assumed that the source timestamps were in UTC. They were not. The source system, an on-premise Oracle database, was writing event timestamps in IST. When the ETL pipeline read these timestamps and compared them against the watermark in Snowflake, the 5.5-hour offset caused previously processed records to be treated as new.

This was not the first time we had seen this issue. A similar bug was reported in the dbt-snowflake repository, where microbatch timestamps were being recognized as TIMESTAMP_NTZ instead of TIMESTAMP_TZ, causing incorrect and duplicative rows to be loaded [Bug] Microbatch timestamps incorrectly offset for system timezone in Snowflake · Issue #1256 · dbt-labs/dbt-snowflake](https://github.com/dbt-labs/dbt-snowflake/issues/1256).

What We Tried and What Failed

Our first instinct was to add manual timezone conversion logic in the ETL pipeline. We modified the dbt model to explicitly convert the source timestamps from IST to UTC before the watermark comparison. This approach seemed logical, but it failed because the source data contained timestamps stored as TIMESTAMP_NTZ in Snowflake. When we tried to convert these timestamps, Snowflake applied the session timezone offset, which was set to UTC. This caused the conversion to be off by 5.5 hours, leading to the same duplicate row issue.

We also tried setting the session timezone to IST to match the source system. This did not work either. The problem was that the watermark comparison was still being done against a TIMESTAMP_TZ column, and the source data was TIMESTAMP_NTZ. The mismatch in data types caused Snowflake to apply the session timezone offset, leading to incorrect comparisons.

A similar issue was documented in the dbt-adapters repository, where dates were not being evaluated equally in microbatching for deletion and view creation, leading to duplicate rows in the target table [Bug] Dates aren't evaluated equally in Microbatching for deletion and view creation](https://github.com/dbt-labs/dbt-adapters/issues/955).

The Working Approach: Real Commands and Fixes

We resolved the issue by taking a more systematic approach. Here are the steps we followed:

1. Standardized all timestamps to UTC at the source level before ingestion. We modified the ETL pipeline to explicitly convert all source timestamps from IST to UTC before loading them into Snowflake. This ensured that all timestamps were in a consistent timezone.

2. Modified the dbt microbatch materialization to use TIMESTAMP_TZ consistently. We updated the dbt model configuration to use TIMESTAMP_TZ for all timestamp columns, ensuring that the data type was consistent across the pipeline.

3. Overrode the snowflake__get_incremental_microbatch_sql macro to remove the problematic to_timestamp_tz cast. We created a local macro that removed the to_timestamp_tz cast, which was causing the offset. This macro was preferred by dbt over the one provided by the dbt-snowflake adapter.

4. Added a post-load validation step using a SQL query to detect and block duplicate inserts. We created a validation query that checked for duplicate rows based on a composite key of business_id and event_time. If duplicates were found, the pipeline would fail and alert the team.

Here is the key command we used to set the session timezone:

ALTER SESSION SET TIMEZONE = 'UTC';

We also modified the file at /models/staging/events.sql to ensure that all timestamps were converted to UTC before ingestion.

The fix was inspired by a similar approach documented in the dbt-snowflake repository, where a user modified the snowflake__get_incremental_microbatch_sql macro to remove the problematic timestamp cast [Bug] Microbatch timestamps incorrectly offset for system timezone in Snowflake · Issue #1256 · dbt-labs/dbt-snowflake](https://github.com/dbt-labs/dbt-snowflake/issues/1256).

Pitfalls We Would Warn an Intern About

• Never assume source timestamps are in UTC. Always verify the timezone context of the source system. A similar issue was reported by Mahesh Basavaraju, where a timezone mismatch in the watermark column caused duplicate records in an Azure Data Factory pipeline The Incremental Load That Duplicated Data, Until We Found the Timezone Trap | Mahesh Basavaraju.

• Be cautious when using dbt microbatch materializations with Snowflake. The default behavior may not handle timezone offsets correctly. The dbt-snowflake adapter has a known bug where microbatch timestamps are being recognized as TIMESTAMP_NTZ instead of TIMESTAMP_TZ, causing incorrect and duplicative rows to be loaded [Bug] Microbatch timestamps incorrectly offset for system timezone in Snowflake · Issue #1256 · dbt-labs/dbt-snowflake](https://github.com/dbt-labs/dbt-snowflake/issues/1256).

• Always test incremental loads with a lookback period. This helps catch duplicate row issues early. A similar lesson was learned by Mahesh Basavaraju, where duplicate records were showing up every day in Azure Data Lake Storage after two weeks of smooth runs The Incremental Load That Duplicated Records | Mahesh Basavaraju.

• Do not rely on session-level timezone settings alone. Enforce UTC at the data level. A timezone mismatch between the Java Virtual Machine (JVM) and the MySQL server can cause duplicate entry errors in Cloudera Data Explorer Duplicate entry errors in Cloudera Data Explorer (Hue) Query Processor.

What We Would Do Differently Next Time

Next time, we would take a more proactive approach to prevent this issue from occurring:

• Implement timezone normalization at the data ingestion layer. Rather than handling timezone conversion in the transformation layer, we would normalize all timestamps to UTC at the point of ingestion. This would ensure that all downstream processes receive data in a consistent timezone.

• Use a dedicated watermark validation table. We would create a table to track processed batches and prevent reprocessing. This would act as a safeguard against duplicate inserts.

• Add automated tests in dbt. We would create tests to verify that no duplicate rows are introduced after each incremental run. This would catch issues early in the development cycle.

• Consider using change data capture (CDC) instead of watermark-based incremental loads. CDC provides a more reliable way to track changes and can help avoid issues related to timezone mismatches and duplicate rows.

A similar approach was taken by Nand Jha, who added explicit timezone assertions and reconciliation steps to prevent timezone-related issues during a migration from a legacy system to Azure Databricks The time a timezone mismatch cost us 3 days of wrong reports | Nand Jha.

Conclusion

Timezone mismatches are a silent killer in data pipelines. They do not crash your system. They just make the numbers look close enough to trust. This incident taught us the importance of verifying timezone contexts, using consistent data types, and implementing robust validation steps. By taking a systematic approach to timezone normalization and incremental load validation, we were able to resolve the issue and restore confidence in the client's daily ETL pipeline.

The scariest bugs are not the ones that crash your pipeline. They are the ones that make the numbers look "close enough" to trust. As data engineers, we must be vigilant about these silent failures and build systems that are resilient to them.

---

Pratap Singh is the founder-engineer at Agentic Academy Labs in Sikar, India. The company ships custom AI and full-stack products, and runs a hands-on developer internship.