Troubleshoot dynamic table refresh issues

This page helps you diagnose and resolve dynamic table refresh problems. For creation-time issues, see Troubleshoot dynamic table creation issues. For permission-related failures, see Troubleshoot dynamic table permission issues.

Start by running the quick health check below, which identifies the most common issues. If your symptom is not visible there, scan the section headings to find a match. For slow refresh diagnostics, see Optimize queries for incremental refresh.

-- Quick health check: find dynamic tables that are failing, suspended, or lagging
SHOW DYNAMIC TABLES;

SELECT "name", "scheduling_state", "refresh_mode", "target_lag",
       DATEDIFF('minute', "data_timestamp", CURRENT_TIMESTAMP()) AS actual_lag_minutes
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY actual_lag_minutes DESC NULLS FIRST;
+-------------------+-------------------+--------------+------------+---------------------+
| name              | scheduling_state  | refresh_mode | target_lag | actual_lag_minutes  |
+-------------------+-------------------+--------------+------------+---------------------+
| dt_orders_daily  | SUSPENDED         | INCREMENTAL  | 30 minutes | NULL                |
| dt_orders        | RUNNING           | INCREMENTAL  | 10 minutes | 12                  |
+-------------------+-------------------+--------------+------------+---------------------+

You can also view refresh status in the Refresh History tab in Snowsight.

Look for rows where scheduling_state is not RUNNING or where actual_lag_minutes greatly exceeds your target lag. If this query returns no rows, see Troubleshoot permission issues to check your role’s privileges.

If you encounter an issue not listed here, contact Snowflake Support.

Refresh fails with UPSTREAM_FAILED status

When a dynamic table depends on another dynamic table that failed to refresh, the downstream table reports UPSTREAM_FAILED. The root cause is always upstream.

  1. Find which dynamic tables show UPSTREAM_FAILED:

    SELECT name, state, state_code, state_message,
       refresh_start_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema'
))
WHERE state IN ('FAILED', 'UPSTREAM_FAILED')
ORDER BY refresh_start_time DESC
LIMIT 20;
    +---------------------+---------+-----------------+------------------------------------------+-------------------------+
| NAME                | STATE   | STATE_CODE      | STATE_MESSAGE                            | REFRESH_START_TIME      |
+---------------------+---------+-----------------+------------------------------------------+-------------------------+
| DT_ORDERS_DAILY    | FAILED  | UPSTREAM_FAILED | Some inputs failed to refresh: [STG_O... | 2025-01-16 12:00:00.000 |
| DT_ORDERS          | FAILED  | USER_ERROR      | SQL compilation error: invalid ident...  | 2025-01-16 11:59:45.000 |
+---------------------+---------+-----------------+------------------------------------------+-------------------------+

  2. Read the state_message column, which names the failing upstream table directly. In this example, dt_orders has a USER_ERROR, which means its own definition has a problem. That failure cascades to dt_orders_daily.

  3. Alternatively, open the Graph view in Snowsight to visualize the pipeline. Navigate to Transformation > Dynamic tables, select any table in the pipeline, and switch to the Graph tab. The failing upstream table is highlighted.

  4. Fix the root-cause table first. After the upstream table refreshes successfully, the downstream UPSTREAM_FAILED tables recover automatically on their next scheduled refresh.

Dynamic table is suspended

A suspended dynamic table stops refreshing entirely. No new refreshes are attempted until you resume it.

  1. Check the scheduling state and suspension reason:

    SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA my_db.my_schema;
    +------------+-------------------+-----------+
| name       | scheduling_state  | ...       |
+------------+-------------------+-----------+
| DT_ORDERS | SUSPENDED         | ...       |
+------------+-------------------+-----------+

  2. Determine why it was suspended. Query the scheduling state to see the reason code:

    SELECT name,
       scheduling_state:state::STRING AS state,
       scheduling_state:reason_code::STRING AS reason_code,
       scheduling_state:reason_message::STRING AS reason_message
  FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY())
  WHERE name = 'DT_ORDERS'
  ORDER BY valid_from DESC
  LIMIT 1;

    The most common causes are manual suspension (USER_SUSPENDED), five consecutive scheduled refresh failures (SUSPENDED_DUE_TO_ERRORS), and cascade from a suspended upstream table (UPSTREAM_USER_SUSPENDED or UPSTREAM_SUSPENDED_DUE_TO_ERRORS).

    For the complete list of suspension reason codes and resume cascade behavior, see Manage dynamic tables.

  3. Fix the root cause (for example, correct the column name in the dynamic table’s definition), then resume:

    ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;

    When you resume a dynamic table, downstream tables that were suspended as a cascade effect (reason UPSTREAM_USER_SUSPENDED or UPSTREAM_SUSPENDED_DUE_TO_ERRORS) also resume automatically. Tables you manually suspended must be resumed individually. Resuming restores schedulability but does not trigger an immediate refresh.

