The Snowflake Timestamp Type I Reach For, and Why
The views I express here are mine alone and do not necessarily reflect the opinions or official position of my employer.
Two people looked at the same dashboard, same row, same event, and saw two different times. Nobody had touched the data. The column was TIMESTAMP_LTZ, and it was rendering the stored value in each viewer's own session timezone, which is exactly what it's designed to do. It just wasn't what either of us expected the first time we saw it happen.
This exact scene is made up, but the timezone confusion behind it is something I've hit at work for real.
Get the type wrong and you don't get an error, you get a value that's technically correct and practically confusing, or a join that silently returns fewer rows than it should.
Native TIMESTAMP vs. storing it as a string
Use a native TIMESTAMP type almost always. Storing dates as an ISO string in a VARCHAR column throws away everything Snowflake gives you for free: date arithmetic, timezone conversion, correct sorting, and index-friendly comparisons. A string that looks sortable often isn't once formats drift even slightly.
There are a few narrow cases where a string is still the right call: staging raw data in a landing layer before you've validated the format, a source system producing inconsistent or non-standard date formats that need cleansing first, or needing to preserve the exact original string for audit or compliance reasons. Outside of those, native TIMESTAMP is the default here.
The three timestamp variants, and what each one is actually for
Snowflake splits TIMESTAMP into three variants, and the difference is entirely about how timezone information is stored and displayed:
| Type | What it is | Best for |
|---|---|---|
TIMESTAMP_NTZ | No timezone attached, a plain wall-clock value | Internal events, logs, anything where "the timezone" isn't part of the meaning |
TIMESTAMP_LTZ | Stored in UTC internally, rendered in the querying session's timezone | User-facing timestamps where each viewer should see their own local time |
TIMESTAMP_TZ | Stores the timezone offset alongside the value | Cross-region data, audit trails, anything where the original offset itself matters |
TIMESTAMP_NTZ is the one I default to in a warehouse. It avoids the ambiguity entirely: what you stored is what you get back, every session, every viewer, no surprises. That's exactly why the LTZ dashboard incident above happened, and exactly why NTZ wouldn't have.
Converting between the two when you actually need a timezone
When a value genuinely needs to move between timezones, CONVERT_TIMEZONE (or the AT TIME ZONE syntax) does this at the query layer:
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', event_time_ntz) AS event_time_local
FROM events;
I reach for this explicitly at the query layer rather than baking a timezone into the stored value itself. Storage stays unambiguous, and the conversion only happens where and when someone actually needs a local time.
DATE vs. TIMESTAMP
If a column genuinely only ever needs a calendar day, "signup date," "report date," reach for DATE. It's a smaller, clearer type, and it sidesteps every timezone question above entirely, since a date has no time component to be ambiguous about in the first place.
The classic bug: comparing NTZ against TZ or LTZ
This is the join that loses rows. Comparing a TIMESTAMP_NTZ column directly against a TIMESTAMP_TZ or TIMESTAMP_LTZ column forces an implicit conversion, and Snowflake has to assume a timezone for the NTZ side to make the comparison, usually the session's current timezone. If that assumption doesn't match what the NTZ value actually represents, rows that should match don't, and there's no error, just a result set smaller than it should be.
The fix is to be explicit rather than implicit: convert both sides to the same type and timezone before comparing, instead of trusting Snowflake to guess your intent.
The same problem shows up when you migrate a timestamp type mid-pipeline
Everything above is about picking the right type up front. The harder version of this problem is changing a timestamp type after a pipeline is already live, and it's the same category of mistake with worse consequences: an ingestor that starts producing a different output type than the table currently expects.
I hit this with an Iceberg table behind a Polaris catalog. Polaris enforces schema strictly, and a write with a changed timestamp type (say, an ingestor switched from a plain string to a native TIMESTAMP_TZ) gets rejected outright rather than silently coerced. The lesson generalizes past Iceberg specifically: always migrate the table's schema before deploying an ingestor that changes the output type, never the other way around. Do it in the wrong order and you don't get a mismatched-but-working pipeline, you get a hard failure at write time, which is at least honest, but only if you know to look there first.
Treat the Type as Part of the Contract, Not an Implementation Detail
The type you pick for a timestamp column is a decision about what question that column is answering: a moment in absolute time, a moment as some specific viewer will see it, or a plain wall-clock value with no timezone claim at all. Get that question right before you write a single row, because by the time a dashboard shows two different times for the same event, or a join quietly drops rows, the type is already load-bearing and much harder to change out from under everything depending on it.