Dynamic Tables

1. Dynamic Tables

In the last video we built Harbr's delivery pipeline using streams and tasks - writing the logic for what to run, when to run it, and how to handle the schedule. It worked, but as a pipeline grows it can become a lot to manage.

2. Dynamic Tables vs Streams and Tasks

Dynamic tables offer a different approach: you describe the output you want, and Snowflake handles the rest. A dynamic table's contents are defined by a query using an automated refresh process. The Snowflake engine detects, schedules and merges.

3. What are Dynamic Tables?

You write the SELECT statement that describes the result you want and Snowflake manages when and how to refresh it. No stream configuration, no tasks to resume. The transformation logic lives purely in the query itself. Here we’ve created a dynamic table definition for Harbr’s delivery summary. The AS block is the query which is just a straightforward GROUP BY across delivery events. TARGET_LAG is the new piece we’ve not introduced before. It tells Snowflake the maximum acceptable staleness: in this case, one hour. The table can be up to one hour behind the source. Snowflake uses that target to decide how frequently to refresh.

4. TARGET_LAG: Reduce Staleness

TARGET_LAG is the contract between you and Snowflake on data freshness. Set it to five minutes and Snowflake guarantees the table is never more than five minutes stale. Set it to one hour and refreshes happen less often, therefore consuming less compute. There's a special value worth knowing: DOWNSTREAM. This tells Snowflake to infer the lag target from whatever depends on this table - useful when chaining dynamic tables together.

5. Full vs Incremental Refresh

Snowflake decides whether each refresh is full or incremental — you don't configure it directly. When Snowflake can track exactly which source rows changed, it refreshes only those rows. Take Harbr's regional summary: if ten new shipments land in EMEA, Snowflake updates only those rows. When the query structure makes that impossible — certain aggregations or set operations — Snowflake falls back to a full refresh. The result is always correct either way.

6. Chaining Dynamic Tables

Dynamic tables can depend on other dynamic tables. In this pipeline, cleaned_events is a dynamic table reading from the raw source. delivery_summary reads from cleaned_events. Each transformation is a separate table with its own query. When cleaned_events uses DOWNSTREAM as its lag target, it defers to whatever delivery_summary needs - so Snowflake coordinates the whole chain to meet the one-hour target without over-refreshing the middle.

7. Dynamic Tables vs Streams and Tasks

Let's go deeper into Dynamic tables vs streams and tasks. Dynamic tables are declarative — you describe the output and Snowflake manages the rest. Because the Snowflake engine detects schedules and merges these are easier to manage. A great use case is keeping date up to date for analytics and reporting. Streams and tasks are imperative — you control exactly what runs and when. These are great for complex custom logic and operational pipelines. In practice many companies use both depending on the use case.

8. DYNAMIC_TABLE_REFRESH_HISTORY

DYNAMIC_TABLE_REFRESH_HISTORY is your audit trail. Each row is one refresh attempt, with its trigger type, state, and how long it took. The state column tells you whether it succeeded or failed. The trigger column shows whether Snowflake fired it on schedule or because a downstream dependency requested it. If a refresh fails, this is where you start. The timestamps let you pinpoint exactly when the table fell out of its lag target which is useful when Harbr's ops team asks why the dashboard was stale.

9. Let's practice!

You've covered dynamic tables, TARGET_LAG, full versus incremental refresh, chaining, and how to choose between dynamic tables and streams plus tasks. Let's put your knowledge to the test.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.