How to Integrate HubSpot Data into BigQuery: Top Methods and Best Practices

Pulling campaign and customer data from HubSpot via its APIs or built-in tools can work well for basic reporting, but when it comes to large-scale, advanced analytics, HubSpot’s native capabilities often fall short.

This is where Google BigQuery comes in: a fully managed data warehouse that handles massive datasets, enables scalable querying, provides deep insights powered by ML, and supports high-speed analysis —features not found in HubSpot’s native stack. 

But here’s the challenge: getting HubSpot data into BigQuery. Due to the complexities of the API and difficulties in setting up the environment, the data integration process can be time-consuming and prone to errors.

In this blog post, we will cover the most effective ways to connect HubSpot to BigQuery, whether through raw APIs, manual exports, or streamlined third-party ELT/ETL solutions like Windsor.ai. 

Let’s get started.

What is HubSpot?

HubSpot is a cloud-based business platform that helps organizations manage various types of data across sales, marketing, support, and other areas. 

The platform is built around five core hubs—Marketing, Sales, Service, CMS, and Operations—each with its own data structures, reporting limitations, and integration challenges. These silos often complicate performance analytics, especially when trying to unify insights across multiple hubs.

What is BigQuery?

BigQuery is a serverless, fully managed data warehouse owned by Google. A critical aspect of BigQuery is that it’s built on Dremel technology, which transforms SQL queries into highly efficient execution trees, allowing for lightning-fast processing of massive datasets. Moreover, BigQuery supports columnar storage, significantly enhancing analytical query performance.

Here are the main components and features of BigQuery:

1. BigQuery GIS enables geospatial analysis by integrating mapping and location-based data directly into queries.
2. BigQuery BI Engine empowers the running of large queries within seconds through an in-memory analysis service. 
3. BigQuery’s ML enables predictive analytics and other advanced machine learning operations directly within the BigQuery environment through simple SQL syntax.
4. BigQuery Data Transfer automates the data stream from other Google services and third-party tools to the database on a scheduled basis. 

Why move data from HubSpot to BigQuery?

You might want to move data from HubSpot to BigQuery due to these reasons and benefits:

• Centralize all your HubSpot data in BigQuery to ensure consistent, unified reporting across teams, leading to improved decision-making. 
• Conduct a large-scale analysis on your sales and marketing data, which is not possible with HubSpot’s built-in analytics. 
• Leverage BigQuery’s enterprise-grade features like fine-grained access controls, audit logging, and built-in encryption to keep your data secure and compliant.
• Blend HubSpot data with other sources and Google tools, gaining deeper insights into your business performance.

Types of HubSpot data you can move to BigQuery

When syncing HubSpot with BigQuery, you can export a wide range of metrics and dimensions, including the following:

• Activity data (clicks, views, opens, URL redirects, etc.)
• Contact lists
• Calls-to-action (CTA) 
• CRM data
• Form submission data
• Customer feedback
• Email marketing analytics
• Sales pipelines 
• Revenues

Things to consider before integrating HubSpot data into BigQuery

Here are a few essential considerations and requirements that you must know about when getting started with the HubSpot to BigQuery data integration:

1. You should be familiar with HubSpot’s RESTful API, which delivers data in JSON format. 
2. Make sure that you use OAuth authentication instead of API keys. It’s considered safer than the latter. 
3. Keep in mind the API rate limits:
   • Maximum 10 API requests per second. 
   • 10,000 requests per day. These limits can lead to throttling. 
4. Explore the API endpoints that HubSpot offers for various types of data. For example, contacts, deals, companies, emails, workflows, and more. It’s essential for rich data extraction. 
5. Make sure that you are preparing data in a format that BigQuery supports. These are JSON and CSV. 
6. The data types should also be compatible with BigQuery. These include STRING, INTEGER, FLOAT, BOOLEAN, RECORD, and TIMESTAMP. 
7. You can load data into BigQuery via Google Cloud Storage or by direct POST requests using CURL or SDKs for popular programming languages.
8. If you’re building your own data pipeline, make sure you:
   • Stick to API usage rules
   • Properly manage OAuth tokens, error handling, and retries.

4 methods to send data from HubSpot to BigQuery

Moving data from HubSpot to BigQuery is not a tricky task—if you pick the right method. The best option depends on your specific needs and expertise. 

Here are four common ways to make it happen:

Method 1. Native Hubspot to BigQuery integration (Beta)

Method 2. Using HubSpot Private App for moving data to BigQuery

Method 3. Manual export and loading through CSV files

Method 4. Using ETL/ELT tools for automated Hubspot to BigQuery data integration

