Skip to main content

Warehouse-native features for real-time data

Modern data warehouses offer native features that can simplify near real-time data patterns. Instead of managing incremental logic yourself, you can declare the desired freshness and let the warehouse handle the refresh mechanics.

This section covers when to use dynamic tables and materialized views instead of incremental models for near real-time data.

  • Dynamic tables are a warehouse-specific feature in Snowflake that lets the warehouse keep a table updated for you. You define what the table should look like, and the warehouse keeps the table fresh automatically.
  • Materialized views are a warehouse-specific feature that lets the warehouse save the results of a query so they’re faster to read, and refresh them as the underlying data changes. Note that the exact behavior depends on the warehouse.
  • Incremental models are a dbt feature that lets dbt update a table by processing only new data. You tell dbt how new data should be added using your incremental logic SQL, and dbt runs the right SQL when the model is built.

When to consider warehouse-native features​

Use dynamic tables or materialized views when:

  • Your requirement is "data always within X minutes of real time" and you don't need precise scheduling control.
  • You want to simplify operational complexity by offloading refresh logic to the warehouse.
  • Your transformations are relatively straightforward.
  • You're willing to trade some control for convenience.

Stick with incremental models when:

  • You need fine-grained control over scheduling and refresh logic,
  • You have complex business rules requiring custom incremental strategies (like microbatching).
  • You need to coordinate refreshes across multiple models in a specific order.

Dynamic tables​

Warehouse support

Dynamic tables are currently supported in Snowflake, with similar features available in other warehouses under different names. Check your warehouse documentation for availability.

With dynamic tables, you can define the target state with SQL, and the warehouse automatically handles incremental refreshes.

For example, the following SQL model uses a dynamic table to keep a table up to date for you:

{{ config(
    materialized = 'dynamic_table',
    target_lag = '5 minutes',
    snowflake_warehouse = 'TRANSFORM_WH'  -- Snowflake syntax
) }}

select
    event_id,
    event_ts::timestamp_ntz as event_ts,
    to_date(event_ts) as event_date,
    user_id,
    event_type,
    payload
from {{ source('raw', 'events') }}
where event_ts >= current_timestamp() - interval '7 days';

target_lag config​

The target_lag parameter tells the warehouse the maximum acceptable staleness of the dynamic table relative to its sources, and helps determine when the table should be refreshed.

For example:

  • target_lag = '1 minute' - Warehouse keeps the table within one minute of its source data, refreshing automatically as needed.

  • target_lag = '5 minutes' - Table may lag up to five minutes behind its sources.

  • target_lag = 'downstream' - Table refreshes only when a downstream table depends on it.

  • target_lag = '1 minute' - Data refreshed to be within 1 minute of the source

  • target_lag = '5 minutes' - Data within 5 minutes

  • target_lag = 'downstream' - Refresh when downstream tables need it

The warehouse automatically determines when to refresh, whether to do a full or incremental update, and how to optimize the refresh query.

Benefits​

  • Declarative freshness: specify "how fresh" not "when to refresh"
  • Warehouse-managed optimization
  • Cost predictability: refreshes run only when needed to meet target_lag
  • Simple setup

Limitations​

  • Less control over exact timing or orchestration logic
  • Cost visibility can be harder to predict than scheduled dbt jobs
  • Less visibility into refresh decisions compared to dbt's explicit incremental logic
  • Currently warehouse-specific (implementation varies by platform)

Materialized views​

Materialized views are available in most modern data warehouses and cache query results that automatically refresh when underlying data changes.

Materialized views work like this:

  • The warehouse detects changes to source tables and refreshes the materialized view.
  • Many warehouses can incrementally update the view rather than recomputing everything.
  • Queries against the materialized view read cached results, not the underlying tables.

For example, the following sql model uses a materialized view to keep a table up to date for you:

{{ config(
    materialized = 'materialized_view'
) }}

select
    user_id,
    date_trunc('hour', event_ts) as event_hour,
    count(*) as event_count
from {{ source('raw', 'events') }}
group by 1, 2;

Resources by warehouse​

Here are some resources for each warehouse:

BigQuery​

Databricks​

Postgres​

Redshift​

Snowflake​

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading