dbt for web events: from raw hits to sessionized marts

Raw web events — the GA4 BigQuery export, a Snowplow stream, your own collector’s output — are append-only, nested, wide, and full of vendor quirks. Nobody should query them directly for reporting, and in most teams everybody does, each with a slightly different unnest incantation. dbt is where that duplication goes to die: the transformation logic gets written once, versioned, tested, and documented, and every dashboard reads the same tables. This guide covers the layering, the incremental mechanics that raw event data demands, and a sessionized fact table you can adapt.

The layering: staging, intermediate, marts

The standard dbt project shape maps cleanly onto web events:

• Sources declare the raw tables (analytics_123456789.events_*, or your collector’s landing table) with freshness checks — the cheapest possible alarm for “tracking silently stopped.”
• Staging models (stg_ga4__events) do exactly one job: rename, retype, and unnest the vendor schema into flat, consistently named columns. One staging model per source, no business logic, no joins. This is where event_params gets unnested once for the whole company.
• Intermediate models (int_events_sessionized) hold reusable derivations — session assignment, bot filtering, channel classification.
• Marts (fct_sessions, fct_pageviews, fct_conversions, dim_users) are the tables BI tools and stakeholders actually touch: documented, tested, stable contracts.

The discipline that matters most: downstream models select from {{ ref('stg_ga4__events') }}, never from the raw source. When GA4 changes its export schema — it has before — you fix one file.

A staging model for the GA4 export, condensed:

-- models/staging/ga4/stg_ga4__events.sql
{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'event_date', 'data_type': 'date'}
) }}
select
  parse_date('%Y%m%d', event_date)                    as event_date,
  timestamp_micros(event_timestamp)                   as event_ts,
  event_name,
  user_pseudo_id,
  user_id,
  (select value.int_value from unnest(event_params)
    where key = 'ga_session_id')                      as ga_session_id,
  (select value.string_value from unnest(event_params)
    where key = 'page_location')                      as page_location,
  device.category                                     as device_category,
  collected_traffic_source.manual_source              as utm_source,
  collected_traffic_source.manual_medium              as utm_medium
from {{ source('ga4', 'events') }}
{% if is_incremental() %}
-- _table_suffix is the ONLY predicate that prunes the sharded events_* export;
-- a filter on any derived column scans every shard in the dataset. Same 3-day
-- lookback the marts use (see below).
where _table_suffix >= format_date('%Y%m%d',
        date_sub(current_date(), interval 3 day))
{% endif %}

Two things about that staging config are load-bearing. It is a table, not a view: materializing staging as a date-partitioned incremental table is what lets every downstream model filter on event_date and get real partition pruning. And the shard filter lives here, on _table_suffix — the habit the BigQuery guide drills (always filter _table_suffix) applies doubly inside dbt, where it is easy to hide a full-history scan behind a ref().

Repetitive parameter extraction is a natural macro ({{ ga4_param('page_location', 'string') }}); community packages exist for the GA4 export, and they are worth reading even if you write your own models — most teams do, because the staging layer is where your tracking plan meets the warehouse and no package knows your taxonomy.

Incremental models against an append-mostly stream

Web event tables are too large to rebuild nightly, so marts are incremental. The complication is that raw web data is not strictly append-only: GA4 daily tables can be restated for roughly 72 hours as late hits and modeling adjustments land, and any collector with offline batching or server-side retries has the same property. An incremental model that only processes “rows newer than my max timestamp” will silently miss those restatements.

The robust pattern on BigQuery is partition replacement with a lookback window: on every run, reprocess the last N days entirely and overwrite those partitions.

-- models/marts/fct_sessions.sql
{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {'field': 'session_date', 'data_type': 'date'},
    cluster_by = ['channel']
) }}

with events as (
  select * from {{ ref('stg_ga4__events') }}
  {% if is_incremental() %}
  -- reprocess a 3-day window to absorb late-arriving data. This prunes because
  -- staging is materialized as a table partitioned on event_date (above).
  where event_date >= date_sub(current_date(), interval 3 day)
  {% endif %}
),