Method 1. Native Hubspot to BigQuery integration (Beta)

Method overview:

• Prerequisites: HubSpot account; Google BigQuery project.
• Tools used: Built-in connector from the HubSpot App Marketplace; BigQuery.
• Type of solution: No-code/low-code 
• Mechanism: Uses HubSpot’s integration layer and REST APIs to send CRM data incrementally to BigQuery. OAuth 2.0 handles secure authorization, and Google Cloud IAM roles control access to the BigQuery dataset.
• Best suited for: Users with modest technical skills who work with small datasets and simple dashboards.
• Limitations: Still in beta; requires manual IAM configuration in Google Cloud; not easily customized; ineffective with complex, large datasets due to API call limits; little error detection without real-time monitoring.

HubSpot offers a built-in connector available via the HubSpot App Marketplace to send your CRM data to BigQuery. It utilizes HubSpot’s REST APIs to retrieve data and Google’s APIs to load it into BigQuery.

Setting up this integration requires granting specific IAM permissions in Google Cloud (e.g., creating a custom role and assigning it to the integration’s service account). This step is vital to allow HubSpot to stream data to your BigQuery project.

This method is helpful for basic data retrieval and allows for incremental updates, meaning fewer complete data reloads are required.

If your datasets are small and your needs are straightforward, setting up the native method is easy and requires minimal ongoing work. 

Still, there are some downsides: 

• This method is still in beta; thus, it might be unstable and prone to errors
• Requires you to manually assign IAM roles and permissions in Google Cloud
• Limited customization
• Not ideal for large or complex datasets
• API rate limits can become an issue
• Basic error tracking, and it’s not in real-time

For more complex workflows or larger projects, using third-party ETL tools or building custom pipelines may be a better choice. 

Method 2. Using HubSpot Private App for moving data to BigQuery

Method overview:

• Prerequisites: HubSpot account with Private App creation permissions; installed Google Cloud SDK (required for using bq CLI commands); Google BigQuery project.
• Tools used: HubSpot Private App integration with OAuth 2.0; BigQuery CLI/API; Node.js (or another scripting language); cron or job scheduler for automation.
• Type of solution: Pre-built custom API integration with automation scripts.
• Mechanism: Set up a HubSpot Private App to safely get tokens needed to make authorized calls to fetch CRM data. Fill existing datasets in BigQuery by loading JSON files through the CLI, then automate syncing with scheduled scripts.
• Best suited for: Technical teams looking for secure, automated, and customizable data pipelines.
• Limitations: Requires programming knowledge for script writing and maintenance; complicated initial setup; custom error detection must be adapted for each failure.

A proven way of moving information from HubSpot to BigQuery is through the use of a HubSpot Private App.

This method uses OAuth 2.0 security and scoped permissions alongside automation techniques to establish a reliable pipeline for syncing CRM data to BigQuery. However, this approach requires extensive technical expertise, including basic scripting and familiarity with Google Cloud tools, to cover the following integration steps:

1. Generating a Private App

Go to HubSpot’s settings -> Integrations  -> Private Apps. Create a new application. 

Provide app information, logos, and set up scopes (e.g., crm.objects.contacts.read, crm.schemas.contacts.read) by checking necessary data and enabling read or write access. After creation, HubSpot will provide an access token that you need to copy for future use when authenticating API requests.

2. Creating tokens

Open the app’s details page and view the token. You can make API calls using this token.

3. Make API calls

Now, you have to use the token to get data. Here’s an example of how to fetch all contacts using Node.js and axios:

const axios = require('axios');
axios.get('https://api.hubapi.com/crm/v3/objects/contacts', {
  headers: {
    'Authorization': 'Bearer YOUR_ACCESS_TOKEN',
    'Content-Type': 'application/json'
  }
})
.then(response => {
  const contacts = response.data.results;
  // Save to file or stream to processing pipeline
  console.log(contacts);
})
.catch(error => {
  console.error('Error fetching HubSpot contacts:', error.response?.data || error.message);
});

4. Set up a BigQuery dataset and table

To proceed with the next steps, make sure that you have Google Cloud SDK installed on your local machine (for using gcloud and bq commands).

• You can also use the Cloud Shell, an online terminal where gcloud is already set up—no installation required.
• If you install the SDK locally, remember to initialize it to authorize your Google Cloud credentials.

With Google Cloud SDK installed, use the bq CLI to create the target table:

To create the BigQuery dataset and table, first make sure the dataset exists:

bq --location=US mk --dataset your_project_id:your_dataset

Then, create the table using:

bq mk \
--table \
--expiration 86400 \
--description "Contacts table" \
your_dataset.your_table \
name:STRING,email:STRING,createdAt:TIMESTAMP

Replace your_dataset and your_table with your actual dataset and table names. The expiration flag is optional — it sets the table lifetime in seconds.

You can define the schema manually or generate it dynamically based on your JSON keys.

5. Load data into BigQuery

Load rows of JSON data using the defined schema with:

bq load \
  --source_format=NEWLINE_DELIMITED_JSON \
  --schema=./contacts_schema.json \
  hs_data.hs_contacts_table \
  ./contacts_data.json

Make sure your JSON schema file (contacts_schema.json) matches BigQuery format.

6. Automate data transfer

Schedule the scripts to run with cron for periodic syncing:

Example cron job to run this daily at 6 PM:

0 18 * * * /path/to/sync_contacts.sh >> /var/log/hs_sync.log 2>&1

Method 3. Manual export and loading through CSV files

Method overview:

• Prerequisites: HubSpot account; access to the Google Cloud Console or the BigQuery API.
• Used tools: HubSpot CSV Export; Google Cloud Console or BigQuery API.
• Type: Manual export-import workflow/low-code.
• Mechanism: CRM data is exported from HubSpot in CSV format and uploaded to the BigQuery Google Cloud Console or API. This process should be repeated for data updates.
• Best suited for: One-time transfers, smaller datasets, and low-budget situations requiring no automation.
• Limitations: All updates should be covered manually; it is hard to manage and scale; it involves many manual steps.

If you prefer not to use automation or third-party tools, you can manually export data from HubSpot and import it into BigQuery using CSV files. This is the most budget-friendly option, but also the most manual and time-consuming, involving the following steps:

1. Export data from HubSpot

Go to your HubSpot dashboard -> Navigate to the required dataset (e.g., Contacts, Deals, Emails) -> Select the “Export” option -> Choose CSV as the format and save the exported file locally (e.g., hubspot_contacts.csv).

2. Prepare your BigQuery table

Before loading the CSV file into BigQuery, you have to prepare a dataset and an empty table that matches the CSV schema.

Example using bq CLI:

bq mk --dataset my_dataset

Create a table manually using the BigQuery interface or define a schema file and run:

bq mk --table \
  --description "HubSpot contacts data" \
  my_dataset.contacts \
  name:STRING,email:STRING,created_at:TIMESTAMP

You can also use a schema JSON file (schema.json) to define fields.

3. Import CSV data into BigQuery

• You can upload the file directly from your local machine:

bq load --source_format=CSV \
  --skip_leading_rows=1 \
  my_dataset.contacts \
  ./hubspot_contacts.csv \
  ./schema.json

• Another option is to upload the file to Cloud Storage first:

gsutil cp ./hubspot_contacts.csv gs://your-bucket-name/
And then load it from GCS to BigQuery:
bq load --source_format=CSV \
  --skip_leading_rows=1 \
  my_dataset.contacts \
  gs://your-bucket-name/hubspot_contacts.csv \
  ./schema.json

Important: BigQuery accepts files in both CSV and JSON formats, so ensure your data is well-organized beforehand.

This method gives you:

• No extra cost, and no complicated setup problems.
• Very easy with smaller datasets or one-time jobs.

But, you may face the following challenges:

• This process takes a lot of time because you have to export and upload manually.
• You will have to re-export and reload data whenever you need to update manually.
• The chance of errors or missing data in the dataset is higher because of the manual setup and constant manual updates.
• You can complete the process entirely through BigQuery’s interface, though for extra flexibility and repeatability, you’ll need to use the command-line interface (CLI) or scripts, which adds technical complexity.
• Your file slows down your workflow and makes it harder to manage.

Method 4. Using ETL/ELT tools for automated Hubspot to BigQuery data integration

Method overview:

• Prerequisites: Active HubSpot account; BigQuery project; ETL/ELT tool access.
• Used tools: Windsor.ai or other platforms that offer data integration services for HubSpot and BigQuery.
• Type: Fully automated, no-code ETL/ELT pipeline solution.
• Best suited for: Both technical and non-technical teams seeking a hands-off setup with maximum scalability, customization, and automated syncing.
• Limitations: Subscription fees; reliance on these platforms.

While the aforementioned methods of syncing HubSpot data with BigQuery can be time-consuming and require technical expertise, using third-party ETL (extract, transform, load) or ELT tools makes the process much faster, easier, and fully managed.

These tools automate the entire process, provide error monitoring and logging, and support easy scaling as your data volume grows.

Why use ELT/ETL tools:

• These tools build complex data pipelines for you, eliminating manual data exports and uploads.
• Set up automated data syncing on your preferred schedule—no more manual updates.
• Custom data structures and hierarchies can be easily defined and mapped.
• Get monitoring dashboards and real-time error alerts.
• Avoid human error or the burden of writing scripts or cron jobs.
• Prepare for growth with easy scaling as your data expands.

Using Windsor.ai’s no-code ELT/ETL connectors, you can automatically import your HubSpot data into BigQuery in minutes without programming skills. 

Here’s how Windsor.ai handles the integration between HubSpot and BigQuery:

✅ Instantly accesses HubSpot’s REST APIs, pulling info such as contacts, deals, engagement, and hundreds of other HubSpot metrics and dimensions, using OAuth for secure access. 

✅ Automatically converts data into BigQuery-friendly formats, such as JSON and Avro. 

✅ Supports nested structures and custom fields. 

✅ Auto-refreshes data in BigQuery tables according to the set schedule.

Below is an overview of the benefits and steps for integrating HubSpot data into BigQuery using Windsor.ai. 

Benefits of using Windsor.ai over native connectors

While HubSpot’s native connectors offer basic integration, Windsor.ai brings enhanced flexibility, scalability, and analytics-ready data pipelines that are ideal for advanced use cases. Here’s what Windsor.ai can bring to the table:

1. Fully automated integration

When using the native connectors, you need to set up a lot of things manually. This becomes especially challenging as your data starts scaling. 

Windsor.ai automates data integration, completely eliminating the manual workflow. You set the connection just once. Then, the system takes over the data transfer and updates, automatically extracting, transforming, and loading the latest data into BigQuery. You no longer have to worry about constant manual exports and updates.

2. Fast and easy setup

It’s important to note that using native connectors can be a slow process. The reason behind this is that you need to handle technical details, permissions, and API keys. 

Windsor can set up things for you in under 5 minutes without complex configurations. 

3. Auto-refreshing 

Native connectors do not automatically update data. You need to manually refresh data after every few days to keep your database up to date. As a result, it can lead to analysis delays. 

Windsor.ai overcomes this barrier by automatically refreshing data at your specified schedule (daily, hourly, or custom). It ensures that your BigQuery reports are always fresh. 

Additionally, you don’t have to worry about data inconsistencies that often occur with manual refreshing. 

4. Custom transformations with no code

When it comes to native connectors, in-built customization is simply not enough. Shaping data requires everything from cleaning raw data to performing various other tasks that involve a lot of manual work once the sync is done. 

In Windsor.ai, you can perform custom transformations without having to touch a coding interface, enabling advanced analytics even for non-techies.

5. Error handling and monitoring

The error-handling capabilities of native connectors are built on very weak foundations. This often means that many things can go wrong, and you wouldn’t have a clue as to why. 

Windsor provides robust embedded error handling. Any issues that arise, whether on the HubSpot or BigQuery side, are immediately brought to your attention, allowing you to make timely fixes and ensure smooth operation.

6. Scalability and reliability

As your business expands, so does your data. Scaling natively connected systems often encounters issues with huge datasets or those that require frequent updates. 

For Windsor.ai, scalability is not the issue. It grows alongside your business and keeps your HubSpot data in BigQuery up-to-date, fresh, and reliable, regardless of its size.

Step-by-step guide: How to use Windsor.ai to connect Hubspot to BigQuery 

If you’re wondering whether it’s really possible to integrate HubSpot data into BigQuery with Windsor.ai in under 5 minutes, this guide will show you exactly how to do it, step by step.

Windsor.ai’s BigQuery Integration Documentation: https://windsor.ai/documentation/how-to-integrate-data-into-google-bigquery/.

1. Sign up for Windsor.ai and start your 30-day free trial.

2. In the data sources list, select HubSpot and connect your account(s):

3. The connection is completed. View the list of your available HubSpot account(s) and select the necessary one(s) from which you want to pull data:

4. Click on the “Next” button to proceed with moving HubSpot data to BigQuery.

5. Next, configure your dataset by selecting the desired date range and specific fields you want to stream into BigQuery. Click on Get Data to preview your extracted data in Windsor.

6. Scroll down to the Data Destinations section and choose BigQuery from these options:

7. Click on the Add Destination Task and authorize your BigQuery account. In the destination task, add the following details from your BigQuery project:

• Task Name: Enter any name you wish.
• Project ID: This can be found in your Google Cloud Console.
• Dataset ID: This can be found in your BigQuery project.
• Table Name: Windsor.ai will create this table for you if it doesn’t exist.
• Backfill: You can backfill historical data when setting up the task (available only on the paid plans).
• Schedule: Define how often data should be updated in BigQuery (e.g., hourly, daily; available on Standard plans and above).

