How to integrate data into Google BigQuery with Windsor.ai

BigQuery is Google’s fully managed, scalable data warehouse, enabling you to analyze large datasets efficiently using SQL. Its serverless architecture lets you focus on data analysis without worrying about managing infrastructure.

Windsor.ai automates the process of integrating data from various sources into BigQuery. Using our data connectors, you can automate reporting and ensure your data is always up-to-date and ready for in-depth analysis.

Explore our video tutorial and a step-by-step guide to automatically integrate your data into BigQuery with the Windsor.ai no-code ELT connector.

1. Prepare your BigQuery environment by ensuring you have access to BigQuery in your Google Cloud account.

2. Log in to your Windsor.ai account or register if you don’t have one.

3. Connect a data source from which you want to import data into BigQuery in Windsor.ai. In our example, we’ll use Google Analytics 4 (GA4). Select the necessary account(s) you want to pull data from and click “Next.”

4. Select the filters and metrics you’d like to send to BigQuery for analysis.

5. Scroll down to the “Data Destinations” section and choose “BigQuery.”

1. After selecting BigQuery as the destination in Windsor.ai, click “Add Destination Task.”

2. Enter the following details:

• 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).

3. 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.

3. Grant access to the specified user in your IAM & Admin Console by following the link in the instructions.

• Add new principals: “[email protected]” in your IAM & Admin Console.
• Assign the role “BigQuery Admin” and click “Save.”

4. Return to the Windsor.ai dashboard and 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. 

5. 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.

6. You can now see the integrated data in BigQuery. Open the relevant project in BigQuery, refresh your dataset, and ensure the integrated data looks correct.