Dynamic Tables – Refresh Modes (Full vs Incremental)
When a Dynamic Table is scheduled to update based on its Target Lag, Snowflake must decide how to process the new data. Snowflake automatically assigns one of two Refresh Modes to the Dynamic Table: Incremental or Full.
Understanding which mode your table uses is critical for cost management and performance tuning.
Topics Covered
- Full vs Incremental Refresh
- How Snowflake Assigns the Mode
- Supported SQL for Incremental Refresh
- Checking the Refresh Mode
1. Full Refresh Mode
In a Full Refresh, Snowflake drops the existing data in the Dynamic Table and completely re-executes the underlying SELECT query against the source tables from scratch.
- Pros: It is mathematically guaranteed to be 100% accurate.
- Cons: Extremely expensive and slow. If your source table has 10 billion rows, a Full Refresh scans all 10 billion rows every time the lag triggers (e.g., every hour).
2. Incremental Refresh Mode
In an Incremental Refresh, Snowflake only looks at the exact rows that changed in the source table since the last refresh. It calculates the "delta" and intelligently merges those changes into the Dynamic Table.
- Pros: Highly performant and significantly cheaper. Only new/changed data consumes compute credits.
- Cons: Snowflake cannot perform an Incremental Refresh on certain complex SQL queries.
How Snowflake Assigns the Mode
You cannot manually force an Incremental Refresh. Snowflake's query compiler analyzes the SELECT statement you used to create the Dynamic Table.
- If the SQL logic supports incremental computation, Snowflake automatically assigns
INCREMENTAL. - If the SQL logic is too complex or uses unsupported functions, Snowflake automatically falls back to
FULL.
Supported SQL for Incremental Refresh
To ensure your Dynamic Table gets the INCREMENTAL mode, stick to standard SQL:
- Simple
SELECTwithWHEREfilters. - Standard
JOINoperations (Inner, Left Outer). - Basic Aggregations (
SUM,COUNT,MIN,MAX) grouped by specific columns.
SQL that Forces a Full Refresh
If you use any of these patterns, Snowflake will permanently assign the table to FULL refresh mode:
- Non-deterministic functions: Functions that return a different result every time they run, such as
CURRENT_TIMESTAMP(),RANDOM(), orUUID_STRING(). - Complex Window Functions: Specifically functions that rely on total ordering without bounds, like
ROW_NUMBER() OVER (ORDER BY ...). UNION(WithoutALL): StandardUNIONrequires a full deduplication scan across the entire dataset. (UseUNION ALLinstead if you want incremental support).
Hands-On Demonstration: Checking the Refresh Mode
Let's build two Dynamic Tables for a telecom company in India. One will be written correctly to support Incremental refresh, and the other will use a non-deterministic function, forcing a Full refresh.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_TELECOM_DB;
USE SCHEMA BEEMS_TELECOM_DB.PUBLIC;
-- The source table
CREATE OR REPLACE TABLE RAW_CALL_RECORDS (
CALL_ID STRING,
CALLER_NAME STRING,
STATE STRING,
DURATION_MINUTES NUMBER
);
2. Creating the Dynamic Tables
-- Dynamic Table 1: Standard Aggregation (Supports Incremental)
CREATE OR REPLACE DYNAMIC TABLE STATE_CALL_SUMMARY
TARGET_LAG = '1 hour'
WAREHOUSE = COMPUTE_WH
AS
SELECT STATE, SUM(DURATION_MINUTES) AS TOTAL_MINUTES
FROM RAW_CALL_RECORDS
GROUP BY STATE;
-- Dynamic Table 2: Uses a Non-Deterministic Function (Forces Full Refresh)
CREATE OR REPLACE DYNAMIC TABLE RANDOMIZED_CALL_AUDIT
TARGET_LAG = '1 hour'
WAREHOUSE = COMPUTE_WH
AS
SELECT CALL_ID, CALLER_NAME, RANDOM() AS AUDIT_SCORE
FROM RAW_CALL_RECORDS;
3. Checking the Refresh Mode
As a Data Engineer, you must verify the refresh mode of your dynamic tables after creating them to ensure you aren't accidentally burning credits with full refreshes.
(To verify your current context, run SELECT CURRENT_USER();)
-- Check the refresh mode of all dynamic tables
SHOW DYNAMIC TABLES;
-- Or, query the Information Schema for a cleaner output
SELECT
NAME,
TARGET_LAG,
REFRESH_MODE, -- Will show INCREMENTAL or FULL
REFRESH_MODE_REASON -- Explains WHY it was forced to FULL
FROM INFORMATION_SCHEMA.DYNAMIC_TABLES
WHERE TABLE_SCHEMA = 'PUBLIC';
If you see FULL for STATE_CALL_SUMMARY, double-check the documentation, as Snowflake continuously updates the list of supported SQL functions for Incremental refreshes!
Summary
- Full Refresh recomputes the entire table from scratch; Incremental Refresh only processes the delta.
- Snowflake automatically assigns the refresh mode based on your SQL syntax.
- Avoid non-deterministic functions (
CURRENT_TIMESTAMP) and complex window functions if you want Incremental refreshes. - Always run
SHOW DYNAMIC TABLESafter creation to verify the assignedREFRESH_MODE.