GA4-Specific dbt Testing Patterns

Standard dbt schema tests — unique, not_null, accepted_values — catch data pipeline failures. They don’t catch GA4 tracking failures. A GA4 property can export data successfully while silently losing conversion data, misattributing sessions, or generating phantom sessions from bot traffic. GA4-specific tests catch these.

Source Freshness

The first line of defense is source freshness monitoring. If GA4’s export pipeline stalls, you want to know before your stakeholders do.

sources:
  - name: ga4
    database: "{{ var('ga4_project_id') }}"
    schema: "{{ var('ga4_dataset') }}"

    tables:
      - name: events
        identifier: "events_*"
        freshness:
          warn_after: {count: 24, period: hour}
          error_after: {count: 48, period: hour}
        loaded_at_field: "TIMESTAMP_MICROS(event_timestamp)"

GA4’s daily export typically completes 6-12 hours after the day ends. A 24-hour warning window flags delays without triggering on normal timing variation. The 48-hour error threshold catches genuine outages.

Base Model Schema Tests

On base__ga4__events, the critical tests are on the fields that everything else depends on:

models:
  - name: base__ga4__events
    columns:
      - name: event__key
        tests:
          - unique
          - not_null

      - name: user__pseudo_id
        tests:
          - not_null

      - name: event__timestamp_utc
        tests:
          - not_null

      - name: session__key
        tests:
          - not_null

event__key uniqueness is the most important test — it catches duplicate events from overlapping incremental windows or bugs in the surrogate key generation. session__key not-null catches the case where ga_session_id extraction is broken (returning null, making the composite key null).

Singular Test: Missing session_start Events

Sessions that have multiple events but no session_start indicate a tracking implementation problem. Some causes are benign (users with ad blockers suppressing the first event), but a high rate indicates something wrong with your tracking code.

-- tests/singular/test_sessions_missing_session_start.sql

WITH session_stats AS (
    SELECT
        session__key,
        COUNT(*) AS session__events,
        MAX(CASE WHEN event__name = 'session_start' THEN 1 ELSE 0 END)
            AS session__has_start
    FROM {{ ref('int__ga4__events_sessionized') }}
    WHERE event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    GROUP BY session__key
)

SELECT
    session__key,
    session__events,
    session__has_start
FROM session_stats
WHERE session__has_start = 0
    AND session__events > 3  -- Legitimate multi-event sessions without start
LIMIT 100

The session__events > 3 threshold filters out single-event edge cases. A session with 10 events and no session_start is almost certainly a tracking problem; a session with 2 events might just be a timing artifact.

This test returns rows if the condition is met, which in dbt means failure. Configure with severity: warn to surface tracking issues without blocking pipeline runs.

Singular Test: Orphaned Transactions

Purchase events without valid session context produce revenue data that can’t be attributed to any channel or campaign. These often indicate Measurement Protocol implementations that don’t pass session context correctly.

-- tests/singular/test_purchase_without_session.sql

SELECT
    event__key,
    event__date,
    session__ga_id,
    user__pseudo_id
FROM {{ ref('base__ga4__events') }}
WHERE event__name = 'purchase'
    AND transaction__id IS NOT NULL
    AND (session__ga_id IS NULL OR user__pseudo_id IS NULL)
    AND event__date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
LIMIT 100

Any orphaned transactions should fail loudly — these represent revenue that will be invisible in channel attribution reports. Unlike the session_start test (where some rate is expected), orphaned transactions should be zero.

Schema Tests for the Sessionized Table

The wide sessionized table has columns that aren’t simple not-null checks — they have expected ranges and accepted values:

models:
  - name: int__ga4__events_sessionized
    columns:
      - name: event__key
        tests:
          - unique
          - not_null

      - name: session__key
        tests:
          - not_null

      - name: session__landing_page
        description: "First page path in the session"
        tests:
          - not_null:
              where: "event__name = 'page_view'"

      - name: session__pageviews
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1000

      - name: session__duration_seconds
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 86400

      - name: session__channel_grouping
        tests:
          - accepted_values:
              values:
                - 'Direct'
                - 'Organic Search'
                - 'Paid Search'
                - 'Paid Social'
                - 'Organic Social'
                - 'Email'
                - 'Referral'
                - 'Display'
                - 'Affiliates'
                - 'Other'

Landing page conditional not-null: The where clause scopes the not-null check to only page_view events. Non-page-view events legitimately have no landing page. This is more precise than checking all events (which would fail correctly for page_views with missing data but also flag every purchase event).

Pageview range test: Sessions with more than 1,000 page views are almost certainly bots. The range test flags them — useful for catching bot traffic that has slipped through GA4’s filters.

Duration range: Sessions longer than 24 hours (86,400 seconds) indicate a session key collision bug or data quality problem. Real user sessions don’t span days.

Channel grouping accepted values: Catches bugs in the channel grouping macro that produce unexpected channel names.

Test Severity Strategy

Not all tests should block production:

tests:
  +severity: warn
  +store_failures: true

Setting severity: warn globally means tests fail softly — they surface in the test results without blocking downstream models or alerting pages. store_failures: true writes failing rows to the warehouse so you can investigate them.

Override for critical tests that should block:

columns:
  - name: event__key
    tests:
      - unique:
          severity: error
      - not_null:
          severity: error

Uniqueness violations on event__key indicate a fundamental pipeline bug. Orphaned transactions should be errors if revenue accuracy is business-critical. Session start rate issues should be warnings — they indicate tracking problems worth investigating, but shouldn’t block reporting.

What These Tests Don’t Catch

These tests catch data quality issues in your pipeline. They don’t catch:

• The 1-2% GA4 UI variance — Expected and documented. See GA4 BigQuery Number Discrepancies.
• Attribution model differences — Your channel grouping will differ from GA4’s interface. Accept and document this gap.
• Late-arriving conversions — The static lookback window handles most of these, but conversions that arrive after the lookback window will appear in wrong dates. Monitor via periodic comparisons.

Recommended test coverage for a GA4 project: freshness on the source, uniqueness on keys, singular tests for GA4-specific tracking failures, and range tests on sessionized metrics.