Snowflake usage query error timezone shift splits daily active users across

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

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

The migration interpreted naive timestamps in the wrong zone (or converted twice), shifting everything by a fixed offset. Confirm the shift is uniform by sampling, correct it with interval arithmetic (add or subtract the offset), and set the timezone explicitly (UTC everywhere) before re-running. Uniform offset means a systematic fix; irregular errors mean a different bug.

The query

agent ran the data migration with the wrong timezone - all the timestamps shifted by 5 hours

Use this when

• Data migration shifted all timestamps by 5 hours
• Migrated timestamps are uniformly offset
• Need to correct a timezone-shifted migration
• An agent's migration used the wrong timezone

Not for

• Timestamps wrong by irregular amounts (a logic bug, not a zone bug)
• Application code formatting times wrong (a display problem)
• Timezone-naive vs aware confusion in new code (a design problem)

Steps

1. Confirm the shift is uniform

Sample rows and compare source vs migrated timestamps:

SELECT [id], [source_ts], [migrated_ts], [migrated_ts] - [source_ts] AS delta
FROM [table] ORDER BY [id] LIMIT 20;

Expected output: the delta is identical (5 hours) on every row. Uniform delta confirms a pure timezone error; varying deltas mean something else.

2. Correct with interval arithmetic

UPDATE [table] SET [migrated_ts] = [migrated_ts] - INTERVAL '5 hours';

(Or + INTERVAL, depending on the shift direction: verify on one row first.) Batch on large tables and run in a transaction you can verify before committing.

Expected output: the deltas from step 1 become zero. Re-run the sample query to prove it.

3. Set the timezone explicitly and re-run correctly

Run the migration with TZ pinned:

TZ=UTC [migration command]

And store timestamps as timestamptz (or UTC-normalized) so the zone is data, not context. Expected output: a fresh migration run produces zero deltas without any correction step.

4. Guard against DST-adjacent reruns

If the data spans a daylight-saving transition, a fixed 5-hour interval is wrong for part of the range: correct with AT TIME ZONE conversions per row instead of a constant interval.

Expected output: the correction handles the transition rows correctly, verified by sampling across the transition date.

5. Make UTC-explicit the agent's default

The agent's data-migration procedure: TZ=UTC on every run, timestamptz for every timestamp column, and a sample delta check (step 1) as a mandatory post-migration verification.

Expected output: timezone shifts stop being silent, because the delta check catches them immediately.

Tool compatibility

postgres (timestamptz, INTERVAL, AT TIME ZONE), mysql (TIMESTAMP vs DATETIME semantics differ: know which you store). The UTC discipline is universal.

Variant phrasings

data migration wrong timezone timestamps shifted

Steps 1 and 2: confirm uniform delta, correct with interval math.

timestamps off by 5 hours after migration

Step 2: subtract (or add) the 5-hour interval; verify direction on one row first.

migration timezone naive vs aware

Step 3: store timestamptz and run with TZ=UTC so the zone is explicit everywhere.

Why it happens

Naive timestamps carry no zone, so every system that touches them applies its own: the migration host's local zone, the database session zone, the driver's default. When any two disagree, every timestamp shifts by the difference, uniformly. The uniformity is the signature: one wrong assumption, applied to every row identically.

Edge cases

• Correcting twice (running the interval fix on already-fixed rows) doubles the error: the step-1 delta check must read zero before you stop, and the fix must run exactly once.
• TIMESTAMP WITHOUT TIME ZONE columns cannot be "corrected" by session settings after the fact: the wrong zone is baked into the stored values, which is why the interval update is the fix.
• Future migrations should convert with AT TIME ZONE explicitly per row rather than relying on session zones: explicit beats ambient.

Provenance

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

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

Thread query: agent ran the data migration with the wrong timezone - all the timestamps shifted by 5 hours

TL;DR: The migration interpreted naive timestamps in the wrong zone (or converted twice), shifting everything by a fixed offset. Confirm the shift is uniform by sampling, correct it with interval arithmetic (add or subtract the offset), and set the timezone explicitly (UTC everywhere) before re-running. Uniform offset means a systematic fix; irregular errors mean a different bug.