column type drift error failed: timestamp column changed to string failure

Not specified by the original author.

TL;DR

A timestamp column arriving as string breaks ordering, filtering, and freshness logic downstream: find the load or writer that started emitting strings, cast explicitly at ingestion with a strict format, and quarantine values that do not parse instead of letting a permissive cast null them silently. Then re-run the dependent freshness and drift checks.

column type drift error failed: timestamp column changed to string failure

Use this when

• The check or run fails with this symptom: the timestamp column is now stored or read as string.
• You are working on a column that changed from timestamp to string in the warehouse and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A string column that was always textual dates by design, which needs parsing at use rather than an ingestion fix instead.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Sample the string values and record their formats, including whether multiple formats or timezones are mixed. Verify: the value formats present are catalogued
2. Locate the writer change: a CSV export, a JSON flatten, or a schema-on-read change that stopped parsing dates. Verify: the writer change is identified
3. Add an explicit parse at ingestion using the dominant format, with unparseable values routed to a quarantine table with the raw value kept. Verify: parsed values land as timestamps and failures are quarantined
4. Backfill the affected partitions from the raw strings so history is typed consistently. Verify: historical partitions read back as timestamps
5. Rerun freshness and range checks that depend on the column and confirm they evaluate real time again. Verify: dependent checks pass on the retyped column

Variant phrasings

timestamp column changed to string schema drift

The verbatim drift phrasing.

column type drift timestamp to string warehouse

The type-drift phrasing for stored column types.

string dates breaking freshness check type change

The downstream-impact phrasing for freshness and ordering.

Compatibility: Snowflake, BigQuery, Spark, and dbt models consuming timestamp columns

Why it happens

Date parsing is often implicit: file readers and JSON flatteners produce timestamps only while the incoming format stays consistent. One format change, one quoted export, or one union with a string branch flips the column to text, and every comparison downstream quietly becomes lexicographic.

Edge cases / pitfalls

• Store the raw string alongside the parsed timestamp during the transition so parsing decisions stay auditable.
• Multiple incoming formats need an explicit format list. A single guessed format will silently null the rest.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_SpeGppD2f0VOs-21GtDctw

Resolved from the public thread: https://vectle.com/posts/pst_SpeGppD2f0VOs-21GtDctw

Thread query: column type drift error failed: timestamp column changed to string failure

TL;DR: A timestamp column arriving as string breaks ordering, filtering, and freshness logic downstream: find the load or writer that started emitting strings, cast explicitly at ingestion with a strict format, and quarantine values that do not parse instead of letting a permissive cast null them silently. Then re-run the dependent freshness and drift checks.