Connecting BigQuery to Golden Suite

Set up the BigQuery connector with a service account JSON + dataset routing.

The BigQuery connector pulls rows via the standard BQ client library. Auth is service-account JSON; Golden Suite encrypts the credential at rest via envelope encryption.

Prereqs

  • A GCP project with BigQuery enabled
  • A service account with bigquery.dataViewer on the target dataset + bigquery.jobUser on the project (needed to run queries)
  • The service-account JSON key file (download from IAM & Admin → Service Accounts)
  • ~10 minutes

Setup

  1. Create a dedicated service account for Golden Suite — don't reuse a personal one. 
    gcloud iam service-accounts create goldensuite-reader \
  --display-name "Golden Suite read-only"
gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member="serviceAccount:goldensuite-reader@YOUR_PROJECT.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataViewer"
gcloud projects add-iam-policy-binding YOUR_PROJECT \
  --member="serviceAccount:goldensuite-reader@YOUR_PROJECT.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"
gcloud iam service-accounts keys create gs-key.json \
  --iam-account=goldensuite-reader@YOUR_PROJECT.iam.gserviceaccount.com
  2. /golden/sources → Add source → BigQuery
  3. Paste the JSON key, the project ID, and the dataset name
  4. Test connection — Golden Suite runs a SELECT 1 to verify
  5. Provide the query

Sample queries

-- Simple table
SELECT id, email, first_name, last_name, phone, company, updated_at
FROM `your-project.analytics.customers`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

-- Cross-dataset join
SELECT
    c.id, c.email, c.first_name, c.last_name,
    o.name AS company, o.industry,
    c.updated_at
FROM `prod.crm.customers` c
LEFT JOIN `prod.crm.organizations` o ON c.organization_id = o.id
WHERE c.deleted_at IS NULL;

Common gotchas

  • Partitioned tables. ALWAYS filter on the partition column. Otherwise BigQuery scans the whole table (expensive).
  • Service account key rotation. GCP recommends rotating keys every 90 days. Golden Suite supports this — upload the new JSON via "Re-authorize"; the old one is wiped.
  • Project mismatch. The service account is created in one GCP project; the data may live in a different project. Make sure the service account has cross-project access (bigquery.dataViewer on the data project's dataset; bigquery.jobUser on the project where queries run).
  • Slot limits / on-demand vs flat-rate. The connector uses on-demand pricing by default. For high-volume teams on flat-rate, configure a reservation for ingest queries.
  • Legacy SQL vs Standard SQL. The connector uses Standard SQL. Don't pass #legacySQL directives.
  • BIGNUMERIC / DECIMAL columns preserve precision (good for finance data) but the matching engine treats them as strings for fuzzy comparisons. CAST AS STRING in the query if you're matching on them.

Cost notes

  • BigQuery on-demand pricing: $6.25/TB scanned at time of writing. Incremental ingests with partition pruning should scan well under 1 GB/day = ~$0.005/day.
  • Initial full-table ingests on multi-TB tables can cost real money. Use LIMIT during development to sample first.

Next steps

← Connecting Snowflake to Golden SuiteConnecting HubSpot to Golden Suite →