Dynamic table refreshes as FULL when you expected INCREMENTAL

When you set REFRESH_MODE = AUTO, Snowflake picks INCREMENTAL or FULL once at creation time. This choice is permanent. If your definition contains constructs that don’t support incremental refresh, AUTO resolves to FULL. The CREATE statement result indicates why FULL was chosen. Always verify the resolved mode after creation.

For how AUTO resolves and how to verify the resolved mode, see Dynamic table refresh modes.

If you need incremental refresh, fix the definition and recreate the dynamic table. The refresh mode is set at creation time and can’t be changed with ALTER. Recreate with CREATE OR REPLACE DYNAMIC TABLE ... REFRESH_MODE = INCREMENTAL to force validation at creation time. See Create a dynamic table for the full syntax.

Important

Setting REFRESH_MODE = INCREMENTAL explicitly causes creation to fail if the definition contains unsupported constructs, rather than falling back to FULL. Always set the refresh mode explicitly to avoid unexpected costs from full refreshes.

Dynamic table is not refreshing after RESUME

After you run ALTER DYNAMIC TABLE ... RESUME, a refresh may not be triggered immediately. If the dynamic table exceeds its target lag, investigate the following.

  1. Confirm the scheduling state changed to RUNNING:

    SHOW DYNAMIC TABLES LIKE 'dt_orders' IN SCHEMA my_db.my_schema;
    +------------+-------------------+
| name       | scheduling_state  |
+------------+-------------------+
| DT_ORDERS | RUNNING           |
+------------+-------------------+

  2. Incremental refresh relies on time-travel data. If the dynamic table was suspended longer than the DATA_RETENTION_TIME_IN_DAYS setting (default: 1 day for Standard Edition, configurable up to 90 days for Enterprise Edition and higher), inherited from the database or schema unless set explicitly, this data has expired and incremental refresh can’t find the historical change data. The refresh fails with:

    Time travel data is not available for table <TABLE>.
The requested time is either beyond the allowed time travel period
or before the object creation time.

    In this case, you must recreate the dynamic table. Use SELECT GET_DDL('DYNAMIC_TABLE', 'my_db.my_schema.dt_orders') to capture the existing definition before recreation. Downstream dynamic tables will also reinitialize after the recreation.

  3. If the scheduling state shows RUNNING but no refreshes appear in the history, trigger a manual refresh to confirm the dynamic table can refresh:

    ALTER DYNAMIC TABLE my_db.my_schema.dt_orders REFRESH;

If the warehouse was dropped or reassigned during suspension, see the warehouse error in Refresh fails with an error.

Refresh fails after a schema change on a base table

When a column referenced by the dynamic table’s definition is dropped or renamed in a base table, refreshes fail. The dynamic table can’t adapt to the schema change automatically unless it uses SELECT * with schema evolution (see Evolve dynamic table pipelines).

  1. Check recent refresh failures for schema-related errors:

    SELECT name, state, state_message
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders',
    ERROR_ONLY => TRUE
))
ORDER BY refresh_start_time DESC
LIMIT 1;
    +------------+---------+--------------------------------------------------------------+
