agent's freshness check used utc while the warehouse used local time

TL;DR

Freshness compares the max loadedat timestamp to now, and if the source timestamps are warehouse-local while dbt evaluates thresholds in UTC (or the reverse), every result is shifted by the offset, producing phantom staleness or false freshness. Align the comparison: store loadedat in UTC, or convert in the source definition with a freshness check on a normalized field, and state the timezone convention in the source YAML.

agent's freshness check used utc while the warehouse used local time

Use this when

• dbt source freshness warns or errors with a lag roughly equal to your timezone offset (for example 7 or 8 hours)
• freshness flips at daylight saving changes or differs between dbt Cloud (UTC) and local runs
• the source loaded_at column is a timestamp without timezone in local time

Not for this skill when

• data really is late by more than the offset, fix the loader schedule
• the loaded_at field points at the wrong column entirely, correct the source config
• only one source is off while others share the same setup, inspect that source table clock specifically

Steps

1. Establish the clocks: query the source table max loaded_at, the warehouse current timestamp functions (with and without timezone), and note what dbt reports as the freshness age for that source. Verify: the offset between reported age and real age is measured, not guessed.

2. Decide the convention: UTC everywhere is the durable choice. If the loader can emit UTC timestamps (or a timestamptz), fix it there; otherwise normalize in dbt by defining the freshness check against a converted expression where your setup supports it, or adjust by documenting the offset in thresholds as a temporary measure. Verify: one timezone convention is chosen and written into the source YAML description.

3. Update the source freshness config (warnafter and errorafter) only after the clock alignment, so thresholds mean real elapsed time. Verify: thresholds reflect true business freshness needs on the aligned clock.

4. Run dbt source freshness now and again after a known load, checking that the reported age tracks real elapsed time across both runs. Verify: reported freshness age matches wall clock age of the latest load within minutes.

Variant phrasings

dbt Cloud versus local disagreement

Cloud runs in UTC; laptops run in local time. A mismatch that appears only in one place points at the runner clock or timestamp type, not the data.

timestamp with timezone sources

Timestamptz columns usually behave correctly because the warehouse normalizes them. The problem shape is naive local timestamps; converting those columns at load time is the clean fix.

Compatibility: dbt source freshness with loadedatfield and warnafter/errorafter thresholds; timestamp semantics per adapter (Snowflake timestampntz vs timestamptz, BigQuery timestamp vs datetime, Postgres timestamp vs timestamptz).

Why it happens

Freshness is arithmetic on timestamps produced by two different systems: the loader that stamps rows and the dbt runner that evaluates now. If those stamps live in different timezone frames and the column type does not carry timezone information, the subtraction silently includes the frame offset. No threshold tuning fixes arithmetic done in mixed frames; it just moves the phantom window.

Edge cases / pitfalls

• Daylight saving shifts the offset twice a year, so an offset-tuned threshold breaks seasonally; frame alignment does not.
• Mixed sources (some UTC, some local) need per-source handling; a global assumption reintroduces the bug for the minority group.
• Document the convention on the source itself, because future loaders onboarding new tables copy existing patterns.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_slip5IAqbo-yjcv3IMqNdA