Use Snowflake dynamic tables for the SQL transformations that move data through bronze, silver, and gold layers. Snowflake tracks the dependency graph and refreshes each table toward its TARGET_LAG, so you describe the desired result with a SELECT instead of wiring every step with streams and tasks. Keep streams and tasks at boundaries that need stored procedures, external calls, strict schedules, custom retries, MERGE-heavy logic, or several tables changed in one transaction.
What dynamic tables change in an ELT pipeline
A dynamic table materializes a SELECT result and keeps that result current. In a conventional pipeline, a task runs on a schedule, a stream identifies changed rows, and procedural code applies the next write. With dynamic tables, the table definition expresses the output and Snowflake manages change detection, refresh ordering, and dependency tracking.
| Existing pipeline concept | Dynamic-table equivalent |
|---|---|
| Task schedule | A freshness objective in TARGET_LAG |
| Task DAG | The dependency graph inferred from dynamic-table SELECT definitions |
| Stream polling and change checks | Snowflake-managed refresh planning |
| INSERT or transformation procedure | A declarative SELECT that defines the desired rows |
This is a change in control style, not a promise that every task-based workload can be replaced. Dynamic tables are strongest when each stage is a SQL transformation whose result can be recomputed or incrementally maintained.
Build bronze, silver, and gold layers
Bronze: land source data with minimal interpretation
Bronze is the replayable landing layer. Preserve source columns and ingestion metadata, and avoid business rules that would make recovery or reprocessing difficult. It can be a regular table loaded by a file pipeline, connector, or task; it does not have to be a dynamic table.
#1 Best Overall
Silver: standardize and clean
Silver dynamic tables are the reusable, conformed data products. Typical work includes casting types, normalizing timestamps, filtering invalid records, deduplicating by a business key, and joining reference dimensions. Put these operations in SQL so Snowflake can track their dependencies.
Gold: publish business-ready results
Gold dynamic tables expose facts, dimensions, aggregates, or semantic outputs for BI tools and applications. The terminal gold table normally carries the time-based freshness objective; intermediate tables can wait for downstream demand.
CREATE DYNAMIC TABLE silver_orders
TARGET_LAG = DOWNSTREAM
WAREHOUSE = elt_wh
REFRESH_MODE = INCREMENTAL
AS
SELECT
order_id,
customer_id,
TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
UPPER(TRIM(status)) AS status,
amount::NUMBER(18,2) AS amount
FROM bronze_orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY ingested_at DESC
) = 1;
CREATE DYNAMIC TABLE gold_daily_sales
TARGET_LAG = '5 minutes'
WAREHOUSE = elt_wh
REFRESH_MODE = INCREMENTAL
AS
SELECT
DATE_TRUNC('day', order_ts) AS sales_day,
customer_id,
SUM(amount) AS sales_amount,
COUNT(*) AS order_count
FROM silver_orders
WHERE status = 'COMPLETE'
GROUP BY 1, 2;In this pattern, the gold table’s five-minute value is a freshness goal, not a guaranteed five-minute execution interval. If a refresh takes longer than the target or the warehouse is unavailable, observed lag can exceed the goal.
Rank #2
How Snowflake coordinates refreshes
Snowflake infers edges from the references in each dynamic-table query. It can refresh upstream data before a dependent table, allowing downstream consumers to see a consistent result from the pipeline rather than a manually ordered set of independent task runs. The dependency graph also lets Snowflake defer an intermediate table configured with TARGET_LAG = DOWNSTREAM until a dependent table needs fresh input.
Use DOWNSTREAM on silver stages when they exist primarily to serve a gold product. Assign an explicit time lag to the terminal products that users actually query. If several branches have different service levels, give each branch’s terminal table its own objective instead of forcing every layer to run at the fastest rate.
Choose a refresh mode deliberately
| Mode | Use when | Important consequence |
|---|---|---|
INCREMENTAL |
The query uses operators supported for incremental maintenance and the workload is append-heavy or change-oriented. | Only changed rows are computed, reducing work when the change set is small. |
FULL |
The definition contains unsupported operators or non-deterministic functions, or a complete rebuild is acceptable. | Each refresh recomputes the complete result; it does not preserve a useful incremental stream history. |
AUTO |
You want Snowflake to choose a mode at creation time. | Review the selected behavior and refresh history rather than assuming incremental maintenance. |
Check operator support for the Snowflake release and account features you use before choosing incremental mode. A query that looks incremental may still require full refresh because of a function or construct Snowflake cannot maintain incrementally.
When streams and tasks remain the right tool
Keep a procedural boundary when the required action is not simply the production of one relational result.
- Stored procedures or external calls: invoke APIs, filesystems, notifications, or other side effects from task-controlled code.
- Strict CRON timing: run at a calendar time or window rather than when a freshness objective is due.
- Custom retry and error handling: branch on an error, retry with workload-specific backoff, or route failures to a bespoke queue.
- MERGE-heavy upserts: standard dynamic-table definitions are
SELECT-based and do not accept aMERGEstatement. - Multi-table transactions: update several targets atomically in one transaction.
- Frequent schema redefinition: avoid repeatedly reinitializing a dynamic table when controlled, incremental schema evolution is more important than declarative refresh.
Custom incremental dynamic tables can express some MERGE or INSERT patterns, including stream-to-static joins, but verify that the specific syntax and feature are supported in your account. This option retains Snowflake-managed dependency scheduling without making every procedural workload a standard dynamic table.
Recommended Free Tools
Dynamic tables versus streams and tasks
| Decision axis | Dynamic tables | Streams and tasks |
|---|---|---|
| Control model | Declarative: define the result with SQL. | Procedural: define statements, order, and actions. |
| SQL and DML | Best for supported SELECT transformations, joins, aggregates, and windows; standard definitions do not contain MERGE. |
Can run DML, stored procedures, and arbitrary task actions. |
| Freshness | Target-lag objective; actual lag varies with work and availability. | Schedule or trigger chosen by the task design. |
| Orchestration | Dependency graph and refresh order managed by Snowflake. | DAG, branching, retries, and ordering are explicitly configured. |
| Change processing | Incremental refresh when the definition and mode support it. | Streams expose change records for code to consume. |
| Schema changes | Definition changes can trigger reinitialization and reprocessing. | Migration logic can be staged explicitly around existing tables. |
| Transactional scope | Designed around a table result. | Can coordinate multiple writes in one transaction. |
What dynamic tables cost
There is no universal rule that dynamic tables are cheaper than tasks. Cost depends on how often refreshes run, how much data each query scans, and how the objects are configured.
Rank #4
- Warehouse compute: refresh queries consume credits on the warehouse assigned to the dynamic table.
- Cloud Services: compilation, dependency tracking, monitoring, and coordination add a service component.
- Storage: refreshed micro-partitions and retention consume storage.
Shorter lag targets and frequent refreshes can increase all-in cost. Compare equivalent workloads by measuring refresh-query credits, Cloud Services usage, storage growth, refresh duration, and the amount of data processed—not by comparing object counts or schedules alone.
Monitor freshness and correctness
After creating or migrating a pipeline, monitor the dynamic-table refresh history and alert on failures, duration growth, and lag above the product objective. Pair operational metrics with data checks:
- row counts and duplicate-key counts at each layer;
- null, type, and referential-integrity checks on conformed columns;
- business totals such as order amounts or event counts;
- warehouse credits, Cloud Services usage, and storage over time.
A table can meet its lag objective while producing incorrect rows, so freshness alerts should not replace quality validation.
Migrate a task pipeline safely
- Inventory the current graph. Record task dependencies, stream consumers,
MERGEstatements, procedures, external calls, schedules, transaction boundaries, and the freshness requirement for each output. - Classify each stage. Mark SQL-only transformations as candidates and retain procedural stages where side effects, strict timing, or multi-table transactions are required.
- Convert one simple stage first. Choose a deterministic transformation, create a dynamic-table version beside the existing target, and compare rows, keys, aggregates, and null behavior.
- Select the refresh mode. Use incremental mode only where the supported operators and change pattern justify it; use full mode for unsupported definitions; use auto when accepting Snowflake’s creation-time choice.
- Build the medallion chain. Keep bronze as the minimally transformed landing layer, use silver dynamic tables for conformance, and place the user-facing freshness objective on each gold output. Set serving-independent intermediate tables to
DOWNSTREAM. - Migrate from leaves toward roots. Validate each replacement against the old output before switching its consumers, then resume the new objects in dependency order.
- Keep a hybrid boundary. Let dynamic tables feed a remaining task or procedure when that downstream action is not expressible as a table result. Remove the old stream only after every consumer has been cut over and its retention window is no longer needed.
Failure modes to plan for
Lag stays above the target
Check refresh duration, warehouse capacity, overlapping work, and upstream availability. A target lag does not reserve compute or guarantee an execution interval; increase capacity, simplify the query, or relax the objective when the measured workload cannot meet it.
A definition unexpectedly performs full refreshes
Inspect the query for unsupported operators or non-deterministic functions and confirm the effective refresh mode. If incremental maintenance is essential, rewrite the transformation or move that stage back to a stream-and-task implementation.
A schema change causes a long rebuild
Plan definition changes as migrations, communicate the reinitialization impact, and retain the prior task-based path when frequent evolution without reprocessing is a hard requirement.
A downstream action needs a transaction or side effect
Stop at the dynamic table that produces the relational input, then hand that result to a task or procedure. Do not force API calls, notifications, or multi-table atomic writes into a table definition.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A practical decision rule
Choose dynamic tables when the pipeline can be expressed as a dependency-linked set of SQL results and freshness is naturally described as a lag objective. Choose streams and tasks when the pipeline’s identity is procedural control: exact schedules, custom retries, side effects, complex DML, or transactional coordination across targets. Most production Snowflake environments can use both, with dynamic tables covering bronze-to-silver-to-gold transformations and tasks retained at the edges that require imperative behavior.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
