agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant r

Not specified by the original author.

TL;DR

Fix this in Snowflake by acting on the exact message first: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one item, then rerun the full scope and reconcile the counts.

agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack

Use this when

• The operation in Snowflake fails with this exact message: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack.
• The same failure repeats on retry with unchanged inputs.
• A review, export or check is blocked until this item completes.

Not for this skill when

• The message is a permission or outage notice from the platform. Resolve that first, then return here if this error remains.
• The operation completes and you only disagree with its output. That is a quality review, not this failure.

Steps

1. Reproduce the failure with the smallest scope that still shows it in Snowflake, and copy the full message plus the item, user or metric involved into the run notes. Verify: the error is reproduced on demand with a named item, not only seen in a log.
2. Apply the specific fix for this error: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Verify: confirm the query returns rows when run as the review role.
3. Check the neighbouring items that share the same config, ontology, baseline or credential, because this class of failure rarely affects only one item. Verify: the neighbouring items are listed and each is marked affected or clear.
4. Rerun the original operation in Snowflake on the previously failing scope, watching for the same message rather than assuming success from a quiet log. Verify: the operation completes and the original message does not appear.
5. Compare the result counts, decisions or alerts against the source in Snowflake so a silent partial success is caught before sign off. Verify: counts match the source or every difference is named and explained.
6. Record the fix, the triggering input and the verification result on the item so the next occurrence is recognised immediately. Verify: the record names the error, the fix applied and the evidence of success.

Why it happens

Snowflake hides objects the current role cannot see, so a missing grant and a missing object produce the same error message.

Variant phrasings

agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dorman

The verbatim form searchers paste from the terminal or console. Treat it identically: same fix, same single item proof, then the full rerun.

How to fix: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dorman

The how-to phrasing for the same failure in Snowflake. The steps above apply unchanged; start at the reproduction step rather than skipping to the rerun.

Snowflake troubleshooting for this failure

The symptom-first phrasing used when the console hides the full message. Surface the full message first, confirm it matches the code block above, then follow the steps.

Compatibility: Snowflake at current versions, plus the platform API or console the query names; no extra tooling beyond a text editor and the source system access needed to verify counts.

Provenance

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

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

Thread query: agent misread Snowflake ACCOUNT_USAGE grants timestamps as UTC: dormant role report off by timezone in review pack

TL;DR: Fix this in Snowflake by acting on the exact message first: check the exact role and warehouse grants in Snowflake, qualify the object with database and schema, and run the query as the review role itself. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one item, then rerun the full scope and reconcile the counts.

Not specified by the original author.

TL;DR

Fix this in Snowflake by acting on the exact message first: rewrite the Snowflake usage query to convert event timestamps to one stated timezone before grouping by day, use a consistent date truncation for every source table, rerun both dates affected by the shift, and verify daily active users sum to the ungrouped total. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one account, batch or record set first, then rerun the full scope and reconcile the counts.

Snowflake usage query error timezone shift splits daily active users across two dates

Use this when

• The operation in Snowflake fails with this exact message or symptom: Snowflake usage query error timezone shift splits daily active users across two dates.
• The same failure repeats on retry with unchanged inputs.
• Expansion scoring, alerting or outreach is blocked until this item completes.

Not for this skill when

• The message is an outage notice from the platform. Resolve that first, then return here if this error remains.
• The operation completes and you only disagree with its output quality. That is a quality review, not this failure.

Steps

1. Reproduce the failure with the smallest scope that still shows it in Snowflake, and copy the full message plus the account, signal or job involved into the run notes. Verify: the error is reproduced on demand with a named scope, not only seen in a log.
2. Apply the specific fix for this error: rewrite the Snowflake usage query to convert event timestamps to one stated timezone before grouping by day, use a consistent date truncation for every source table, rerun both dates affected by the shift, and verify daily active users sum to the ungrouped total. Verify: the smallest failing scope now completes, or the named account, signal or job behaves as the fix predicts.
3. Check the neighbouring accounts, signals or jobs that share the same config, schema or source, because this class of failure rarely affects only one item. Verify: the neighbouring items are listed and each is marked affected or clear.
4. Rerun the original operation in Snowflake on the previously failing scope, watching for the same message rather than assuming success from a quiet log. Verify: the operation completes and the original message does not appear.
5. Compare the result counts, scores or status against the source in Snowflake so a silent partial success is caught before sign off. Verify: counts match the source or every difference is named and explained.
6. Record the fix, the triggering input and the verification result on the item so the next occurrence is recognised immediately. Verify: the record names the error, the fix applied and the evidence of success.

Why it happens

Grouping timestamps recorded in different timezones splits one day of activity across two dates, halving each day and inventing a drop and a spike.

Variant phrasings

Snowflake usage query error timezone shift splits daily active users across two dates

The verbatim form searchers paste from the terminal or console. Treat it identically: same fix, same single scope proof, then the full rerun.

How to fix - Snowflake usage query error timezone shift splits daily active users across two dates

The how-to phrasing for the same failure in Snowflake. The steps above apply unchanged; start at the reproduction step rather than skipping to the rerun.

Snowflake troubleshooting for this failure

The symptom-first phrasing used when the console hides the full message. Surface the full message first, confirm it matches the code block above, then follow the steps.

Edge cases

• If the fix works on the sample scope but the full rerun still fails, a second account or record with the same fault is present; widen the scope in stages until the next failing item is found.
• If scores change after the fix but downstream alerts or reports do not, check the downstream refresh timing before recalculating again, because those surfaces often read a synced copy.
• If the source information itself is missing for the affected accounts, record that as a source gap rather than scoring the accounts as zero.

Compatibility: Snowflake at current versions, plus the platform API or console the query names; no extra tooling beyond the source system access needed to verify counts.

Provenance

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

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

Thread query: Snowflake usage query error timezone shift splits daily active users across two dates

TL;DR: Fix this in Snowflake by acting on the exact message first: rewrite the Snowflake usage query to convert event timestamps to one stated timezone before grouping by day, use a consistent date truncation for every source table, rerun both dates affected by the shift, and verify daily active users sum to the ungrouped total. Do not rerun the whole job unchanged, because the same inputs produce the same failure. Prove the fix on one account, batch or record set first, then rerun the full scope and reconcile the counts.

Not specified by the original author.

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

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

Thread query: 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.