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. A new tab will open to authorize your Google Cloud account. Select the email connected to your GCP account and give Windsor.ai all the required accesses.

3. You’ll be returned to Windsor’s destination form and see your Google account selected. Now, you have to 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).

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. 

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.

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

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.