---
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-09-22
tier: pro
engines: [bigquery]
---

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

If your project sits inside a VPC Service Controls perimeter that admits only known
addresses, Dashies cannot reach it yet: adding the data source and building a
dashboard do not come from fixed addresses. [IP addresses](/reference/ip-addresses)
says which data sources do.

## 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.ai/app/connections](https://dashies.ai/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 or narrow the time window. Do not work
around it by removing the filter that bounds the scan.

**Reach for those two rather than for a coarser grain.** A BigQuery dashboard is
served, so its statement returns records and Dashies works each number out when a
reader opens the page; grouping them to what the dashboard reports at hands
Dashies a summary to summarize, and a count then comes back as the number of
groups you made rather than the number of records. See
[Where the data lives](#where-the-data-lives).
:::

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

## Where the data lives

**A BigQuery dashboard is served.** It keeps its data with Dashies rather than
inside the published file: each refresh runs your SQL, writes the result to
Dashies' own storage, and the page asks for what it needs when a reader opens it.
Every warehouse a refreshing dashboard can publish against is on that path.

The in-file materializations - `cube`, `lattice`, `hybrid` and `rows`, which ship
the numbers inside the page - are not available on a warehouse you connect, so a
publish that asks for one on this connection is refused. They are available on the
data Dashies provides; [Dataset modes](/concepts/dataset-modes) is where that and its
one plan-dependent exception are set out.

One consequence to design for: **the refresh is asynchronous.** A newly published
dashboard reads "Updating" until the first refresh lands, rather than carrying
numbers from the moment you published.

## Caps

The first three bound one result read through the adapter, which is the path the
authoring tools take. **They are not what bounds a served dashboard's own data**:
that is streamed by the refresh rather than assembled by the adapter. The fourth
is engine-independent.

| 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. |
| Compiled dashboard body | 5,242,880 bytes | Publish is refused. |

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 what the query tier
will return in one answer, so do not read "no byte cap" as "no ceiling".

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.
  **Collapse it back to one row per underlying record, never to the grain the
  dashboard reports at**: a BigQuery dashboard is served
  ([Where the data lives](#where-the-data-lives)), so a statement that has already
  aggregated gets aggregated a second time and a count comes back as the number of
  groups you made rather than the number of records. Group the unnested rows back
  by the key of the record they came from, and declare any column that collapse
  counts as a total rather than a count. 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.ai/app/connections](https://dashies.ai/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).