Keep in mind that if you add new fields or edit the query after the table was initially created, you must manually edit your schema and add or edit those fields in your BigQuery table. Alternatively, you can create a new destination task, and Windsor.ai will auto-create a new table with full schema.

While Windsor.ai auto-creates the table schema on first setup, it does not automatically update the schema when new fields are added or edited later. 

8. Select advanced options (optional).

Windsor.ai supports clustering and partitioning for BigQuery tables to help you improve query performance and reduce costs by optimizing how data is stored and retrieved.

• Partitioning: Segments data only by date ranges (either by date column or by ingestion time, e.g., a separate partition for each month of data). Learn more about partitioned tables.
• Clustering: Segments data by column values (e.g., by source, account, campaign). Learn more about clustered tables.

You can combine table clustering with table partitioning to achieve finely-grained sorting for further query optimization.

9. When completed, click “Test connection.” If the connection is set properly, you’ll see a success message at the bottom; otherwise, an error message will appear.

When successful, click “Save” to run the added destination task to BigQuery. 

• See the task running in the selected data destination section. The green ‘upload‘ button with the status ‘ok‘ indicates that the task is active and running successfully.

10. Now, when you open your specified BigQuery project, you should see your HubSpot data uploaded there:

That’s it: a quick and completely no-code HubSpot to BigQuery integration method via Windsor.ai.

Best practices for integrating HubSpot data into BigQuery 

If you’re integrating HubSpot data into a BigQuery account using Windsor.ai, following these integration best practices will ensure your success.

• First, list which specific HubSpot objects and fields you need. This prevents you from moving irrelevant data, hence leading to better performance. 
• Remember to plan your data sync schedules carefully. With Windsor.ai, you can use incremental syncs to update only new or changed records. This reduces system load and helps minimize costs.
• While full data reloads are possible, avoid doing this unless critically needed. Since you can do incremental updates with Windsor.ai, you will have an easier time doing this.
• Make sure that the data types in HubSpot and BigQuery match: confirm that dates go to dates, numbers to numbers, and text fields go to text fields. This practice ensures that your data remains clean and reliable and prevents data discrepancies.
• Besides integrating data, Windsor gives you the option to check logs regularly. Taking a proactive approach provides you with error reports that you can quickly fix to prevent any data integration issues.

Summary: Windsor.ai vs other integration methods 

Conclusion

Integrating HubSpot data with BigQuery is a powerful way to generate insights and drive smarter business decisions. Unlike native connectors or manual methods, which can be time-consuming, complex, and lack automation and customization, Windsor.ai offers a streamlined, fully automated ETL/ELT solution. It handles data extraction and loading for you, enabling faster setup, scheduled data refreshes, and robust error monitoring.

Try Windsor.ai for free today: https://onboard.windsor.ai/ — and unlock the full potential of your HubSpot to BigQuery integration.

FAQs 

How do I connect HubSpot to BigQuery?

Identify the method that fits you best based on your project requirements. For simple syncing, you can use HubSpot’s built-in integrations or export data through CSV files. For large datasets and fully automated workflows, you can rely on the ETL/ELT tools like Windsor.ai.

How do I connect data to BigQuery?

BigQuery supports data uploads from many sources. Data can be uploaded through BigQuery’s web UI, command-line scripts, or automatically through third-party connectors and ETL pipelines.

Can I transfer HubSpot data to BigQuery in real-time? 

Yes. By using Windsor.ai software, you can perform real-time data transfer from HubSpot to BigQuery. 

What is the best tool for HubSpot to BigQuery integration?

Windsor.ai is one of the top tools for integrating HubSpot with BigQuery. It allows you to set up the connection in under 5 minutes, with no coding required. The platform supports automated data syncing, incremental updates, custom field mapping, and real-time error monitoring, making it ideal for both technical and non-technical users.

How does Windsor.ai ensure secure data integration?

Windsor utilizes encryption for data during transmission and storage. It uses secure OAuth for verifying account connections and limits permission scopes according to business needs. This stops unauthorized access and protects sensitive company information.

Why should I choose Windsor to connect HubSpot to BigQuery?

Windsor features a user-friendly no-code interface, robust data transformation options, and support for incremental updates, eliminating the need for manual work. And budget-wise, compared to other popular ETL/ELT solutions, Windsor.ai’s pricing plans for data integration into BigQuery are significantly more affordable, starting at just $19/month.