Snowflake Dynamic Table — Complete Guide — 2

In Part 1 of my blog series, I introduced the concept of Dynamic tables and provided a step-by-step guide on how to create them. Now, in Part 2, we will explore advanced techniques and strategies for effectively working with Dynamic tables. Join me as we dive deeper into this topic and unlock the full potential of Dynamic tables in your data management workflow.

After successfully creating a dynamic table, you can utilize the following command to obtain a list of the total number of dynamic tables available in the database. This command provides valuable insights into the extent of dynamic table utilization within your data environment.

show dynamic tables;

The mentioned command provides a comprehensive view of dynamic tables within the current/specified database or schema, as well as across the entire account. It offers detailed information such as refresh mode, target lag, and other relevant details. It is important to note that this command differs from the traditional Show tables command, as it specifically focuses on dynamic tables and provides additional insights related to their functionality and configuration.

Figure 1 showcases the supplementary features tailored for Dynamic Tables.

Here are a few other helpful commands:

DESC DYNAMIC TABLE EMPLOYEE_DET;

The provided command allows you to describe the columns within the dynamic table called EMPLOYEE_DET.

Snowsight:

Now, let’s explore how to access dynamic tables within a schema and view detailed information about a specific dynamic table using Snowsight. This feature allows you to seamlessly navigate through the dynamic tables within your schema and access valuable insights about each table. By leveraging Snowsight, you can effortlessly analyze the structure, properties, and metadata of dynamic tables, empowering you to make informed decisions and gain a comprehensive understanding of your data assets.

Figure 2. Steps to access and view information about dynamic tables

In the figure above, we can follow the steps outlined in Fig.2 to access and view information about dynamic tables:

Step 1: Start by navigating to the Data section and selecting Databases. Using the database object explorer located next to the left navigation panel, choose the desired database schema.

Step 2: Once on the schema details page, click on the Dynamic Tables tab. Alternatively, you can expand the Dynamic Tables section in the database object explorer to view the list of dynamic tables. In this example, we will be focusing on the Employee_DET dynamic table.

Step 3: The selected dynamic table will be highlighted to indicate that it has been chosen.

Step 4: Detailed information about the dynamic table will be displayed on the table details page

On this page, you will find tabs that offer various pieces of information about the dynamic table, similar to what is provided on the table details page. These tabs include:

1. Definition: This tab displays the definition of the dynamic table, along with the privileges that have been granted for working with the table. It provides insights into the structure and characteristics of the dynamic table.
2. Columns: The Columns tab presents information about the columns within the dynamic table. It showcases details such as column names, data types, and any additional attributes or constraints associated with each column.
3. Data Preview: In this tab, you can get a preview of the data stored in the dynamic table. It displays a subset of the table’s rows, typically up to 100 rows, allowing you to quickly assess the content and format of the data.

By navigating through these tabs, you can conveniently access essential details about the dynamic table, gain visibility into its structure, and preview a snapshot of the data it holds. This information empowers you to make informed decisions and effectively work with the dynamic table.

In the next section, we will explore the relationship between Dynamic tables and base tables within a Directed Acyclic Graph (DAG). This connection plays a crucial role in understanding the flow and dependencies of data transformations and operations. By examining this relationship, we can gain insights into how Dynamic tables are integrated into the overall data processing pipeline.

DAG (Directed acyclic Graph):

Fig. 3 DAG — Understanding connection between tables

In the figure above, we can follow the steps outlined in Fig.3 to view information about the DAG and understand the connections between tables.

Step 1: Select the Graph tab to visualize the DAG (Directed Acyclic Graph) of the selected dynamic table.

Step 2: Explore the connections between base tables in this example, where the Employee and Employee_skill tables are joined together to generate the Employee_Det table. Additional tables, if present, will also be displayed.

Step 3: Gain insights from the consolidated view of the EMPLOYEE_DET dynamic table. Discover the Target lag set for this table and the Maximum lag experienced in the last 24 hours. In the given example, the maximum lag observed during the last 24-hour cycle was 3 minutes and 57 seconds, indicating the time taken to refresh the table.

In the next section, if you wish to view the Refresh History of selected dyanmic table in the UI, you can follow these steps:

Fig 4. Refresh History Page

Step 1: The selected dynamic table will be highlighted to indicate that it has been chosen.

Step 2: To access the Refresh History tab.

Step 3: The top section of this tab provides essential details, including:

• The current date and time indicating the up-to-date status of the dynamic table.
• The target lag time specified for the dynamic table.
• The longest actual lag time experienced by the dynamic table within the specified interval.

Step 4: Additionally, the tab presents a comprehensive table that chronicles the history of refreshes. Each entry in the table contains the following information:

• The transactional timestamp when the refresh was evaluated (note that this timestamp might be slightly earlier than the actual start time of the refresh).
• The duration taken for the refresh process to complete.
• The target lag and the maximum lag achieved just before the refresh commits.
• The status of the refresh.

To access the query profile, simply click on the query profile icon, which will then direct you to the corresponding query profile page.

Get ready to explore the fascinating world of dynamic table metadata in my upcoming medium series! Discover the secrets of effortlessly accessing and utilizing valuable information through the Information Schema and other powerful features. Don’t miss out on these insightful insights and practical tips to make the most of dynamic table metadata.

References:-

• https://www.snowflake.com/

About me:

I am a Data Engineer and Cloud Architect with experience as a Senior Consultant at EY GDS. Throughout my career, I have worked on numerous projects involving legacy data warehouses, big data implementations, cloud platforms, and migrations. If you require assistance with certification, data solutions, or implementations, please feel free to connect with me on LinkedIn.