page78
60
Within Snowflake, a materialized view can be declared only on a single table and is
essentially a way to declare alternative cluster keys on a base table. Using materialized
views facilitates micro-partition pruning via aggregation, a topic I will discuss in
Chapter 4.
In Snowflake, materialized views differ significantly from legacy RDBMS equivalent
implementations. Be prepared to set aside any assumptions on materialized
view implementation and capability. Within Snowflake, materialized views incur
maintenance, runtime, and storage costs. Before implementing and using materialized
views, you must strike a balance to ensure optimally cost-effective solutions are
developed and delivered. Similar considerations apply to the use of dynamic tables,
discussed next.
You can find further information on materialized views at
https://docs.snowflake.
com/en/user-guide/views-materialized
.
For those familiar with legacy RDBMSs, comparable functionality to that provided by
dynamic tables has been around for a long time, where the ability to join multiple tables
and create a table-like object offers significant performance improvements. A dynamic
table maintains the result set from a query on a scheduled timer. Think of a dynamic
table as the conflation of a stream, a task, and, in this example, a multitable-based view.
There is a trade-off: you will incur additional storage costs along with serverless
compute costs for provisioning dynamic tables.
In this example, you will use an X-Small warehouse for the periodic refreshes. This
was chosen as a reasonable compromise because you will consume one credit every
time your X-Small warehouse runs for an hour. You may want to investigate using a
different size warehouse.
CREATE OR REPLACE DYNAMIC TABLE dt_supplier_part COPY GRANTS
TARGET_LAG = '30 MINUTES'
WAREHOUSE = tpc_wh_xsmall
AS
SELECT p.p_name AS part_name,
p.p_retailprice AS retail_price,
ps.ps_supplycost AS supply_cost,
ps.ps_availqty AS available_quantity,
ChapTer 3
The Query profiler