| NAME       | STATE   | STATE_MESSAGE                                                |
+------------+---------+--------------------------------------------------------------+
| DT_ORDERS | FAILED  | SQL compilation error: invalid identifier 'ORDER_STATUS'     |
+------------+---------+--------------------------------------------------------------+

  2. Identify the schema change. Compare the dynamic table’s definition against the current base table schema:

    -- View the dynamic table's definition
SELECT GET_DDL('DYNAMIC_TABLE', 'my_db.my_schema.dt_orders');

-- View the base table's current columns
DESCRIBE TABLE my_db.my_schema.raw_orders;

    Look for column names in the GET_DDL output that don’t appear in the DESCRIBE output.

    -- GET_DDL shows the definition references order_status
-- DESCRIBE TABLE shows the column was renamed to status
+---------------+-------------------+
| name          | type              |
+---------------+-------------------+
| ORDER_ID      | NUMBER(38,0)      |
| CUSTOMER_ID   | NUMBER(38,0)      |
| ORDER_DATE    | DATE              |
| PRODUCT_NAME  | VARCHAR(16777216) |
| QUANTITY       | NUMBER(38,0)      |
| UNIT_PRICE    | NUMBER(10,2)      |
| STATUS        | VARCHAR(16777216) |
+---------------+-------------------+

  3. Resolve the mismatch. Either restore the dropped column on the base table or recreate the dynamic table with an updated definition that references the correct columns.

Tip

If your dynamic table uses explicit column lists and a base table schema change causes a refresh failure, consider switching to SELECT * or SELECT * EXCLUDE (...) for automatic schema evolution. See Evolve dynamic table pipelines for details.

Dynamic table reinitializes unexpectedly

A reinitialization is a forced full recompute of the entire dynamic table, even if it normally uses incremental refresh. This is more expensive than a regular refresh and produces a large change set for streams that depend on the dynamic table. Streams on the dynamic table survive reinitialization but their next read returns the entire table as changes. Recreate the stream if you want to reset the offset and avoid processing this large change set. See Streams on a dynamic table return a large change set after reinitialization.

  1. Confirm that a reinitialization happened by checking the refresh_action column:

    SELECT name, refresh_action, refresh_trigger, state, refresh_start_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders'
))
ORDER BY refresh_start_time DESC
LIMIT 5;
    +------------+----------------+-----------------+-----------+-------------------------+
| NAME       | REFRESH_ACTION | REFRESH_TRIGGER | STATE     | REFRESH_START_TIME      |
+------------+----------------+-----------------+-----------+-------------------------+
| DT_ORDERS | REINITIALIZE   | CREATION        | SUCCEEDED | 2025-01-16 12:00:00.000 |
| DT_ORDERS | INCREMENTAL    | SCHEDULED       | SUCCEEDED | 2025-01-16 11:50:00.000 |
+------------+----------------+-----------------+-----------+-------------------------+

  2. Identify the trigger. The most common causes are CREATE OR REPLACE on the dynamic table itself (appears as refresh_trigger = CREATION in refresh history) or on an upstream base table, and schema changes (dropping or renaming a column) on a base table that the dynamic table references.

    For the complete list of reinitialization triggers, see Evolve dynamic table pipelines.

  3. To control when reinitialization happens and avoid refresh failures during DDL changes (such as dbt runs), suspend the dynamic table before the DDL and resume it after. The dynamic table still reinitializes on its initial refresh after resume, but you avoid transient failures and control the timing:

    -- Before running DDL on base tables
ALTER DYNAMIC TABLE my_db.my_schema.dt_orders SUSPEND;

-- Run your DDL (dbt run, CREATE OR REPLACE, schema changes)

-- After DDL completes
ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;

For more information about reinitialization, see Reinitialization triggers.

Refreshes show cost but produce no new rows

