Snowflake Dynamic Tables automatically refresh materialised views based on a target data lag — without Airflow, without scheduling, without orchestration overhead. Here is what they are and when to use them.
The quick answer
Snowflake Dynamic Tables are a materialisation option that automatically refresh based on a target data lag — a user-specified maximum acceptable delay between source data changes and the dynamic table reflecting those changes. You define a dynamic table with a SQL SELECT query and a target lag (e.g., "1 minute", "1 hour", "1 day"). Snowflake's infrastructure monitors the upstream tables, detects changes, and refreshes the dynamic table incrementally to meet the lag target — without you scheduling refreshes, configuring orchestration, or managing pipeline dependencies. Dynamic tables sit between traditional materialised views (no lag control, refreshed manually) and full streaming pipelines (real-time but complex).
How dynamic tables work
When you create a dynamic table, Snowflake analyses the SQL query to understand which upstream tables it depends on. When those upstream tables change, Snowflake calculates the delta (the rows that have changed since the last refresh) and applies those changes to the dynamic table. This incremental refresh is more efficient than rebuilding the table from scratch on every refresh cycle.
**Target lag**: specified as a time interval (1 MINUTE, 15 MINUTES, 1 HOUR, 1 DAY) or as DOWNSTREAM (the lag is determined by what downstream dynamic tables need). Snowflake attempts to keep the dynamic table's data within the target lag — it may be fresher, but not staler.
**Downstream lag**: when multiple dynamic tables are chained (Table A feeds Dynamic Table B which feeds Dynamic Table C), you can set intermediate tables to DOWNSTREAM lag. Snowflake solves for the refresh schedule that satisfies the end-to-end lag target for the downstream consumers, without you computing the intermediate refresh frequencies.
**Automatic dependency tracking**: Snowflake detects upstream table changes automatically. You do not need to schedule refreshes or build event-based triggers — the dynamic table framework handles this.
**Incremental computation**: where possible, Snowflake applies only changed rows rather than rebuilding from scratch. For SQL patterns that support incremental processing (joins, filters, projections), this means refreshes are fast and low-cost even for large tables. Complex SQL patterns (DISTINCT, GROUP BY without ordering, certain window functions) may require full rebuilds — Snowflake indicates this at create time.
What dynamic tables replace
**Scheduled tasks + materialised views**: the previous Snowflake pattern for near-real-time materialisation was: create a task (scheduled SQL job) that ran a CREATE OR REPLACE TABLE query on a schedule. Dynamic tables eliminate the need to write this scheduling and task management logic — the framework handles it.
**dbt incremental models with short refresh cadence**: for transformation pipelines where you need frequent refreshes (every 5–15 minutes), dbt incremental models with Airflow scheduling is complex to configure and operate. Dynamic tables offer a simpler alternative for these use cases within Snowflake's native infrastructure.
**Simple Kafka consumer pipelines for warehousing**: for use cases where you want near-real-time data availability in the warehouse without a Kafka consumer, dynamic tables on top of Snowflake's Snowpipe Streaming (which loads data in near-real-time from Kafka) can achieve sub-minute lag without a separate stream processing layer.
When to use dynamic tables
Dynamic tables are well-suited for:
**Near-real-time operational dashboards**: dashboards that need to show data updated within minutes, not hours. With a 1–5 minute target lag, dynamic tables keep dashboard data fresh without the infrastructure of a streaming pipeline.
**Transformation pipelines with complex refresh dependencies**: when a transformation chain has multiple intermediate tables and the dependency between them is complex, dynamic tables with DOWNSTREAM lag automatically solve the refresh scheduling problem. You define the end-to-end lag requirement; Snowflake figures out how often each layer needs to refresh.
**Eliminating orchestration for Snowflake-internal transformations**: if your entire transformation pipeline runs within Snowflake (Snowpipe ingestion → staging tables → dbt-like SQL transformations → serving tables), dynamic tables can replace Airflow for the scheduling layer for workflows where near-real-time is needed.
**Replacing expensive full table refreshes**: if you currently rebuild large tables from scratch on a frequent schedule (every 15 minutes) because you want fresh data, dynamic tables' incremental refresh approach is more cost-efficient — especially for large tables where only a small fraction of rows change per cycle.
When not to use dynamic tables
**Complex dbt projects**: dynamic tables do not replace dbt for complex transformation projects. dbt provides testing, documentation, versioning, and project structure that dynamic tables do not. For production dbt pipelines, continue using dbt — the daily or hourly refresh cadence of most dbt transformations does not require dynamic tables.
**Multi-system pipelines**: dynamic tables are Snowflake-native. If your pipeline spans Snowflake + Python + external APIs + dbt, dynamic tables only cover the Snowflake portion. Airflow or Dagster remains appropriate for the full pipeline orchestration.
**SQL patterns incompatible with incremental computation**: some SQL patterns require dynamic tables to do full rebuilds, eliminating the incremental efficiency advantage. Review Snowflake's documentation on supported SQL patterns before relying on incremental computation for cost assumptions.
Cost model
Dynamic tables are billed on the serverless compute model — Snowflake-managed compute is used for refreshes, billed at a premium over virtual warehouse compute (~1.5–2x the credit rate of equivalent warehouse compute). For infrequent refreshes (hourly or daily), the cost overhead is minimal. For very frequent refreshes (every minute) on large, complex SQL, the serverless compute premium can be significant — benchmark against a scheduled task using a dedicated virtual warehouse for high-frequency refresh patterns.
Monitor dynamic table refresh costs in Account Usage: DYNAMIC_TABLE_REFRESH_HISTORY provides per-table refresh history, credit consumption, and lag achieved.
Dynamic tables vs materialised views
Snowflake materialised views (available prior to dynamic tables) have significant limitations: no incremental computation for complex SQL, no user-specified lag control, manual refresh triggering. Dynamic tables supersede materialised views for most use cases. The exception: simple materialised views with low-cardinality GROUP BY patterns may still be appropriate for simple, frequently queried aggregations — review Snowflake's materialised view documentation for the current feature overlap.
For the Snowflake architecture context, see snowflake architecture guide. For the streaming ingestion layer that feeds dynamic tables, see kafka for data engineers. For the broader real-time architecture context, see real-time data architecture.
Our data architecture consulting practice implements Snowflake dynamic table pipelines — from architecture design through deployment and monitoring. If you are evaluating near-real-time transformation within Snowflake, book a free 30-minute audit.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →