[Python] Timestamp precision mismatch when reading Delta table from ADLS Gen2 with Python...

Problem Description


I'm experiencing timestamp precision issues when reading Delta tables created by Azure Data Factory CDC operations. The pipeline extracts data from Azure SQL Database to Azure Data Lake Gen2 as Delta format, but reading the Delta table with Python libraries fails due to timestamp precision mismatches.

Environment

• Source: Azure SQL Database (CDC incremental extract)
• Destination: Azure Data Lake Gen2 (Delta table format)
• ETL Tool: Azure Data Factory
• Reading Libraries: Python with pandas/polars + deltalake + pyarrow

Code and Errors

Pandas Approach


from deltalake import DeltaTable

delta_table_uri = f"abfss://{container}@{account_name}.dfs.core.windows.net/{table_path}"
dt = DeltaTable(delta_table_uri)
df = dt.to_pandas()


Error:

ArrowInvalid: Casting from timestamp[ns] to timestamp[us, tz=UTC] would lose data: 4379020357742690304

Polars Approach


import polars as pl

df = pl.read_delta(delta_table_uri, storage_options=storage_options)


Error:

SchemaError: data type mismatch for column DeliveryDate: expected: datetime[μs, UTC], found: datetime[ns]

Analysis

1. Azure SQL Database datetime2 columns are set to precision 3 (millisecond precision)
2. The Delta table schema shows microsecond precision (datetime[μs, UTC])
3. However, the actual data appears to be stored as nanosecond precision (timestamp[ns])
4. This suggests Azure Data Factory/Spark may be writing timestamps using int96 format, causing precision escalation

What I've Tried

• Verified that the source SQL Server timestamps are millisecond precision (datetime2(3))
• Confirmed the Delta table schema reports microsecond precision
• Researched Spark's int96 timestamp storage format

Questions

1. 
   Root Cause: Why is there a mismatch between the Delta table's reported schema (microseconds) and actual data precision (nanoseconds)?
   
   
2. 
   Azure Data Factory: Is there a setting in ADF to control timestamp precision when writing to Delta format?
   
   
3. 
   Python Workaround: How can I read this Delta table in Python while handling the precision mismatch? Are there specific parameters for deltalake or polars to handle this?
   
   
4. 
   Long-term Solution: What's the recommended approach to ensure timestamp precision consistency in this Azure ecosystem?
   

Additional Context

• The Delta table was created through Azure Data Factory's built-in Delta format support
• This issue occurs specifically with datetime columns from the CDC process
• Other data types read successfully from the same Delta table

Any insights on resolving this timestamp precision issue would be greatly appreciated!

Continue reading...