Null Timestamps and IST Timezone Drift Broke Our Client Pipeline

The Incident: Null Timestamps and IST Timezone Drift Broke Our Client Pipeline

The Setup: A Routine Client Migration

Week of 2026-01-13. We were migrating a mid-sized Indian e-commerce platform's daily transaction pipeline from legacy MySQL to Snowflake using Airflow 2.4, Python 3.9, PyArrow, and the Snowflake Connector. The client expected clean daily revenue reports by Monday morning.

What We Tried First (And What Failed)

Our first DAG assumed source timestamps were UTC. We used pd.to_datetime(df['event_time']) without timezone parsing and loaded data into Snowflake with TIMESTAMP_NTZ columns. By Thursday, the analytics team flagged that daily revenue reports were off by 5.5 hours. Worse, null timestamps were silently coerced to 1970-01-01 by PyArrow, and no validation layer caught the drift.

The Root Cause: Two Silent Failures

Two issues compounded: IST vs UTC mismatch and null handling. The legacy MySQL stored timestamps in IST (UTC+5:30) but carried no timezone metadata. PyArrow converted blank timestamp fields to epoch zero instead of NULL. On top of that, duplicate transaction IDs appeared across multiple daily batches, inflating counts.

The Working Fix: Real Commands and File Paths

We added explicit timezone parsing with pd.to_datetime(df['event_time'], utc=True, errors='coerce'), changed Snowflake columns to TIMESTAMP_LTZ with session timezone set to Asia/Kolkata, quarantined null timestamps using df_nulls = df[df['event_time'].isna()], and deduplicated by transaction ID plus date with df.drop_duplicates(subset=['txn_id', 'event_date']). The Airflow DAG lives at /opt/airflow/dags/client_migration.py. Our validation query in Snowflake became SELECT COUNT(*) FROM raw_transactions WHERE event_time < '1970-01-02'.

Pitfalls We Would Warn an Intern About

Never assume source timezone. Always ask the DBA or source team. PyArrow silently converts null timestamps to epoch zero. Use errors='coerce'. TIMESTAMP_NTZ in Snowflake ignores session timezone. Use TIMESTAMP_LTZ for local time. Test with boundary data: 11:55 PM IST and 12:05 AM IST records. Check for duplicate keys before loading. Dedup logic must include date context.

What We Would Do Differently Next Time

Add a timezone assertion step in the DAG that fails if source TZ is not declared. Implement a reconciliation step comparing daily aggregates between old and new systems for 2 weeks. Use a quarantine table in Snowflake for invalid records instead of dropping them. Add schema validation with Great Expectations before any transformation. Document the source system timezone in DAG comments and the data dictionary. Schedule a post-migration review with the client to confirm report accuracy.

Why This Matters

Timezone and null timestamp bugs are the silent killers of data pipelines. They do not crash systems. They shift data just enough to make people nervous, as Nand Jha observed during a similar migration to Azure Databricks The time a timezone mismatch cost us 3 days of wrong reports. Every event after 5:30 PM IST was assigned to the next calendar day. The monthly total was fine. The daily split was completely off.

The same pattern appears in CDC pipelines where MSSQL date/time types do not carry timezone information, and Debezium interprets these values as UTC internally Resolving Timezone Drift in Debezium CDC Pipelines. A custom conversion layer introduced ambiguity when combined with Debezium internal handling.

Concrete Steps That Saved Us

1. Parse timestamps with UTC awareness and coerce errors.
2. Use TIMESTAMP_LTZ in Snowflake with Asia/Kolkata session timezone.
3. Quarantine nulls before loading.
4. Deduplicate by transaction ID and date.
5. Validate with boundary data at 11:55 PM and 12:05 AM IST.

import pandas as pd

df['event_time'] = pd.to_datetime(df['event_time'], utc=True, errors='coerce')
df_nulls = df[df['event_time'].isna()]
df = df.dropna(subset=['event_time'])
df = df.drop_duplicates(subset=['txn_id', 'event_date'])

ALTER SESSION SET TIMEZONE = 'Asia/Kolkata';
SELECT COUNT(*) FROM raw_transactions WHERE event_time < '1970-01-02';

Lessons for Interns

• Always ask the source team what timezone they write in.
• PyArrow null timestamp coercion to epoch zero is dangerous.
• TIMESTAMP_NTZ ignores session timezone in Snowflake.
• Test boundary data at midnight IST.
• Check duplicate keys before loading.

What We Do Now

Every ingestion pipeline has an explicit timezone assertion. If the source does not declare it, we do not assume. We ask. We test with date boundary data. We add reconciliation steps comparing daily aggregates for 2 weeks post-migration. We quarantine invalid records instead of dropping them. We validate schemas with Great Expectations before transformation. We document source timezones in DAG comments and data dictionaries.

As Shreyansh Kesharwani noted, most production issues happen because upstream systems silently change timestamp formats, datatypes, null handling patterns, or source file structures Debugging a Production Issue in Databricks. Strong validation layers and defensive coding are as important as transformation logic.

Integrate.io recommends defining explicit behavior for null and unparseable timestamps: reject the row, substitute NULL, log to a dead-letter queue, or use a configurable sentinel Top 7 ETL Tools for Timestamp and Timezone Normalization. NULL is generally the safest default.

Tools-Hut reminds us that a proper timezone is a named region with rules, not just an offset Timezone Bugs That Bite. India does not observe DST, but your servers might be in places that do.

The scariest bugs are not the ones that crash your pipeline. They are the ones that make the numbers look almost right.

Sources

• The time a timezone mismatch cost us 3 days of wrong reports | Nand Jha
• Resolving Timezone Drift in Debezium CDC Pipelines
• Debugging a Production Issue in Databricks | Shreyansh Kesharwani
• Top 7 ETL Tools for Timestamp and Timezone Normalization | Integrate.io
• Timezone Bugs That Bite | Tools-Hut