BigQuery

This guide covers how to configure Google BigQuery as a warehouse in Zeotap, including project setup, service account creation, and required IAM permissions.

Prerequisites

• A Google Cloud project with BigQuery enabled
• A service account with read access to the target dataset(s)
• The BigQuery API enabled in your GCP project

Connection Configuration

Connection Fields

Authentication

Project ID

The project ID is the unique identifier for your Google Cloud project. You can find it in the GCP Console under IAM & Admin > Settings, or in the project selector dropdown.

Note: Use the project ID (e.g., my-company-analytics), not the project name (e.g., “My Company Analytics”) or project number (e.g., 123456789012).

Dataset

The default dataset that Zeotap will query. Models can reference tables in other datasets using fully-qualified names (e.g., project.dataset.table), but the default dataset is used when table names are unqualified.

BigQuery dataset names are case-sensitive. Zeotap normalizes identifiers to lowercase to match BigQuery conventions when using backtick-quoted identifiers.

Service Account Setup

Zeotap authenticates with BigQuery using a GCP service account. Follow these steps to create one.

Step 1: Create the Service Account

# Using gcloud CLI
gcloud iam service-accounts create zeotap-reader \
  --project=my-company-analytics \
  --display-name="Zeotap Reader" \
  --description="Read-only access for Zeotap CDP"

Or in the GCP Console:

1. Go to IAM & Admin > Service Accounts
2. Click Create Service Account
3. Name it zeotap-reader
4. Click Create and Continue

Step 2: Grant Required Roles

The service account needs the following IAM roles:

# Grant BigQuery Data Viewer on the dataset
gcloud projects add-iam-policy-binding my-company-analytics \
  --member="serviceAccount:zeotap-reader@my-company-analytics.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"
 
# Grant BigQuery Job User (needed to run queries)
gcloud projects add-iam-policy-binding my-company-analytics \
  --member="serviceAccount:zeotap-reader@my-company-analytics.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"
 
# Grant BigQuery Data Editor (needed to create/write the operational datasets)
gcloud projects add-iam-policy-binding my-company-analytics \
  --member="serviceAccount:zeotap-reader@my-company-analytics.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor"

Zeotap creates and writes to six operational datasets in the project, on first use — plus cdp_prep when Data Prep is enabled for the workspace, and cdp_metadata when the metadata export is (see below):

• cdp_planner — plan tables used for incremental sync change detection
• cdp_audit — sync run and audit history
• cdp_journey — journey orchestration member state and event log tables
• cdp_identity — identity resolution output (default dataset; configurable per identity graph)
• cdp_raw — landing dataset for loader (inbound ELT) runs (default dataset; configurable per loader)
• audit_logs — observability events and event delivery logs

Project-level roles/bigquery.dataEditor covers all of them (it includes bigquery.datasets.create). If you prefer not to grant it project-wide, pre-create the six datasets and grant the service account WRITER access on each. The connection test verifies write access to every one of these datasets and reports remediation SQL if a step fails.

cdp_prep — only if Data Prep is enabled for the workspace

Data Prep is a per-workspace entitlement, off by default. A workspace that has it builds prepared tables into a seventh dataset, cdp_prep, and its connection test then carries a seventh write probe (write_prep). Until Data Prep is enabled nothing creates or reads this dataset, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS `my-company-analytics.cdp_prep`
  OPTIONS(location = 'europe-west1');

Project-level roles/bigquery.dataEditor already covers it. Pre-creating the dataset instead needs WRITER on it, like the six above — a prepared table is rebuilt, and when it is incremental updated in place, so the service account needs to create tables (the table plus its transient <slug>__next / <slug>__delta siblings), run MERGE/INSERT/DELETE against them, read them back to compute the next delta, and delete the siblings after the swap. WRITER on the dataset is all of that.

Create the dataset in the same location as the other six: BigQuery pins a dataset’s location at creation and will not move it, and a query cannot join datasets across locations.

Revoking the entitlement does not drop the dataset or its tables, so this access can be withdrawn at your convenience rather than urgently.

cdp_metadata — only if the metadata export is enabled for the workspace

The metadata export is a per-workspace entitlement, off by default, that keeps the workspace’s audience catalogue as the table cdp_metadata.audiences. When it is enabled for a workspace, the connection test of the source chosen to hold the table carries one more write probe (write_metadata); the workspace’s other sources are tested exactly as before. Until then nothing creates or reads this dataset, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS `my-company-analytics.cdp_metadata`
  OPTIONS(location = 'europe-west1');

Project-level roles/bigquery.dataEditor already covers it. Pre-creating the dataset instead needs WRITER on it: each export creates audiences__next, inserts into it, replaces audiences with it, and drops it. Create it in the same location as the other datasets.

