Optimize dynamic table performance¶

Choose a refresh mode¶

The refresh mode you choose has a significant impact on performance because it determines how much data Snowflake processes during each refresh. For information about how each mode works, see Dynamic table refresh modes.

Use the following decision process to select a refresh mode:

1. Review your query against the list of supported query constructs. Not all query operators support incremental refresh. For operators that are supported, see Optimize queries for incremental refresh to understand how they affect performance.

2. Estimate your change volume, which is the percentage of your data that changes between refreshes. Incremental refresh, for example, works best when less than five percent of data changes.

3. Evaluate your data locality. Check whether your source tables are clustered by the keys that you plan to use in joins, GROUP BY, or PARTITION BY clauses in your dynamic table query. Poor locality reduces incremental refresh efficiency. To improve locality, see Improve data locality.

4. Choose a mode based on the following table:

   Mode	When to use
   Incremental	Your query uses supported operators, less than five percent of data changes between refreshes, and your source tables have good data locality. Note Incremental refresh can still scan source tables, not just the rows that changed. For example, a new row in one side of a join must match against all rows in the other table. Even a small number of changes can trigger significant work.
   Full	A large percentage of data changes, your query uses unsupported operators, or your data lacks locality.
   Auto	You’re prototyping or testing. Avoid AUTO in production because its behavior might change between Snowflake releases.

5. When you create a dynamic table, specify the mode with REFRESH_MODE = INCREMENTAL or REFRESH_MODE = FULL in your CREATE DYNAMIC TABLE statement.

To check which refresh mode a dynamic table uses, see Refresh mode.

Optimize your queries and pipeline¶

The structure of your dynamic table queries and pipeline directly affects refresh performance. Use the following guidelines to reduce the work during each refresh.

Simplify individual queries

• Use inner joins instead of outer joins. Inner joins perform better with incremental refresh. Verify referential integrity in your source data so that you can avoid outer joins.

• Avoid unnecessary operations. Remove redundant DISTINCT clauses and unused columns. Exclude wide columns (like large JSON blobs) that aren’t frequently queried.

• Remove duplicates efficiently. Use ranking functions instead of DISTINCT where possible.

For detailed guidance on how specific SQL operators affect incremental refresh performance, see Optimize queries for incremental refresh.

Split transformations across dynamic tables

Breaking complex transformations into multiple dynamic tables makes it easier to identify bottlenecks and improves debugging. With immutability constraints, you can also use different refresh modes for different stages.

• Add filters early. Apply WHERE clauses in the dynamic tables closest to your source data so that downstream tables process fewer rows.

• To avoid repeated DISTINCT operations in downstream tables, remove duplicate rows earlier in your pipeline.

• Reduce the number of operations per table. Move joins, aggregations, or window functions into intermediate dynamic tables instead of combining them all in one query.

• Materialize compound expressions (like DATE_TRUNC('minute', ts)) in an intermediate table before grouping by them. For details, see Optimize aggregations.

The following example shows how to split a complex query into intermediate dynamic tables.

Initial complex query:

CREATE DYNAMIC TABLE final_result AS
  SELECT ...
  FROM large_table a
  JOIN dimension_table b ON ...
  JOIN another_table c ON ...
  GROUP BY ...;

Split the complex pipeline by adding an intermediate dynamic table:

CREATE DYNAMIC TABLE intermediate_joined AS
  SELECT ...
  FROM large_table a
  JOIN dimension_table b ON ...;

CREATE DYNAMIC TABLE final_result AS
  SELECT ...
  FROM intermediate_joined
  JOIN another_table c ON ...
  GROUP BY ...;

For detailed information and example of how operators affect performance, see Optimize queries for incremental refresh.

Mark historical data immutable¶

Use the IMMUTABLE WHERE clause to tell Snowflake that certain rows won’t change. This reduces the scope of work during each refresh.

For syntax, examples, and detailed guidance, see Use immutability constraints.

Adjust your warehouse configuration¶

The warehouse that you specify in your CREATE DYNAMIC TABLE statement runs all refreshes for that table. Warehouse size and configuration directly affect refresh duration and cost.

For more information about warehouses and dynamic tables, see Understand warehouse usage for dynamic tables. For general warehouse performance optimization strategies, see Optimizing warehouses for performance.

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

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

ALTER DYNAMIC TABLE my_dynamic_table UNSET INITIALIZATION_WAREHOUSE;

Identify the right target lag¶

Target lag controls how often your dynamic table refreshes. Shorter target lag means fresher data but more frequent refreshes and higher compute cost. For more information about how target lag works, see Understanding dynamic table target lag.

Use the following recommendations to optimize target lag for your workload:

• Use DOWNSTREAM for intermediate tables that don’t need independent freshness guarantees. These tables refresh only when downstream tables need them.

• Check the refresh history to find the right lag: Use DYNAMIC_TABLE_REFRESH_HISTORY or Snowsight to analyze refresh durations and skipped refreshes. Set the target lag slightly higher than your typical refresh duration.

ALTER DYNAMIC TABLE my_dynamic_table SET TARGET_LAG = '1 hour';

ALTER DYNAMIC TABLE my_dynamic_table SET TARGET_LAG = DOWNSTREAM;

Improve data locality¶

Locality describes how closely Snowflake stores rows that share the same key values. When rows with matching keys span fewer micro-partitions (good locality), incremental refreshes scan less data. When matching keys span many micro-partitions (poor locality), incremental refresh can take longer than full refresh.

For more information about how Snowflake stores data, see Micro-partitions & Data Clustering.

ALTER TABLE my_source_table CLUSTER BY (join_key_column);