How to integrate data into Google BigQuery with Windsor.ai
What is Google BigQuery?
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.
How to connect BigQuery to Windsor.ai
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.”
Configuring BigQuery as the destination for your data
1. After selecting BigQuery as the destination in Windsor.ai, click “Add Destination Task.”
2. Now, you have to enter the following details:
- Task name: Enter any name you wish.
- Authentication type: You can authorize via a Google Account (OAuth 2.0) or a Service Account file.
- 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.
FAQs
What are the key steps to connect Windsor.ai with BigQuery?
To connect Windsor.ai with BigQuery:
- Start by selecting a data source and previewing the data.
- Then, choose BigQuery as the data destination and enter the required details, such as Project ID, Dataset ID, and Table Name.
- Test the connection and save the settings. Windsor.ai will then automatically start streaming data to BigQuery.
How secure is the connection between Windsor.ai and BigQuery?
Windsor.ai uses secure OAuth authentication and encrypted data transfer protocols. You must also configure your AWS roles and privileges to limit access to sensitive data.
What are the authentication options for connecting BigQuery to Windsor.ai?
Windsor.ai supports two authentication methods:
- Google Account (OAuth 2.0): Recommended for quick setup, testing, or personal use.
- Service Account (JSON Key File): Ideal for automated, scheduled, and production-level data transfers.
1) Use OAuth if:
- You’re setting up or testing the integration manually.
- You prefer signing in with your Google Workspace or Gmail credentials.
- You don’t need fully automated, unattended updates.
Note: OAuth tokens can expire and may require re-authentication.
2) Use a Service Account if:
- You need scheduled or automated data updates.
- You want a stable, secure connection that doesn’t rely on a personal login.
- You’re integrating Windsor.ai in a production or multi-user environment.
Note: Service Accounts are designed for server-to-server connections and are more suitable for long-term automation.
How do I create a Service Account and get the key file to integrate with Windsor.ai?
1. Go to Google Cloud Console.
2. Select your project or create a new one.
3. Navigate to IAM & Admin -> Service Accounts.
4. Click Create Service Account, give it a name, and click Create and Continue.
5. Assign roles such as:
- BigQuery Data Editor
- BigQuery Job User
6. Complete the process and go to the Manage Keys section for the service account.
7. Click Add Key -> Create New Key, select JSON, and download the file. It’s your credential file for Windsor.ai.
Can I connect multiple data sources through Windsor.ai?
Absolutely! Windsor.ai supports over 315 data sources, allowing you to centralize your data from various marketing channels and business tools in BigQuery.
What should I do if I get the error "Reauthentication is needed?"
You might encounter this error when working with BigQuery destination tasks on Windsor onboard:
Reauthentication is needed. Please run `gcloud auth application-default login` to reauthenticate.
This error is caused by the expiration of the OAuth token you provided when adding/editing your BigQuery destination task(s). Specifically, this issue occurs when the session length configured in your Google Workspace admin console expires. Once the session ends, the authenticating user’s OAuth token becomes invalid, causing authentication failures and the error message above.
To resolve it, your Google Workspace administrator needs to adjust the Reauthentication policy settings to extend the session length for Google Cloud services. Visit https://support.google.com/a/answer/9368756 for more details.
There are two options to mitigate it:
- Set the Windsor app as a Trusted App and then exempt Trusted Apps from reauthentication https://support.google.com/a/answer/7281227?hl=en.
- Change settings to ‘Never require reauthentication for any app.’
Once you have changed your settings in Google Admin, please reconnect your Google account in the affected destination tasks.
Why do I see the error "Exception: SQL table misses some of your configured fields?"
This likely happened because new fields were added or modified after the table was initially created. 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.
To fix:
- Manually add the missing columns to the required BigQuery table, or
- Run a new destination task – Windsor.ai will auto-create a new table with the full schema on the next sync.
What should I do if the connection test between Windsor.ai and BigQuery fails?
Ensure that:
- The correct Project ID, Dataset ID, and Table Name are provided.
- Your Google Cloud project has billing enabled.
What prerequisites do I need to set up BigQuery integration?
You need to have the following things in place:
- An active Google Cloud account.
- Access to BigQuery with the necessary permissions.
- An active Windsor.ai account.
Can I schedule automated data uploads from Windsor.ai to BigQuery?
Yes, Windsor.ai allows you to set a schedule for automated data uploads. You can define the update frequency in the windsor.ai app to ensure your BigQuery database remains up-to-date.
Tired of manually transferring data to BigQuery? Try Windsor.ai today to automate the process
