Dynamic Tables for Data Vault

How experimenting with Dynamic Tables on Snowflake led to improvements in the Data Vault patterns.

We have seen enormous interest in Dynamic Tables for Snowflake, what are Dynamic Tables and where should they fit into your Data Vault architecture?

As a reminder these are the table types that make up raw and business vault.

And the information mart patterns we need to efficiently get the data out of the vault!

What are Dynamic Tables?

Announced at Snowflake Summit 2022 as Materialised Tables (later renamed), Dynamic Tables is the declarative form of Snowflake’s Streams and Tasks (announced 2019). As Streams define an offset to track change data capture (CDC) changes on underlying tables and views, Tasks can be used to schedule the consumption of that data. We covered this in depth on how this works in this article and suggested an automation pattern applicable to your Data Vault.

To define a Dynamic Table, you simply:

• Declare your SQL you need to transform your data as a CREATE DYNAMIC TABLE AS statement (similar to CREATE TABLE AS (CTAS)
• Attach a virtual warehouse to perform the transformation work.
• Define a refresh (LAG) or a downstream dependency on another dynamic table.