Snowflake Dynamic Tables are declarative data pipelines that automatically refresh their contents based on a defined SQL query, significantly reducing or eliminating the need for external orchestration tools like Airflow for many in-warehouse ELT transformation workflows.
Dynamic Tables function as a managed, continuously updated materialized view, where Snowflake handles the scheduling, dependency tracking, and incremental processing. This shifts the focus from orchestrating individual tasks to simply declaring the desired final state of your data.
Here's how Dynamic Tables compare to traditional Airflow DAGs for in-warehouse transformations:
| Feature | Airflow DAGs (for ELT) | Snowflake Dynamic Tables |
| :-------------------- | :--------------------------------------------------- | :----------------------------------------------------- |
| Orchestration | External, imperative, explicit task definition | Internal, declarative, automatic refresh |
| Dependency Mgmt. | Manual definition (e.g., >>, set_upstream) | Automatic detection and resolution within Snowflake |
| Refresh Logic | Custom Python/SQL operators, explicit scheduling | SQL-driven, Snowflake-managed continuous refresh |
| Incremental Proc. | Requires custom logic in SQL/Python | Built-in, optimized for efficient updates |
| Cost Model | External compute (VMs, managed service) | Snowflake compute (virtual warehouse) for refreshes |
Key Aspects of Dynamic Tables:
1. Declarative Definition: You define the table using a standard CREATE DYNAMIC TABLE statement, specifying the source query and a TARGET_LAG parameter. The TARGET_LAG dictates the maximum acceptable data staleness (e.g., TARGET_LAG = '1 hour' or TARGET_LAG = '5 minutes'). Snowflake then ensures the table is refreshed within that lag.
```sql
CREATE DYNAMIC TABLE my_reporting_table
TARGET_LAG = '1 hour'
WAREHOUSE = my_elt_wh
AS
SELECT
t1.id,
t1.name,
t2.value,
CURRENT_TIMESTAMP() AS last_updated
FROM raw_data.events t1
JOIN transformed_data.lookup_table t2 ON t1.lookup_id = t2.id
WHERE t1.event_date >= DATEADD(day, -7, CURRENT_DATE());
```
2. Automated Dependency Resolution: If my_reporting_table depends on transformed_data.lookup_table, Snowflake automatically detects this dependency. When lookup_table changes, my_reporting_table is automatically queued for refresh, respecting its TARGET_LAG.
3. Incremental Refresh Optimization: Dynamic Tables are designed for efficient incremental updates. Snowflake intelligently processes only the new or changed data from upstream sources, minimizing compute consumption. You can specify REFRESH_MODE = AUTO (default) or FULL if a full rebuild is occasionally necessary (though AUTO is generally preferred).
4. Cost Control: Refreshes consume compute resources from a specified virtual warehouse. The TARGET_LAG parameter is critical for cost management; a shorter lag means more frequent refreshes and potentially higher costs. Monitor SNOWFLAKE.ACCOUNT_USAGE.DYNAMIC_TABLE_REFRESH_HISTORY to track refresh costs and performance.
For complex pipelines involving only SQL transformations within Snowflake, Dynamic Tables offer a powerful, simplified, and cost-effective alternative to managing custom Airflow DAGs and their associated infrastructure.