BigQuery Setup

Overscore connects to BigQuery through a Google Cloud service account. This guide walks you through creating a dedicated, read-only, narrowly-scoped service account, granting it the minimum permissions, optionally shielding sensitive data, and connecting it in the Hub.

We take this seriously: your data is the most important thing you trust us with. The single best way to keep it safe is to connect a service account that can only read the specific data you want on your dashboards — nothing more. This guide is written so that the secure way is also the easy way.

Overview

The connection flow works like this:

  1. You create a dedicated service account in Google Cloud (used only for Overscore)
  2. You grant it read-only access — and only to the datasets you choose
  3. (Optional, recommended for sensitive data) You point it at an anonymized or aggregated dataset instead of raw tables
  4. You download the JSON key file
  5. You upload the key in the Overscore Hub

Your credentials stay on the server. The service account key is never sent to the browser or included in your deployed dashboard bundles. All queries are executed server-side, and only the results are returned to the client.

Overscore only ever reads. It runs read query jobs and returns the results — it never writes, creates tables, or deletes anything. So a read-only service account is all it needs to work. There is no reason to grant it more.

Step 1: Create a dedicated service account

Use a separate service account for Overscore — don't reuse one your other systems share. A dedicated account means you can scope it tightly, see exactly what it accesses, and rotate or revoke it without affecting anything else.

  1. Go to the Google Cloud Console
  2. Select the project that contains your BigQuery datasets
  3. Navigate to IAM & Admin → Service Accounts
  4. Click Create Service Account
  5. Give it a descriptive name, e.g. overscore-reader
  6. Click Create and Continue

When prompted to grant roles, you can skip ahead — we'll grant the minimum roles deliberately in the next step. Click Done.

Step 2: Grant permissions — pick one of two setups

Both setups are least-privilege and strictly read-only. The difference is one role, and it decides one thing: whether the AI can discover your warehouse, or has to be told what to look at.

Both start with BigQuery Job User (roles/bigquery.jobUser) at the project level. It's required to run any query at all, and BigQuery jobs are project-scoped, so it can't be narrowed.

Setup A — Locked down

Job User (project) + Data Viewer (roles/bigquery.dataViewer) on each dataset you choose.

Overscore reads only the datasets you name. It cannot list what datasets exist, so the AI will ask you for the dataset name rather than discovering it. Once it has a name it can still read that dataset's tables and columns, because a dataset-level Data Viewer grant includes metadata for that dataset. Only enumeration is blocked.

Choose this when you know which datasets matter and want the smallest possible blast radius.

Setup B — Discoverable

Job User (project) + Metadata Viewer (roles/bigquery.metadataViewer) at the project level + Data Viewer on each dataset you choose.

| Role | Scope | Purpose | | --- | --- | --- | | BigQuery Job User | project | run queries | | BigQuery Metadata Viewer | project | list datasets, tables and schemas — never row data | | BigQuery Data Viewer | the dataset(s) you choose | read data, only from those datasets |

The AI can now see the structure of your whole project and write correct SQL without asking. Data access is identical to Setup A — still only the datasets you shared. What you're trading is that table and column names become visible project-wide. Never any row data.

Choose this when your warehouse is large or unfamiliar and you'd rather the AI explore than interview you.

Which to pick

If your project has a handful of clean datasets and you already know their names, Setup A is simpler and tighter. If your project is sprawling and only a few datasets are worth using, Setup B is usually better in practice: the AI can see what's there and you can tell it which ones to use, instead of spelling out every table by hand.

You can start with A and add Metadata Viewer later. It's one grant and nothing else changes.

Don't grant Editor, Admin, Owner, or any write/delete role under either setup. Overscore will never use them, and they only expand what could be reached if a key were ever exposed.

Grant Job User (project level)

  1. In the Cloud Console, go to IAM & Admin → IAM
  2. Click Grant Access
  3. In New principals, paste your service account email (e.g. overscore-reader@your-project.iam.gserviceaccount.com)
  4. Assign the role BigQuery Job User
  5. Click Save

Grant Data Viewer (dataset level — the important part)

