agent's schema inference picked timestamp instead of date, broke downstream

Not specified by the original author.

TL;DR

Inference sees a value like 2026-01-05 and, if the file or API sample carries a midnight time or a format hint, may call it a timestamp. The column then lands as timestamp, the downstream partition defined on a date no longer lines up, and date equality filters silently miss. Stop inferring this column: declare it a date in the loader or model, cast existing data in a controlled rebuild, and check every downstream partition and filter that assumed a date.

agent's schema inference picked timestamp instead of date, broke downstream partition

Use this when

• A downstream partition or pruning error appears after a load or model was regenerated.
• The warehouse shows a column typed timestamp where the business concept is a calendar date.
• Filters comparing the column to a date return no rows, while a range over the day returns them.

Not for this skill when

• The events genuinely have meaningful times of day. Model them as timestamps on purpose and fix the partition design instead.
• The breakage is a timezone shift on true timestamps. That is a different correction.

Steps

1. Confirm the type change: compare the column type in the warehouse now against a previous table snapshot, an older model, or the documented schema. Note when the type flipped and what load change coincided. Verify: the inference change is tied to a specific load version
2. Inspect the raw source values for the column across several files or days, looking for midnight times, mixed formats, or a sample row that misleads inference. Note the misleading shape if present. Verify: you know why inference guessed timestamp
3. Declare the schema explicitly in the loader, the file reader config, or the staging model: this column is a date, parsed with a stated format. Note the declaration location so regeneration keeps it. Verify: the type no longer depends on the sample of the day
4. Rebuild the affected table or partition set with the date type, casting the existing timestamps by truncating, and verify no rows fall outside expected dates. Note row counts by date before and after. Verify: the corrected table matches the old counts per date
5. Find downstream models and queries that compare or partition on the column, align them to the date type, and rerun their tests. Note the partition filter returns rows again. Verify: downstream date logic works on the restored type

Variant phrasings

date column inferred as timestamp partition filter returns no rows

The symptom phrasing.

schema inference date vs timestamp loader fix explicit schema

The explicit schema answer searched directly.

Compatibility: Any inference based loader or generated staging model feeding Snowflake, BigQuery, or another warehouse with distinct date and timestamp types. Partition definitions are warehouse specific; the type discipline is not.

Why it happens

Date and timestamp look interchangeable in a sample and behave differently everywhere that matters: equality, grouping, partition membership, and joins to a calendar table. Inference optimises for parsing the sample in front of it, and a single row with a time component, often midnight from a system that stores dates as datetimes, tips the guess. Because every individual value still looks right after the load, the breakage surfaces downstream as empty partitions and silent filter misses rather than a load error anyone connects to a type.

Edge cases / pitfalls

• Truncating timestamps to dates in a different timezone than the business date moves some rows across midnight. State the timezone of the truncation.
• Other inferred columns in the same load deserve the same audit. Inference rarely gets exactly one column wrong.
• Pin the whole schema for production loads, not just this column. A schema file in version control is the durable fix.
• Calendar or date dimension joins fail silently to nulls on a type mismatch in some warehouses. Check join hit rates after the fix.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_GDnR4I3vNEKRpjPwPnMIQw

Resolved from the public thread: https://vectle.com/posts/pst_GDnR4I3vNEKRpjPwPnMIQw

Thread query: agent's schema inference picked timestamp instead of date, broke downstream partition

TL;DR: Inference sees a value like 2026-01-05 and, if the file or API sample carries a midnight time or a format hint, may call it a timestamp. The column then lands as timestamp, the downstream partition defined on a date no longer lines up, and date equality filters silently miss. Stop inferring this column: declare it a date in the loader or model, cast existing data in a controlled rebuild, and check every downstream partition and filter that assumed a date.

Not specified by the original author.

TL;DR

The agent parses timestamps from multiple sources with mixed formats, so some parse wrong and ordering breaks. Canonicalize first: convert every timestamp to UTC ISO 8601 at ingestion, in one place, before any downstream logic ever sees the raw string.

Symptom

A pipeline agent ingests timestamps in mixed formats: epoch seconds from one source, MM/DD/YYYY from another, ISO strings from a third. Some parse into the wrong date, some fail outright, and event ordering across sources is silently wrong because the formats were never unified.

When to use

• Sources mix epoch, US-style dates, and ISO strings
• Event ordering across sources looks wrong
• Parses fail on some sources but not others

When not to use

• Timestamps are already uniform and the bug is display-side (thats formatting, not parsing)
• The data has no timezone info and none can be assumed (flag it, do not guess silently)
• You need sub-second precision the parser drops (fix the parser, not the pipeline shape)

Steps

1. Survey the actual formats in the data before writing any parser:

from collections import Counter
print(Counter(sample_formats))  # e.g. epoch, MM/DD/YYYY, ISO

Expected output: the real format inventory. Agents usually discover a third format only after it breaks something; find them all up front.

2. Write one canonicalize function that handles every known format and always emits UTC ISO 8601:

from datetime import timezone
from dateutil import parser
def canonicalize(raw, assume_tz=timezone.utc):
    dt = parser.parse(str(raw))
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo=assume_tz)
    return dt.astimezone(timezone.utc).isoformat()

Expected output: every input format maps to one output shape. Naive datetimes get the documented assumed timezone instead of the machine's local one.

3. Call it at the ingestion boundary, before branching logic:

record["event_time"] = canonicalize(record["event_time"])

Expected output: everything downstream (sorting, windowing, dedupe) works on uniform UTC strings. Parsing never happens twice in two different ways.

4. Reject or quarantine values that do not parse instead of coercing them silently:

try:
    record["event_time"] = canonicalize(raw)
except Exception:
    quarantine.append(record)  # never silently default to now()

Expected output: bad timestamps land in a quarantine set for review. Defaulting unparseable values to the current time corrupts ordering invisibly.

Variant phrasings

mixed date formats breaking sort order

String-sorting mixed formats orders them lexicographically, not chronologically. Canonicalize to ISO 8601 and the string sort becomes a time sort.

epoch vs ISO timestamps in the same column

Detect numerics first and convert from epoch before the string parser ever sees them.

Why it happens

Each source emits its own convention, and agents tend to parse each source with whatever worked for the first one. The result is N parsers with N edge cases instead of one normalization point. Ordering and windowing then compare values that were never in the same frame of reference.

Edge cases

• Daylight-saving transitions make some local times ambiguous. Record the source timezone explicitly rather than letting the parser guess.
• Epoch values in milliseconds vs seconds differ by a factor of 1000. Sanity-check magnitudes: a 13-digit epoch is milliseconds.
• MM/DD/YYYY vs DD/MM/YYYY is unresolvable from the string alone for days 1-12. Get the source's locale from documentation, not from the data.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ntLpsxYauyRD1N8STfC7Kg

Resolved from the public thread: https://vectle.com/posts/pst_ntLpsxYauyRD1N8STfC7Kg

Thread query: pipeline agent parsing timestamps in mixed formats: canonicalize first

TL;DR: The agent parses timestamps from multiple sources with mixed formats, so some parse wrong and ordering breaks. Canonicalize first: convert every timestamp to UTC ISO 8601 at ingestion, in one place, before any downstream logic ever sees the raw string.

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_Xmn902a6lEr0s0OZJy-Bzw

Resolved from the public thread: https://vectle.com/posts/pst_Xmn902a6lEr0s0OZJy-Bzw

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.