Dynamic table refresh boundary¶

Use a dynamic table refresh boundary to decouple dynamic table pipelines while still reading upstream results.

When one dynamic table references another, the two are refreshed together as a single pipeline. While this works in the majority of scenarios, there are certain use cases, such as sharing data across team boundaries, where data refresh needs can vary. You can declare the two dynamic tables as independent (and thus belonging to different pipelines) by wrapping the upstream reference in DYNAMIC_TABLE_REFRESH_BOUNDARY(). Snapshot isolation is only guaranteed within a single pipeline, so dynamic tables across a refresh boundary do not provide snapshot isolation with each other.

Overview¶

By default, when a dynamic table reads from another dynamic table, Snowflake:

Refreshes both tables together as part of a single pipeline.
Coordinates refreshes so that downstream dynamic tables see snapshot isolation across all upstream dynamic tables in the pipeline.
Enforces pipeline-level rules such as target lag checks.

In some pipelines, you don’t want every relationship to cause both dynamic tables to be refreshed together. Common examples include:

Cross-team pipelines where one team publishes a dynamic table that another team consumes, but the downstream dynamic table should not influence or inherit the upstream pipeline.
Incremental migrations where you convert an upstream pipeline step to a dynamic table but don’t want downstream consumers to start coordinating refreshes with it.
Dynamic-table-on-view-on-dynamic-table patterns, where a dynamic table reads from a view that queries another dynamic table. This pattern is unsupported unless the view is wrapped in DYNAMIC_TABLE_REFRESH_BOUNDARY().

A refresh boundary makes this separation explicit: inputs inside the boundary are treated as belonging to a separate pipeline and are read like regular tables.

Syntax¶

DYNAMIC_TABLE_REFRESH_BOUNDARY( <object_name> )

Where:

object_name: A table, view, dynamic table, or common table expression (CTE) name.

Use this keyword in the FROM / JOIN clause (including within CTEs and UNION branches) of a dynamic table definition.

Examples¶

The following example reads from a view that queries another dynamic table. Without a refresh boundary, creating a dynamic table that reads from a view on another dynamic table is unsupported. Wrapping the view in DYNAMIC_TABLE_REFRESH_BOUNDARY() makes this pattern possible:

CREATE DYNAMIC TABLE analytics.click_analytics_dt
  WAREHOUSE = analytics_wh
  TARGET_LAG = '5 minutes'
AS
SELECT *
FROM DYNAMIC_TABLE_REFRESH_BOUNDARY(analytics.enriched_clicks_view);

The following example joins a directly referenced dynamic table with a view whose upstream dynamic table refreshes on a longer schedule. Wrapping the view in DYNAMIC_TABLE_REFRESH_BOUNDARY() prevents the downstream dynamic table from triggering the expensive upstream refresh every 5 minutes, while still allowing it to read the latest available version. Snapshot isolation is not guaranteed across the refresh boundary:

CREATE DYNAMIC TABLE data_eng.enriched_clicks_dt
  WAREHOUSE = de_wh
  TARGET_LAG = '5 minutes'
AS
SELECT
  c.*,
  p.product_name
FROM data_eng.clickstream_dt AS c
LEFT JOIN DYNAMIC_TABLE_REFRESH_BOUNDARY(product_db.active_products_view) AS p
  ON c.product_id = p.product_id;

Behavior¶

How refresh boundaries change dependencies¶

When you wrap an input in DYNAMIC_TABLE_REFRESH_BOUNDARY() inside a dynamic table definition:

That input is treated as a refresh boundary input for this definition.
Any dynamic tables reachable from that input are not included in the pipeline for this definition.
On refresh, the dynamic table reads those objects at their current version, not at the data timestamp coordinated across the pipeline.

As a result:

No cascading refresh across the boundary: Refreshing the downstream dynamic table does not trigger refreshes of dynamic tables that are only reachable through a refresh boundary.
Independent scheduling: Target lag and refresh scheduling for the downstream dynamic table ignore dynamic tables that are only reachable through the boundary.
No snapshot isolation across the boundary: The downstream dynamic table reads whatever version of the upstream data is available at refresh time. The data across the boundary is not guaranteed to be aligned with the snapshot isolation that applies to other upstream dependencies.

Snapshot isolation vs. refresh boundaries¶

Within a single pipeline (without a boundary), Snowflake guarantees snapshot isolation across all upstream dynamic tables participating in that pipeline.

Refresh boundaries intentionally weaken this guarantee on the dependency that crosses the boundary:

Inside the boundary: objects refresh and coordinate according to their own pipelines.
Outside the boundary: the downstream dynamic table reads whatever version is available at its refresh time.

A single dynamic table definition can therefore reference both types of inputs:

Direct references to upstream dynamic tables, which participate in snapshot isolation and coordinated refreshes within the pipeline.
Refresh boundary references, which read the latest available version of the upstream data independently, without snapshot isolation.

Use refresh boundaries only on dependencies where you do not require snapshot isolation between the upstream and downstream dynamic tables.

Use cases¶

