Ad platforms & SCD2 snapshots

The ad platforms — Google Ads, Meta Ads, Bing Ads, TikTok Ads, RTB House, Insider — each land differently-shaped data, mostly via Integrate.io (see Raw sources & permissions for exactly which). This page covers how that gets staged per-platform, then unified through dbt's native snapshot feature into one platform-agnostic history.

Staging is per-platform, and mostly thin

One folder per platform — GA4 alone has more staging models than every ad platform combined (see GA4 & web analytics). Most ad-platform staging models are a straightforward rename/type pass over the landed source — google_ads__campaign_stg.sql unquotes and renames the Google resource-based columns, derives customer_id from the campaign resource name, and appends a deterministic surrogate campaign_pk.

One platform breaks the source→staging pattern: Insider has no dedicated campaign dimension table at all. insider__campaigns_stg.sql doesn't read a source() — it reads another staging model, ref('insider__driveau_driveudp_devtable_stg') (a raw event-level table), and derives synthetic campaign start/end dates via min()/max() over event timestamps. Insider's "staging" layer is doing intermediate-layer aggregation work, because that's the only way to get a campaign dimension out of event data that doesn't have one.

What each platform's staging folder actually contains

The platforms aren't a uniform shape — each connector exposes a different mix of dimension and report tables, and staging mirrors that difference exactly rather than normalising it away:

Two practical consequences: performance-metric grain isn't consistent across platforms (TikTok has hourly data nothing else does), and entity depth isn't consistent either (RTB House has no ad-group/ad tier to snapshot, so it only appears once in the SCD2 table below).

SCD Type 2: dbt's native snapshot, not hand-rolled

This is dbt's built-in snapshot feature — a snapshots/ directory with strategy: check configs, not a hand-rolled valid_from/valid_to MERGE, organised one folder per platform:

The Google Ads campaign snapshot, in full:

{% snapshot google_ads__campaign_snapshot %}
{{
  config(
    unique_key = "campaign_id",
    strategy = 'check',
    invalidate_hard_deletes = true,
    check_cols = [
      'campaign_name','campaign_status','serving_status','channel_type',
      'channel_sub_type','experiment_type','start_date','end_date',
      'bidding_strategy_type','bidding_strategy_ref',
      'ad_serving_optimization_status','target_roas','target_cpa_micros',
      'target_spend_micros','campaign_budget_ref','final_url_suffix',
      'tracking_url_template','tracking_url','optimization_score'
    ]
  )
}}
select * from {{ ref('google_ads__campaign_stg') }}
{% endsnapshot %}

strategy: check compares the listed check_cols on every run; if any differ from the last snapshotted version, dbt closes out the old row (dbt_valid_to) and inserts a new one. invalidate_hard_deletes: true means a campaign that disappears from the source entirely gets its current row closed out too, not left dangling as if still current.

Only the mutable configuration/structure entities are snapshotted — a campaign's status, budget, and targeting. Performance-metric tables (daily/hourly spend and click reports) are never snapshotted; they're append-only fact data, not something that needs "as of a point in time" history the way a campaign's settings do.

Unifying every platform into one dimension

paid_media__campaign_scd_int.sql is where the platforms' snapshots actually converge: it unions every snapshot output, normalises campaign names against a campaign_normalization_long seed (so the same real-world campaign named slightly differently across platforms is recognisable as one thing), and computes is_latest/is_deleted flags per (platform_source, campaign_id) via a window function ordered by dbt_valid_from DESC.

The mart layer's current-state dimension is then just a filter on that:

-- paid_media__campaigns_dim.sql
select * from {{ ref('paid_media__campaign_scd_int') }}
where is_latest = true

Google Ads, end to end — two parallel paths

A dimension path (structure/config, SCD2) and a metrics path (spend/performance, plain append) run separately and only meet at the reporting layer:

The intermediate layer here does real cross-platform transformation — reconciling differently-shaped schemas across platforms and computing SCD flags — not the thin pass-through seen in Redbook's intermediate layer. Not every reporting model follows this shape, though: business__campaign_performance_rpt.sql deliberately doesn't reference any paid-media staging model at all — its own header comment states it excludes spend entirely and points to a different report (business__campaign_daily_rpt) for margin/ROI.

Two Meta Ads accounts share one platform folder

dbt_project.yml declares separate source-schema vars for two Meta accounts — meta_ads_drive_schema and meta_ads_caradvice_schema — both landing under the same meta_ads staging folder. If you're tracing Meta data and the numbers look like they're missing something, check which account's schema a given model actually reads from; the folder name alone doesn't tell you.

See also

• CRM domain (Salesforce)
• GA4 & web analytics
• Marts