How I Use Fivetran Lite, dbt Cloud, and HubSpot to Catch Attribution Drift

How I Use Fivetran Lite, dbt Cloud, and HubSpot to Catch Attribution Drift

Monthly reporting usually breaks before the meeting starts. The dashboard still loads. The CAC chart still has bars. HubSpot still says paid search sourced $84,000 in closed-won revenue. Nobody notices that 19 deals lost their campaign ID after a workflow edit three weeks earlier.

That is attribution drift. It is boring until it costs you a budget decision.

I started treating campaign attribution as a tested data product in 2026 because the source systems finally made it cheap enough for small teams to do. Fivetran’s Lite connector model matters here. According to Fivetran’s own Lite connector docs, Lite connectors are API-based connectors built for specific use cases, with partial endpoint coverage, a Private Preview to GA release path, and the same SLA for major data integrity, security, and reliability issues as standard connectors. Fivetran also says Lite connectors use the same consumption-based Monthly Active Rows pricing model. So the savings are not magic row discounts. The savings come from avoiding a custom ingestion project for every niche marketing tool.

For a lean marketing team, that distinction is the whole thing.

In the setup I like, Fivetran handles the pipes, dbt Cloud handles the assertions, and HubSpot remains the operating system for campaigns, deals, and lifecycle data. The warehouse is where we stop trusting the story at face value.

The Case That Changed My Mind

The cleanest example came from an anonymized B2B SaaS team I worked with in April 2026. Twelve people in go-to-market. About $4.8 million ARR. Paid search, LinkedIn Ads, founder-led LinkedIn, two webinars per quarter, and a HubSpot Professional portal that had been alive since 2021.

Their stack was normal: HubSpot, Google Ads, LinkedIn Ads, Webflow forms, Stripe for self-serve upgrades, Snowflake, Fivetran, and dbt Cloud. Reporting lived in Looker Studio because the founder liked sending screenshots in Slack before the Tuesday pipeline call. No shame. Half the companies I see at this stage are some version of that.

The problem was also normal. Their May board deck showed organic search producing 31 percent less closed-won revenue than April. Paid social looked up 22 percent. The first reaction was to shift another $18,000 into LinkedIn for June.

The numbers were wrong.

The culprit was a HubSpot campaign cleanup. Someone renamed Q2 campaign records, merged two old webinar campaigns, and changed the campaign UTM policy so new campaigns used automatically generated UTM values. HubSpot’s knowledge base, updated August 3, 2026, says campaign UTM values can be generated automatically and that old values remain as secondary values unless deleted. That is helpful inside HubSpot. In a warehouse model that expects one clean campaign UTM, it can turn into soup.

We found three issues in the warehouse. First, 47 contacts created between May 6 and May 13 had utm_campaign values that no longer matched the campaign table. Second, 19 closed-won deals had campaign GUIDs attached to contacts but no valid campaign join after the cleanup. Third, $62,400 in May closed-won revenue moved from organic search to paid social because the model fell back to last non-null UTM source when the campaign join failed.

That fallback looked reasonable in SQL. It was poison in the board deck.

Why Lite Connectors Belong In This Conversation

Fivetran’s 2026 connector catalog has a lot of Lite labels now. The public connector docs list examples from Adform Lite and Amazon Attribution Lite to dbt Cloud Lite, plus dozens of operational SaaS apps that would have been custom scripts two years ago. Fivetran’s public API also exposes connector_class as standard or lite, which is useful if you maintain connector inventory in code.

I do not treat Lite as a signal that the data is lower quality. I treat it as a signal that the contract is narrower.

That matters for campaign attribution. Marketing teams love to ask one connector for five jobs: spend ingestion, campaign metadata, creative performance, CRM lifecycle, and revenue attribution. A Lite connector may cover the endpoint you need and skip the endpoint you assumed would be there. Fivetran says Lite starts from the available API and targets a specific use case. That is a practical design, but it puts more responsibility on your dbt layer.

For HubSpot itself, Fivetran’s standard connector has improved in ways that help attribution work. Fivetran’s HubSpot changelog says Marketing Campaigns API support landed in June 2025 with tables including MARKETING_CAMPAIGN, MARKETING_CAMPAIGN_ASSET, MARKETING_CAMPAIGN_BUDGET_ITEM, MARKETING_CAMPAIGN_CONTACT, and MARKETING_CAMPAIGN_SPEND_ITEM. In June 2026, Fivetran changed MARKETING_CAMPAIGN_CONTACT to incremental sync and stopped capturing deletes for that table. That one sentence should make every marketing ops person sit up.

Deletes are where attribution drift hides.

HubSpot’s own Campaigns API guide, last modified March 30, 2026, uses /marketing/campaigns/2026-03 and returns a campaignGuid UUID. It also documents hs_utm, hs_campaign_status, hs_start_date, hs_end_date, and hs_currency_code. The same guide notes that hs_goal was sunset on July 9, 2025 and silently ignored after that. If your warehouse model still expects hs_goal, the API will not scream. Your revenue report will just get weird later.

The Data Model I Actually Want

I do not start with a giant multi-touch attribution model. That is where teams burn three weeks and still ship a chart nobody trusts.

I start with four models.

stg_hubspot__marketing_campaign is a thin staging model over Fivetran’s MARKETING_CAMPAIGN. It keeps campaign_guid, hs_name, hs_utm, hs_campaign_status, dates, currency, and _fivetran_synced.

stg_hubspot__deal is a thin model over DEAL, filtered to _fivetran_deleted = false when that column exists. Fivetran’s March 2026 HubSpot changelog says merged records are now marked as deleted, which is exactly the kind of source behavior you want to respect early.

