HubSpot to ClickHouse: Sync Methods, Setup & API Limits Guide

Yes, HubSpot CRM data can be kept in sync with ClickHouse, in four ways: export records to CSV and load them with clickhouse-client, script against HubSpot's CRM APIs, run an open-source ELT tool, or use a managed platform. A CSV export suits a one-off look; anything queried daily needs a tool that polls HubSpot for changes, because HubSpot has no ClickHouse export and ClickPipes don't read SaaS APIs.

HubSpot's API limits decide how often you can sync, and incremental searches omit deleted records, so deletes and merges need a separate reconciliation path. ClickHouse doesn't update rows in place, so a ReplacingMergeTree table keeps the newest version of each record and queries use FINAL. Estuary supports this route with a HubSpot connector that polls changed records continuously and a native ClickHouse materialization that writes on a schedule you set.

Choosing a HubSpot to ClickHouse method

The right method depends on how fresh the CRM data needs to be and how much pipeline code you want to own.

Fivetran is another managed option: its ClickHouse destination, generally available since May 2026, supports ClickHouse Cloud only, and it bills by monthly active rows; ETL tools for ClickHouse compares it with the rest.

How HubSpot API pulls become ClickHouse rows

A sync reads HubSpot on one clock and writes ClickHouse on another:

• Backfill: page through each object's list endpoint, GET /crm/v3/objects/{object}, 100 records a request, associations inline.
• Incremental reads: search for records whose hs_lastmodifieddate (lastmodifieddate on contacts) is later than the cursor, then batch-read them. New edits take a few moments to become searchable, so windows overlap.
• Cursor: each run saves the last timestamp it read, and the next resumes there.
• Writes: each insert creates a new part; in a ReplacingMergeTree table ordered by record ID, background merges later keep the highest version of each record.

Two things need a separate pass: archived (deleted) records are excluded from default list results and all search results, and calculated properties change without moving hs_lastmodifieddate. List archived records with archived=true while they remain available.

HubSpot requirements

You need a credential with read scopes for every object you sync, such as crm.objects.deals.read, plus the matching crm.schemas.*.read scope. Custom-object scopes need Enterprise.

• Marketplace OAuth apps, which most managed tools use, are installed by a Super Admin or a user with App Marketplace access permissions. Each installing account allows them 110 requests per 10 seconds, Search excluded; the API Limit Increase add-on doesn't raise that.
• Legacy private apps (Development → Legacy apps, super admins only) get 100 requests per 10 seconds on Free and Starter, 190 on Professional and Enterprise, and a daily allowance (250,000, 625,000 or 1,000,000) shared by every private app in the account, so agree a share with the HubSpot admin. Each API Limit Increase raises those to 250 and +1,000,000, up to two.
• Searches have their own ceiling: five per second for the whole account and 10,000 results per query.

Every property you request comes back on every record, so prune unused custom properties; a million contacts is 10,000 list calls before associations.

ClickHouse requirements

On the ClickHouse side, plan tables around re-inserted rows.

• Database and user with rights to create, alter, insert, select and truncate. ClickHouse Cloud accepts only TLS (native protocol on 9440, HTTPS on 8443); self-hosted servers also offer plain 9000 and 8123.
• ReplacingMergeTree(version) ordered by the HubSpot record ID; an is_deleted second parameter (ClickHouse 23.2+) lets a tombstone row hide a record.
• Association tables. HubSpot lets one pair of records carry several association labels, so a link table orders by (from_id, to_id, association_type_id).
• A settled sorting key: ClickHouse can only extend ORDER BY with newly added columns.

