Snowflake dynamic tables not refreshing - suspended tables, failed refreshes and lag that keeps growing

A dashboard built on a dynamic table shows yesterday's numbers. Nobody changed the query. The table hasn't refreshed, or it refreshes but never catches up to its target lag. This guide finds the cause in about five minutes and covers the fixes that won't make things worse.

#Check the health of every dynamic table

Start with one query across the schema or database. It shows which tables are behind and by how much.

SHOW DYNAMIC TABLES IN DATABASE analytics;

SELECT "name", "schema_name", "scheduling_state", "refresh_mode",
       "refresh_mode_reason", "target_lag", "warehouse",
       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;

data_timestamp is the point in time the table's contents reflect. If actual_lag_minutes is much bigger than target_lag, the table is stale. scheduling_state tells you if it is still scheduled at all.

#Read the refresh history

Failed and skipped refreshes are recorded with a state code and message:

SELECT name, state, state_code, state_message,
       refresh_action, refresh_trigger,
       refresh_start_time, refresh_end_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
       NAME_PREFIX => 'ANALYTICS.MARTS'))
WHERE state IN ('FAILED', 'UPSTREAM_FAILED', 'SKIPPED')
ORDER BY refresh_start_time DESC
LIMIT 50;

Run it with the database set to the dynamic table's database. For history older than the table function keeps, or across the account, use SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY (account usage views have ingestion latency).

To see why a table is suspended, check the graph history:

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

#Symptom, cause, fix

#Fix it

#Fix the error, refresh, then resume

Don't resume a table that is still broken. It will fail again and you'll be back to suspended. Fix the cause, prove it with a manual refresh, then resume:

-- Needs OPERATE on the dynamic table (OWNERSHIP also works)
ALTER DYNAMIC TABLE analytics.marts.dt_orders REFRESH;

-- Check the refresh worked
SELECT state, state_message, refresh_end_time
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
       NAME => 'ANALYTICS.MARTS.DT_ORDERS'))
ORDER BY refresh_start_time DESC
LIMIT 1;

ALTER DYNAMIC TABLE analytics.marts.dt_orders RESUME;

In a chain, fix and resume from the most upstream table down.

#Restore change tracking on base tables

Incremental refreshes depend on change tracking on every base table. If a load job replaced the table, turn it back on:

ALTER TABLE raw.sales.orders SET CHANGE_TRACKING = TRUE;

Expect a reinitialization refresh next, which rebuilds the dynamic table. Streams on the dynamic table will then show a delete and insert for every row. Warn the consumers of those streams before it happens.

Warning

dbt models materialized as table run CREATE OR REPLACE by default. If a dynamic table reads from one, every dbt run destroys the change history and forces a reinitialization. Make the upstream model incremental, or accept FULL refreshes on purpose.

#Deal with skipped refreshes and growing lag

If the refresh duration is longer than the target lag, refreshes get skipped. Compare them:

SELECT name,
       AVG(DATEDIFF('second', refresh_start_time, refresh_end_time)) AS avg_refresh_s,
       COUNT_IF(state = 'SKIPPED') AS skipped
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
       NAME_PREFIX => 'ANALYTICS.MARTS'))
WHERE refresh_start_time > DATEADD('day', -1, CURRENT_TIMESTAMP())
GROUP BY name
ORDER BY avg_refresh_s DESC;

Then change whichever lever is cheapest:

ALTER DYNAMIC TABLE analytics.marts.dt_orders SET WAREHOUSE = transform_m_wh;
ALTER DYNAMIC TABLE analytics.marts.dt_orders SET TARGET_LAG = '30 minutes';

-- Intermediate tables: refresh only when a downstream table needs them
ALTER DYNAMIC TABLE analytics.int.dt_orders_enriched SET TARGET_LAG = DOWNSTREAM;

Also check whether the refresh warehouse is queuing behind other work. A dedicated warehouse for dynamic table refreshes makes the timing predictable. See the Snowflake slow-query guide for queuing and spilling.

#Recreate when you have to

A table must be recreated if its refresh mode is wrong or the base data it needs has expired. Save the definition first:

SELECT GET_DDL('DYNAMIC_TABLE', 'analytics.marts.dt_orders');

Caution

CREATE OR REPLACE DYNAMIC TABLE drops the existing contents and grants, and any streams on it go stale. Re-apply grants (or use COPY GRANTS), recreate dependent streams, and do it in a quiet window. The first refresh is a full build, so budget the warehouse time.

#Check permissions when refreshes fail

Refreshes run with the owner role's privileges. If someone revoked a grant from that role, refreshes fail. The usual causes are lost SELECT on a base table, lost USAGE on the warehouse, database or schema, or a role that was dropped and recreated. The state_message usually names the object. Check the grants:

SHOW GRANTS TO ROLE transform_role;

Row access policies and masking policies on base tables can also block incremental mode. This happens most often with policies that call context functions such as CURRENT_ROLE(). Either move the policy onto the dynamic table itself or use REFRESH_MODE = FULL.

#Prevent it next time

• Alert on staleness. Schedule a task or monitoring job that runs the health query and alerts when actual_lag_minutes is more than twice target_lag, or when scheduling_state is not ACTIVE.
• Suspend around DDL. Before you change a base table's structure, suspend the dynamic tables that read from it. Resume them afterwards.
• Never replace base tables. Use MERGE, INSERT or INSERT OVERWRITE so change tracking survives.
• Pin the refresh mode. Set REFRESH_MODE = INCREMENTAL explicitly on tables that must be incremental. Creation then fails loudly instead of quietly falling back to FULL.
• Use TARGET_LAG = DOWNSTREAM for intermediate tables, so only the tables people query have a lag to meet.
• Document the owner role and keep it out of routine role cleanups.