BQ Optimizer Login

02 / Setup guide

Connect with the right permissions.

Prepare a GCP Service Account JSON key for your target project, then connect that project and region to BQ Optimizer. We verify access before saving the connection.

Content reviewed

Before you start

You need permission to create or manage a GCP Service Account and grant it the roles listed below on the target GCP project. The project in the key file can differ from the project you want to analyze; you explicitly choose the query target when creating the connection.

Replace your-project-id and the example Service Account email in the commands with your own values. Run these commands in your own GCP environment. Do not publish your JSON key or paste it into chat. Read what we access and store before uploading it.

Set up Service Account permissions

BQ Optimizer needs to query INFORMATION_SCHEMA.JOBS and INFORMATION_SCHEMA.TABLES. If you have not configured Service Account permissions yet, follow the steps below before uploading a key.

Validation checks both views. You need bigquery.jobs.create, bigquery.jobs.listAll, bigquery.tables.list, bigquery.tables.get, bigquery.routines.list, and bigquery.routines.get. roles/bigquery.user alone is not enough. Grant roles/bigquery.user, roles/bigquery.resourceViewer, and roles/bigquery.metadataViewer.

Use predefined roles Recommended

bigquery.user + bigquery.resourceViewer + bigquery.metadataViewer

1 Grant all three roles to the target Service Account 
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:SA_EMAIL" \
  --role="roles/bigquery.user"

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:SA_EMAIL" \
  --role="roles/bigquery.resourceViewer"

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:SA_EMAIL" \
  --role="roles/bigquery.metadataViewer"
2 Create and download a JSON key

In GCP Console, create a JSON key for the Service Account from step 1 and download it locally before filling out the upload form.

  1. Open GCP Console → IAM & Admin → Service Accounts
  2. Select the correct project and click the target Service Account
  3. Open the KEYS tab
  4. Click ADD KEY → Create new key
  5. Choose key type JSON, click CREATE, and the browser will download a *.json file
Once downloaded, a JSON key cannot be retrieved again. Store it securely; if lost, delete the old key and create a new one.

Or grant all three roles via GCP Console (UI)

  1. Open GCP Console IAM
  2. Select the correct project, then click Grant Access
  3. Enter the Service Account email under New principals; add BigQuery User, BigQuery Resource Viewer, and BigQuery Metadata Viewer
  4. Click Save
Advanced: custom least-privilege role (bqOptimizerViewer)

Grant only the required permissions. Use this when you want the smallest possible IAM surface.

1 Create the bqOptimizerViewer custom role 
gcloud iam roles create bqOptimizerViewer \
  --project=PROJECT_ID \
  --title="BQ Optimizer Viewer" \
  --description="Query INFORMATION_SCHEMA.JOBS and TABLES" \
  --permissions=bigquery.jobs.create,bigquery.jobs.listAll,bigquery.tables.list,bigquery.tables.get,bigquery.routines.list,bigquery.routines.get \
  --stage=GA
2 Grant the custom role to the target Service Account 
gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:SA_EMAIL" \
  --role="projects/PROJECT_ID/roles/bqOptimizerViewer"
3 Create and download a JSON key

In GCP Console, create a JSON key for the Service Account from step 2 and download it locally.

  1. Open GCP Console → IAM & Admin → Service Accounts
  2. Select the correct project and click the target Service Account
  3. Open the KEYS tab, then click ADD KEY → Create new key
  4. Choose key type JSON, click CREATE, and the browser will download a *.json file
FAQ

Why is roles/bigquery.user alone not enough?

It has jobs.create and tables.list, but not jobs.listAll (needed for INFORMATION_SCHEMA.JOBS) or tables.get / routines.get (needed for INFORMATION_SCHEMA.TABLES).

Why add roles/bigquery.resourceViewer?

It provides bigquery.jobs.listAll so validation can read all users' jobs in the project.

Why add roles/bigquery.metadataViewer?

It provides tables.get, tables.list, routines.get, and routines.list required to query INFORMATION_SCHEMA.TABLES.

Difference between bigquery.jobs.list and bigquery.jobs.listAll?

list only shows jobs you ran; listAll shows jobs for all users in the project.

How long until permissions take effect?

Usually immediately; wait up to 1–2 minutes if needed.

Create the connection

  1. Sign in and add a Connection.Open the connection form. You will be asked to sign in if you have not already.
  2. Choose the query target.Upload the JSON key, enter the target GCP project ID, and choose the GCP BigQuery region that contains your workloads.
  3. Validate key and save.We verify access to job history and table metadata. A failed validation does not create a connection; review the permissions and target region before retrying.

Each Connection represents one target project and region. Creating it starts the initial metadata collection automatically. You do not need to install an agent or change your pipelines.

Send reports to Salesforce Slack

Create a Salesforce Slack incoming webhook for the channel where your team wants to receive reports. Follow Salesforce Slack's incoming webhook instructions, then add that webhook URL as a Notification channel in BQ Optimizer. Treat the webhook URL as a credential.

If you do not have a notification channel, the connection setup takes you to the channel form. Otherwise, it returns you to the Dashboard. Add a notification channel to configure delivery.

We send a one-time first-report notification after the initial collection stage completes when an enabled channel is available. If you enable a channel later, the pending first-report notification is queued then. Regular reports run weekly; the Notifications page also offers “Send report now.”

A weekly report can include unused-table and query-cost findings. A complete report with no findings is skipped; incomplete data is not treated as proof that there is nothing to review.

Wait for the history, then review it

The Dashboard shows collection progress and refreshes for a limited time while collection is pending. Your first results use a shorter history; additional history arrives in the second stage. Read how unused tables are identified before interpreting the numbers.

Why can my key access one project but fail validation here?

The key's Service Account needs the required permissions on the target project, and the selected region must match your workloads. Successfully authenticating a key alone does not establish access to that project's metadata.