Sync Hubspot CRM data to BigQuery on a schedule

Keep your analytics warehouse in lockstep with your CRM by replicating HubSpot objects (contacts, companies, deals, tickets, and more) into Google BigQuery every day. This blueprint uses CloudQuery to extract every HubSpot table and load it into a BigQuery dataset, so revenue, marketing, and operations teams query fresh CRM data instead of waiting on manual exports or brittle one-off scripts. It solves the classic problem of CRM data being trapped behind an API: no warehouse, no SQL, no joins with product or billing data.

How it works

1. A schedule trigger (io.kestra.plugin.core.trigger.Schedule) fires the flow daily at 6:00 AM via the cron expression 0 6 * * *.
2. The sync task (io.kestra.plugin.cloudquery.Sync) runs CloudQuery with a source config for HubSpot and a destination config for BigQuery.
3. HubSpot is authenticated with the HUBSPOT_APP_TOKEN environment variable, BigQuery with a GCP service account written to sa.json and referenced through GOOGLE_APPLICATION_CREDENTIALS.
4. The source selects all tables (tables: ["*"]) and throttles to max_requests_per_second: 5 to stay within HubSpot API limits.
5. The BigQuery destination writes to project_id: kestra-prd, dataset_id: hubspot in append mode.

What you get

• A hands-off daily replica of your full HubSpot CRM in BigQuery.
• All HubSpot objects loaded, ready to join with product, billing, or marketing data.
• Built-in API rate limiting so syncs do not trip HubSpot throttles.
• A single declarative file instead of a maintained ELT script or paid connector.

Who it's for

• Data engineers building a warehouse single source of truth.
• Analytics engineers modeling CRM data in dbt or SQL.
• RevOps and marketing teams needing reliable, queryable CRM metrics.

Why orchestrate this with Kestra

CloudQuery syncs data but has no scheduler, retry logic, alerting, or lineage of its own. Kestra adds event and schedule triggers, automatic retries on transient API failures, execution history, and full observability of every run, all defined in declarative YAML and versioned in Git. You can chain this sync into downstream dbt transformations or notifications, something a standalone CLI cannot coordinate.

Prerequisites

• A HubSpot private app token with read access to CRM objects.
• A Google Cloud project with BigQuery enabled and a service account key.
• A target BigQuery dataset (here hubspot in project kestra-prd).

Secrets

• HUBSPOT_API_TOKEN: HubSpot private app token, passed to HUBSPOT_APP_TOKEN.
• GCP_SERVICE_ACCOUNT: GCP service account JSON, written to sa.json.
• CLOUDQUERY_API_KEY: CloudQuery API key for premium plugins.

Quick start

1. Add the three secrets above to your Kestra instance.
2. Update project_id and dataset_id in the BigQuery destination to your own.
3. Adjust the cron value if you need a different sync cadence.
4. Save and trigger the flow, then query the hubspot dataset in BigQuery.

How to extend

• Narrow the tables list to sync only the HubSpot objects you need.
• Switch write_mode from append to overwrite for full refreshes.
• Add a downstream dbt task to model the raw tables after each sync.
• Add a notification task to alert a channel when a sync completes or fails.

Links

• Kestra CloudQuery plugin docs