Views - Part II
1. Views - Part II
In the last video, we learned that a standard view is a saved query, clean, flexible, and really useful for simplifying your code and controlling access to data. Now I want to tackle the natural follow-up question. What if the query underlying your view is expensive and you're running it constantly? Do you really want Snowflake re-executing it from scratch every single time someone queries it? The answer is probably not. And Snowflake has a solution for this, one we actually just covered a couple videos ago. That's right. We're back to dynamic tables. But this time, we'll focus on the performance gains we'll gain from them. If you remember from the dynamic tables video, a dynamic table stores the results of a query and refreshes automatically on a schedule you control via the `TARGET_LAG` parameter. That's exactly the behavior we want when we have a complex, expensive view that needs to stay current without being re-executed on every query. So rather than re-teach dynamic tables here, I want to focus on one specific question. When you're in view territory and you're thinking, "I want this to be faster and pre-computed", why should you reach for a dynamic table rather than a materialized view? The answer really comes down to one thing: joins. Materialized views are the older Snowflake solution to this problem. They're basically a standard view with the word `MATERIALIZED` added. They store pre-computed results just like a dynamic table. The critical limitation is that materialized views don't support joins. They're restricted to querying a single table. That's a significant constraint because most real-world transformations involve joining tables together. Dynamic tables have no such restriction. Joins, subqueries, and window functions are all supported. That's the primary reason dynamic tables have become the recommended approach for new workloads, and why for most use cases, if you find yourself reaching for a materialized view, you should probably reach for a dynamic table instead. If you're maintaining existing Snowflake code that uses materialized views, they still work fine, so there is no need to migrate anything immediately. But for anything new, dynamic tables are the way to go. Just to orient you, if you run `SHOW DYNAMIC TABLES`, you'll see your dynamic tables. And if you run `SHOW MATERIALIZED VIEWS`, you'll see any materialized views in your account. The metadata is similar, but notice that dynamic tables give you refresh and lag information that's more useful for monitoring. Let's cover one last thing before we wrap up. We haven't talked about secure views yet, and this is a good moment to introduce them since we're already in view territory. A secure view is a view where the underlying query definition is hidden from anyone who doesn't own it. This matters most when you're sharing data with other users or roles and you don't want them to see the logic behind the view, but you do want them to see the results. You create one simply by adding the `SECURE` keyword to your `CREATE VIEW` statement. That looks like this. With our secure view created, we can see that everything else works exactly the same as a standard view. You query it the same way, drop it the same way, and show it the same way. Let's run a simple query and see the result. As you can see, we're still able to see our data, but if another user with insufficient permissions tried to describe the data, they would get an error message. We'll talk more about why this matters when we get to role-based access control in module two. One last thing to mention with secure views is that they bypass the query optimizer. This means that while you benefit from having compartmentalized access to the data, you also will likely see lower performance and higher wait times when querying that data. To recap, we learned three major things in this video. One, when you want the view to be pre-computed and fast to query, dynamic tables are the recommended approach, not materialized views, primarily because dynamic tables support joins and complex transformations. Two, materialized views still work and you'll see them in existing code, but for new workloads, aim to use dynamic tables. And three, secure views let you expose view results to other users without revealing the underlying logic, just by adding the `SECURE` keyword. Next up, we'll get hands-on with views in the assignment. Then we move into an incredibly flexible topic — semi-structured data.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.