A zero-row refresh still consumes compute if Snowflake detects upstream changes and resumes the warehouse to evaluate them.

  1. Check whether recent refreshes consumed warehouse resources:

    SELECT name, state,
       statistics:numInsertedRows::INT AS rows_inserted,
       statistics:numDeletedRows::INT AS rows_deleted,
       DATEDIFF('second', refresh_start_time, refresh_end_time) AS duration_sec
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders'
))
ORDER BY refresh_start_time DESC
LIMIT 10;
    +------------+-----------+---------------+--------------+--------------+
| NAME       | STATE     | ROWS_INSERTED | ROWS_DELETED | DURATION_SEC |
+------------+-----------+---------------+--------------+--------------+
| DT_ORDERS | NO_DATA   | NULL          | NULL         | 0            |
| DT_ORDERS | SUCCEEDED | 0             | 0            | 3            |
| DT_ORDERS | SUCCEEDED | 0             | 0            | 2            |
| DT_ORDERS | SUCCEEDED | 15            | 2            | 5            |
+------------+-----------+---------------+--------------+--------------+

  2. Understand the two types of zero-row refreshes:

    TypeWarehouse usedCostExplanation
    NO_DATANoNoneNo changes detected in upstream objects. The warehouse stays suspended.
    Zero-row SUCCEEDEDYesCompute credits consumedChanges were detected upstream, so the warehouse resumed to evaluate them, but the net result was zero rows applied to the dynamic table.

  3. If zero-row refreshes happen frequently and the cost is a concern, check whether upstream processes are generating changes that don’t affect the dynamic table’s output (for example, updating columns not referenced in the definition).

For more information, see Understanding costs for dynamic tables.

dbt or CREATE OR REPLACE breaks change tracking

When a base table is replaced with CREATE OR REPLACE, the new table is a different object. The dynamic table loses its reference to the original table’s change tracking history, causing incremental refresh to fail.

  1. Check refresh history for change-tracking errors:

    SELECT name, state, state_message
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders',
    ERROR_ONLY => TRUE
))
ORDER BY refresh_start_time DESC
LIMIT 5;

    The specific error depends on what changed. If only change tracking was lost (same schema), you see:

    Change tracking is not enabled or has been missing for the time range requested on table <TABLE>.

    If the schema also changed:

    Base TABLE <TABLE> dropped, cannot read from stream on it.

  2. If you use dbt or other tools that run CREATE OR REPLACE TABLE, use one of these workarounds:

    Option A: Suspend and resume around DDL changes

    Suspending prevents failures during the DDL window. The dynamic table still reinitializes after resume, but you avoid transient failures and control the timing.

    -- Before dbt run
ALTER DYNAMIC TABLE my_db.my_schema.dt_orders SUSPEND;

-- Run dbt

-- After dbt completes
ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;

    Option B: Re-enable change tracking after replacement

    If your tooling must use CREATE OR REPLACE, add a post-hook to re-enable change tracking:

    ALTER TABLE my_db.my_schema.raw_orders SET CHANGE_TRACKING = TRUE;

    Alternatively, include CHANGE_TRACKING = TRUE directly in the CREATE OR REPLACE TABLE statement.

    Option C: Use INSERT OVERWRITE instead of CREATE OR REPLACE

    INSERT OVERWRITE preserves the table object and its change tracking history:

    INSERT OVERWRITE INTO my_db.my_schema.raw_orders
SELECT * FROM my_db.my_schema.raw_orders_staging;

  3. After applying the workaround, resume the dynamic table if it was suspended:

    ALTER DYNAMIC TABLE my_db.my_schema.dt_orders RESUME;

Streams on a dynamic table return a large change set after reinitialization

