Picture this: You have a query, and you want to materialize its results in a table. Instead of going through the hassle of creating a separate target table, writing transformation code, and manually updating the data, Snowflake’s dynamic tables come to the rescue.
What are Dynamic tables in Snowflake?
Dynamic tables serve as a remarkable solution by effortlessly materializing the results of your specific query. Often, we encounter extensive transformation logic involving multiple joins and union statements, which we typically address by creating new tables or views. However, when these queries or transformations need to be frequently used, they consume substantial computing resources. Thankfully, with the advent of dynamic tables, we now have the ability to materialize these complex transformations or query logic. This not only conserves computing power but also enhances the efficiency of our processes.
Dynamic Tables revolutionize the process of creating and managing data pipelines, providing teams with a seamless experience and the assurance to build robust pipelines for production. Previously, data engineers had to rely on Streams, Tasks, and manual management of database objects (tables, streams, tasks, and SQL DML code) to construct data pipelines in Snowflake. However, with the introduction of Dynamic Tables, the process becomes significantly simplified. Take a look at the following diagram for a visual representation of this streamlined approach:
Why did I start using Snowflake Dynamic Tables?
Here are some reasons why I started implementing the dynamic tables —
- We have multiple complex Snowflake views that are built using complicated logic and involve numerous tables and views. These views are essential for our daily tasks, but because they are quite complex, they take a long time to process and use up a lot of computing resources. One such example is the creation of Fact views.
- To enhance efficiency and reduce costs, we decided to materialize these complex views. By materializing the views, we aim to accelerate our processes and minimize resource consumption. This approach will enable us to store pre-computed results, resulting in faster data retrieval and reduced computing expenses.
- Furthermore, these views serve as the foundation for generating reports on Looker, which are accessed by various stakeholders within the company. However, if the views are complex and take a significant amount of time to load, it directly impacts the speed and responsiveness of the Looker reports for these stakeholders.
When to use Dynamic tables with use cases?
1. You don’t want to write your own code to track data dependencies and manage the refreshes of data:
Use case: Suppose you have a complex data pipeline with multiple dependent tables. By leveraging dynamic tables, you can automate the tracking of data dependencies and refresh schedules, saving you the effort of writing and maintaining custom code for these tasks.
2. You want to avoid the added complexity of streams and tasks:
Use case: If you have a scenario where using streams and tasks to manage data updates is overly complex for your needs, dynamic tables provide a simpler alternative. You can materialize the results of your queries without the additional overhead of managing streams and tasks.
3. You don’t need fine-grained control over the schedule of refreshes:
Use case: Let’s say you have a use case where the specific schedule of data refreshes is not critical. Dynamic tables offer a convenient solution by handling the refreshes automatically, relieving you from the need to manage and configure granular refresh schedules.
4. You need to materialize the results of a query of multiple base tables:
Use case: Imagine you have a complex query involving multiple base tables and you frequently rely on the results of this query. By utilizing dynamic tables, you can materialize these query results, allowing for faster access and reducing the computational overhead of executing the query repeatedly.
5. You don’t need to use query constructs currently unsupported for dynamic tables:
Use case: In situations where you have queries that rely on unsupported query constructs like stored procedures, non-deterministic functions (not listed in the supported functions documentation), external functions, or user-defined functions (UDFs), dynamic tables may not be suitable. In such cases, alternative methods would need to be considered to materialize and optimize the query results.
Privileges Required for Creating Dynamic Tables
This is the most important part of dynamic tables. Creating dynamic tables requires specific privileges to be granted. These privileges are crucial for managing and manipulating dynamic tables effectively.
To create a dynamic table, it is necessary to possess the following privileges:
- USAGE privilege on the database and schema where the table will be created.
- CREATE DYNAMIC TABLE privilege on the schema where the table will be created.
- SELECT privilege on the existing tables and views that will be queried to populate the dynamic table.
- USAGE privilege on the warehouse will be utilized for refreshing the table.
Moreover, to query a dynamic table or create a dynamic table that queries another dynamic table, the following privilege is required:
- SELECT privilege on the dynamic table itself.
Before proceeding with the creation of dynamic tables, it is crucial to ensure that the aforementioned privileges are granted and each point is adequately addressed. Confirm that the necessary privileges have been assigned to the user or role to prevent any authorization issues during the creation and querying of dynamic tables.
How to create Dynamic Tables?
To create a dynamic table, you can use the CREATE DYNAMIC TABLE command, which involves specifying the query to be used, the desired lag of the data, and the warehouse to be utilized for performing the refreshes.
Here’s an example syntax for creating a dynamic table:
CREATE OR REPLACE DYNAMIC TABLE dynamic_table_name
LAG = <lag_value> -- 1 minute is the minimum lag time.
WAREHOUSE = warehouse_name
AS
<query>;
dynamic_table_name: This is the name you want to assign to the dynamic table.<query>: Replace this placeholder with the actual query that retrieves the data you wish to materialize.<lag_value>: Specify the desired lag time (in days) for the data in the dynamic table. This determines how often the dynamic table is refreshed with the latest data.warehouse_name: Provide the name of the Snowflake warehouse that will be used for performing the refreshes of the dynamic table.
Once the dynamic table is created, it will automatically refresh based on the specified lag value, ensuring the data remains up-to-date.
Please find the below example of creating the dynamic tables.
CREATE OR REPLACE DYNAMIC TABLE fact_readings
LAG = '1 minutes' -- 1 minute is the minimum lag time.
WAREHOUSE = dynamic_table_warehouse
AS
SELECT reading_id, meter_name FROM fact_readings;
Similar to a materialized view, the columns present in a dynamic table are determined by the columns specified in the SELECT statement used during its creation. If any columns are expressions, it is necessary to provide aliases for those columns within the SELECT statement.
Monitoring Dynamic Tables
To obtain a list of dynamic tables within a schema and gather information about a specific dynamic table, there are multiple ways:
- Using Snowsight
- Using SQL command
In Snowsight, you can view the list of dynamic tables under
Data → Databases → Schema → Dynamic Tables
You can view the details about a specific dynamic table by selecting the dynamic table under the Dynamic Tables tab. The Dynamic Table details page will appear.
Furthermore, within the dynamic table details page, you will find various tabs that offer valuable insights into the dynamic table:
- The Graph tab showcases the directed acyclic graph (DAG) containing the dynamic table.
- The Refresh History tab provides a comprehensive view of the refresh history, allowing you to track and monitor the refreshes of the dynamic table.
If you want to view the list of dynamic tables using SQL command, use the SHOW DYNAMIC TABLES command.
SHOW DYNAMIC TABLES LIKE 'fact_%' IN SCHEMA factdb.myschema;
To obtain information regarding the columns within a dynamic table, you can utilize the DESCRIBE DYNAMIC TABLE command. For instance, if you wish to list the columns present in the dynamic table named “fact_readings,” you can execute the following command:
DESC DYNAMIC TABLE product;How to Drop a Dynamic Table?
To remove a dynamic table, you have two options: Snowsight or SQL commands.
Using Snowsight:
- Navigate to the dynamic table details page.
- Click on the More menu, located in the upper-right corner of the page.
- Select the “Drop” option.
Using SQL:
- Employ the DROP DYNAMIC TABLE command.
- For instance, if you want to drop the dynamic table named “product,” execute the following SQL command:
DROP DYNAMIC TABLE fact_readings;As Dynamic Tables are presently accessible in a private preview, a comprehensive understanding of their costing, performance, and efficiency aspects will be gained upon the release of the public version. In forthcoming articles, I will provide additional insights, recommendations, updates, and use cases to further illuminate the capabilities and benefits of Dynamic Tables. Stay tuned for more valuable information and analysis in the upcoming content.
Before you leave:
If you liked this article, don’t forget to give me a few claps, follow me and thus receive all updates about new publications.
I hope this helps. Let me know if you have more questions.
I offer consultancy services for various areas, including data warehouse migrations, cost reduction strategies, data management practices, and data modelling expertise. If you require assistance or guidance in any of these domains, feel free to reach out, and I’ll be more than happy to provide my expertise and support.
Email — imharmanbhatia@gmail.com
Please feel free to reach out via email, LinkedIn or Twitter.
References —
https://www.snowflake.com/blog/dynamic-tables-delivering-declarative-streaming-data-pipelines/