How We Solved the Snowflake Parquet Timestamp Corruption Problem

TL;DR: If your timestamps look like they're from the year 37366 after loading Parquet files into Snowflake, add USE_LOGICAL_TYPE = TRUE to your file format. We'll show you exactly how.

Picture this: You've just loaded what should be a straightforward dataset into Snowflake via Parquet files. Everything looks perfect when you test with MySQL, PostgreSQL, and even when you query the Parquet files directly with DuckDB.

Then you check your Snowflake table and see this:

Expected:

2005-05-25 11:30:37

What you actually get:

37366-12-22 06:16:40

If you've been there, you know the sinking feeling. Your timestamps are completely corrupted, and you have no idea why.

We hit this exact issue with DBConvert Streams, and after diving deep into Snowflake's Parquet handling, we found both the root cause and the simple fix.

Understanding the Problem

What Is Parquet and Arrow?

For those new to these technologies:

• Parquet is a columnar storage format that's highly efficient for analytics workloads
• Apache Arrow is an in-memory data format that provides rich metadata about data types

Understanding Arrow Logical Types

Arrow separates physical storage from logical interpretation:

Physical Type: How the data is actually stored in bytes

• Example: int64 (8-byte integer)
• Value: 1117027837000

Logical Type: Metadata that tells you how to interpret those bytes

• Example: TIMESTAMP_MILLIS
• Meaning: "This int64 represents milliseconds since Unix epoch (1970-01-01)"

Without logical type metadata, a consumer seeing 1117027837000 wouldn't know:

• Is this milliseconds? Microseconds? Days? Seconds?
• Since when? Unix epoch? Some other reference point?
• Should this even be treated as a timestamp?

This metadata is exactly what Snowflake ignores by default.

The Root Cause

DBConvert Streams uses an Apache Arrow-based Parquet writer that stores timestamps in the timestamp[ms] format with the TIMESTAMP_MILLIS logical type. This approach works flawlessly with most modern data tools:

✅ MySQL/PostgreSQL sources - timestamps read correctly
✅ DuckDB - interprets Parquet files properly
✅ Apache Spark - handles the format natively
✅ Presto - reads metadata correctly
❌ Snowflake - ignores the metadata by default

Here's what happens under the hood:

1. Our Parquet writer converts 2005-05-25 11:30:37 to 1117027837000 (milliseconds since Unix epoch)
2. Physical storage: Saves this as an int64 value in the Parquet file
3. Arrow metadata: Labels this column as TIMESTAMP_MILLIS (meaning "interpret this int64 as milliseconds since epoch")
4. Most tools: Read the metadata and correctly interpret 1117027837000 as milliseconds → 2005-05-25 11:30:37
5. Snowflake (default): Ignores the TIMESTAMP_MILLIS metadata and assumes 1117027837000 represents days since epoch
6. Result: 1117027837000 days ≈ 37366 years = corrupted far-future date

The Solution

The fix is surprisingly simple and comes straight from Snowflake's own documentation. You need to tell Snowflake to respect the logical type metadata:

CREATE OR REPLACE FILE FORMAT parquet_with_timestamps
TYPE = 'PARQUET'
USE_LOGICAL_TYPE = TRUE;  -- This is the magic line

Then use this file format when loading:

COPY INTO target_table
FROM @my_stage/file.parquet
FILE_FORMAT = (FORMAT_NAME = 'parquet_with_timestamps');

That's it. With USE_LOGICAL_TYPE = TRUE, Snowflake correctly reads the Arrow logical type metadata and interprets timestamps as milliseconds rather than days.

Alternative Approaches We Tested

Before finding the correct solution, we tested several workarounds:

Note: Both TIMESTAMP_MILLIS and TIMESTAMP_MICROS are Arrow logical types that tell consumers how to interpret the underlying int64 values. The issue wasn't the logical type itself, but Snowflake ignoring all logical type metadata by default.

The SQL transformation approach looked like this:

COPY INTO target_table
FROM (
  SELECT TO_TIMESTAMP(timestamp_col / 1000) AS timestamp_col, other_cols
  FROM @stage/file.parquet
)
FILE_FORMAT = (TYPE = PARQUET);

While functional, this approach requires manual column mapping for every table—not viable for our automated replication platform.

Why This Solution Is Superior

✅ Correct native timestamps — no more corrupted dates
✅ Optimal storage efficiency — 8 bytes per timestamp
✅ High performance — leverages Snowflake's native timestamp operations
✅ Universal compatibility — same Parquet files work across DuckDB, Spark, Presto, and Snowflake
✅ Zero data transformation — direct load without manipulation

How We Discovered This

The issue became apparent when testing with the Sakila sample database (16,044 rows of payment data). Every timestamp in the payment table showed dates in the year 37366—clearly wrong for a DVD rental system circa 2005!

The solution came from the Snowflake Community documentation: How to load logical type TIMESTAMP data from Parquet files into Snowflake.

Key Takeaways

1. Default isn't always safe — Snowflake's default Parquet handling can silently corrupt timestamp data
2. Read the metadata — Modern data formats include rich type information for a reason
3. Test with realistic data — Edge cases reveal problems that synthetic data might miss
4. Community resources are gold — Sometimes the answer is already documented, just not where you expect

What's Next

We're incorporating this fix into the upcoming DBConvert Streams release with native Snowflake target support. The new version will:

• Include USE_LOGICAL_TYPE = TRUE by default for all Snowflake operations (ensuring proper handling of timestamps, decimals, dates, and other Parquet logical types)
• Support direct Parquet output to Snowflake stages
• Handle timestamp precision automatically
• Provide built-in Snowflake connection management

Stay tuned for the release announcement, and if you're currently dealing with similar timestamp issues, try the USE_LOGICAL_TYPE = TRUE fix—it might just save your day.

Having similar data integration challenges? DBConvert Streams handles complex database migrations and real-time replication between different database types, with built-in solutions for common compatibility issues like this one.