HubSpot to BigQuery: how to load CRM data your team can actually query

How do you connect HubSpot to BigQuery?

You connect HubSpot to BigQuery by reading CRM objects through the HubSpot CRM API v3 and loading them into BigQuery tables, because HubSpot has no native BigQuery destination. Something sits in the middle: a managed connector, a scheduled script against the API, or an event stream from webhooks. Each path is governed by HubSpot per-second and daily rate limits per account.

HubSpot exposes CRM objects (contacts, companies, deals, tickets, line items) and engagements through the v3 API. The list endpoints return objects a page at a time using the after cursor from the paging.next token, and you must name the properties you want because the API does not return all of them by default. For incremental work the CRM Search API lets you filter and sort on hs_lastmodifieddate, and a private app can subscribe to webhooks for near-real-time change events. The managed path is the HubSpot to BigQuery integration, which owns the paging, the watermark, the staging table, the MERGE, and the retries.

Extraction method	What it is	When to use
CRM Search API on hs_lastmodifieddate	Filter and sort objects by last modified time, windowed by a date range	Incremental syncs. The default for steady-state replication into BigQuery
List endpoint with after cursor	Sequential paging through all objects of a type via paging.next	The initial backfill, or object types without a useful modified filter
Webhooks via a private app	Push events on object create and change, delivered as they happen	Freshness. Pair with a periodic reconciliation sync to catch missed events

Practically, the connection has three parts. First, a HubSpot private app (or OAuth app) with least-privilege read scopes for the objects you replicate, which issues the token the reader uses. Second, a BigQuery landing spot: a dataset, a service account with load and query rights, and a naming convention for staging versus modeled tables. Third, the transfer itself, which pages each object type, lands each batch, and merges into the target. If your team standardizes on a different warehouse, the sibling path is HubSpot to Snowflake, which follows the same read and merge shape.

How do you load HubSpot data incrementally?

Load incrementally by filtering the CRM Search API on hs_lastmodifieddate, storing the maximum modified timestamp you saw as a watermark, and resuming from that value next run. Deals, contacts, and companies all carry hs_lastmodifieddate, so a search sorted ascending on that field returns exactly the objects that changed, and you advance the watermark to the max value in the batch.

The Search API has tighter limits than the list endpoints and a hard 10,000-record pagination ceiling per query, so you cannot page endlessly through a single search. Window by time instead: query a bounded range (last watermark to now, or fixed hourly windows during a large backfill), and if a window would exceed the ceiling, split it into smaller ranges. Overlap protects correctness. Subtract a few minutes from the watermark before each run, because an object can be committed with a modified timestamp slightly behind another that already loaded, and re-reading a handful of records costs nothing when the load side is idempotent.

For freshness beyond a scheduled poll, subscribe a private app to webhooks so HubSpot pushes object create and change events as they happen. Webhooks are excellent for latency but not for completeness: deliveries can be missed, so you still run a periodic reconciliation sync on the watermark to backfill anything the stream dropped. Treat webhooks as the fast path and the hs_lastmodifieddate sweep as the source of truth.

How do HubSpot properties map to BigQuery types?

Map the HubSpot object id to an INT64 or STRING key, amount and number properties to NUMERIC (never FLOAT for money), datetime properties to TIMESTAMP, date properties to DATE, enumeration and select properties to STRING, and booleans to BOOL. The two that bite are datetime properties, which arrive as epoch milliseconds, and deal amounts, which arrive as strings and must be cast.

HubSpot property / type	BigQuery type	Notes
object id (contact / company / deal)	INT64 or STRING (key)	Stable numeric id. The natural MERGE key so retries update in place
amount (deal)	NUMERIC	Arrives as a string in the API. Cast to NUMERIC, never FLOAT for money
datetime property (hs_lastmodifieddate, createdate)	TIMESTAMP	Epoch milliseconds. Divide by 1000 and store as UTC TIMESTAMP
date property (closedate day)	DATE	Calendar date with no time component. Keep as DATE for clean grouping
enumeration / select (dealstage, pipeline)	STRING	Store the internal value. Join to a label table for readable reports
boolean checkbox	BOOL	Cast the HubSpot value to a real boolean so filters read cleanly

