BigQuery

Connect Google BigQuery to WasteNot to pull data directly from your warehouse. Write simple queries to define exactly what data you need for building audiences.

Prerequisites

• A Google Cloud project with BigQuery enabled
• A service account with access to the data you want to query
• Someone with access to your Google Cloud console or CLI

Connecting BigQuery

Setup has two steps: creating the data source in WasteNot, then linking it to your Google Cloud project.

Step 1: Create the data source

1. Add a new BigQuery data source in WasteNot
2. Enter a Name
3. Click Save

Step 2: Link to your Google Cloud project

After creating the data source, go to its detail page. You will see a form and setup instructions.

Your engineering team will need to fill in:

• Project Id — Your Google Cloud project ID
• Workload Identity Pool Provider Id — Created during the setup process below
• Service Account Email — e.g., my-sa@my-project.iam.gserviceaccount.com

The page includes step-by-step setup instructions in three formats:

• ClickOps — Walk through it in the Google Cloud console
• gcloud — Copy and run a script in the terminal
• Terraform — Add a config block to your infrastructure code

Once the Google Cloud side is set up and the fields are filled in, click Connect.

Custom streams

Once connected, create a stream for each type of customer activity you want to use in audience rules. A stream runs a SQL query, and each row returned becomes an event in WasteNot.

WasteNot passes @start_datetime and @end_datetime to every BigQuery query. Use them to filter your source timestamp column, including the start of each sync window and excluding the end:

WHERE created_at >= @start_datetime
  AND created_at < @end_datetime

1. Go to your BigQuery data source detail page
2. Click New Stream
3. Enter a name, select the event type that matches the query results, and paste your SQL query
4. Check Enabled and click Save

WasteNot will run enabled streams on a schedule and sync the results automatically.

Example queries

Each query returns one row per event and aliases your warehouse columns to the field names WasteNot expects. Replace the sample project, dataset, table, and column names with your own.

Orders

SELECT
  CAST(order_id AS STRING) AS id,
  customer_email AS email,
  order_total AS value,
  created_at AS occurred_at
FROM `my_project.shopify.orders`
WHERE created_at >= @start_datetime
  AND created_at < @end_datetime

Email opens

SELECT
  CAST(event_id AS STRING) AS id,
  recipient_email AS email,
  opened_at AS occurred_at
FROM `my_project.email.events`
WHERE event_type = 'open'
  AND opened_at >= @start_datetime
  AND opened_at < @end_datetime

See Writing Custom Queries for the full field requirements, more examples, and database-specific parameter syntax.

Frequently asked questions

Who on my team should set this up?

Anyone with access to your Google Cloud project and permission to create service accounts. This is typically someone on your engineering or data team.

Can I query data from multiple datasets?

Yes. Grant the service account read access to each dataset and use fully qualified table names such as my_project.my_dataset.my_table in your queries.

What permissions does the service account need?

The service account needs:

• BigQuery Job User on the Google Cloud project WasteNot uses to run queries
• BigQuery Data Viewer on each dataset the queries read

WasteNot does not need permission to create, update, or delete your BigQuery data.

My data source shows "Failed" status

Common causes include:

• The project ID, provider ID, or service account email in WasteNot does not match Google Cloud
• The workload identity provider is missing WasteNot's attribute mapping or condition
• The provider cannot impersonate the service account
• The service account cannot create query jobs or read a dataset referenced by a stream

Check the setup values with your engineering team, or reach out to support@wastenot.io for help.