Design patterns for dynamic tables

This page collects common recipes for structuring dynamic table pipelines. Use the decision table below to find the right starting point. For per-table configuration (target lag, refresh mode, warehouse selection), see Quick-start best practices for dynamic tables.

Choose a pattern

PatternUse whenHow it works
Multi-table pipelineYour definition has more than one logical stepTARGET_LAG = DOWNSTREAM on intermediates; medallion layers
Controller tableYou need coordinated refreshes across independent pipelinesZero-row coordination table with independent leaf lags
SCD Type 1 deduplicationSource appends CDC records; you need current stateQUALIFY ROW_NUMBER; handles out-of-order arrival

Decompose a definition into a multi-table pipeline

Break a monolithic definition into a pipeline of two or more dynamic tables, each handling one logical step. Many teams organize pipeline layers using medallion vocabulary (bronze, silver, gold). In dynamic table terms: bronze is the raw landing table, silver is a cleaned or conformed dynamic table with TARGET_LAG = DOWNSTREAM, and gold is an aggregated dynamic table with a time-based freshness goal.

Decomposing joins and aggregations across multiple dynamic tables enables each step to refresh incrementally. Place joins in the first dynamic table and aggregations in the next.

Important

Avoid combining joins, aggregations, and window functions in a single dynamic table. This forces the entire computation to re-run on every refresh. Separating these operators into different pipeline stages enables incremental refresh for each step.

CREATE OR REPLACE DYNAMIC TABLE dt_orders_enriched
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        o.order_id,
        o.order_date,
        o.quantity,
        o.unit_price,
        o.quantity * o.unit_price AS line_total,
        c.region,
        c.segment,
        ...
    FROM raw_orders o
    JOIN dim_customers c ON o.customer_id = c.customer_id;

CREATE OR REPLACE DYNAMIC TABLE dt_orders_daily
    TARGET_LAG = '30 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT
        DATE_TRUNC('day', order_date) AS order_day,
        region,
        segment,
        COUNT(*) AS order_count,
        SUM(line_total) AS daily_revenue
    FROM dt_orders_enriched
    GROUP BY ALL;

The silver table (dt_orders_enriched) uses TARGET_LAG = DOWNSTREAM so it refreshes only when the gold table needs fresh data. The gold table (dt_orders_daily) owns the time-based freshness goal and drives the entire pipeline. A common variation adds a second gold table that rolls up hourly data into daily totals as an additional aggregation layer.

Synchronize multiple pipelines with a controller table

A controller dynamic table merges independent pipelines into a single coordinated refresh. When triggered, the controller reads all inputs at the same timestamp. Between controller refreshes, leaf tables update on their own schedules. Use this pattern when a dashboard or downstream consumer joins data from independent domains that must be temporally consistent.

The controller is a zero-row dynamic table that depends on multiple leaf tables. Leaves have their own time-based target lag and refresh independently between controller refreshes.

CREATE OR REPLACE DYNAMIC TABLE dt_orders
    TARGET_LAG = '5 minutes'
    WAREHOUSE = transform_wh
AS
    SELECT order_id, customer_id, order_date, line_total
    FROM raw_orders;

CREATE OR REPLACE DYNAMIC TABLE dt_returns
    TARGET_LAG = '5 minutes'
    WAREHOUSE = transform_wh
AS
    SELECT return_id, order_id, return_date, refund_amount
    FROM raw_returns;

CREATE OR REPLACE DYNAMIC TABLE dt_checkpoint
    TARGET_LAG = DOWNSTREAM
    WAREHOUSE = transform_wh
AS
    SELECT 1 AS sync_flag FROM dt_orders, dt_returns LIMIT 0;

If the controller breaks, leaves continue refreshing independently on their own schedules. If one leaf dynamic table fails, the controller also fails. Monitor all tables in the controller group, not the controller alone.

Important

Avoid listing too many leaf tables in the FROM clause. The cross join in the controller is never run (because of LIMIT 0), but compilation time grows with the number of tables. For pipelines with more than five or six leaves, consider grouping leaves into intermediate controllers.

Latest row deduplication with SCD Type 1

When your base table receives append-only change data capture (CDC) records, use QUALIFY ROW_NUMBER to keep only the latest row per business key. The window function picks the correct row regardless of ingestion order, handling out-of-order arrival without additional logic.

CREATE OR REPLACE DYNAMIC TABLE dt_customers_current
    TARGET_LAG = '10 minutes'
    WAREHOUSE = transform_wh
    REFRESH_MODE = INCREMENTAL
AS
    SELECT * EXCLUDE (raw_metadata_col)
    FROM raw_customers_cdc
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY updated_at DESC
    ) = 1;

Using SELECT * EXCLUDE supports incremental schema evolution: columns added to or dropped from the base table automatically flow through without changing the dynamic table definition. For more on schema evolution, see Evolve dynamic table pipelines.

To handle soft deletes, add a filter to the QUALIFY expression:

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY updated_at DESC
) = 1
AND NOT is_deleted

This expresses soft-delete logic as a single window expression with no additional orchestration.

For best incremental performance, use a monotonic ORDER BY column (a sequence number or timestamp that only increases). Keep the QUALIFY expression as the top-level construct in the dynamic table. If you need further transformations, place them in a downstream dynamic table rather than combining them in the same definition.

Tip

Use ROW_TIMESTAMP as the built-in mechanism for tracking when rows were last refreshed. This is cleaner than adding CURRENT_TIMESTAMP() as a column in the definition, which forces full refresh mode.

Anti-patterns

Setting all tables to TARGET_LAG = DOWNSTREAM

Why it fails: If no dynamic table in the pipeline has a time-based target lag, nothing triggers a refresh. The pipeline produces tables that don’t change and no error is raised.

What to do instead: At least one dynamic table (the terminal table) must have a time-based target lag. Intermediate tables use DOWNSTREAM.

-- Wrong: nothing triggers a refresh
CREATE OR REPLACE DYNAMIC TABLE dt_staging
    TARGET_LAG = DOWNSTREAM ...;
CREATE OR REPLACE DYNAMIC TABLE dt_summary
    TARGET_LAG = DOWNSTREAM ...;

-- Correct: terminal table drives the pipeline
CREATE OR REPLACE DYNAMIC TABLE dt_summary
    TARGET_LAG = '30 minutes' ...;

Short target lag relative to retention time

Why it fails: If lag exceeds the retention window due to transient errors, the table goes stale and must be fully recomputed.

What to do instead: Set target lag several times shorter than retention time to leave a buffer for recovery. This applies to both incremental and full refresh modes.

What’s next