Understand warehouse usage for dynamic tables

Dynamic tables support dual warehouses to optimize performance and cost for different types of refresh operations. You can specify a dedicated warehouse for initializations and reinitializations, which are typically more resource-intensive, while you use another warehouse for all other refreshes.

Initializations and reinitializations perform full data scans and might take significantly longer than incremental refreshes. These operations often occur during events such as failovers, where a newly promoted primary dynamic table must perform a full reinitialization on its first refresh. By assigning a larger or more performant warehouse to these operations, you can reduce recovery time and meet strict RTO/RPO requirements without oversizing the warehouse that is used for regular incremental refreshes.

For information about warehouse usage cost, see Understanding cost for dynamic tables.

Run the following SQL commands to set the INITIALIZATION_WAREHOUSE property, which directs initializations and reinitializations to a dedicated warehouse. All other refreshes use the warehouse specified by the WAREHOUSE property.

  • CREATE DYNAMIC TABLE: You can set the INITIALIZATION_WAREHOUSE property by using any of the variants of the CREATE DYNAMIC TABLE command.

    CREATE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = 'DOWNSTREAM'
  WAREHOUSE = 'XS_WAREHOUSE'
  INITIALIZATION_WAREHOUSE = '4XL_WAREHOUSE'
  AS <query>
    Copy

  • ALTER DYNAMIC TABLE: You can use this command to set or unset the INITIALIZATION_WAREHOUSE property. When the INITIALIZATION_WAREHOUSE property is SET, the specified warehouse is used for all initializations and reinitializations; when the property is UNSET, the dynamic table uses the warehouse that is specified by the WAREHOUSE property for all refreshes.

    ALTER DYNAMIC TABLE my_dynamic_table SET INITIALIZATION_WAREHOUSE = '4XL_WAREHOUSE';
    Copy
    ALTER DYNAMIC TABLE my_dynamic_table UNSET INITIALIZATION_WAREHOUSE;
    Copy

If INITIALIZATION_WAREHOUSE isn’t set, all refreshes —– including initializations and reinitializations — run on the warehouse specified by WAREHOUSE.

To view the warehouse being used, you can use the following interfaces:

You must use a role that was granted the MONITOR privilege on the dynamic tables. For more information, see List dynamic tables or view information on specific columns and Monitor your dynamic tables using SQL table functions.

How warehouse configurations affect dynamic table performance

Warehouse size doesn’t always correlate directly with dynamic table refresh cost. To find the best size, test your refresh query on multiple warehouse sizes and compare performance.

Larger warehouses can improve performance in two main ways:

  • Memory: If a query exceeds the warehouse’s memory, it spills to local storage, increasing work and slowing execution. A larger warehouse can avoid spills and significantly speed up the refresh.

  • Parallelism: Larger warehouses process more tasks in parallel. If the query has enough parallelizable work, increasing the warehouse size typically reduces runtime at roughly proportional cost; for example, doubling the size often reduces the runtime in half.

    However, after a certain point, additional parallelism provides diminishing returns. Cost-optimal sizing is usually enough to avoid memory spills, but not so large that parallelism is saturated.

For incremental dynamic tables, the initial refresh often needs more compute than later refreshes. A common workflow is to use INITIALIZATION_WAREHOUSE to run initial refreshes on a larger instance.

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

Limitations and considerations when using dual warehouses

To refresh the dynamic table manually, you can run ALTER DYNAMIC TABLE … REFRESH COPY SESSION. This command runs the refresh in a copy of the current session by using the current user and warehouse. When COPY SESSION is specified, INITIALIZATION_WAREHOUSE is ignored, even for initializations and reinitializations. The warehouse from COPY SESSION takes precedence over both WAREHOUSE and INITIALIZATION_WAREHOUSE.

Language: English