sql
CREATE TABLE hubspot.contacts
(
    id          Int64,
    synced_at   DateTime64(3, 'UTC'),
    updated_at  DateTime64(3, 'UTC'),
    properties  String,          -- JSON object of property name → value
    companies   Array(Int64),
    is_deleted  UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(synced_at, is_deleted)
ORDER BY id;

Setting up HubSpot to ClickHouse replication

1. List objects, properties and associations analysts need (GET /crm/v3/properties/{object}), and prune unused custom properties.
2. Grant read scopes for exactly those objects to an OAuth app or a private app.
3. Fit the schedule to the API budget, after agreeing a share of the daily limit if you use a private app.
4. Create the ClickHouse tables, versioned by sync time, not updatedAt: a calculated-property refresh leaves updatedAt unchanged, so it would win only through ClickHouse's tie rule (last insert wins), which a re-run of an older batch would also use.
5. Backfill object by object, largest last, watching the account's API usage.
6. Poll incrementally on hs_lastmodifieddate with overlap, refresh calculated properties on a slower schedule, and list archived records (archived=true) to insert tombstones.
7. Validate. Compare SELECT count() FROM hubspot.contacts FINAL with HubSpot's total. After the next sync, a deal whose amount you changed should show the new value under FINAL. Archive a test contact, merge two others, and join deals to contacts through the association table.

What happens after the initial load?

Each sync writes changed records as new rows, and ClickHouse keeps the latest version per record ID once parts merge.

• Updates arrive as a second row for the same ID. SELECT * FROM deals FINAL returns the latest; a plain SELECT may return both, so put FINAL in the views behind pipeline totals.
• Calculated properties (formulas, rollups) don't move hs_lastmodifieddate, so their ClickHouse values age until a dedicated refresh. A refresh of just those properties must be merged into the full record first: ReplacingMergeTree replaces whole rows, so a partial row blanks the rest.
• Deletes send a record to the recycle bin (90 days to restore; permanent and GDPR deletions bypass it). Search and default list responses exclude archived records; a separate list request with archived=true returns them while available, so a pipeline can insert tombstones. Permanent deletions still need reconciliation or a webhook captured before removal.
• Merges create a new record ID by default; the survivor's hs_merged_object_ids lists the absorbed IDs (semicolon-separated), which stay in ClickHouse.
• New properties appear inside a JSON properties column at once; typed columns need ALTER TABLE … ADD COLUMN. New custom objects need their scope added, and an OAuth app re-authorized, before any tool can read them.

HubSpot to ClickHouse data type mapping

Two HubSpot traps matter most: date properties are stored as midnight UTC, and a multiple-checkbox value arrives as one semicolon-separated string.

LowCardinality pays off below about 10,000 distinct values. ClickHouse warns that Nullable almost always costs performance, so give often-blank properties a default where blank means nothing.

Reading HubSpot properties from a JSON column

Kept as JSON text, properties are read with JSONExtractString(properties, 'dealstage'). Convert amounts from the text, toDecimal64OrNull(JSONExtractString(properties, 'amount'), 2), not through Float64, which can lose a cent: ClickHouse's own example has toDecimal64(1.15, 2) return 1.14. Epoch dates convert with fromUnixTimestamp64Milli(toInt64(value)).

Performance, latency and ClickHouse cost

Freshness is set by the sync interval, the API budget and how much each read carries; ClickHouse adds the cost of parts and FINAL.

HubSpot API budget

• Cost per poll: one search per object, then, per hundred changed records, a batch read and an associations read for each related type.
• Calculated-property refreshes page through every record of the object; once a day is usually enough.
• A private-app backfill spends the shared daily limit (reset at midnight, account time); its 429s name the DAILY policy. OAuth responses carry no daily-remaining headers, so watch API call usage in HubSpot.
• Search stops at 10,000 results per query (a 400 past that), so busy windows must be split by time.

Insert size, parts and FINAL

Every insert writes a part that merges must absorb, and ClickHouse asks for batches of at least 1,000 rows (ideally 10,000 to 100,000) at about one insert query per second, or inserts fail with Too many parts. Buffer polled records. FINAL deduplicates at query time in memory, a small cost on CRM-sized tables. On ClickHouse Cloud, idling pauses an unqueried service, and connections can time out while it resumes.

Production considerations

• Retries. Back off on HTTP 429; re-inserted records collapse by ID, so retries are harmless.
• Source impact. HubSpot asks integrations to keep errors under 5% of daily requests. Spread large backfills across days.
• Monitoring. Alert on 429 rate, HubSpot's API call usage, time since the last ClickHouse insert, and active parts in system.parts.
• Security. Read-only HubSpot scopes, a ClickHouse user limited to one database, and TLS.

Common problems and troubleshooting

Duplicate HubSpot records in ClickHouse query results

The same deal appears twice with different amounts:

sql
SELECT id, count() AS versions
FROM hubspot.deals
GROUP BY id
HAVING versions > 1
ORDER BY versions DESC
LIMIT 10;

Unmerged versions, which ClickHouse never promises to merge by any particular time: add FINAL, or aggregate with argMax(column, synced_at). If FINAL still returns two rows per ID, the ORDER BY includes more than the record ID (an updated_at column, say), so versions never collide; rebuild the table.

Deleted and merged HubSpot records still counted

A count() of contacts in ClickHouse, even with FINAL, is higher than HubSpot's total. Merged-away IDs can be found from the survivors:

sql
SELECT DISTINCT toInt64(absorbed) AS absorbed_id
FROM hubspot.contacts FINAL
ARRAY JOIN splitByChar(';', JSONExtractString(properties, 'hs_merged_object_ids')) AS absorbed
WHERE absorbed != '';

Fixes, cheapest first: filter those IDs in a view; insert tombstones (is_deleted = 1) for them and for IDs listed by archived=true; or reload the table, the only certain fix for permanent deletions.

Rows full of NULLs after a calculated-property refresh

Deals suddenly show only their ID and a few calculated properties. The refresh wrote partial records straight into a ReplacingMergeTree table, so each partial row replaced the full one. Merge refreshes into the stored record before writing, then reload the affected table.

Using Estuary for HubSpot to ClickHouse

For this route, Estuary pairs its HubSpot capture with the ClickHouse materialization, which writes to ClickHouse Cloud or a self-hosted server over the native protocol. Each HubSpot object becomes a collection, then a table keyed on record ID. Against the concepts above:

• Authorization: OAuth, read-only, even where HubSpot's consent screen groups read and write permissions. Custom objects are discovered as custom_<name>.
• Polling: each CRM object runs a fast pass over HubSpot's recent-changes endpoints, back to back, and a delayed pass an hour behind on the Search API that catches what the fast pass missed, splitting windows over 10,000 results by time. A restarted capture resumes from its saved cursors.
• API budget: Estuary's app is Marketplace-listed, so each installing account allows it 110 requests per 10 seconds. On a 429 the connector widens the gap between requests, up to 5 minutes, and retries.
• Versioning and schedule: tables use ReplacingMergeTree(flow_published_at) ordered by record ID, so the newest write wins whether or not updatedAt moved. They update every 30 minutes by default, one row per record per sync.
• Deletes: HubSpot's CRM endpoints give the connector nothing to delete, so archived and merged-away records keep their last row even with Hard Delete on; only lists re-read whole (owners, deal pipelines, properties, forms) produce deletes.
• Calculated properties: a daily re-read (23:55 UTC by default) refreshes them; standard (not delta-update) bindings merge each partial document into the stored record before writing.
• Associations and properties: associations arrive as arrays of record IDs, without labels or type IDs (first page per record only); properties is one object. Both land in String columns holding JSON; multiple-checkbox values stay semicolon-separated.
• Types: at Depth 2 each property gets a column, and decimal numbers become Float64; castToString on amount fields keeps the exact text for toDecimal64(amount, 2) in a view.
• Storage: standard tables also keep the whole JSON document in flow_document; Exclude Flow Document (Advanced Options) drops it.
• Compliance: SOC 2 Type II, HIPAA and GDPR, per Estuary's security page.

Implementing HubSpot to ClickHouse with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in HubSpot requirements and ClickHouse requirements. Estuary also needs:
   • A HubSpot user with Super Admin or App Marketplace access to authorize Estuary's OAuth app; the dashboard offers no token option.
   • ClickHouse 25.3 or later, and a user with the connector's grants, including SELECT on system.columns, system.parts and system.tables.
   • Network access from Estuary's IP addresses to the server's native port; the connector has no SSH tunnel option.
2. Create the HubSpot capture. On the Sources page, click New Capture, choose Hubspot Real-time (not HubSpot (deprecated)), name it, and fill in the endpoint properties:
   • Authentication: click the authenticate button and approve the consent screen. Eight read scopes are required; optional ones such as tickets and crm.objects.custom.read decide which objects are discovered.
   • Capture Property History: off by default; it adds propertiesWithHistory and halves the records fetched per request.

3. Select objects. Click Next. Output Collections lists one binding per object the scopes allow, from CRM objects to marketing emails, forms and workflows.
   • Remove objects you don't need; each spends API calls.
   • Interval is zero on CRM objects (continuous polling); lengthen it per binding to spend less.
   • Calculated Property Refresh Schedule defaults to 55 23 * * *.
   • Keep Automatically keep schemas up to date on.

Click Save and publish. Backfills start, with polling alongside.

4. Create the ClickHouse materialization. On the capture's details page, click Materialize, choose ClickHouse, and fill in the endpoint properties:
   • Address: host:port. Without a port, the connector uses 9440 (TLS) or 9000 when SSL Mode is disable.
   • Database: the target database.
   • Authentication: Username and Password.
   • Hard Delete: affects only the full-refresh lists. Left off, their removed rows stay, marked d in _meta/op, and FINAL still returns them, so filter on that column.

5. Choose the sync schedule.Sync Frequency defaults to 30 minutes; 0s writes as each transaction completes, adding more parts to merge. Timezone, Fast Sync Start Time and Fast Sync Stop Time limit frequent writes to working hours; outside them, syncs run every 4 hours.

6. Shape each table. Click Next, choose a naming convention, and review each binding under Source Collections:
   • Table: the ClickHouse table name.
   • Field Depth: Depth 1 keeps properties in one JSON column; Depth 2 adds a column per property (field selection).
   • Delta Update: leave it off for CRM objects, so calculated-property refreshes merge into full rows; turn it on only for append-only data such as email_events, which then lands in a plain MergeTree table.
7. Publish. Click Save and publish. Estuary creates one ReplacingMergeTree table per object and writes the backfill without waiting for the schedule.
8. Verify that data is landing.
   • The capture details page shows PRIMARY in its Shard Information card; a collection from its Collections list shows records in Data Preview.
   • The materialization details page charts Data Read.
   • In ClickHouse, run SELECT id, JSONExtractString(properties, 'dealstage') FROM deals FINAL LIMIT 10, then the checks in Setting up HubSpot to ClickHouse replication.

How Estuary handles API budget, ClickHouse rebuilds and extra destinations

Every HubSpot object is written once to a collection: JSON files in cloud storage, either your own bucket or Estuary's trial storage. The ClickHouse materialization reads the collections, not HubSpot, so a backfill that rebuilds a table spends no API calls.

Re-creating a ClickHouse table without polling HubSpot

How far back a rebuild can go is set by where collections live: roughly 20 days on Estuary's trial storage, full history in a bucket you own. A reset costs a full HubSpot read, and the truncated tables stay partial until it completes.

ClickHouse and Snowflake from one HubSpot read

A second materialization on the same collections, Snowflake for finance, say, reads their history and follows new documents while HubSpot is polled once. Curri runs HubSpot among 16+ sources through Estuary into Snowflake, not ClickHouse, and its success story reports a "50%+ cost reduction vs. Fivetran".

When another approach may make more sense

• A one-time analysis: export current values to CSV and INSERT the file; HubSpot's export caps association columns at 1,000 related IDs by default.
• Airbyte already runs your pipelines: add its ClickHouse destination, which deduplicates through ReplacingMergeTree and expects FINAL in queries.
• Standard CRM reporting only: HubSpot's own reports may be enough, and a pipeline adds nothing.
• ClickPipes-only ingestion on ClickHouse Cloud: point a ClickPipe at Estuary's ClickHouse Kafka API connector (Dekaf), which publishes the HubSpot collections as Kafka topics.

Related resources

• Estuary: HubSpot capture connector, ClickHouse materialization, ClickHouse via Dekaf and ClickPipes, sync schedules, backfills and dataflow resets, HubSpot connector, ClickHouse destination
• HubSpot: API usage guidelines, CRM search, associations, restoring deleted records
• ClickHouse: ReplacingMergeTree, insert strategy, JSON functions

To try the route, see the HubSpot to ClickHouse integration or create a HubSpot capture in the Estuary dashboard. Pricing: estuary.dev/pricing.