Snowflake stores data in database tables that are logically structured with rows and columns. We have three basic types of table in Snowflake: permanent, temporary, and transient. You can find their description here. But it is not everything…
IIn 2022, during the Summit, Snowflake introduced new types of table to the world, among other so-called Dynamic Tables. In general, we would like to use a dynamic table to track changes and update it based on the underlying table. Sounds like something that you can do by a stream and a task. Something you probably already know how to use in Snowflake. So why would you like to use dynamic table instead?
Let’s make a simple example!
You have one table with raw information about customers: their customer id, names, address, phones, emails… You would like to have a separate table that would tell you only information about the customer id and the corresponding email.. But the raw table can grow… In that case, we have to create a stream that will track a table and a task that will update your table.
So, firstly, you have to create the table mail_customers_table that will store the customer ids and emails:
Then you have to create a stream that tracks changes in customer data:
And finally create the task which will update the mail_customers_table:
It looks like you have to write a lot of lines of code. You also have to take out about a lot of details. This is where dynamic tables could come in! Using a dynamic table, you can get the same result in just a few simple lines! You have to specify only TARGET_LAG (which defines how often you would like your table to be refreshed) and which WAREHOUSE the dynamic table will use to compute.
So, if you prefer not to write a lot of code for tracking data dependencies and managing data refresh, now you have some alternative in dynamic tables. This way you can avoid the complexity of streams and tasks. It is still a new feature in its preview state, but dynamic tables seem to be a good help for the future.
