Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 41 ยท Projects and Practice

Project: Incremental Events Model

Build a clickstream-style incremental dbt model with a loaded_at filter, lookback window, deduplication and a daily rollup, simulating three runs in SQL.

Event data is where incremental models earn their keep: millions of rows a day, mostly append-only, with a trickle of late and duplicated rows. In this project you design an incremental model for web events, simulate three consecutive dbt run calls in SQL, and check that late and duplicate rows are handled correctly. The dbt files are read-only (dbt cannot run here); the simulation runs in your browser.

The requirements

  • Source: raw_events with event_id, user_id, event_type, occurred_at (when it happened) and loaded_at (when it reached the warehouse).
  • Rows can be delivered twice, and some arrive up to two days late.
  • Output: fct_events (one row per event, no duplicates) and agg_daily_events (a daily rollup).

The model

SQL
-- models/marts/fct_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='delete+insert',
    on_schema_change='append_new_columns'
) }}

with source as (
    select * from {{ ref('stg_web__events') }}

    {% if is_incremental() %}
      where loaded_at > (select max(loaded_at) from {{ this }})
    {% endif %}
),

deduplicated as (
    select *
    from (
        select *,
               row_number() over (partition by event_id order by loaded_at desc) as rn
        from source
    )
    where rn = 1
)

select event_id, user_id, event_type, occurred_at, loaded_at
from deduplicated

It uses what you learned: loaded_at as the filter (late rows still arrive loaded later), unique_key so a re-delivered event replaces its predecessor, and in-batch deduplication with row_number().

Simulating three runs

First set up the source and run the model for the first time:

SQL Run 1: the initial build

Press Run (or Ctrl + Enter) to see the result here.

Now the next day brings new rows, a re-delivered event 3 with a corrected type, and a late event that happened yesterday. This second run applies the incremental filter, deduplicates, and delete+inserts by key:

SQL Run 2: new, duplicate and late rows

Press Run (or Ctrl + Enter) to see the result here.

Five distinct events are present: event 3 now has type purchase (the correction replaced the old row), event 5 appears once although it was delivered twice, and the late event is included because its loaded_at is newer than the watermark. A filter on occurred_at would have dropped event 5.

The daily rollup

agg_daily_events aggregates fct_events by event date. For a rollup that is cheap to rebuild, a plain table is enough. When it gets big, make it incremental too with a lookback of two days, because late events change the counts of past days:

SQL
{{ config(materialized='incremental', unique_key='event_date') }}

select
    date(occurred_at) as event_date,
    count(*) as events,
    count(distinct user_id) as users
from {{ ref('fct_events') }}
{% if is_incremental() %}
where date(occurred_at) >= (select date(max(event_date), '-2 days') from {{ this }})
{% endif %}
group by 1

(date(..., '-2 days') is SQLite style, which is also what the editor runs; on other warehouses, use dbt.dateadd.) Here is the aggregation itself:

SQL Daily rollup

Press Run (or Ctrl + Enter) to see the result here.

Tests and safeguards

YAML
models:
  - name: fct_events
    columns:
      - name: event_id
        data_tests: [unique, not_null]
      - name: event_type
        data_tests:
          - accepted_values:
              arguments:
                values: ['view', 'click', 'purchase']
              config: {severity: warn}
      - name: loaded_at
        data_tests:
          - dbt_utils.recency:
              arguments: {datepart: hour, field: loaded_at, interval: 24}
  • unique on event_id is the guard against duplicates, run after every build.
  • recency fails if no new events have been loaded for 24 hours (a stalled pipeline).
  • Schedule a weekly dbt run --select fct_events --full-refresh to repair drift (see late data).
flowchart LR
  R["raw_events"] -->|"loaded_at > watermark"| B["Batch"]
  B --> D["Deduplicate by event_id"]
  D --> U["Delete + insert by key"]
  U --> F["fct_events"]
  F --> A["agg_daily_events"]

Each run: filter by load time, deduplicate, replace by key, roll up.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why filter on loaded_at instead of occurred_at?

    The load time keeps increasing, so a late event is selected by the next run.
  2. 2. What prevents event 3's old row from staying next to its corrected version?

    The key identifies the old row; it is deleted and the corrected one inserted.
  3. 3. Why does the daily rollup need a lookback window?

    Recomputing the last few days picks up the late rows' effect.
  4. 4. Which test detects a stalled ingestion?

    recency fails when the newest row is older than the allowed interval.

Summary

  • Filter incremental loads by load time, deduplicate each batch and replace rows by key.
  • Late and duplicate rows are normal; design for them.
  • Rollups over incremental tables need a lookback window.
  • Protect the model with unique, recency and a scheduled full refresh.

Next: Project: Testing and Quality Gates