-- utm_source/utm_medium are event-scoped and null on most of a session's events,
-- so the session's channel must be derived from its FIRST non-null touch. Grouping
-- by an event-level channel instead would split one session into several rows and
-- break the unique test on session_key.
first_touch as (
  select
    concat(user_pseudo_id, '.', cast(ga_session_id as string)) as session_key,
    array_agg(utm_source ignore nulls order by event_ts limit 1)[safe_offset(0)] as first_source,
    array_agg(utm_medium ignore nulls order by event_ts limit 1)[safe_offset(0)] as first_medium
  from events
  where ga_session_id is not null
  group by session_key
)

select
  concat(e.user_pseudo_id, '.', cast(e.ga_session_id as string)) as session_key,
  e.user_pseudo_id,
  min(e.event_date)                                              as session_date,
  min(e.event_ts)                                                as session_start,
  max(e.event_ts)                                                as session_end,
  countif(e.event_name = 'page_view')                            as pageviews,
  countif(e.event_name = 'purchase')                             as purchases,
  {{ classify_channel('ft.first_source', 'ft.first_medium') }}   as channel
from events e
join first_touch ft
  on ft.session_key = concat(e.user_pseudo_id, '.', cast(e.ga_session_id as string))
where e.ga_session_id is not null
group by session_key, e.user_pseudo_id, ft.first_source, ft.first_medium

(No trailing semicolon — dbt wraps model SQL in a create table ... as statement, and a semicolon inside it fails compilation.)

Size the lookback to your source’s restatement window (three days covers GA4’s documented behavior as of mid-2026; add margin if you import offline conversions later than that). On warehouses without partition replacement, the equivalent is a merge strategy keyed on session_key with the same lookback filter — the principle, reprocess the mutable tail, never trust max-timestamp alone, is warehouse-independent.

Two session-specific edge cases to handle deliberately: sessions that straddle a partition boundary (group before you filter, or accept midnight clipping and document it), and sessions still open when the run executes (they will be finalized on the next run because the lookback re-covers them — which is exactly why the lookback pattern beats append-only logic here).

Channels, identity, and the other derivations

That classify_channel macro is worth singling out: encode the channel classification standard as a dbt macro — an ordered, first-match-wins case expression over source/medium — and every mart in the company classifies traffic identically, and identically to how you configured your analytics UI. The same encode-once argument applies to bot filtering and to identity: if you stitch user_pseudo_id to user_id, do it in one intermediate model with a documented rule (e.g. “latest user_id seen for a device wins, never across devices”), because sessionization and every user-level metric inherit whatever that rule decides.

Testing: the part that pays rent

Web tracking breaks upstream constantly — a release drops a dataLayer push, a consent banner change halves collection, a tag fires twice. Your dbt tests are often the first place that damage becomes visible, so test the seams:

• Uniqueness and nullability on contract columns: session_key unique in fct_sessions; user_pseudo_id, event_ts not null in staging.
• Accepted values on channel — every session lands in a channel from the standard’s list, including (Unassigned); a spike in (Unassigned) is a UTM-governance alarm, not noise to hide.
• Source freshness on the raw tables — catches export failures within hours.
• Volume anomaly checks: a test asserting yesterday’s event count is within an expected band of the trailing average catches the “consent banner ate half our traffic” class of incident that row-level tests never see.

# models/marts/_marts.yml
models:
  - name: fct_sessions
    columns:
      - name: session_key
        tests: [unique, not_null]
      - name: channel
        tests:
          - accepted_values:
              # The full channel set from the classification standard — the test must
              # mirror the macro exactly or every run fails on the channels you omitted.
              values: ['Direct', 'Organic Search', 'Paid Search', 'Organic Social',
                       'Paid Social', 'Email', 'Affiliates', 'Referral', 'Display',
                       'Paid Video', 'Organic Video', 'Audio', 'SMS', 'Push',
                       'Cross-network', 'Organic Shopping', 'Paid Shopping', '(Unassigned)']

Schedule the runs with whatever orchestrator you already operate — Airflow and Dagster both treat dbt as a first-class citizen — and make test failures page a human. A broken model that fails loudly is an incident; a broken model that keeps loading is a quarter of wrong numbers.

The payoff

The end state is small and boring in the best way: a handful of documented marts, an incremental strategy that absorbs late data without manual backfills, and a test suite that catches tracking regressions before stakeholders do. Most importantly, questions about definitions — what counts as a session, which channel a click belongs to — now have a canonical answer in version control instead of five competing saved queries.