An 18-minute dashboard join: cache mode and join strategy

Fact view: explicit loads via Scheduler, not a 1-hour TTL. A TTL cache is query-driven; with a 1-hour TTL and hourly refreshes timed slightly off, users arrive just after expiry and each distinct dashboard query triggers a cold cache miss, which means a Snowflake warehouse resume - and the 60-second minimum bills far more than the query itself. Scheduler-driven explicit loads every 30 minutes decouple Snowflake spend from user behaviour: 20 loads per day regardless of whether 3 or 300 people look. Freshness requirement of 30 minutes is met by construction.

Oracle dimension: full cache with a 24-hour TTL. 5 million rows is small, it changes slowly, and it is the broadcast side of the join. Full cache is appropriate because every query needs the whole dimension; partial cache would add complexity for no benefit.

Currency table: full cache with a long TTL, refreshed after the daily rate publication. 60 rows re-read per query is pure waste.

The combination matters more than any single choice: with the fact pre-loaded and the dimension cached, the hourly refresh hits two local caches and Snowflake sees no traffic at all outside the load window.

The join crosses sources. Snowflake holds the fact, Oracle holds the dimension, and no single wrapper can evaluate a join across two systems, so Denodo must bring one side to the other and join in its own engine. It chooses to move the 5-million-row dimension; 2.3 million fact rows times roughly 300 bytes of joined width explains the 22 GB heap and the spilling. Increasing heap only postpones the problem - it does not reduce the bytes moved per refresh.

Alternatives:

1. Replicate the dimension into Snowflake (a nightly copy of 5 million rows) and expose a single-source view so Snowflake executes the join on its own warehouse. Best option for this workload: the join happens next to the 400-million-row table, join pruning is possible, and Snowflake's per-second billing is amortised over a real computation rather than a resume. Trade-off: a copy that must be governed and refreshed.

2. Intelligent Query Acceleration / Denodo Embedded MPP on the Denodo side, joining cached fact and cached dimension in a distributed engine. Viable, and it removes cross-region transfer, but it duplicates storage and adds a component to operate.

3. Rejected: cache the joined result of the full quarter for all 300 users. It looks attractive, but every dashboard filter combination becomes a distinct cached entry, so the cache grows faster than it is reused and each entry is stale within 30 minutes.

The decision criterion is where the bytes are cheapest to move and where the engine can do the work most efficiently - not which feature is newest.

A bigger warehouse cannot fix this by itself, because the expensive step is not Snowflake's scan - it is moving 5 million dimension rows and 2.3 million fact rows into the Denodo heap and joining them there. Warehouse size only affects the part of the work Snowflake does; it does nothing for the cross-source transfer, the 22 GB heap or the spill.

It becomes the right call when the join has already been pushed to a single source and the remaining cost is genuine Snowflake compute. In that case a larger warehouse for a short window is often cheaper than maintaining caches: Snowflake bills per second with a 60-second minimum, so a Large warehouse running 40 seconds can cost less than a Small warehouse running 5 minutes, and the data is always current.

The trade-off to state plainly: caching trades freshness and operational complexity for source load and cost. Here the business accepts 30-minute-old numbers, which is exactly what makes Scheduler-driven loads attractive. If the tolerance were 30 seconds, caching the fact would be off the table and the answer would shift to pushdown plus right-sizing the warehouse.

So the recommendation is not 'cache because caching is cheaper' but 'push the join to one engine, then size that engine, then cache only what the freshness tolerance permits'.