Decoupling cross-team pipelines¶

Different teams might own different parts of a logical pipeline:

Team A: publishes a core dynamic table used across the organization.
Team B: defines a downstream dynamic table that joins the core dynamic table with team-specific data.

Team B can wrap Team A’s output in a refresh boundary to:

Avoid pulling Team A’s dynamic tables into their own pipeline.
Keep their own refresh schedule independent.
Treat Team A’s dynamic table similar to an external, periodically updated table.

Enabling dynamic table on view on dynamic table¶

Without a refresh boundary, creating a dynamic table that reads from a view on another dynamic table is unsupported. With a refresh boundary, you can explicitly mark the view dependency as a boundary:

CREATE VIEW v_orders AS
SELECT *
FROM orders_dt;

CREATE DYNAMIC TABLE order_summary_dt
  WAREHOUSE = analytics_wh
  TARGET_LAG = '15 minutes'
AS
SELECT
  customer_id,
  COUNT(*) AS num_orders
FROM DYNAMIC_TABLE_REFRESH_BOUNDARY(v_orders)
GROUP BY customer_id;

Here, order_summary_dt:

Reads from orders_dt through a refresh boundary.
Does not belong to the same pipeline as orders_dt.
Reads whatever version of orders_dt is available when it refreshes.

Example: team-owned boundary view¶

A common pattern is for one team to own both a dynamic table and a view on top of it, and to apply the refresh boundary inside the view definition. Other teams then consume that view without introducing new dependencies to the owning team’s dynamic table.

-- Team A: owns product_catalog_dt and publishes a boundary view
CREATE DYNAMIC TABLE product.product_catalog_dt
  WAREHOUSE = product_wh
  TARGET_LAG = '1 hour'
AS
SELECT *
FROM product.raw_products;

CREATE VIEW product.active_products_public_v AS
SELECT * FROM DYNAMIC_TABLE_REFRESH_BOUNDARY(product.product_catalog_dt)
WHERE is_active = TRUE;

-- Team B: consumes Team A's view in their own dynamic table
CREATE DYNAMIC TABLE analytics.active_product_clicks_dt
  WAREHOUSE = analytics_wh
  TARGET_LAG = '5 minutes'
AS
SELECT
  c.*,
  p.product_name
FROM analytics.clickstream_dt AS c
JOIN product.active_products_public_v AS p
  ON c.product_id = p.product_id;

In this pattern:

Team A controls the refresh boundary by wrapping product_catalog_dt inside product.active_products_public_v.
Team B and other teams define their own dynamic tables that reference only the published view.
Those downstream dynamic tables do not add product_catalog_dt to their own pipeline; product_catalog_dt remains outside their pipelines even though its data is visible through the view.

Incremental migration to dynamic tables¶

If you migrate an existing pipeline step to a dynamic table, you might not want downstream consumers to:

Start triggering refreshes of the new dynamic table.
Inherit new target lag requirements.

Wrapping the new dynamic table (or a view on top of it) in a refresh boundary lets downstream dynamic tables consume it without being added to the same pipeline.

Target lag¶

Refresh boundaries also influence how target lag is enforced.

The target lag of an upstream dynamic table must be the same as or shorter than that of any downstream dynamic table within the same pipeline. Dynamic tables referenced through DYNAMIC_TABLE_REFRESH_BOUNDARY() do not belong to the same pipeline, so this rule does not apply across the boundary.

Upstream dynamic tables inside a refresh boundary keep their own target lag and scheduling behavior; they are not tightened or relaxed by downstream choices across the boundary.

Restrictions and limitations¶

Refresh boundaries are subject to a few important rules:

Same dynamic table both inside and outside a refresh boundary is not allowed

All references to the same upstream dynamic table within a single definition must be either directly in the definition or wrapped in DYNAMIC_TABLE_REFRESH_BOUNDARY(). Mixing both would allow the same dynamic table to be read at different versions. Snowflake blocks these definitions and returns a descriptive error.

Unsupported boundary targets

DYNAMIC_TABLE_REFRESH_BOUNDARY() must wrap a named object (table, view, dynamic table, or CTE). It cannot wrap:

Inline subqueries.
Table functions or UDTFs.
Arbitrary TABLE(...) calls.

Effect outside dynamic tables

You can call DYNAMIC_TABLE_REFRESH_BOUNDARY() in regular SELECT queries, but outside of a dynamic table definition it is a no-op.

Best practices¶

When using refresh boundaries in dynamic table pipelines:

Use a refresh boundary when:

You want to consume another team’s dynamic table without joining its pipeline.
You do not need snapshot isolation from a particular upstream dependency.
A dynamic table depends on a view that references another dynamic table. This pattern is only supported when either the view or the upstream dynamic table is wrapped in DYNAMIC_TABLE_REFRESH_BOUNDARY().

Avoid a refresh boundary when:

You need snapshot isolation across that dependency.
You want downstream refreshes to coordinate with upstream dynamic tables and, if needed, cascade refreshes.
You rely on global target lag relationships across the entire pipeline.