Dynamic table refresh modes¶

Every dynamic table has a refresh mode that controls how its contents are refreshed. There are three modes: INCREMENTAL, FULL, and AUTO. The refresh mode is set at creation time. To change it, use CREATE OR REPLACE or CREATE OR ALTER, both of which trigger reinitialization.

-- Set the refresh mode explicitly in the CREATE statement
CREATE OR REPLACE DYNAMIC TABLE dt_orders
  TARGET_LAG = '10 minutes'
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL  -- or FULL or AUTO
AS
  SELECT ... FROM raw_orders ...;

For the full CREATE syntax with all options, see CREATE DYNAMIC TABLE.

INCREMENTAL refresh¶

An incremental refresh analyzes only the data that changed since the last refresh and merges those changes into the dynamic table. This is typically the most efficient mode because it avoids reprocessing data that hasn’t changed.

Use an incremental refresh when:

The definition uses only constructs supported for incremental refresh.
Fewer than five percent of the base table data changes between refreshes.
You want to minimize warehouse compute cost per refresh.

If the definition contains a construct that doesn’t support incremental refresh, creation fails with a SQL compilation error identifying which construct is incompatible.

FULL refresh¶

A full refresh re-executes the entire definition against the current state of all base tables and replaces the materialized output with the new query results.

Use a full refresh when:

The definition uses constructs not supported by incremental refresh (for example, INTERSECT, EXCEPT, or exact percentile functions like PERCENTILE_CONT).
A large fraction of the base table data changes between refreshes, making incremental processing more expensive than a full scan.
You need predictable performance regardless of how much data changes between refreshes.

CREATE OR REPLACE DYNAMIC TABLE dt_non_null_orders
  TARGET_LAG = '1 hour'
  WAREHOUSE = transform_wh
  REFRESH_MODE = FULL
AS
  SELECT order_id, product_name, quantity, unit_price
  FROM raw_orders
  EXCEPT
  SELECT order_id, product_name, quantity, unit_price
  FROM raw_orders
  WHERE order_status IS NULL;

Because EXCEPT is not supported for incremental refresh, this definition requires REFRESH_MODE = FULL.

Why some computations require full refresh¶

Incremental refresh works by processing only the rows that changed since the last refresh. This is efficient when the output for each new row can be computed on its own (for example, filtering rows, applying a format function, or joining new orders against a customer table). The new rows don’t affect the results that were already computed for previous rows.

But some computations depend on the relationship between all rows, not just the new ones. Consider finding the median order value: if you have 1,000 orders and 5 new ones arrive, the median can shift. To find the new median, you need to re-sort all 1,005 values and pick the middle one. There is no shortcut that lets you update the answer by looking at only the 5 new rows.

These computations fundamentally require seeing all data on every refresh:

Computation	Why it needs all rows	Example
Exact percentile or median	Adding one row can shift which value lands in the middle of the sorted list.	`PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)`
Set difference or intersection	Determining what’s in one set but not another requires comparing both full sets.	`SELECT ... EXCEPT SELECT ...`

Full refresh is required because these computations need access to all rows. Set REFRESH_MODE = FULL explicitly so that anyone reading the DDL understands the cost trade-off.

Common workloads where full refresh is the right starting point¶

Incremental refresh is designed for workloads where a small fraction of data changes between refreshes. When the data change pattern does not fit this model, full refresh is the appropriate starting mode for these workloads.

Bulk-reload or truncate-and-replace patterns. When an upstream system replaces all rows on every load cycle, incremental refresh does strictly more work than full refresh. Set REFRESH_MODE = FULL. If the INSERT OVERWRITE doesn’t change actual data values and you can define a primary key with RELY, incremental refresh can still be efficient. For details on optimizing INSERT OVERWRITE workloads with primary keys, see Optimize input data for dynamic tables.
Dimension tables refreshed wholesale. Small-to-medium dimension tables that are periodically replaced in their entirety are faster to rebuild than to diff.
Definitions that stack multiple blocking operators. When a query combines several blocking operators (GROUP BY, DISTINCT, window functions), full refresh can outperform incremental. See Optimize queries for incremental refresh for guidance on splitting complex queries.
High-churn workloads with heavy deletes. When base tables regularly purge large volumes of expired data, incremental refresh must scan many micro-partition files to identify deleted rows. Full refresh avoids this overhead entirely.

To determine whether incremental refresh is helping or hurting an existing dynamic table, compare average refresh times using DYNAMIC_TABLE_REFRESH_HISTORY. For details, see Optimize queries for incremental refresh.

AUTO refresh¶

AUTO is the default refresh mode. When you set REFRESH_MODE = AUTO (or omit the parameter entirely), Snowflake evaluates the definition once at creation time and resolves it to either INCREMENTAL or FULL. After creation, the resolved mode is locked in and does not change. For the full CREATE syntax with all options, see CREATE DYNAMIC TABLE.

