Monitor dynamic table performance¶

Key performance indicators¶

To monitor dynamic table performance, focus on the metrics described in this section.

Diagnose slow refreshes¶

When refreshes take longer than expected, follow these steps to identify the cause:

1. Check the refresh history for trends. Does refresh duration increase over time, or was there a sudden spike? For steps, see Monitor the refresh status for your dynamic tables.

2. Review the query profile to identify bottlenecks:

   • High partition scans suggest poor data locality.

   • Bytes spilled suggests that the warehouse is too small.

   • Specific operators taking a long time might indicate an opportunity to optimize your dynamic table query.

   For instructions, see Analyze query profiles.

3. Check lag metrics. If lag consistently exceeds your target, refreshes might not keep up with your data volume. For instructions, see Monitor the refresh status for your dynamic tables.

4. Review upstream dependencies. Check whether upstream tables cause delays or produce large volumes of changes.

   Use the Graph view in Snowsight to look for the following:

   • Upstream tables executing a refresh (shown with executing status).

   • Failed or suspended upstream tables.

   • Upstream tables taking longer than usual to refresh.

   To access the Graph view, see View the graph of tables connected to your dynamic tables.

5. Check the volume of changes that the dynamic table processes. Large volumes of changes from upstream dependencies can slow down refreshes.

   Use the DYNAMIC_TABLE_REFRESH_HISTORY function to see how many rows changed in recent refreshes:

   SELECT
     name,
     data_timestamp,
     statistics:numInsertedRows::INT AS rows_inserted,
     statistics:numDeletedRows::INT AS rows_deleted,
     refresh_action
   FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
     NAME => 'my_dynamic_table'
   ))
   ORDER BY data_timestamp DESC
   LIMIT 10;
   

   Copy

   When change volume is high relative to total table size (more than five percent of the table rows), consider using full refresh mode instead.

Common patterns and recommended actions:

• Refresh duration is stable, but lag is high: Your target lag is likely too aggressive for the current warehouse size and data volume. Refreshes finish successfully but can’t keep up with incoming changes. Check whether your target lag and warehouse resources match your data volume.

• Refresh duration suddenly spikes and bytes spilled are high: The warehouse doesn’t have enough memory to process the refresh, either because the warehouse is too small or because other queries are running at the same time. Increase the warehouse size or move dynamic table refreshes to a dedicated warehouse.

• Partition scans increase over time, but data volume stays the same: Your data locality is poor, which forces Snowflake to scan more partitions than necessary. Check your clustering keys and data locality. Also check whether upstream changes affect many scattered partitions instead of a few contiguous ones.

• Each refresh processes a large portion of the table (more than 5 percent of rows or partitions): Incremental refresh provides little benefit when most of the table changes frequently. Switch to full refresh mode or redesign your pipeline to reduce the amount of data that changes with each refresh.

Based on your findings, apply appropriate fixes from Optimize dynamic table performance.

Analyze query profiles¶

The query profile shows detailed execution statistics for each refresh. When a refresh is slow, the query profile helps you identify opportunities for optimization.

To access the query profile:

SELECT
  name,
  refresh_start_time,
  query_id
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'my_dynamic_table'
))
WHERE state = 'SUCCEEDED'
ORDER BY refresh_start_time DESC
LIMIT 5;

SELECT *
FROM TABLE(GET_QUERY_OPERATOR_STATS('<query_id>'));

What to look for:

• Partitions scanned vs. pruned: When partition scans are high relative to the total number of partitions, the cause is usually poor data locality or missing clustering.

• Time distribution: Check which operators consume the most time. Operators that take disproportionately long might indicate an opportunity to optimize your query. See Optimize queries for incremental refresh for operator-specific guidance.

• Bytes spilled to local or remote storage: High bytes spilled often indicate that the warehouse is too small for the refresh workload or that other queries running on the same warehouse reduce the memory available for refreshes. Consider increasing the warehouse size or running dynamic table refreshes on a dedicated warehouse to reduce contention.

For more guidance on how to address issues found in the query profile, see Optimize dynamic table performance.

Monitor warehouse usage¶

To check whether your warehouse can handle your dynamic table workload and find ways to reduce costs, monitor warehouse usage.

Key metrics to monitor:

• Bytes spilled: Bytes spilled to local or remote storage means that the warehouse might be too small. Consider increasing warehouse size. For more details on identifying and troubleshooting bytes spilled, see Finding queries that spill to storage.

• Warehouse utilization: Check whether the warehouse has enough resources for refresh workloads. Low utilization means you might have an oversized warehouse. High queue times mean your warehouse is too small or runs too many concurrent queries.

• Query queuing: Queued queries delay refresh execution. If refreshes frequently queue, increase warehouse size, use a dedicated warehouse for dynamic table refreshes, or consider a multi-cluster warehouse to handle variable workloads.

• Credit usage: Track credits to balance performance with costs. Monitor regularly to find opportunities to right-size warehouses or adjust refresh schedules.

To view warehouse usage and queue times, see Reducing queues. Optimize warehouse configuration for dynamic tables with Optimize dynamic table performance.

Monitor dependencies¶

Dependencies between dynamic tables can impact performance. Performance issues in upstream tables cascade to downstream tables because a downstream table must wait for upstream tables to complete their refreshes before it can start its own refresh.

To diagnose performance issues related to upstream dependencies, see Diagnose slow refreshes.

To view the graph of dependencies, see View the graph of tables connected to your dynamic tables.

Set up alerts for performance issues¶

You can set up alerts to notify you when performance degrades. We recommend creating alerts for the following conditions:

• Refresh duration exceeds a threshold.

• Lag consistently misses the target.

Alerts use event tables to track refresh events. For setup instructions, see Event table monitoring and alerts for dynamic tables.