What are the different states a dynamic table can be in (e.g., active, suspended)?

Snowflake's dynamic tables can exist in various states that reflect their current status and operational condition. Here's a breakdown of the key states:

Scheduling State (SCHEDULING_STATE):

• RUNNING: The dynamic table is currently scheduled to refresh at regular intervals.
• SUSPENDED: The refresh schedule is temporarily paused. This can happen manually or automatically due to errors.

Refresh State (DYNAMIC_TABLE_STATE_HISTORY):

• INITIALIZING: The dynamic table is being created for the first time.
• ACTIVE: The table is successfully created and operational. Within this state, there are sub-states:
  • SUCCEEDED: The most recent refresh completed successfully.
  • SKIPPED: A scheduled refresh was skipped due to reasons like upstream table not being refreshed or load reduction for performance reasons.
  • IMPACTED: The dynamic table itself might be functional, but upstream dependencies might be experiencing issues, potentially impacting data accuracy.
• FAILED: The most recent refresh attempt encountered an error. The table might still contain data from the previous successful refresh.

Additional States:

• CANCELLED: A currently running refresh was manually stopped.

How to View Dynamic Table States:

You can utilize Snowflake's system functions to get insights into the current and historical states of your dynamic tables. Here are two commonly used functions:

• DYNAMIC_TABLE_STATE_HISTORY: This function provides detailed information about the refresh history of a dynamic table, including timestamps and states like SUCCEEDED, FAILED, or SKIPPED.
• DYNAMIC_TABLE_GRAPH_HISTORY: This function offers a broader view of your entire data pipeline, showcasing the scheduling state (RUNNING or SUSPENDED) of all dynamic tables and their dependencies.

By understanding these states and leveraging the available functions, you can effectively monitor the health and performance of your dynamic table pipelines in Snowflake.