Important

AUTO resolves once at creation time. It does not re-evaluate on subsequent refreshes. If something changes later (for example, an upstream view is modified so that incremental refresh is no longer supported), refreshes fail rather than switching to full refresh without a warning. To change the refresh mode, recreate the dynamic table with CREATE OR REPLACE DYNAMIC TABLE.

How AUTO chooses¶

Snowflake examines the definition for constructs, operators, functions, and base table properties. If the definition supports incremental refresh and Snowflake expects it to be the more cost- and time-effective choice, AUTO resolves to INCREMENTAL. If any part of the definition is incompatible, or if the definition complexity makes performance hard to predict, Snowflake conservatively assumes high change volumes and resolves to FULL.

When AUTO resolves to FULL, the CREATE statement succeeds and its result message indicates why FULL was chosen. Verify the resolved mode after creation with SHOW DYNAMIC TABLES.

Verify the resolved mode¶

Run SHOW DYNAMIC TABLES and inspect the refresh_mode and refresh_mode_reason columns:

SHOW DYNAMIC TABLES LIKE 'dt_orders' ->> SELECT "name", "refresh_mode", "refresh_mode_reason" FROM $1;

+-------------------+--------------+---------------------+
| name              | refresh_mode | refresh_mode_reason |
|-------------------+--------------+---------------------|
| DT_ORDERS         | INCREMENTAL  | NULL                |
+-------------------+--------------+---------------------+

When refresh_mode is INCREMENTAL, refresh_mode_reason is NULL. When AUTO resolves to FULL, the refresh_mode_reason column explains why incremental refresh wasn’t possible:

+------------------------+--------------+-------------------------------------------------------+
| name                   | refresh_mode | refresh_mode_reason                                   |
|------------------------+--------------+-------------------------------------------------------|
| DT_NON_NULL_ORDERS     | FULL         | Unsupported query operator type: SetOperation[EXCEPT] |
+------------------------+--------------+-------------------------------------------------------+

Tip

In production, set the refresh mode to INCREMENTAL or FULL explicitly rather than relying on AUTO. Explicit modes make costs predictable and prevent silent fallback to full refresh. Use AUTO for exploration and prototyping, then lock in the resolved mode before promoting to production.

Choose a refresh mode¶

Use this table to decide which refresh mode to set for your dynamic table:

Criteria	INCREMENTAL	FULL	AUTO
Best for	Append-heavy base tables with small change volumes	Definitions with unsupported constructs or high change volumes	Exploration and prototyping
Cost per refresh	Lower (processes only changed data)	Higher (reprocesses all data)	Depends on resolved mode
Unsupported constructs	Creation fails immediately	Always works	Silently resolves to FULL
Mode visibility	Explicit in DDL	Explicit in DDL	Requires SHOW DYNAMIC TABLES to verify
Production recommendation	Yes, when the definition is compatible	Yes, when incremental isn’t possible or is slower	Use for exploration, then set explicitly

Refresh mode is immutable¶

The refresh mode is set at creation time and can’t be changed with ALTER DYNAMIC TABLE. To switch modes, recreate the dynamic table with CREATE OR REPLACE, or use CREATE OR ALTER DYNAMIC TABLE (public preview), which triggers a reinitialization. For the full CREATE syntax with all options, see CREATE DYNAMIC TABLE.

Note

CREATE OR REPLACE drops and recreates the dynamic table. The initial refresh runs again, and all previously materialized data is replaced. Downstream dynamic tables that depend on this table trigger a reinitialization.

Incremental refresh does not have a fixed fallback threshold¶

The refresh optimization page recommends keeping changes to fewer than five percent of data between refreshes for best incremental performance. The five-percent figure is a performance guideline. Snowflake never automatically switches a dynamic table’s refresh mode based on change volume.

What does happen: when a large fraction of the base table data changes between refreshes, incremental refresh can become slower than full refresh. The configured refresh mode stays as you set it. Snowflake does not automatically switch to a different mode.

If incremental refresh is consistently slower than full refresh for your workload, recreate the dynamic table with REFRESH_MODE = FULL.

Refresh modes in pipelines¶

Each dynamic table in a pipeline can have its own refresh mode. An upstream dynamic table using full refresh does not force downstream dynamic tables to use full refresh.

One constraint applies: a dynamic table in incremental refresh mode can only be downstream from a dynamic table with full refresh mode if the upstream full-refresh dynamic table has a system-derived unique key or an IMMUTABLE WHERE constraint. For more information, see Optimize input data for dynamic tables and Immutability constraints and backfill.

What’s next¶

Optimize queries for incremental refresh to learn how to write definitions that work well with incremental refresh.
Supported queries for dynamic tables for the full list of constructs compatible with incremental refresh.
Monitor dynamic tables to track refresh status, cost, and performance.
Set the target lag for a dynamic table to control how often refreshes are triggered.