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 the minimum permissions (least privilege)

Overscore needs exactly two capabilities: permission to run a query, and permission to read the data that query touches. That's two roles:

  • BigQuery Job User (roles/bigquery.jobUser) — lets the account run queries. Must be granted at the project level (BigQuery jobs are project-scoped).
  • BigQuery Data Viewer (roles/bigquery.dataViewer) — lets the account read table and view data. Grant this at the dataset level — only on the datasets you want Overscore to see.

That's the whole list. Don't grant Editor, Admin, Owner, or any write/delete role — 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.

If you want Overscore to explore 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.
  • API keys authenticate dashboard requests. Each deployed dashboard uses an API key (set via environment variable) to authorize data requests. The API key identifies which project and dashboard the request is for, but 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.

"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