Understand warehouse usage for dynamic tables¶

How warehouse size affects refresh performance¶

A larger warehouse doesn’t always result in higher costs. In many cases, doubling the warehouse size doubles the per-second cost but halves the runtime. This results in similar total cost with faster refreshes. Larger warehouses improve performance in two ways:

• Memory: When a refresh needs more memory than the warehouse provides, data spills to local storage. This spillage increases the total compute work and slows the refresh process. A larger warehouse has more memory and can avoid spills entirely.

• Parallelism: Larger warehouses run more tasks simultaneously. Refreshes that scan large amounts of data across many partitions benefit the most. Small data sets and sequential operations see diminishing returns when you use a larger warehouse.

For more information about warehouse sizing, see Warehouse size.

Dual warehouse support¶

Dynamic tables support separate warehouses for different refresh types:

• WAREHOUSE: Runs regular incremental refreshes.

• INITIALIZATION_WAREHOUSE: Runs initializations and full refreshes, which perform full data scans and are typically more resource-intensive.

  Important

  When you refresh manually by running ALTER DYNAMIC TABLE REFRESH COPY SESSION, the command uses the current session’s warehouse. Snowflake ignores the INITIALIZATION_WAREHOUSE in this scenarios, even for initializations.

This separation lets you use a larger warehouse for resource-intensive initializations without paying for that capacity during regular incremental refreshes. Dual warehouse support is useful in the following common scenarios:

• You want to enable faster recovery when you promote a secondary dynamic table to primary and must reinitialize the table.

• You must meet strict RTO/RPO requirements, but don’t want to increase costs for day-to-day operations.

When you don’t set the INITIALIZATION_WAREHOUSE parameter, Snowflake runs all refreshes on the warehouse specified by WAREHOUSE.