Skip to main content

Lambda views for near real-time dashboards

Snowflake examples ahead

This page uses Snowflake for code examples, but you can adapt the lambda view pattern to other warehouses.

A lambda view pattern combines a batch / incremental fact table with a small near real-time (NRT) slice of very recent data and exposes them through a single view. This is a legacy-but-still-useful pattern some teams have used to deliver near real‑time operational dashboards on top of dbt and a warehouse.

When to use lambda views​

  • You need fresher reads than your normal incremental schedule, but
  • You can't (or don't want to) use dynamic tables or materialized views, or you want to keep logic entirely in dbt SQL. The examples used in this page assume the following setup:

Assumptions​

The examples used in this page assume the following setup:

  • Raw events land continuously into raw.events using your warehouse's streaming ingestion feature (like Snowpipe, Databricks Auto Loader, Kafka, or a similar ingestion mechanism).
  • You already maintain an incremental fact table that is rebuilt every few minutes using incremental_strategy='merge'.
  • Most dashboards are fine reading from that incremental table, but a small set of operational dashboards want "as‑of‑now" data (for example, the last few minutes of events).

How this pattern works​

  • The base incremental table is rebuilt every few minutes using incremental_strategy='merge'.
  • The NRT view is a view that selects only events newer than the max event_ts already persisted in the base incremental table.
  • The lambda view UNION ALLs the base table and the NRT view, de-duplicating rows based on primary key semantics.

Downstream BI or dashboards query only the lambda view.

Base incremental table​

You can reuse the incremental merge from Snowflake pattern 1 as your base table; for completeness:

-- models/fct_events.sql
{{ config(
    materialized = 'incremental',
    incremental_strategy = 'merge',
    unique_key = 'event_id',
    cluster_by = ['event_date'],
    snowflake_warehouse = 'TRANSFORM_WH'
) }}

with source_events as (

    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') }}

    {% if is_incremental() %}
      -- Only pull new/changed rows since last successful load
      where event_ts >
            (select max(event_ts) from {{ this }})
    {% endif %}

)

select *
from source_events;

Schedule this model to run, for example, every 5–15 minutes as part of your near real‑time job.

NRT view: rows more recent than the base table​

The NRT view returns only events with event_ts greater than the maximum timestamp in the base table, so there is no overlap or double counting:

-- models/fct_events_nrt.sql
{{ config(
    materialized = 'view'
) }}

with base_max as (
    select max(event_ts) as max_event_ts
    from {{ ref('fct_events') }}
),

fresh_events as (
    select
        e.event_id,
        e.event_ts::timestamp_ntz   as event_ts,
        to_date(e.event_ts)         as event_date,
        e.user_id,
        e.event_type,
        e.payload
    from {{ source('raw', 'events') }} as e
    cross join base_max
    where e.event_ts > base_max.max_event_ts
)

select *
from fresh_events;

Characteristics:

  • No scheduling required — it's just a view over raw.events filtered by max(event_ts) from fct_events.
  • Every query against fct_events_nrt scans only "since last batch" data, which should be a small time window (for example, a few minutes or hours, depending on your job cadence).

Lambda view: single read path for BI​

The lambda view combines historical data from the base incremental table with the most recent events from the NRT view.

-- models/fct_events_lambda.sql
{{ config(
    materialized = 'view'
) }}

select
    event_id,
    event_ts,
    event_date,
    user_id,
    event_type,
    payload
from {{ ref('fct_events') }}

union all

select
    event_id,
    event_ts,
    event_date,
    user_id,
    event_type,
    payload
from {{ ref('fct_events_nrt') }};

Point your BI tools and dashboards to analytics.fct_events_lambda. Most data comes from the pre-computed incremental table, while the most recent events (since the last dbt run) come from a live query against raw.events.

This approach is outlined in this original dbt lambda view blog post which describes how teams like JetBlue wired near real‑time operational dashboards on Snowflake and dbt.

Considerations​

Take the following into consideration when using this pattern:

  • Cost profile
    • Every query against fct_events_lambda must read the NRT slice from raw.events in addition to the base table.
    • Use this pattern only for truly operational dashboards that justify the extra per‑query cost.
  • Freshness
    • Freshness is bounded by:
      • Your dbt incremental job frequency (age of fct_events), plus
      • Ingestion latency into raw.events (Snowpipe / streaming layer).
  • Complexity vs alternatives
    • For many modern Snowflake implementations, a dynamic table or materialized view with a small target_lag can provide similar "always within X minutes" service level agreements with less custom SQL and warehouse‑managed incremental logic.
    • Lambda views are best positioned as an advanced / legacy pattern you can still use for when you:
      • Want all logic in dbt SQL
      • Lack the right warehouse feature in your environment
      • Are extending an existing implementation already built this way

Was this page helpful?

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

0
Loading