Troubleshooting dynamic tables

This topic addresses solutions for troubleshooting dynamic tables that don’t run as expected:

Some actions might be restricted due to limitations on using dynamic tables or if you don’t have the necessary privileges. For more information, see Known limitations for dynamic tables and Dynamic table access control.

For information about dynamic table costs, see Understanding cost for dynamic tables.

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

Troubleshoot dynamic table refresh

Issue

Solution

My dynamic table is using full refresh instead of incremental refresh.

A dynamic table’s actual refresh mode is determined at creation time and is immutable afterward. If not specified explicitly, the refresh mode defaults to AUTO, which selects a refresh mode based on various factors such as query complexity, or unsupported constructs, operators, or functions.

For consistent behavior across Snowflake releases, explicitly set the refresh mode on all dynamic tables. For example, if you want your dynamic tables to refresh only incrementally, you must explicitly set the refresh mode to INCREMENTAL when creating them, keeping in mind that there might be some limitations on using incremental refresh. For more information, see Set the refresh mode for all production dynamic tables.

Using a role with the necessary privileges, you can verify the refresh mode using one of the following methods:

  • Using SQL: Run the SHOW DYNAMIC TABLES statement. In the output, the text column shows the user-specified refresh mode, the refresh_mode column shows the actual refresh mode, and the refresh_mode_reason shows why the actual refresh mode was chosen.

  • Using Snowsight: In the navigation menu, select Monitoring » Dynamic Tables, and then select your dynamic table. You can view the refresh mode for the dynamic table in the Table Details tab.

My dynamic table’s incremental refresh is slow.

A dynamic table’s refresh performance relies on specific assumptions about the workload or data it’s handling.

Use Refresh History to view variance or spot outliers:

  1. Sign in to Snowsight.

  2. In the navigation, go to Monitoring » Dynamic Tables.

  3. Select your dynamic table and go to the Refresh History tab.

  4. Use your dynamic table’s refresh durations over the last 24 hours to troubleshoot.

My dynamic table is running an empty refresh but I am seeing a cost.

Refreshes that produce zero net new rows (that is, zero rows added, updated, or deleted) consume warehouse resources when they’re associated with changes in any of the upstream objects referenced by the dynamic table.

For example, if the associated virtual warehouse is suspended and no changes in base objects are identified, the suspended virtual warehouse doesn’t resume and no credits are consumed. This is referred to as a NO_DATA refresh. Conversely, if changes are identified, the virtual warehouse is automatically resumed to process the updates, which consumes warehouse resources even if the net result is zero rows applied to the dynamic table.

If you’re seeing a cost but you haven’t made any changes to your dynamic table, it might be due to a change in your source table. You can use the Refresh History tab in Snowsight to check if virtual warehouse credits were consumed:

  1. Sign in to Snowsight.

  2. In the navigation menu, select Monitoring » Dynamic Tables.

  3. Select your dynamic table and go to the Refresh History tab.

  4. Check the Warehouse used only checkbox to view refreshes that used the warehouse to update.

For more information, see Understanding cost for dynamic tables.

My dynamic table skipped a refresh.

Dynamic tables refresh on a schedule. When a scheduled refresh starts, the following situations might cause the refresh to skip:

  • If the dynamic table being refreshed has another dynamic table upstream, and the refresh for the upstream failed or was skipped.

  • If a previous refresh for the dynamic table is still running.

  • If the dynamic table’s refresh often takes longer than the target lag, Snowflake might skip a refresh to reduce the rate of future skips.

Manual refreshes are never skipped but they can cause other refreshes to skip, especially if you perform frequent manual refreshes on a dynamic table. Doing so can prevent downstream dynamic tables from refreshing. For this reason, Snowflake doesn’t recommend you frequently perform manual refreshes on a dynamic table with downstream dynamic tables that are expected to refresh according to target lag.

My dynamic table’s refresh failed.

Like a regular Snowflake query, a dynamic table refresh can fail due to issues with the dynamic table’s query definition, input data (for example, parsing errors), or internal system issues.

To determine why the refresh failed, do the following:

  1. Sign in to Snowsight.

  2. In the navigation menu, select Monitoring » Dynamic Tables.

  3. Select your dynamic table and go to the Table Details tab.

  4. Hover over Last Completed Refresh under Details. A dialog detailing the error code and message for the failed refresh appears.

If the refresh failed due to an upstream_failed error code, it might be because one of the upstream dynamic tables has an issue. To determine which upstream table caused the failure:

  1. In the Snowsight navigation menu, select Monitoring » Dynamic Tables and then select your dynamic table.

  2. Open the Graph page to view your dynamic table’s graph history.

  3. From the graph, select the upstream dynamic table that’s red, and check its Refresh History.

My dynamic table is reinitializing.

Your dynamic table might be reinitializing due to one of the following reasons:

  • One or more of the inputs of the dynamic table are replaced. For example, if your dynamic table is defined on a view, and you replace the view, the dynamic table has to reinitialize.

  • If the schema of the inputs changed and your dynamic table relies on the changed columns.

  • Data access policies are added, removed, or changed on the dynamic table’s inputs.

  • Cloned incremental dynamic tables might need to reinitialize on their first refresh after being created.

  • Replicated dynamic tables with incremental refresh reinitialize after failover before they can resume incremental refresh.

For general information about initialization, see Understanding dynamic table initialization.

Troubleshoot dynamic table creation

Issue

Solution

My dynamic table is taking a long time to create.

When you create a dynamic table using a CREATE DYNAMIC TABLE statement, its initial refresh takes place either at a scheduled time (ON_SCHEDULE) or synchronously at creation (ON_CREATE). The initial data population, or initialization, depends on when this initial refresh occurs. For example, synchronous initialization triggers a refresh of any referenced upstream dynamic tables, which can add additional processing time to the total duration of initialization.

Initialization can take some time, depending on how much data is scanned. To view progress, do the following:

  1. Sign in to Snowsight.

  2. In the navigation menu, select Monitoring » Query History.

  3. In the Filters dropdown, enter CREATE DYNAMIC TABLE in the SQL Text filter and enter your warehouse name in the Warehouse filter.

  4. Select the query with your dynamic table under SQL text and use the Query Details and Query Profile tabs to track progress.

For general information about initialization, see Understanding dynamic table initialization.

Debug dynamic tables

Issue

Solution

I can’t see the metadata for my dynamic table.

To view the metadata and Information Schema of a dynamic table, you must use a role that has the MONITOR privilege on that dynamic table. For more information, see Privileges to view a dynamic table’s metadata.

My dynamic table is suspended.

A dynamic table might be suspended for several reasons:

  • It was suspended directly using the ALTER DYNAMIC TABLE … SUSPEND command.

  • It is downstream of a suspended dynamic table.

  • It failed to refresh five consecutive times (skips don’t contribute to this count).

  • It is a replicated dynamic table, either in a replication group or failover group. See Replication and dynamic tables.

  • It was cloned from a dynamic table that has one or more base tables dropped at the time of cloning.

To see the reason why your dynamic table was suspended, do the following:

  1. Sign in to Snowsight.

  2. In the navigation menu, select Monitoring » Dynamic Tables.

  3. Select your dynamic table and go to the Table Details tab.

  4. Hover over Scheduling State under Details. A dialog detailing the reason and date of the suspension appears.
Language: English