Learn how to set up and manage Google BigQuery as a high-performance destination for your data exports with DataFlowed.
Overview
Google BigQuery serves as a powerful, scalable destination for your exported data. Unlike Google Sheets, BigQuery is designed for large datasets and complex analytics. Before creating exporters, you'll need to connect at least one BigQuery destination to your account. This guide explains how to add and manage BigQuery destinations for your data exports.
Prerequisites
Before you can configure BigQuery, make sure you have:
Granted BigQuery and Google Cloud Platform access permissions to your account
A Google Cloud Project with BigQuery enabled
Billing enabled on your Google Cloud Project
The BigQuery Admin or Editor role on your project
Adding a BigQuery Destination
There are two ways to add a BigQuery destination to your account:
Method 1: Select an Existing Dataset
Connect to an existing BigQuery dataset in your Google Cloud Project:
From the Google Integration dashboard, find the BigQuery section
Click "Add BigQuery Destination" to open the destination selection dialog
In the "Select Existing Dataset" section, choose your Google Cloud Project
Select an existing dataset from the dropdown
Click "Use Selected Dataset" to confirm
The form for selecting an existing BigQuery dataset
The selected dataset will be connected to your account and can be used for data exports.
Method 2: Create a New Dataset
Create a new BigQuery dataset in your Google Cloud Project:
From the Google Integration dashboard, find the BigQuery section
Click "Add BigQuery Destination" to open the destination selection dialog
In the "Create New Dataset" section, choose your Google Cloud Project
Enter a name for your new dataset (e.g., dataflowed_exports)
Click "Create New Dataset" to confirm
The form for creating a new BigQuery dataset
Note
You can only access projects where you have BigQuery permissions. If you don't see your project, check your Google Cloud IAM settings.
BigQuery Configuration Options
When setting up a BigQuery destination, you can configure several options to optimize performance and cost:
Table Prefix
All tables created in your BigQuery dataset will use a prefix to organize your data. The default prefix is dataflowed, but you can customize this to match your naming conventions.
Example: With prefix mycompany, tables will be named mycompany_shopify_orders, mycompany_google_ads_campaigns, etc.
Write Disposition
Control how data is written to BigQuery tables:
Replace (Truncate): Each export run will clear the existing data and replace it with new data
Append: Each export run will add new data rows without removing existing data
Write if Empty: Data will only be written if the table is empty
Schema Management
BigQuery can automatically detect data types from your data, or you can define a custom schema:
Auto-detect Schema: BigQuery automatically determines column types based on your data
Custom Schema: Define specific data types for each column (advanced users)
Performance Optimizations
Configure BigQuery for optimal performance and cost:
Partitioning: Enable date-based partitioning to improve query performance and reduce costs
Clustering: Specify columns for clustering to optimize query performance
Managing Connected BigQuery Destinations
After connecting BigQuery destinations, you can manage them from the Google Integration dashboard:
Viewing Connected Destinations
All your connected BigQuery destinations are listed in the BigQuery section of the Google Integration dashboard. For each destination, you can see:
The dataset name and project ID
A link to open the dataset directly in BigQuery
Configuration options like table prefix and write disposition
An option to remove the destination from your account
Removing a Destination
To remove a BigQuery destination from your account:
Find the destination in the BigQuery section of the dashboard
Click the "X" icon next to the destination name
Confirm the removal when prompted
Important
Removing a destination will not delete your data from BigQuery, but any exporters using this destination will stop working. You'll need to update those exporters to use a different destination.
BigQuery vs Google Sheets: When to Use Each
DataFlowed supports both BigQuery and Google Sheets as destinations. Here's when to use each:
Use BigQuery When:
You have large datasets (thousands or millions of rows)
You need to perform complex SQL queries and analysis
You want to integrate with other Google Cloud services
You need to build data pipelines and workflows
You want to use machine learning capabilities
Cost optimization is important for large datasets
Use Google Sheets When:
You have smaller datasets (up to thousands of rows)
You need to share data with non-technical team members
You want to create charts and visualizations quickly
You need real-time collaboration on data
You want to manually review and edit the data
You prefer a familiar spreadsheet interface
Best Practices
Dataset Organization: Use separate datasets for different types of data (e.g., one for analytics data, another for advertising data)
Table Naming: Use descriptive table names that clearly indicate the data source and type
Partitioning: Enable partitioning on date fields to improve query performance and reduce costs
Clustering: Use clustering on frequently queried columns to optimize performance
Cost Management: Monitor your BigQuery usage and costs through the Google Cloud Console
Data Retention: Consider implementing data retention policies to manage storage costs
Access Control: Use BigQuery's IAM features to control who can access your data
Troubleshooting
No BigQuery Projects Found
If no BigQuery projects appear in the selection:
Verify you're using the same Google account that has BigQuery access
Check that BigQuery is enabled in your Google Cloud Project
Ensure billing is enabled on your project
Verify you have the BigQuery Admin or Editor role
Check that you've granted the necessary OAuth scopes
Export Errors
If exports to BigQuery are failing:
Check that your Google account is still connected and authorized
Verify that you haven't exceeded BigQuery quotas or limits
Ensure your Google Cloud Project still has billing enabled
Check the export logs for specific error messages
Verify that your BigQuery destination is still active
Performance Issues
If BigQuery queries are slow or expensive:
Enable partitioning on date fields to reduce data scanned
Use clustering on frequently queried columns
Optimize your SQL queries to scan only necessary data
Consider using BigQuery's cost controls and quotas
