---
title: Connect BigQuery
description: Create a service account, paste its key into the Dashies form, and start refreshing dashboards from a Google BigQuery project on a schedule.
updated: 2026-08-04
tier: pro
engines: [bigquery]
---

Google BigQuery, via a service account. This is the simplest of the six engines
to connect: there is no host, no port, and no network allowlist to configure.

## Before you start

Create a dedicated service account in the BigQuery project, grant it enough
BigQuery access to run a query and list the project's datasets, and download a
JSON key.

:::note{title="We do not name the exact IAM roles"}
The Dashies error copy says only `Check the service account's key and its
BigQuery roles.` Rather than print a role pair we cannot guarantee is current,
this page states the capability the service account needs: run a query in the
project, and list the project's datasets. Grant the least-privilege roles in your
organisation that provide those two.
:::

## 1. Add the data source

Open [dashies.xyz/app/connections](https://dashies.xyz/app/connections), click to
add a data source, and pick **BigQuery**.

| Field | Required | Example | Notes |
|---|---|---|---|
| Project ID | yes | `myproject-123456` | 6 to 30 characters, lower case, starts with a letter. |
| Service account email | yes | `dashies@myproject-123456.iam.gserviceaccount.com` | |
| Private key | yes | a PKCS#8 PEM block | Up to 16 KiB in the form. |
| Display name | no | `Analytics warehouse` | Up to 120 characters. |

You do **not** paste the whole JSON key file. Dashies assembles the minimal
service-account JSON from the three fields above.

The private key is the `private_key` value out of the downloaded JSON, and it
looks like this:

```bash
-----BEGIN PRIVATE KEY-----
MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQ...
-----END PRIVATE KEY-----
```

Both paste forms work. If you copy the value straight out of the JSON file it
will contain literal `\n` sequences rather than real newlines, and Dashies
converts them before validating. If the key does not parse, the response is the
canned `The service account key is invalid.`

## 2. It verifies in one step

BigQuery is a single-step connect. Creating the data source stores the key,
mints a token, lists the project's datasets, and comes back **active** with the
datasets it found, or **error** with a class. There is no separate Test click on
first connect.

Up to 200 datasets are stored as the allowlist. That allowlist is what cube SQL
may read, and it is re-discovered whenever you press Test. There is no Resync
button on BigQuery.

If it fails:

| Message | Meaning |
|---|---|
| `BigQuery denied access. Check the service account's key and its BigQuery roles.` | Authentication or authorization. Both `401` and `403` are reported the same way on purpose. |
| `BigQuery took too long to respond.` | The whole verify has a 20-second budget. |
| `We couldn't reach BigQuery.` | Network-level failure reaching Google. |
| `We couldn't read that project. Check the Project ID and try again.` | The project id is wrong, or the account cannot see it. |

## What Dashies can see

Introspection reads `INFORMATION_SCHEMA.COLUMNS` across the datasets in the
allowlist. Table and column names come back; no data rows are read.

BigQuery reports no row estimates. Only Postgres does.

## Cost, and the guard that stops a surprise bill

:::warning{title="Every query is capped at 20 GiB billed"}
Dashies pins `maximumBytesBilled` at **20 GiB per query**. A cube that would scan
more than that **errors** rather than running. That is deliberate: an unbounded
cube on a large table should fail loudly at authoring time, not arrive as a bill.

If you hit it, partition-prune in the SQL, narrow the time window, or coarsen the
grain. Do not work around it by removing the filter that bounds the scan.
:::

Queries also have a 30-second timeout, and the inline result path pages at 50,000
rows.

## Caps

| Limit | Value | What happens past it |
|---|---|---|
| Rows per dataset query | 100,000 | Refused before any page is fetched. |
| Bytes scanned per query | 20 GiB billed | Query errors. |
| Query timeout | 30 seconds | Query errors. |
| Data island, whole dashboard | 8,388,608 bytes | Publish is refused. |
| Compiled dashboard body | 5,242,880 bytes | Publish is refused. Usually binds first. |

There is no execution-time byte cap on BigQuery results the way there is on
Postgres. The adapter reads BigQuery's own row total up front and refuses before
fetching. A wide cube is still bounded, one layer later, by the island and body
limits above, so do not read "no byte cap" as "no ceiling".

BigQuery supports every materialization, including Parquet offload for a
row-level dataset.

How these ceilings relate to each other, and which one binds first, is in
[sizes and ceilings](/concepts/dataset-modes#sizes-and-ceilings).

## Dialect notes

Cube SQL is GoogleSQL.

- Table references are backtick-quoted and fully qualified:
  `` from `myproject-123456.analytics.orders` ``.
- **Bucket dates with the time zone as the third argument**:

  ```sql
  select timestamp_trunc(ordered_at, MONTH, 'America/Los_Angeles') as month,
         sum(amount) as revenue
  from `myproject-123456.analytics.orders`
  group by 1
  order by 1
  ```

- **The obvious relative window does not run.** `timestamp_sub(current_timestamp(),
  interval 12 month)` fails, because `TIMESTAMP_SUB` accepts only MICROSECOND
  through DAY on a `TIMESTAMP`. Go through `DATE` instead:

  ```sql
  where ordered_at >= timestamp(date_sub(current_date('America/Los_Angeles'), interval 12 month))
  ```

- Conditional count is `countif(c)`.
- **There is no aggregate percentile function, and the obvious substitute is
  wrong.** Never back a declared median or percentile measure with
  `APPROX_QUANTILES`. It is approximate, which already breaks the exactness a
  lattice cell promises, and its answer changes with the number of dimensions in
  the cube. Measured on a live 300,000-row table, the same population returned
  22518 from a two-dimension lattice and 22164 from a three-dimension one, against
  a true median of 22785. Two cells of one lattice can disagree about the same
  rows.
- BigQuery preserves an unquoted output alias exactly as written.
- A `TIMESTAMP` value arrives in the data island as an ISO-8601 UTC string,
  rounded to milliseconds. `DATE`, `DATETIME`, and `TIME` come back verbatim, and
  the last two keep microseconds. Bucket and format in SQL rather than parsing the
  raw text.
- A nested `ARRAY` or `STRUCT` column has to be addressed into before it is
  usable, and `cross join unnest(...)` **multiplies rows**. Nothing rejects that:
  the cube runs, publishes, refreshes, and the numbers are simply wrong. Aggregate
  back to the grain you meant. See [Verify your numbers](/guides/verify-your-numbers).

## Rotating the key

Edit the data source and paste a new key under **Service account key**.

## Check it worked

1. The data source reads **active** on
   [dashies.xyz/app/connections](https://dashies.xyz/app/connections), and lists
   the datasets it discovered.
2. Ask your AI tool to introspect it and run one query:

   > Introspect my BigQuery data source, then validate this cube SQL against it:
   > `select 1 as ok`

3. Then [author a dashboard against it](/guides/author-a-dashboard).