Skip to main content

HubSpot

HubSpot is a CRM platform for marketing, sales, and service. Load HubSpot contacts, companies, deals, and pipelines into MotherDuck on a schedule with a Flight that runs dlt's HubSpot source.

HubSpot is a CRM platform covering marketing, sales, and service. Its CRM objects, contacts, companies, deals, and tickets, are what most revenue and funnel analysis is built on. To analyze them in MotherDuck, run dlt's HubSpot source and load them into a MotherDuck database.

How it works with MotherDuck​

dlt ships a HubSpot verified source that reads the CRM v3 API and resolves object properties for you, and you can run it in a Flight, so MotherDuck runs the pipeline on a schedule with no infrastructure of your own.

The source exposes these resources:

ResourceContents
contacts, companies, deals, tickets, products, quotesCore CRM objects with their properties.
ownersUsers who can own records.
pipelines_deals, pipelines_ticketsPipeline and stage definitions, for labeling stage IDs on records.
stages_timing_deals, stages_timing_ticketsTime each record spent in each pipeline stage.
propertiesCustom-label metadata. Ships empty, see Known limitations.

All twelve load by default, so use with_resources() to narrow the set. A separate hubspot_events_for_objects resource pulls web analytics events for a specific list of object IDs.

Prerequisites​

  • A MotherDuck account on a plan that includes Flights.
  • A HubSpot private app access token. HubSpot retired plain API keys, so a private app token or OAuth token is the only option.
  • Read scopes on the objects you want, such as crm.objects.contacts.read, crm.objects.companies.read, and crm.objects.deals.read. Scope the token to reads only: ingestion never writes back.
  • A target database in MotherDuck. The examples use hubspot.

Store the token as a Flight secret​

The token is a credential, so it belongs in a Flight secret rather than the Flight's config map. Name the key after dlt's own config variable so dlt resolves it without any glue code in your Flight.

The secret has to exist before you create the Flight, otherwise MD_CREATE_FLIGHT rejects the reference with user_secret not found.

The quickest way is a pre-filled dialog. This link opens Add secret with the type, name, and parameter row already set, so you only paste the token:

Create the hubspot Flight secret in your own MotherDuck account.

You can also open Settings > Secrets and add it by hand with type Flights, or use SQL from a write-enabled connection:

CREATE SECRET hubspot IN motherduck (
    TYPE flights,
    PARAMS MAP {
        'SOURCES__HUBSPOT__API_KEY': '<your_private_app_token>'
    }
);

To keep the literal token out of your SQL and shell history, run that statement from the duckdb CLI, where getenv() resolves client-side:

CREATE SECRET hubspot IN motherduck (
    TYPE flights,
    PARAMS MAP {
        'SOURCES__HUBSPOT__API_KEY': getenv('HUBSPOT_PRIVATE_APP_TOKEN')
    }
);
note

The key is named API_KEY because that's the argument name in dlt's source, not because HubSpot API keys still work. The value must be a private app or OAuth access token.

Create the Flight​

The HubSpot source isn't a single file, so install it as a dependency instead of pasting it into source_code. See Use a dlt verified source for how this works and what to watch for.

HubSpot needs no packages beyond dlt itself:

duckdb==1.5.5
dlt[motherduck]==1.30.0
dlt-verified-sources @ https://github.com/dlt-hub/verified-sources/archive/3957506893a7da821dbcc6acd51c7ca4475d1f53.tar.gz

That commit is a known-good pin. Check the commit history for a newer one, and keep a SHA rather than master.tar.gz: a Flight reinstalls its dependencies on every run, so an unpinned URL can change the connector between two runs of a Flight you haven't touched.

MOTHERDUCK_TOKEN is injected for you, so dlt's MotherDuck destination picks up the credential without configuration.

import os

import dlt
import duckdb

from sources.hubspot import hubspot

DB = "hubspot"


