HubSpot to ClickHouse: How to Move Your Data

Moving HubSpot into ClickHouse is what teams do when CRM reporting has outgrown HubSpot's own dashboards and a general-purpose warehouse feels like more machinery than the problem needs. ClickHouse is fast, cheap to run and very good at the kind of aggregation this data invites.

This guide covers the managed path with Airbyte. Nearly all the constraints here are on HubSpot's side rather than ClickHouse's, and one of them is a distinction in how the connector syncs incrementally that determines whether your rate limit budget goes further or not at all.

HubSpot to ClickHouse at a glance:

Why move data from HubSpot to ClickHouse?

Two situations account for most of these pipelines.

The first is joining the commercial picture together. HubSpot knows about deals and marketing activity. Your product database knows whether those customers use anything, and your billing system knows whether they paid. Answering questions that span all three needs one place to query, and ClickHouse handles it at a fraction of the operational overhead of a full warehouse.

The second is powering something fast. If you already run ClickHouse behind an internal tool or a customer-facing dashboard, putting CRM data next to the event data already there means one engine serving both. If your only need is standard CRM reporting, HubSpot does that and this pipeline earns you nothing.

What do you need before you start?

Two credentials, and two agreements with people outside your team:

A Private App or OAuth application. With read scopes on every object you intend to sync. Association streams need read scopes on both objects in the relationship, and custom objects need the custom object read scope.

An agreed share of the daily API budget. The burst limit is per app, so a surge from your sync will not immediately throttle anyone else. The daily limit belongs to the HubSpot account and every connected application draws from it, so find out what else talks to HubSpot before choosing a schedule.

A pruned set of custom properties. The more properties an object carries, the more data comes back on every record on every request. HubSpot instances accumulate properties the way garages accumulate paint tins, and removing ones nobody queries is a genuine performance improvement.

A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through the native protocol into typed columns rather than storing everything as JSON in a raw table.

If your ClickHouse instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a HubSpot to ClickHouse pipeline in Airbyte?

Step 1: Decide your objects and associations

List the objects your analysts need, and separately list the relationships between them that matter. Those relationships do not fall out of the object streams on their own, and defining them is a configuration step rather than something you can add later without thought. Contacts to companies and deals to companies cover most cases.

Step 2: Configure the HubSpot source

Click Sources in the left navigation, then New Source, and select HubSpot, following adding a source. Authenticate, set a start date, and define each association stream by naming a from object and a to object. Then refresh the source schema, which is the step people forget when a stream they configured stubbornly fails to appear in the catalogue.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. Deduplication uses the ReplacingMergeTree table engine, and for it to order duplicates correctly your cursor column should be a timestamp or numeric type rather than a string.

Step 4: Create the connection and set a lookback window

Click Connections, then New connection, select your streams and a sync mode. Set a lookback window if you are syncing the search-backed streams, which include deals, companies, contacts, tickets, leads and the engagement streams, because those can miss records due to irregularities in HubSpot's API. Deduplication in ClickHouse means the resulting overlap does not leave permanent duplicates.

That is a real advantage of a warehouse-style destination over a streaming one here. Against Kafka, widening the lookback republishes records as duplicates that consumers must handle. Against ClickHouse, they collapse.

Why is my incremental sync not saving any API calls?

Because incremental means two different things depending on the stream. Server-side incremental asks HubSpot for records changed since the last sync, and the API returns only those. Client-side incremental asks for everything, receives everything, and discards what the connector has already seen.

Both produce correct data in ClickHouse. Only the first reduces what you spend against the account's daily budget. Which one a stream uses is a property of that stream rather than a setting you choose, so the value of knowing this is diagnostic: when an incremental sync is unexpectedly slow, this is usually why, and the fix is dropping streams or reducing frequency rather than hunting for a misconfiguration that does not exist.

Association streams are the pleasant exception. They sync incrementally, fetching associations only for records modified since the last sync, which makes them cheap against your rate limit and a good reason to model relationships properly rather than deriving them later.

Why do duplicates appear and then disappear?

ReplacingMergeTree collapses duplicate rows during background merges, which happen when ClickHouse decides they should rather than when your sync finishes. So a query run shortly after a sync can legitimately return duplicates that the same query returns cleanly an hour later.

This is expected behaviour rather than a fault, and it catches out analysts who have not been told. To guarantee deduplicated results at query time, add the FINAL operator. It costs performance, so the usual pattern is a view using FINAL for correctness-critical queries and direct table access for exploratory work.

On CRM data specifically, deal counts and pipeline totals are exactly the numbers people notice being wrong, so put those behind a view that uses FINAL. Contact activity counts and other approximate measures can read the table directly and gain the speed.

Frequently asked questions

Why is my incremental HubSpot sync still slow?

Some streams use client-side incremental, where the API returns everything and the connector filters locally. The data is correct but there is no reduction in API usage or transfer time.

Why does my ClickHouse table contain duplicate records?

ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.

Does my sync compete with other HubSpot integrations?

For the daily limit, yes. It is shared across every app on the account. The burst limit is per app, so bursts are isolated but daily budget is not.

How do I get deal and company relationships into ClickHouse?

Define an association stream with the from and to objects, grant read scopes on both, then refresh the source schema so the generated stream appears in the catalogue.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want a ClickHouse view using FINAL for correctness-critical queries, which is a short piece of SQL.

Get your HubSpot data into ClickHouse

Agree your share of the account's daily API budget before choosing a schedule, define the association streams you need rather than hoping relationships appear, prune custom properties nobody queries, and put your pipeline totals behind a view that uses FINAL.

Airbyte's connector catalog includes 600+ pre-built connectors, so CRM data can sit beside product and billing sources in the same engine. For the same source into a warehouse, see HubSpot to BigQuery, and for another source landing in ClickHouse, Mixpanel to ClickHouse.

Start syncing now →