When a dynamic table reinitializes, the internal table version resets. After a reinitialization, the stream exposes all row-level differences between its last-read offset and the reinitialized output. Every row in the new output appears as an INSERT, and every row from the previous version appears as a DELETE. This can produce a very large change set. For more details, see Use streams on dynamic tables.

  1. Confirm that the dynamic table reinitialized recently:

    SELECT name, refresh_action, refresh_trigger, refresh_start_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders'
))
WHERE refresh_action = 'REINITIALIZE'
ORDER BY refresh_start_time DESC
LIMIT 1;
    +------------+----------------+-----------------+-------------------------+
| NAME       | REFRESH_ACTION | REFRESH_TRIGGER | REFRESH_START_TIME      |
+------------+----------------+-----------------+-------------------------+
| DT_ORDERS | REINITIALIZE   | CREATION        | 2025-01-16 12:00:00.000 |
+------------+----------------+-----------------+-------------------------+

  2. Recreate the stream to reset the offset to the current state of the dynamic table:

    CREATE OR REPLACE STREAM my_db.my_schema.dt_orders_stream
    ON DYNAMIC TABLE my_db.my_schema.dt_orders;

  3. To prevent recurrence, minimize reinitializations. See Dynamic table reinitializes unexpectedly.

For more information about streams on dynamic tables, see Use streams on dynamic tables.

Scheduled refreshes are being skipped

A skipped refresh means Snowflake chose not to run a scheduled refresh. The dynamic table’s data doesn’t advance, but no error is reported.

  1. Check for skipped refreshes in the refresh history:

    SELECT name, state, state_code, state_message, data_timestamp
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema.dt_orders'
))
WHERE state = 'SKIPPED'
ORDER BY refresh_start_time DESC
LIMIT 10;
    +------------+---------+-------------------------------------+-------------------+-------------------------+
| NAME       | STATE   | STATE_CODE                          | STATE_MESSAGE     | DATA_TIMESTAMP          |
+------------+---------+-------------------------------------+-------------------+-------------------------+
| DT_ORDERS | SKIPPED | NOT_EFFECTIVE_TICK_TO_REFRESH        | ...               | 2025-01-16 11:50:00.000 |
| DT_ORDERS | SKIPPED | UPSTREAM_FAILED                     | ...               | 2025-01-16 11:40:00.000 |
+------------+---------+-------------------------------------+-------------------+-------------------------+

  2. Interpret the state_code:

    State codeMeaningResolution
    UPSTREAM_FAILEDAn upstream dynamic table failed or was skippedFix the upstream table first
    NOT_EFFECTIVE_TICK_TO_REFRESHSnowflake determined this scheduled refresh attempt was not effective (refresh duration greatly exceeds target lag)Increase the warehouse size, optimize the definition, or increase target lag

    SKIPPED with UPSTREAM_FAILED means a single refresh attempt is skipped because an upstream table failed this cycle. SUSPENDED means the table stopped refreshing entirely after repeated failures (five consecutive failures by default).

    If a previous refresh is still running when the next scheduled refresh attempt arrives, the new scheduled refresh attempt is skipped and the current refresh is allowed to complete. To reduce skips from overlapping refreshes, increase the warehouse size or increase the target lag.

  3. If you see many consecutive skips with NOT_EFFECTIVE_TICK_TO_REFRESH, Snowflake is adjusting because refreshes take much longer than the target lag. For example, a dynamic table with a 1-minute target lag but 1-hour refresh duration causes most scheduled refresh attempts to be skipped.

    To resolve this, either increase the target lag to a realistic value or improve refresh performance. See Optimize queries for incremental refresh.

Note

Manual refreshes (ALTER DYNAMIC TABLE ... REFRESH) are never skipped, but frequent manual refreshes on a dynamic table with downstream consumers can prevent those downstream dynamic tables from refreshing on schedule.

Refresh fails with an error

If a refresh fails because of a problem with the dynamic table’s own definition, input data, or permissions, the refresh history records the error details.

  1. Find the most recent failures:

    SELECT name, state, state_code, state_message, query_id,
       refresh_start_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
    NAME_PREFIX => 'my_db.my_schema',
    ERROR_ONLY => TRUE
))
ORDER BY refresh_start_time DESC
LIMIT 10;
    +------------+---------+------------+----------------------------------------------+--------------+-------------------------+
