Step by step: Hubspot connector from Fivetran to dlthub

At dltHub, we often see users who outgrow their old pipeline vendors and look to us for migration help.

If you have 3-5 pipelines to migrate, here’s how a pipeline migration looks - follow along and you can move your connectors in a week.

If you have 30+ connector and need a helping hand, we provide migration help.

Why migrations are hard

Data engineering has plenty of long migration horror stories. Fundamentally the difficulty comes from these challenges:

• The vendor lock: Proprietary connectors obfuscate the source shape: Under the pretense of making data easier to use, proprietary connector vendors rename and reshape your data without providing any lineage. This creates no significant utility for you, but a mystery for you to manually untangle if you want to leave. The logic is never available in documentation because then the vendor lock wouldn’t work. Agents won’t magically know it either, creating a complex verification burden.
• The migration itself can come with its own challenges, for example backfilling source data that is no longer available requires additional work.

To solve for this, here is how I approached this specific migration…

1. Keep the old stack as a reference
2. Reverse engineer the vendor schema
3. Build the dlt pipeline
4. Build the dlt transformations
5. Verification

Step 1: The old stack as reference

I had a HubSpot to Motherduck pipeline running in Fivetran that was syncing data at regular intervals.

Below is a snapshot of the tables in Motherduck

I then modelled the raw data with Fivetran's dbt_hubspot package v(1.8.0), sales module only.

Step 2: Reverse engineering the vendor schema

With the Fivetran pipeline running, I mapped its output schema into a reference file. From that, I worked out the pipeline spec: which columns to keep, which keys to merge on, which write disposition each table needs, which endpoints to call.

Step 3: Build the dlt pipeline

I built the pipeline in the dltHub workbench. The rest-api-pipeline toolkit scaffolded the project, wired credentials, and covered most of the schema declaratively — including HubSpot's search-based incremental endpoints (POST /crm/v3/objects/{type}/search), which turned out to be fully expressible: rest_api_source supports body-based cursor pagination and a convert hook for translating the tracked cursor into epoch milliseconds at filter time.

Two categories resisted the declarative approach, for structural reasons:

• Batch associations (POST /crm/v4/associations/.../batch/read) require collecting parent IDs up front and chunking them into shared request bodies — an aggregation pattern outside what a per-record declarative source can model.
• Fan-out endpoints (pipelines/stages, properties/options, email events/status-changes) split a single response across two output tables, which the declarative path doesn't merely under-support; it fails on them outright.

Both went to custom @dlt.resource functions. Toolkit: roughly three-quarters of the pipeline. Custom: the rest.

Mapping the schema back to the HubSpot API looked like this:

Tables loaded in Motherduck

Step 4: Build the dlt transformations

With the raw data loaded, I rebuilt the modeling layer, using Fivetran's dbt_hubspot package as the reference. Scope is the sales module only. Each dbt model became a @dlt.hub.transformation function — SQL that reads the raw hubspot dataset and writes into hubspot_mart.

Thirteen models, same names and same grain as the originals: hubspot__deals, hubspot__companies, hubspot__engagements, the five engagement subtype tables, hubspot__deal_stages, the two history tables, and the two intermediates.

dlt’s transformations for hubspot__companies:

HubSpot's raw tables are normalized: a deal record stores IDs, not names. The actual pipeline name or owner name lives in a separate table. So most mart tables needed joins just to attach human-readable labels back onto each row. I used left joins specifically, so a deal doesn't vanish from the mart just because, say, its owner or pipeline record is missing. It still shows up, with a blank in that field instead.

The porting work was mostly joins: label lookups to put names back on IDs, and collapsing the engagement fan-outs (plus the same aggregation for per-deal engagement counts).

The other decision: schema shims. The dbt package's output carries columns the new stack can never fill: _fivetran_synced, source_relation, portal_id. I checked whether anything downstream actually uses them (nothing does; _fivetran_synced is selected and cast, never joined or filtered) and emitted them as NULL / ''. The mart's shape stays identical.

Step 5: Verification

With both parts running, I diffed the new mart against the reference. The row counts didn’t match, and chasing down why surfaced two connector behaviors.

First, an undocumented rename: the reference mart's deal_history table has a field called deal_pipeline_id. HubSpot's API calls that property pipeline. Same data, relabeled by the connector, and documented nowhere I could find. Code review wouldn't have flagged it either, because the code was correct against the API's names.

Second, a routing exclusion: dealstage changes don't appear in deal_property_history at all - the connector diverts them to the separate deal_stage table. This one is documented, but on a troubleshooting page rather than the schema reference.

Gaps

The rule for the diff was simple: any mismatch gets an explanation or it's a bug. Here's what doesn't line up, and why:

• Deal history counts differ by design. The final deal_history carries ~200 more rows than the reference — the new sync ran two days after Fivetran's last one, so it holds change events Fivetran never saw. Within the shared time window, the data matches. Fresher data showing up as a "mismatch" is the migration working.
• Fivetran metadata is gone. _fivetran_synced and source_relation exist as NULL/empty shims for schema compatibility. If something in your stack genuinely depends on Fivetran's sync timestamps, that's a real migration item, not a shim.
• portal_id is NULL in the new mart. Fivetran stamps every row with the HubSpot account ID — one constant value for the whole portal. It carries no per-row information, so this is a trivial parity fix: hardcode the ID. Not lost data.
• Timestamps are stored differently. The reference stores naive local time; the new stack stores timezone-aware UTC. Same instants, different representation — worth knowing before you run the diff yourself.
• Sales module only. The marketing and service modules of dbt_hubspot weren't part of this migration.

The verdict

Two things surprised me, and both came from the same place: the connector's docs told me what tables to expect, not everything it did to the data on the way. Neither showed up in code review. Both showed up in a row count.

That's the part to plan for. The build is bounded work; you can scope it from the schema. The validation is where the unknowns live, and it's the only place they surface.

Migrating yourself?

Moving 3-5 pipelines? Block a week, you can do everything in this post yourself with the dltHub workbench.

10+ pipelines? Or simply need an extra set of hands? Let us help!