Same Shared Data. Different Answers. The Snowflake Timezone Trap

The provider could see the amount. The consuming application received NULL.

Both were using the same shared data. The query ran successfully. The orders were there. Yet a calculated amount was missing, and a downstream application workflow was blocked.

We started with the usual suspects: permissions, masking policies and changes to the share. The issue turned out to be inside a currency-rate join—where a calendar date was being compared with a timestamp.

The data was present. The join was missing it.

An anonymized case with simplified SQL and illustrative visuals.

Why the order appeared but the amount disappeared

The application consumed a view built on shared data. Its amount field depended on finding an exchange rate for the order's currency and booking date.

The relevant logic looked like this:

SELECT
    o.order_id,
    o.local_amount * r.conversion_rate AS converted_amount
FROM orders o
LEFT JOIN exchange_rates r
    ON o.currency_code = r.currency_code
   AND o.book_date = r.conversion_timestamp;

The currency condition looked ordinary. The date condition hid the problem:

• book_date was a DATE: a calendar day.
• conversion_timestamp was a TIMESTAMP_LTZ: a moment interpreted using the session timezone.

The query was asking whether a day and an instant were equal. That required an implicit conversion, and the session's timezone became part of the comparison.

Because it was a LEFT JOIN, a missed match preserved the order and supplied NULL for the rate. Multiplying the amount by that missing rate produced NULL too.

That is why a successful query and valid source data were not enough to rule out a calculation problem.

The timezone difference hiding behind the SQL

The provider environment used UTC. The consumer was believed to be using Pacific time. That difference gave us a reason to examine the date comparison instead of treating the NULL as a sharing problem.

Snowflake stores TIMESTAMP_LTZ internally in UTC, while its operations use the session timezone. Snowflake timestamp types

Consider a rate timestamp at midnight UTC. A date interpreted as midnight in Los Angeles represents a different instant. During daylight saving, local midnight is seven hours later in UTC; in winter, it is eight hours later.

The same source timestamp can therefore be involved in a matching comparison in one session and a failed comparison in another.

No rows need to change for this to happen. A session setting is enough to change the interpretation.

Possible fixes and what each changes

Option 1: align the consumer's session timezone with the provider.

For a UTC business-date rule, the session workaround is:

ALTER SESSION SET TIMEZONE = 'UTC';

With the session aligned to UTC, the example's midnight comparison can match. This is a useful diagnostic and possible workaround, but every consuming connection must use the intended setting. Changing it can also affect other date and timestamp logic in the application.

Option 2: extract the date before comparing.

If the intended comparison is at calendar-date grain, make the conversion explicit on the rate side:

-- Before
AND o.book_date = r.conversion_timestamp

-- Explicit date comparison
AND o.book_date = TO_DATE(r.conversion_timestamp)

The result is a comparison between two DATE expressions. It removes the time-of-day equality requirement, but the extracted date must still be the intended business date. With an LTZ input, TO_DATE() can return different calendar dates under different session timezones.

The ideal fix for a UTC business-date rule

Normalize to the business timezone first, then extract the date. This makes the date rule explicit in the shared view instead of depending on each consumer's session setting.

For LTZ rate timestamps whose business date is defined in UTC:

AND o.book_date = TO_DATE(
    CONVERT_TIMEZONE('UTC', r.conversion_timestamp)
)

The conversion produces a timestamp with an explicit UTC offset; TO_DATE() then extracts that calendar date. For the same LTZ instant, the resulting UTC date stays consistent whether the consuming session uses UTC or Los Angeles. For a regional business calendar, substitute the intended region. NTZ inputs also need their source timezone defined. Snowflake timezone conversion

Why does that matter? Midnight UTC can be the previous calendar day in Pacific time. If a rate exists for the adjacent day, a shifted date might return an amount rather than NULL while selecting a different day's rate.

So the validation question is more precise than “did the amount come back?” It is: “did the order match the intended business date's rate?”

Approach	Expected behavior for the midnight-UTC example	Remaining dependency
Set the session to UTC	The date-to-timestamp comparison can match	Every consuming connection needs the intended setting
Use TO_DATE() alone	Compare calendar dates instead of exact instants	The extracted LTZ date can shift with the session timezone
Convert to UTC, then use TO_DATE()	Compare the explicitly defined UTC business date	UTC must be the correct business calendar; the rate key must identify the intended row

These are expected behaviors of the simplified example, not measured production outcomes. Explicit timezone conversion cannot supply a genuinely missing rate or resolve multiple rates for the same key.

What I would test for the next shared view

A small set of checks can expose this class of issue early:

1. Inspect the effective timezone in the actual application connection with SHOW PARAMETERS LIKE 'TIMEZONE' IN SESSION.
2. Test the same date logic under the provider and consumer session settings, including winter and summer boundaries.
3. Check the selected rate's business date, not just whether the calculated amount is populated.
4. Confirm that currency plus the chosen date identifies the intended rate. Multiple matches can duplicate orders.

Account defaults are useful context, but users and sessions can override them. The application's effective setting is what matters. Snowflake parameter hierarchy

The lesson I took from this was simple: when the source has a value and the consumer sees NULL, follow the calculation. An unmatched join can look like missing data, and an implicit conversion can quietly make the result depend on who runs the query.

A shared view should make the business meaning of its dates explicit.

Have you encountered a query that succeeded while an important join quietly failed? Share the pattern in the comments, or connect with me on LinkedIn.