int_marketing_touchpoints normalizes UTMs from form submissions, ad landing pages, and campaign-contact associations into one row per contact per touch. It does not decide attribution. It just says what happened.

fct_campaign_revenue joins deals to contacts, contacts to touchpoints, and touchpoints to campaigns. This is where closed-won amount gets assigned to campaign, source, medium, and reporting month.

Small. Testable.

Fivetran’s dbt HubSpot package is useful context here. Its README says it has 193 materialized models and supports dbt Core >=1.3.0, <3.0.0. I still write my own attribution mart because every company has different lifecycle rules, but I borrow the package’s habit of separating source-shaped staging from analytics-shaped outputs.

The Tests That Catch Drift

The first dbt tests are boring by design. Boring tests save money.

In dbt, built-in tests like not_null, unique, accepted_values, and relationships catch the dumb failures before they become expensive meetings. dbt Labs has written about using code-based, version-controlled tests for data quality checks, and this is where that advice earns its keep.

Here is the kind of schema file I would put around the campaign spine in dbt Cloud:

version: 2

models:
  - name: stg_hubspot__marketing_campaign
    columns:
      - name: campaign_guid
        data_tests:
          - not_null
          - unique
      - name: hs_utm
        data_tests:
          - not_null:
              config:
                where: "hs_campaign_status in ('active', 'in_progress', 'completed')"
      - name: hs_campaign_status
        data_tests:
          - accepted_values:
              arguments:
                values: ['planned', 'in_progress', 'active', 'paused', 'completed']

  - name: fct_campaign_revenue
    columns:
      - name: campaign_guid
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_hubspot__marketing_campaign')
                field: campaign_guid
      - name: closed_won_amount
        data_tests:
          - not_null

That catches missing campaign IDs, duplicate campaign IDs, unexpected status values, and revenue rows pointing at campaign records that no longer exist in the modeled campaign table.

Then I add custom SQL tests, because attribution breaks in ways generic tests cannot see.

One test flags closed-won revenue with no campaign when the deal has at least one marketing contact association in the prior 180 days. For the April 2026 SaaS team, this caught the 19 deals that would have vanished from campaign reporting.

select
  deal_id,
  close_date,
  closed_won_amount
from {{ ref('fct_campaign_revenue') }}
where deal_stage = 'closed_won'
  and close_date >= dateadd(day, -45, current_date)
  and campaign_guid is null
  and has_marketing_touchpoint_180d = true

Another test checks UTM shape. I do not care if the naming convention is pretty. I care that it is consistent enough to join.

select
  campaign_guid,
  hs_utm
from {{ ref('stg_hubspot__marketing_campaign') }}
where hs_campaign_status in ('active', 'in_progress', 'completed')
  and (
    hs_utm is null
    or length(hs_utm) < 4
    or regexp_like(lower(hs_utm), '\s')
  )

The whitespace check sounds petty until someone pastes q2 webinar with a trailing space into an ad platform and you spend Friday afternoon reconciling two strings that look identical in a dashboard.

The third test is the one I care about most. It compares current-month channel revenue against a 28-day rolling baseline and fails when the swing is too large without enough deal volume to explain it. For this client, we started with a 35 percent swing threshold and a minimum of 10 closed-won deals per channel. Paid social failed on May 24, 2026. That was five business days before the reporting pack was due.

A statistical purist will hate that test. Fine. It is an alarm bell, not a paper.

Running It In dbt Cloud

I prefer three dbt Cloud jobs for this setup.

The first runs after the morning Fivetran sync, usually around 7:15 a.m. in the company’s local time zone. It builds staging and intermediate models, then runs source freshness and the basic schema tests.

The second runs at noon and only tests the attribution mart. This catches same-day campaign edits before the sales team starts updating deals in bulk.

The third runs at 6 p.m. on business days and posts failures to Slack. I route failures into a #revops-data-alerts channel with the model name, failing row count, and a link to the dbt Cloud run. No giant alert essay. The operator needs the bad rows and the owner.

For the SaaS team, this took two short implementation passes. The first pass was four hours: identify HubSpot tables, create staging models, add generic tests, and wire a dbt Cloud job. The second pass was six hours: write the custom attribution tests, backfill 2026 data, and tune thresholds against January through April closed-won history.

The first month it ran, it caught $62,400 in misclassified revenue. The next month, it caught a smaller issue: 11 Google Ads campaigns using utm_medium=cpc while the organic content workflow wrote utm_medium=paid_search for syndicated posts. That one only moved $8,900, but it would have made paid search look 6.7 percent better than it was.

Those are not warehouse problems. They are operating problems that show up in the warehouse early if you ask the warehouse the right questions.

What I Would Not Do

I would not wait for a perfect attribution model. If your team has under 50,000 monthly website sessions and fewer than 200 closed-won deals per quarter, the argument over first-touch, last-touch, and W-shaped attribution is probably less valuable than making sure campaign IDs still join.

I would not let HubSpot be the only source of truth for performance reporting. HubSpot is excellent at running campaigns and CRM workflows. It is also editable by humans under deadline pressure. A workflow change on May 13 can rewrite the story you tell on May 31.

I would not assume Fivetran Lite means complete endpoint coverage. Read the connector docs. Check connector_class if you inventory connectors through the API. Put tests around the fields that money depends on.

The practical setup is plain: Fivetran moves the data, Lite connectors fill the long tail where they fit, dbt Cloud tests the contracts, and HubSpot stays close enough to the work that marketers keep using it. The win is not a prettier dashboard. It is catching the drift while there is still time to fix the story behind the number.