The epoch and string-amount traps are worth repeating because both are correctness bugs, not cosmetics. A datetime property like hs_lastmodifieddate comes back as milliseconds since the Unix epoch, so divide by 1000 and cast to a UTC TIMESTAMP, otherwise your watermark comparisons and date partitions land in the wrong year. Deal amounts come back as JSON strings, so cast them to NUMERIC before you sum them: leaving them as strings breaks aggregation, and casting to FLOAT introduces rounding drift that shows up when finance reconciles a pipeline total.

How do you avoid hitting HubSpot API rate limits?

Avoid HubSpot rate limits by requesting only the properties you need, paging with the after cursor rather than re-querying, fetching only changed records through the watermark instead of full reloads, and windowing Search API calls to respect its tighter budget. When a 429 comes back, back off and retry with exponential delay instead of hammering the endpoint.

The v3 API enforces per-second limits (roughly 100 to 190 requests per 10 seconds depending on your tier and whether you have the API add-on) plus a daily cap, so the goal is fewer, denser calls. A narrow property list is the first lever: since properties are not all returned by default, asking only for the fields you model keeps each response small and each page cheap. The watermark compounds the saving, because a steady-state run touches only the objects that changed since last time, which is a tiny fraction of the CRM.

Treat throttling as expected, not exceptional. Wrap each request in retry logic that recognizes 429 and honors the retry hint, waits, and resumes from the same after cursor. Remember the Search API carries its own tighter limits and the 10,000-record ceiling, so window those calls by time and never lean on deep pagination. The same discipline carries over if you later add our data integration platform for other sources feeding the same warehouse.

How do you model HubSpot associations and avoid duplicate rows in BigQuery?

Model associations as a bridge table and avoid duplicates by MERGE-ing on the object id. HubSpot associations (deal to contact, deal to company) come from the associations API, so store them as a separate table of id pairs rather than flattening them onto the deal row. For each object type, MERGE from staging into the modeled table keyed on the stable numeric id.

The load has a consistent shape. Land each raw batch as newline-delimited JSON in a staging table (or a raw JSON column), then run MERGE target USING staging ON target.id = staging.id so inserts and updates happen in one statement. Idempotence is the whole point: if the pipeline dies mid-run and replays the last window, every matching id updates rather than duplicates, and the target converges to the same state a clean run would reach. That is why the watermark can safely overlap without producing double rows, and why associations belong in their own bridge table keyed on both ids.

Deletes and merges need explicit handling or the warehouse keeps ghosts. HubSpot merges duplicate contacts and can archive records, so poll the archived endpoint (or handle merge events from webhooks) and either soft-delete or hard-delete those ids in BigQuery. Model the association bridge so a deal that moves from one company to another updates cleanly. Once attribution shows which campaigns convert, the natural next step is to audit the landing page copy and layout to lift conversion, closing the loop between CRM data and the pages that generate it.

How much does it cost to load HubSpot into BigQuery?

The cost has two parts: BigQuery, which bills for bytes scanned on query plus storage, and the pipeline that feeds it. BigQuery at HubSpot CRM volumes is inexpensive, because CRM objects are modest compared to event or log data, so storage is a rounding error and query cost depends almost entirely on how much data each query has to scan.

Partitioning is the main cost lever. Partition the fact tables by the modified date (or close date for deals) so a query filtered to a date range scans only the relevant partitions instead of the whole table, and cluster by pipeline or stage so common filters prune further. Get partitioning right and a dashboard that queries the last 90 days of deals scans a fraction of the bytes, which is the difference between a cheap warehouse and a surprising bill. The pipeline is the other line item, and this is where metered per-row billing can bite: a busy sales month means more changed objects, which on a usage-priced connector means a bigger invoice for the exact month you least want one.

On the pipeline side, our pricing is flat by plan (Starter $49, Growth $149, Scale $399, Enterprise custom) rather than metered per row, so a heavy quarter does not produce a spike. See flat pricing for the full breakdown. Keep the tables partitioned and clustered, keep the pipeline plan flat, and the total cost of a HubSpot to BigQuery load stays predictable as your CRM grows.