Dynamic Tables
1. Dynamic Tables
We just spent two videos learning about tables — including how to create them, the different data types, how to insert data, and so on. We also just learned about views and materialized views. I want to take a moment here to introduce you to a Snowflake object type that sits right in the middle of that world, and has become one of my favorite things in Snowflake: **dynamic tables**. What problem does a dynamic table solve? Well, imagine you've got raw data coming into Snowflake regularly — like new food truck orders landing in the Tasty Bytes orders table every hour. Downstream from that, you've got an analytics table that your BI team is querying. That analytics table is a cleaned-up, aggregated version of the raw data. The question is: how do you keep that analytics table up-to-date as the raw data changes? You've got a few options. You could write a stored procedure that reruns the transformation on a schedule. Alternatively, you could use a materialized view — but as we've learned, those don't support joins, and your transformation almost certainly involves joins. Maybe you could set up streams and tasks, which we'll cover later in the course. Or you could use a dynamic table, which is often the cleanest option of all. Here's the core idea. A dynamic table is defined by a query, just like a view. But unlike a standard view (which reruns that query every time you query the view), a dynamic table stores the results — like a materialized view. And unlike a materialized view, it refreshes on a schedule you control, and it fully supports joins and complex transformations. It's a hybrid. Let's see one in action using our Tasty Bytes data. Let's say we want a dynamic table that always holds a clean summary of total revenue by city, updated regularly. Here's how we'd create it. Let's talk through the parts of this that are new to us. The query at the bottom — with the `SELECT`, `JOIN`, and `GROUP BY` statements — is just standard SQL. Nothing new there. What makes this a dynamic table instead of a view is everything above the `AS` keyword. `TARGET_LAG` is the key parameter. It tells Snowflake how far behind the source data you're willing to let your dynamic table get. Here we set one minute, meaning Snowflake will refresh this table frequently enough that it's never more than a minute behind the raw orders data it's based on. You could set this to five minutes, one hour, one day, or whatever makes sense for your use case. You also specify a warehouse, because unlike a standard view, a dynamic table actually must run compute to refresh itself. Snowflake uses that warehouse when it's doing those refreshes in the background. Let's run this and then query our dynamic table: `SELECT * FROM tasty_bytes.revenue_by_city`. Here's our revenue by city, nicely aggregated and ready to go. Now, one of the things I really like about dynamic tables is how well they pair with streaming ingestion. If you've got data arriving continuously — either through Snowpipe or Snowpipe Streaming, which we'll cover in Module 3 — you can set a very short target lag and your downstream tables stay almost real-time with your source data automatically, without any orchestration code on your part. That's very powerful. Let me quickly show you two other commands worth knowing. You can check the status of your dynamic table with `SHOW DYNAMIC TABLES`. This gives you useful metadata, including when it was last refreshed and what its current lag is. If you want to force a refresh immediately rather than waiting for the next scheduled one, you can run `ALTER DYNAMIC TABLE tasty_bytes.revenue_by_city REFRESH`. When should you reach for a dynamic table versus the other options we've covered? Here's a rough rule of thumb: - If you have a transformation that runs on a schedule and involves joins or complex logic, dynamic tables are almost always the right choice over materialized views. - If you want your data to refresh continuously with minimal orchestration, dynamic tables pair beautifully with streaming ingestion, as long as you've set your target lag value to be small enough. - If your transformation is simple and you just need a reusable query without storing results, a standard view is still perfectly fine. To recap, in this video we learned four things: 1. Dynamic tables store the results of a query and refresh automatically on a schedule you define. 2. The `TARGET_LAG` parameter controls how current the data stays. 3. Unlike materialized views, dynamic tables support joins and complex transformations. 4. You can check status with `SHOW DYNAMIC TABLES` and trigger a manual refresh with `ALTER DYNAMIC TABLE ... REFRESH`. We're going to see dynamic tables again in Module 3 when we talk about data engineering workloads, because they're a key part of how modern data pipelines get built in Snowflake. For now, consider them a tool in your toolkit that sits somewhere between a view and a table — they have the flexibility of a view and the performance of a table. Pretty great, isn't it?2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.