def main():
    os.environ.setdefault("HOME", "/tmp")
    os.environ["DESTINATION__MOTHERDUCK__CREDENTIALS__DATABASE"] = DB

    # dlt attaches the database but never creates it, so make sure it exists.
    duckdb.connect("md:").execute(f'CREATE DATABASE IF NOT EXISTS "{DB}"')

    pipeline = dlt.pipeline(
        pipeline_name="hubspot",
        destination="motherduck",
        dataset_name="hubspot_raw",
    )

    source = hubspot(include_custom_props=True).with_resources(
        "contacts",
        "companies",
        "deals",
        "owners",
        "pipelines_deals",
    )

    print(pipeline.run(source, loader_file_format="parquet"))


if __name__ == "__main__":
    main()

Create the Flight with MD_CREATE_FLIGHT, passing that Python as source_code, the pinned dependencies as requirements_txt, and flight_secret_names := ['hubspot'] so the token reaches the run. Leave schedule_cron off until a manual MD_RUN_FLIGHT succeeds, then add a schedule with MD_UPDATE_FLIGHT.

Query the result​

dlt creates one table per resource in the hubspot_raw schema. Join deals to their pipeline definitions to turn stage IDs into labels:

SELECT
    stages.label AS stage,
    count(*) AS deals,
    sum(deals.amount) AS total_amount
FROM hubspot.hubspot_raw.deals AS deals
JOIN hubspot.hubspot_raw.pipelines_deals__stages AS stages
    ON deals.dealstage = stages.id
GROUP BY ALL
ORDER BY total_amount DESC;

Source options​

ArgumentDefaultEffect
include_custom_propstrueAdd every custom property to whatever the connector already selects. "Custom" here means any property whose name doesn't start with hs_, which also covers HubSpot-native fields like amount and email.
propertiesNonePer-object property lists, keyed by the singular object type: {"deal": [...], "contact": [...]}. Replaces the connector's defaults outright rather than merging with them.
include_historyfalseAlso load property change history into {resource}_property_history tables.
soft_deletefalseLoad archived records with a deleted flag instead of dropping them.

Known limitations​

  • include_history=True multiplies the row count. It adds one row per property change per record. Turn it on only for the objects you need it for, and expect a much longer first load.
  • Narrowing properties takes two arguments, not one. A portal with hundreds of custom properties makes the batch reads large and slow, but a properties list doesn't shrink them on its own. With the default include_custom_props=True, the connector unions your list with every custom property it finds, so asking for two properties on a portal with 200 custom ones still selects 202. Pass include_custom_props=False alongside properties to get only what you asked for.
  • A properties dict has to cover every object you load. Because it replaces the defaults instead of merging with them, an object type you leave out reaches the fetch with None and the run fails with TypeError: 'NoneType' object is not iterable. A property name that doesn't exist in your portal fails earlier, with ValueError: The requested props {...} don't exist in the source!.
  • The properties resource loads nothing. It reads a PROPERTIES_WITH_CUSTOM_LABELS list in the connector's settings.py, which ships empty, so the resource yields no rows and dlt creates no table for it. Populating that list means editing the connector, which a dependency install rules out. Read property metadata from HubSpot's properties API instead.
  • Deleted records are absent by default. Without soft_delete=True, a record deleted in HubSpot vanishes from the next load with no trace of when it went. With it on, archived records load with an is_deleted flag.
  • Daily API quotas apply per account. A scheduled load competes with everything else using the same token. Give ingestion its own private app so you can see and limit its usage.
  • The connector isn't editable when installed as a dependency. To change extraction logic, use dlt init hubspot motherduck in a local project.

Send data back to HubSpot​

To go the other direction and drive HubSpot from MotherDuck data, see Update a HubSpot list from a MotherDuck query with a Flight, which reconciles a static contact list against a query result.

Managed alternatives​

If you'd rather not run the pipeline yourself, Fivetran and Airbyte both offer a HubSpot source and a MotherDuck destination.

Related content​