Granting Data Viewer per-dataset (instead of project-wide) is what keeps the blast radius small: even in the worst case, the account can only read the specific datasets you explicitly shared.

  1. Go to BigQuery in the Cloud Console
  2. In the Explorer panel, click the dataset you want Overscore to read
  3. Click Sharing → Permissions
  4. Click Add Principal
  5. Paste the service account email and assign BigQuery Data Viewer
  6. Click Save
  7. Repeat for each dataset you want on your dashboards — and only those

If you ever want to give Overscore access to a new dataset, you add it here. If you want to cut off a dataset, you remove it here. The account can never see a dataset you didn't explicitly share.

Grant Metadata Viewer (project level — Setup B only)

Skip this if you chose Setup A.

  1. Go to IAM & Admin → IAM → Grant Access
  2. In New principals, paste the service account email
  3. Assign the role BigQuery Metadata Viewer
  4. Click Save

This is what makes INFORMATION_SCHEMA work across the project, so the AI can list datasets and tables instead of asking. It reads structure only and can never return a row of data.

If you want Overscore to read your whole warehouse

The per-dataset grant above is the tightest option, and it's what we recommend. But if your data lives across many datasets and you'd rather give Overscore the freedom to explore the whole warehouse — without adding datasets one at a time — you can grant BigQuery Data Viewer at the project level instead:

  1. Go to IAM & Admin → IAM → Grant Access
  2. In New principals, paste the service account email
  3. Assign the role BigQuery Data Viewer
  4. Click Save

This is still strictly read-only — the account can run queries and read data, but never write, create, or delete. The only thing that changes is scope: it can now read every dataset in the project, so the blast radius if the key were ever exposed is your whole warehouse rather than a handful of datasets. This is the right minimum for "let me explore freely": read-only Data Viewer (project) + Job User (project), and nothing more. Once you've settled on which datasets your dashboards actually use, tightening back down to per-dataset grants is the safer long-term setup.

If your tables contain personal data — emails, names, phone numbers, addresses — the strongest move is to never point Overscore at the raw tables at all. Instead, give it access to a separate dataset that contains only anonymized or aggregated data, and scope its Data Viewer role to that dataset only. Then the account literally cannot read raw PII, even if you wanted it to.

There are two ways to do this. Pattern B is the simplest and most robust; Pattern A avoids duplicating data.

Create a dataset that holds only anonymized and/or aggregated tables, populated by a query you control. Because the data is physically copied, the service account never needs any access to the raw dataset.

-- 1. Create a dataset to hold only "safe" data
CREATE SCHEMA IF NOT EXISTS `your-project.analytics_safe`;

-- 2. Build an anonymized / aggregated table from your raw data.
--    Hashing turns an email into a stable, non-reversible key: you can
--    still count distinct customers, but no one can read the address.
CREATE OR REPLACE TABLE `your-project.analytics_safe.orders_daily` AS
SELECT
  DATE(created_at)                                              AS day,
  region,
  COUNT(*)                                                      AS orders,
  SUM(order_total)                                              AS revenue,
  COUNT(DISTINCT TO_HEX(SHA256(CONCAT('keep-this-salt-secret:', LOWER(email)))))
                                                                AS distinct_customers
FROM `your-project.raw.orders`
GROUP BY day, region;

Then, in BigQuery, use Scheduled Queries to re-run this on a schedule (e.g. daily) so the safe dataset stays fresh. Finally, grant the overscore-reader service account Data Viewer on analytics_safe only (Step 2) — and not on raw.

Pattern A: an anonymized view (no data duplication)

If you'd rather not copy data, create a view that masks the sensitive columns, then mark it as an authorized view so the service account can read the view without any access to the underlying raw table.

CREATE SCHEMA IF NOT EXISTS `your-project.analytics_safe`;

CREATE OR REPLACE VIEW `your-project.analytics_safe.orders` AS
SELECT
  order_id,
  -- Pseudonymize the email: usable as a stable key, not readable as PII
  TO_HEX(SHA256(CONCAT('keep-this-salt-secret:', LOWER(email)))) AS customer_key,
  -- Simply omit direct identifiers (full_name, phone, street_address, …)
  region,
  order_total,
  created_at
FROM `your-project.raw.orders`;

Then authorize the view so it can read raw on the account's behalf:

  1. In BigQuery, open the raw dataset → Sharing → Authorize Views
  2. Add your-project.analytics_safe.orders
  3. Grant the overscore-reader service account Data Viewer on analytics_safe only