| NAME       | STATE   | STATE_CODE | STATE_MESSAGE                                | QUERY_ID     | REFRESH_START_TIME      |
+------------+---------+------------+----------------------------------------------+--------------+-------------------------+
| DT_ORDERS | FAILED  | USER_ERROR | SQL compilation error: invalid identifier ... | 01b7e3f2-... | 2025-01-16 11:59:45.000 |
+------------+---------+------------+----------------------------------------------+--------------+-------------------------+

  2. Common error messages and resolutions:

    Error messageCauseResolution
    SQL compilation error: invalid identifier '<COLUMN>'A column was dropped or renamed in a base tableRecreate the dynamic table with the correct column references
    Change tracking is not enabled or has been missing for the time range requestedBase table was replaced (CREATE OR REPLACE) or change tracking was disabledRe-enable change tracking or recreate the dynamic table. See dbt or CREATE OR REPLACE breaks change tracking.
    Dynamic Table '<TABLE>' could not be refreshed because warehouse '<WH>' is missingThe warehouse was dropped or the owner role lost USAGE on itRecreate or reassign the warehouse, or grant USAGE back to the owner role
    Insufficient privileges or Unknown functionThe dynamic table’s owner role lost privileges on source objects or functionsGrant the required privileges back to the owner role. Refreshes run as the owner role, not the querying user’s role.
    Some inputs failed to refresh: [<TABLE>]An upstream dynamic table failedSee Refresh fails with UPSTREAM_FAILED status
    Query contains context functions on which change tracking is not supportedA context function or policy on a base table prevents incremental refresh. This typically occurs when the dynamic table references a view that was later replaced with one containing incompatible constructs.Recreate the dynamic table after removing the incompatible function, or use FULL refresh mode
    Dynamic Table '<TABLE>' needs to be recreated because a base table changedA referenced column was dropped from a base table (incompatible schema change)Recreate the dynamic table with an updated definition

Tip

For resource failures (out of memory, query timeout) or errors not listed in the table above, use the query_id from refresh history to open the query profile in Snowsight. The execution graph shows which operator failed and resource consumption details. See Exploring execution times.

  1. To find failures across all dynamic tables in a database, use an event table. See for setup instructions.

What’s next

  • To troubleshoot creation-time issues, see Troubleshoot dynamic table creation issues.

  • To troubleshoot permission-related failures, see Troubleshoot dynamic table permission issues.

  • To investigate slow refreshes or optimize refresh performance, see Optimize queries for incremental refresh.

  • To set up proactive monitoring and alerts for dynamic table refresh failures, see Monitor dynamic tables.

  • To understand the difference between INCREMENTAL, FULL, and AUTO refresh modes, see Dynamic table refresh modes.

  • To query an event table for dynamic table failures across a database:

    SELECT timestamp,
       resource_attributes:"snow.executable.name"::VARCHAR AS dt_name,
       resource_attributes:"snow.query.id"::VARCHAR AS query_id,
       value:message::VARCHAR AS error
FROM my_event_table
WHERE resource_attributes:"snow.executable.type" = 'DYNAMIC_TABLE'
  AND resource_attributes:"snow.database.name" = 'MY_DB'
  AND value:state = 'FAILED'
ORDER BY timestamp DESC;
    +-------------------------+-------------------+--------------+----------------------------------------------+
| TIMESTAMP               | DT_NAME           | QUERY_ID     | ERROR                                        |
+-------------------------+-------------------+--------------+----------------------------------------------+
| 2025-01-16 12:00:00.000 | DT_ORDERS        | 01b7e3f2-... | SQL compilation error: invalid identifier ... |
+-------------------------+-------------------+--------------+----------------------------------------------+

    For more information about setting up event tables for dynamic table monitoring, see Monitor dynamic tables.

  • If you encounter an issue not covered here, contact Snowflake Support.