PipeloomHubSpot to BigQuery
Load HubSpot contacts, companies, deals and engagements into BigQuery for marketing and revenue analysis.
The pipeline as it appears on the Pipeloom canvas. You can add transforms or triggers on the same canvas later.
What to know about HubSpot into BigQuery
Lookback for the property history streams
HubSpot calculated properties always show the latest sync time, so the property_history streams can skip records. Set Property History Lookback Window (43200 is 30 days) and rely on Append + Deduped to remove the overlap in BigQuery.
Wide CRM objects and JSON columns
HubSpot objects can have hundreds of properties. They land as typed BigQuery columns, and nested values land as JSON, so select only the columns you need and filter on _airbyte_extracted_at to limit bytes scanned.
Rate limits and BigQuery quotas are separate
HubSpot limits the source side (100 to 190 requests per 10 seconds by tier). BigQuery limits the destination side with a project-wide concurrent-query quota. If a sync reports the quota error, set a separate Job Execution Project ID.
Memberships are full refresh
The list_memberships stream supports only full refresh, keyed by recordId and listId. Sync it on a slower schedule than contacts and deals.
Set it up
- Create the HubSpot sourceAuthenticate with A Private App access token or a Service Key. You need: A HubSpot account. A Private App (or Service Key) with the scopes for the streams you want, for example CRM read scopes for contacts, companies and deals.
- Create the BigQuery destinationAuthenticate with A Google Cloud service account with the BigQuery User and BigQuery Data Editor roles, using a JSON key. You need: A Google Cloud project with BigQuery turned on, and a dataset to write to. Create it in the same location as the datasets you will join against. A service account with the BigQuery User and BigQuery Data Editor roles. For production loads, a Cloud Storage bucket for staging with an HMAC key, and the Storage Object Admin role for the service account.
- Connect them and pick streamsDraw the edge on the canvas and choose streams and sync modes. HubSpot supports full refresh and incremental; BigQuery supports every mode: full refresh overwrite, append and overwrite deduped, incremental append and incremental append deduped.
- Run, then scheduleThe first sync loads each stream in full. Then set the schedule.
What lands in BigQuery
- A table per stream with typed columns plus _airbyte_raw_id, _airbyte_generation_id, _airbyte_extracted_at and _airbyte_meta.
- Tables partitioned by day on _airbyte_extracted_at and clustered by that column and the primary key, so filtering on the partition column scans less data.
Type mapping
- object and array → JSON
- string → STRING
- integer → INT64
- number → NUMERIC
- timestamp with time zone → TIMESTAMP
- timestamp without time zone → DATETIME
- boolean → BOOL
When HubSpot's schema changes
The destination tables are updated as the source changes. Namespaces map to BigQuery datasets, and invalid characters in names are replaced with underscores.
Streams you can sync
Includes Contacts, Companies, Deals, Deal Pipelines, Contact Lists, Engagements, Tickets, Forms and more. The full list is in the reference.
What it costs
A sync is metered at 1 credit per vCPU-minute, and a default sync uses 2.5 vCPUs. As an example, not a benchmark, a sync that takes 5 minutes uses 13 credits. Run 24 times a day, that is about 9,360 credits a month, more than Free's 1,500 but within Starter's 12,000 ($19 a month). Your own sync time decides the real figure, so try the calculator with your numbers.
Questions and fixes
Why is my HubSpot sync slow?
Custom properties make syncs longer, and the connector shares HubSpot's rate limits. Burst limits are 100 requests per 10 seconds on Free and Starter and 190 on Professional and Enterprise, with daily limits per account from 250,000 to 1,000,000.
Why is the engagements stream slow after a gap?
If the last sync was within 30 days and fewer than 10,000 records are new, the connector uses HubSpot's recent-engagements API. Otherwise it reads all engagements, which is much slower. Syncing more often keeps it on the fast path.
Why are records missing from the property history streams?
HubSpot calculated properties (formulas, rollups and hs_analytics_* fields) carry the time of the latest sync, which can move the cursor past unsynced records. Set Property History Lookback Window in the source, for example 43200 (30 days). Those streams use Append + Deduped, so the overlap does not create duplicates.
Why are custom objects or a stream like workflows missing?
Custom objects need the crm.objects.custom.read scope, then Refresh source schema on the connection. Streams such as workflows are skipped, with a warning in the logs, when the app lacks their scope.
Why does a sync fail with "Quota exceeded for concurrent script queries per project"?
That quota is shared with everything else in the project. Set Job Execution Project ID to a separate project so the connector's jobs count against that quota, or sync fewer streams at once. Data still lands in the dataset under Project ID.
Why does a load fail with "Fail to complete a load job in big query"?
BigQuery load jobs time out after 30 minutes of waiting, and two syncs loading into the same table at once are not supported. Make sure each table is written by one sync, and use an incremental mode to load less per sync.
Should I use the Cloud Storage bucket or standard inserts?
Use the bucket for production because it is faster for large volumes. Standard inserts need nothing to stage and suit small volumes and quick tests. Buckets using customer-managed encryption keys are not supported.
Sync HubSpot to BigQuery today
Free forever, no card. Starter is $19 a month for three seats.