Every export replaces the audiences table, so give the people and tools that read it access on the dataset, not on the table — a table-level grant does not survive the replacement.

Revoking the entitlement does not drop the dataset or its table.

For more granular control, you can grant roles/bigquery.dataViewer at the dataset level instead of the project level:

# Dataset-level access (more restrictive)
bq update \
  --source=dataset_policy.json \
  my-company-analytics:customer_data

Where dataset_policy.json includes:

{
  "access": [
    {
      "role": "READER",
      "userByEmail": "zeotap-reader@my-company-analytics.iam.gserviceaccount.com"
    }
  ]
}

Step 3: Create and Download the Key

gcloud iam service-accounts keys create zeotap-key.json \
  --iam-account=zeotap-reader@my-company-analytics.iam.gserviceaccount.com

This generates a JSON file containing the service account credentials. The file looks like:

{
  "type": "service_account",
  "project_id": "my-company-analytics",
  "private_key_id": "key-id-here",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "zeotap-reader@my-company-analytics.iam.gserviceaccount.com",
  "client_id": "123456789",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/..."
}

Step 4: Provide the Key to Zeotap

In the Zeotap UI, paste the entire contents of the JSON key file into the Service Account JSON field. Zeotap encrypts and securely stores this key.

Data Types

BigQuery has some unique data types that Zeotap handles:

Cross-Dataset Queries

Models can query tables across multiple datasets within the same project, or even across projects:

-- Same project, different dataset
SELECT u.user_id, u.email, o.total_amount
FROM `customer_data.users` u
JOIN `transactions.orders` o ON u.user_id = o.user_id
 
-- Cross-project query (service account must have access to both projects)
SELECT *
FROM `other-project.dataset.table`

Ensure the service account has bigquery.dataViewer access to all referenced datasets.

Example Configuration

curl -X POST https://agentic.zeotap.com/api/v1/sources \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Analytics BigQuery",
    "type": "bigquery",
    "config": {
      "project_id": "my-company-analytics",
      "dataset": "customer_data",
      "credentials_json": "{\"type\":\"service_account\",\"project_id\":\"my-company-analytics\",...}"
    }
  }'

Incremental Syncs and Change History

By default a sync detects change by materializing the model’s output into a plan table each run and diffing it against the previous run’s — correct on any table, but it re-scans the whole model every time, and in insert mode it re-delivers every current row every time.

Where the source table allows it, Zeotap reads BigQuery’s change history instead and delivers only what landed since the last run. Nothing needs to be configured for the common case, and eligibility is checked automatically on each run — a table that does not qualify silently uses the plan-table diff, so there is nothing to switch on and nothing that can break by being unavailable.

What qualifies

Insert-mode syncs over event or time-series tables are the case this helps most: an append-only table needs no configuration at all, because APPENDS reads the time-travel window every BigQuery table already has.

To enable change history for the other modes:

ALTER TABLE `customer_data.customers`
SET OPTIONS (enable_change_history = TRUE);

Enabling it stores change metadata and adds a small amount of storage and compute cost. It only records changes made after it is enabled — turning it on does not make earlier changes readable.

What does not qualify

Both functions require a plain managed table. Views, materialized views, external tables, wildcard tables, clones and snapshots are read with the plan-table diff instead, as are models with joins, CTEs, aggregates or window functions — a change feed can only be read from a single source table.

First runs, and syncs that fall behind

The first run of a sync always delivers the model’s complete output, then records where it stopped. Later runs read forward from there.

Change history reaches back only as far as the dataset’s time-travel window (two to seven days), and CHANGES additionally reads at most one day per run and cannot report the last ten minutes. A sync that has been paused, failing, or scheduled less often than its window allows simply delivers a full snapshot on its next run and resumes reading incrementally after that — no data is missed and no action is needed.

Cost Considerations

BigQuery charges based on the amount of data scanned by queries. To manage costs:

• Use partitioned tables — Partition on date columns and filter by partition in your model SQL to reduce data scanned
• Use clustered tables — Clustering on frequently filtered columns improves query performance and reduces cost
• Limit preview queries — Model previews run your SQL with a LIMIT clause, but wide SELECT * queries still scan all columns
• Monitor with BigQuery audit logs — Track queries from the Zeotap service account in Cloud Logging

-- Cost-efficient model: uses partition filter
SELECT customer_id, email, lifetime_value
FROM `customer_data.customers`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

Troubleshooting

Next Steps

• Create a model using your BigQuery warehouse
• Use the SQL editor to write and preview queries
• Set up a Reverse ETL sync to activate your data