Views, materialized views, and dynamic tables¶

Snowflake provides a variety of structures to view, materialize, and otherwise transform data. Three of the most common mechanisms are:

• Views: Snowflake provides what would be considered a traditional database view. In general, a view allows the result of a query to be accessed as if it were a table, including linking (or in database parlance, joining) two or more tables or other views into a single logical view. Once defined, views can be queried like any other table.

• Materialized views: Materialized views differ from traditional views by providing the ability to pre-compute the dataset based on materialized view query. Because the result is pre-computed, querying a materialized view is faster than executing a query against the base table of the view. This performance difference can be significant when a query is run frequently or is sufficiently complex. As a result, materialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.

• Dynamic Tables: Dynamic tables materialize the results of a specified query. Instead of creating a separate target table and writing code to transform and update the data in that table, you can define the target table as a dynamic table, and you can specify the SQL statement that performs the transformation. Background automation then keeps the dynamic table up to date based on the refresh criteria that you specify.