Diagnosing common dynamic table refresh issues

This topic addresses solutions for troubleshooting dynamic tables that don’t refresh 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 Dynamic table limitations and Dynamic table access control.

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

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 Best practices for choosing dynamic table refresh modes.

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 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.
Language: English