How warehouse configurations affect dynamic table performance¶

The size of a warehouse doesn’t always correlate to its cost. To pick an optimal warehouse size, experiment and compare the performance of a query on different warehouse sizes.

Typically, larger warehouses increase query performance through increased memory and parallelism:

• Memory: If a query requires more memory than the warehouse can provide, intermediate data spills to local storage, increasing the total amount of work the query has to do. A larger warehouse can prevent this spilling, significantly speeding up the query.

• Parallelism: Parallelism lets a query run faster by dividing the work into more pieces, each completing more quickly. As long as the query offers enough parallelism, increasing warehouse size typically reduces query time without raising costs. For example, moving from a small to a medium sized warehouse typically doubles the cost and halves the runtime.

  However, beyond a certain point, additional parallelism offers diminishing returns. Thus, the cost-optimal warehouse size is typically large enough to prevent spilling but small enough to avoid saturating parallelism. The optimal size for freshness is usually slightly larger.

If your dynamic tables refresh incrementally, the initial refresh often requires a larger warehouse than subsequent refreshes. Adjust your workflow by starting with a larger warehouse size, creating your dynamic tables, and then sizing down the warehouse again.

For more information about warehouse sizing and associated cost, see Virtual warehouse credit usage.