Now the account can query analytics_safe.orders but has no path to the raw table.

Tip: Whichever pattern you use, point your dashboard queries at the safe dataset (e.g. your-project.analytics_safe.orders_daily). Overscore requires fully-qualified table names, so the dataset you reference is exactly the dataset the account needs access to — nothing leaks in by accident.

Step 4: Download the JSON key

  1. In IAM & Admin → Service Accounts, click your overscore-reader account
  2. Go to the Keys tab
  3. Click Add Key → Create New Key
  4. Select JSON as the key type
  5. Click Create

A JSON file will download to your computer. This file contains the private key for the service account — keep it secure and don't commit it to version control. (You can always delete a key here and create a new one to rotate it.)

The JSON file looks something like this:

{
  "type": "service_account",
  "project_id": "your-gcp-project",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "overscore-reader@your-gcp-project.iam.gserviceaccount.com",
  "client_id": "...",
  ...
}

Step 5: Upload in the Hub

  1. Sign in to the Overscore Hub
  2. Open your project
  3. Go to the Settings tab
  4. Under Data Connection, upload the service account JSON file you just downloaded
  5. Click Connect BigQuery

Once connected, the Hub displays the GCP Project and Service Account email it's using, so you can confirm it's the dedicated reader account you intended. The key itself is encrypted at rest and only ever decrypted server-side when a query runs.

Step 6: Test the connection

After connecting, verify it works:

  1. Navigate to any dashboard in your project
  2. Go to the Queries section
  3. Create a test query against a dataset you shared, using a fully-qualified table name:
SELECT COUNT(*) AS rows
FROM `your-project.analytics_safe.orders_daily`
  1. Click Run to execute the query

If the connection is working, you'll see the result. If there's an error, check the Troubleshooting section below.

Security model

Overscore is designed so that your BigQuery credentials never touch the browser:

  • Service account keys are stored encrypted on the server. They are never included in API responses or dashboard bundles.
  • Least privilege. Because Overscore only reads, a read-only account scoped to specific datasets is all it needs. Anything beyond that is unnecessary risk — which is why this guide grants exactly two roles and nothing more.
  • Query execution happens server-side. The Overscore API receives a query request, authenticates the user, runs the query against BigQuery, and returns only the results.
  • Cached results are stored as Apache Arrow files. Dashboards load these into DuckDB-WASM for fast local access — no direct BigQuery connection from the browser.
  • Viewer access tokens authorize dashboard requests. A deployed dashboard does not contain an API key. The Cloudflare worker that serves it sends an X-OS-Access-Token header with each data request, derived from a signed cookie the Hub sets after it verifies the viewer's session. Access follows the viewer's identity rather than a shared secret. The token identifies the viewer and the project, and does not contain or expose BigQuery credentials.

This means even if someone inspects your deployed dashboard's JavaScript bundle, they will not find any database credentials.

Troubleshooting

"Permission denied" or "Access Denied" errors

Make sure the service account has BigQuery Job User at the project level and BigQuery Data Viewer on the dataset you're querying. If you scoped Data Viewer to specific datasets, confirm the table in your query lives in one of those datasets.

Overscore can't list your datasets / it keeps asking for the dataset name / INFORMATION_SCHEMA.SCHEMATA "Access Denied"

This is expected with a per-dataset Data Viewer grant — listing datasets needs project-level metadata access, which a dataset-only grant doesn't include. Two options: tell Overscore the dataset name when it asks, or add BigQuery Metadata Viewer at the project level (read-only, no row data) so it can discover datasets on its own. See Let Overscore discover your datasets.

"Not found: Table …" when the table clearly exists

Overscore requires fully-qualified table names (`project.dataset.table`) and does not assume a default dataset. Confirm you've fully qualified the table, and that the service account has Data Viewer on that specific dataset.

"BigQuery API not enabled"

Go to the API Library in Google Cloud Console and enable the BigQuery API for your project.

"Invalid key" when uploading

Ensure you're uploading the original JSON file downloaded from Google Cloud. The file must be valid JSON with a type field set to "service_account" and include project_id, private_key, and client_email.

Next steps