BigQuery destination: common problems and their solutions

This guide overviews the most common issues when integrating Windsor.ai data into BigQuery, covering authentication, data mismatches, backfilling, refresh scheduling, and platform limitations, as well as lists troubleshooting steps.

Issues:

• Test connection fails in Windsor.ai
• The destination task remains in the “failed” or “not started” state
• BigQuery tables are not created by Windsor.ai

Possible causes:

• Invalid User, Project ID, or Dataset ID
• Google Cloud billing is not enabled
• OAuth scope denied during authentication
• Missing IAM permissions (e.g., BigQuery Data Editor)

Solutions:

• Double-check your Project ID and Dataset ID:
  Select the correct Project ID and Dataset ID while creating a connection to BigQuery. The Dataset ID must already exist, while Windsor.ai can auto-create the table if it doesn’t exist.

• Enable billing in Google Cloud Console:
  BigQuery will reject any operation if the project doesn’t have billing enabled. Go to Google Cloud Console > Billing and ensure your project is attached to a valid billing account.
• Re-authenticate using Google OAuth:
  During setup, Windsor.ai will request access to your Google Cloud account. If you skipped or denied any scope (like BigQuery access), re-authenticate by removing the connection and starting over. Select the correct Google account that has access to the relevant BigQuery project you are using.
• Grant required IAM roles:
  Ensure the Google account you use to authorize Windsor.ai has the roles:

– BigQuery Data Editor: Allows inserting and updating table data.

– BigQuery Job User: Allows running load and query jobs.

Go to Google Cloud Console -> IAM and Admin -> Select your user and assign the required roles.

Issues:

• Inaccurate or missing data in BigQuery tables
• Numbers don’t match source platforms (e.g., GA4, Facebook Ads)

Possible causes:

• Different filters or accounts selected in the windsor.ai dashboard
• API caching (Windsor.ai caches data for 6 hours)
• Fields missing due to permissions or selection settings

Solutions: 

• Use consistent filters, date ranges, and aggregation levels:
  Ensure the Windsor.ai data preview matches the data you want in BigQuery by using the correct filters and accounts.

• Clear Windsor.ai cache:
  Windsor caches data for 6 hours. Click “Clear cache” in the data preview to fetch fresh data if recent changes aren’t visible in the preview table.
• Select all required data sources:
  Check if Windsor’s data preview is showing the proper data. If not, verify that the correct data sources are connected and selected.
• Validate with raw API data:
  Use Windsor.ai’s API to run the same query manually and verify results.

Issues:

• Historical data is missing or incomplete in BigQuery
• Only recent data (e.g., last 30 days) is visible

Possible causes:

• The default sync covers the limited time range
• API limits on date range or request size
• Backfill was not configured correctly in Windsor.ai

Solutions:

• Manually define the date range in the query:
  By default, Windsor.ai may only pull the last 7 or 30 days of data. Use the custom date selector to define the full historical range.

• Break the backfill into smaller chunks:
  Fetch data in monthly or weekly segments to avoid timeouts or API quotas.
• Use the Backfill option in the destination task setup:
  Windsor.ai allows historical backfill configuration within the destination task.

• Monitor job success in the destination task logs:
  Check the Windsor.ai dashboard for status logs and verify that the data is loading as expected.

Issues:

• BigQuery tables do not refresh on schedule
• The destination task runs once, then stops

Possible causes:

• The schedule interval is not saved
• Insufficient plan limits
• API rate limits or errors in the data source
• The destination platform is under maintenance

Solutions:

• Set and save your schedule explicitly:
  In the destination task settings, select “daily” or “hourly” schedule type and click “Save.”

• Check your subscription tier:
  Refresh frequency depends on your plan:

– Free/Basic = Daily

– Plus/Professional = Hourly

• Review error logs:
  Click the Query History in Windsor.ai’s dashboard to identify issues like token expiration or data source errors.

• Review BigQuery job history:

Check the BigQuery job history for any related errors in the job run. Go to Google Cloud Console -> BigQuery and see the job history tab for the project.

Issues:

• BigQuery upload fails due to schema issues
• New fields not appearing

Possible causes:

• Schema changes (e.g., added or removed fields) are not reflected
• BigQuery schema is manually altered
• Field names changed in the source platform

Solutions: 

• Recreate the table if needed:
  Delete the table in BigQuery and re-run the destination task in windsor if schema mismatches persist.
• Use partitioned tables:
  Use date-based partitioning while creating the destination task for better schema evolution, performance, and cost control.

• Avoid manual schema updating in BigQuery:Don’t modify the BigQuery schema manually (e.g., renaming columns or changing data types), as Windsor.ai may not adapt automatically.

Issues:

• Daily data appears off
• Total metrics don’t match the source platform

Possible causes:

• Windsor.ai uses your local time, while source platforms may use other time zones
• Aggregation level (e.g., ad group vs. campaign) differs between tools
• Reporting logic differs across platforms (e.g., Shopify includes refunded orders)

Solutions:

• Set the same timezone in all tools:
  Use UTC across Windsor.ai, BigQuery, and your source platforms to avoid off-by-one-day errors.
• Match platform reporting logic:
  Understand platform-specific calculation rules (e.g., Shopify may include refunded orders in dashboards but not in the API). Use the platform documentation for reconciliation.

• Use Windsor.ai task logs:
  Check sync status, errors, and run history directly from the destination task panel.
• Check BigQuery job History:
  Use the BigQuery console’s Job History tab to inspect failed or partial load jobs.
• Compare with raw API:
  Use Windsor.ai’s API to reproduce the query and compare outputs.

By following these troubleshooting steps, users can resolve common issues when streaming data to BigQuery via Windsor.ai.

If the issue persists, contact our customer support for further assistance.

Keep in mind that investigating data discrepancies takes time (1-3 days), so it’s better to organize the report and send it to [email protected]. We’ll take a detailed look and reply to you as soon as we find a resolution.