Cross-Platform Ad Testing Patterns

Testing cross-platform ad models requires specific attention because the failure modes are distinct: platform connectors go stale independently, intermediate normalization can break for one platform while others look fine, and timezone discrepancies create variance that you need to distinguish from actual bugs.

Source Freshness

Source freshness tests are your first line of defense. Every platform connector has different latency characteristics, and you want to know when data stops flowing before stakeholders notice empty dashboards.

sources:
  - name: google_ads
    freshness:
      warn_after: {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    loaded_at_field: _fivetran_synced
  - name: facebook_ads
    freshness:
      warn_after: {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    loaded_at_field: _fivetran_synced
  - name: linkedin_ads
    freshness:
      warn_after: {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    loaded_at_field: _fivetran_synced

The 24-hour warn / 48-hour error threshold works well as a starting point. Some platforms update more frequently than others — Google Ads data typically arrives within hours, while LinkedIn can lag by a full day. Tighten the thresholds per platform as you learn your connectors’ actual behavior.

Run dbt source freshness as a separate step before your main dbt run. If a source is stale, you want to know before you rebuild models on top of incomplete data. Some teams configure this as a circuit breaker: if any ad source fails the freshness check, skip the ad reporting models entirely rather than producing partial results.

Spend Reconciliation

Spend reconciliation is the highest-priority test for cross-platform ad models. The unified spend total should match the sum of individual platform spends, and any significant variance signals a transformation bug.

models:
  - name: mrt__marketing__campaign_report
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_day
            - platform
            - campaign__id
    columns:
      - name: campaign__spend
        tests:
          - not_null
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              max_value: 1000000

The unique_combination_of_columns test catches a specific failure mode: duplicated rows after a bad JOIN or incomplete deduplication in the intermediate layer. If the same campaign on the same day appears twice, spend gets double-counted. This test catches it immediately.

For spend-specific validation, a singular test that compares the unified total against the sum of platform totals is invaluable:

-- tests/assert_unified_spend_reconciles.sql
WITH unified AS (
    SELECT SUM(campaign__spend) AS total_spend
    FROM {{ ref('mrt__marketing__campaign_report') }}
    WHERE date_day >= CURRENT_DATE() - 7
),
platform_sum AS (
    SELECT SUM(campaign__spend) AS total_spend
    FROM (
        SELECT campaign__spend FROM {{ ref('int__google_ads__campaign_report') }}
        WHERE date_day >= CURRENT_DATE() - 7
        UNION ALL
        SELECT campaign__spend FROM {{ ref('int__facebook_ads__campaign_report') }}
        WHERE date_day >= CURRENT_DATE() - 7
        UNION ALL
        SELECT campaign__spend FROM {{ ref('int__linkedin_ads__campaign_report') }}
        WHERE date_day >= CURRENT_DATE() - 7
    )
)
SELECT
    unified.total_spend AS unified_total,
    platform_sum.total_spend AS platform_total,
    ABS(unified.total_spend - platform_sum.total_spend) AS difference
FROM unified
CROSS JOIN platform_sum
WHERE ABS(unified.total_spend - platform_sum.total_spend) / NULLIF(platform_sum.total_spend, 0) > 0.03

The 3% threshold accounts for timezone-related discrepancies. Fivetran’s dbt_ad_reporting DECISIONLOG documents this as expected variance. Anything above 3% signals a pipeline bug worth investigating.

Grain Testing

Grain violations are the most common transformation bug in cross-platform models. A bad JOIN in the intermediate layer can silently fan out rows, doubling or tripling spend for one platform while the others look correct.

Apply dbt_utils.unique_combination_of_columns to every unified model across date, platform, and entity ID columns:

models:
  - name: mrt__marketing__campaign_report
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_day
            - platform
            - campaign__id

  - name: mrt__marketing__ad_group_report
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_day
            - platform
            - ad_group__id

  - name: mrt__marketing__ad_report
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - date_day
            - platform
            - ad__id

These tests should run in CI and on every production dbt run.

Statistical Anomaly Detection

The dbt-expectations package adds statistical anomaly detection that catches problems automated structural tests miss. Spend spikes, impression drops, or conversion patterns that fall outside historical norms can signal:

• A connector that started pulling duplicate data
• A platform API change that altered how a metric is reported
• A campaign that was paused or launched without the data team’s awareness

columns:
  - name: campaign__spend
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 1000000
      - dbt_expectations.expect_column_mean_to_be_between:
          min_value: 10
          max_value: 50000
          group_by: [platform]

These tests create guardrails around “normal” behavior. They won’t catch every issue, but they’ll catch the dramatic ones — a spend column that’s suddenly 6 orders of magnitude too high because someone forgot the Google micros-to-currency division, for instance.

Manual Reconciliation

Automated tests catch structural problems. Weekly manual reconciliation of warehouse spend totals against platform UIs catches the subtle ones.

Pick a recent 7-day window, pull the spend total from each platform’s native dashboard, and compare it against your warehouse. Document the variance for each platform and investigate anything outside the 1-3% expected range.

One reported case from a 500-person fintech: their CLTV calculation broke due to a third-party data source issue, causing them to lose part of the 100,000 GBP they spent on Google that day plus several days of recalibration time. Automated tests didn’t catch it because the structural integrity of the data was fine — the numbers were valid but wrong.

A Testing Strategy Summary

For ad reporting, spend is the primary metric to protect, followed by conversion accuracy, then impression and click counts.