Skip to content

Guides

Connect BigQuery

Connect a Google BigQuery project through a service account, with its key or without any key, and start refreshing dashboards from it on a schedule.

Google BigQuery, via a service account, signing in one of two ways: with the account's key, or without a key, where your Google Cloud lets this one data source act as the account and no key exists at all. Either way 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 says which data sources do.

Before you start

Create a dedicated service account, give it the three roles below, and download a JSON key for it.

RoleGrant it onWhat Dashies uses it for
BigQuery Job User (roles/bigquery.jobUser)The project you enter as the Project IDRunning queries. Every query is a job in that project: reading your schema, checking a dashboard's SQL, and each refresh.
BigQuery Read Session User (roles/bigquery.readSessionUser)The same projectReading each refresh's results. A refresh reads them through the BigQuery Storage Read API, which needs bigquery.readsessions.create and bigquery.readsessions.getData on that project.
BigQuery Data Viewer (roles/bigquery.dataViewer)The project, or each dataset your dashboards readReading the tables your SQL names, and listing the datasets.

Google's role picker lists them under those titles. BigQuery Data Viewer is read-only, and the two project roles let the service account run queries and read their results.

Job User and Data Viewer alone are not enough

Neither carries the Storage Read API permissions, so a service account with only those two can connect and publish, and every refresh is then refused. Dashies checks for BigQuery Read Session User when you add the data source, as described below.

Google Cloud organizations created on or after May 3, 2024 block service account key creation by default (the iam.managed.disableServiceAccountKeyCreation constraint). In one of those, either an organization policy administrator allows key creation for the project before you download the key, or you connect without a key, which needs no exception because no key is ever created. The service account and its three roles are the same either way.

The two steps below are the key path.

1. Add the data source

Open dashies.ai/app/connections, click to add a data source, and pick BigQuery.

FieldRequiredExampleNotes
Project IDyesmyproject-1234566 to 30 characters, lower case, starts with a letter.
Service account emailyesdashies@myproject-123456.iam.gserviceaccount.com
Private keyyesa PKCS#8 PEM blockUp to 16 KiB in the form.
Display namenoAnalytics warehouseUp 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:

-----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, checks the service account's two project roles, 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.

The role check

Dashies asks Google whether the service account holds bigquery.jobs.create, bigquery.readsessions.create and bigquery.readsessions.getData on the project, through Cloud Resource Manager's testIamPermissions. If any is missing, the data source is saved as error and the message names the role to grant. Pressing Test on the data source asks again.

That call goes through the Cloud Resource Manager API, which Google leaves off in a new project. When it is off, Dashies cannot ask, so the data source connects and says the roles are not confirmed. To have them checked, turn the Cloud Resource Manager API on in the project the service account belongs to, and in the project you entered as the Project ID if that is a different one, then press Test: it checks the roles again and marks the data source as failing if one is missing. Turning an API on grants no permission to anyone. A role you have just granted can take a few minutes to take effect.

If it fails:

MessageMeaning
The service account is missing BigQuery Read Session User on this project, ...The role check found bigquery.readsessions.create or bigquery.readsessions.getData missing. The message names BigQuery Job User instead, or both roles, when those are what is missing.
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.

A refresh refused later because the service account lost BigQuery Job User or BigQuery Read Session User says the warehouse refused what Dashies asked for, and names the roles. One refused because the service account can no longer read a table says the warehouse refused access to an object the query reads.

Connect without a key

A BigQuery data source can sign in without a service account key, through Google Cloud's workload identity federation. Dashies generates a signing key for the data source and publishes its public half at the data source's own issuer URL. Your Google Cloud trusts that issuer, and only for this data source, to act as a service account you created, which holds the same three roles. No key of yours is created, downloaded, pasted or stored.

It takes three steps, and the middle one runs in Google Cloud.

1. Add the data source

On dashies.ai/app/connections, add a data source, pick BigQuery, and choose Keyless under Sign-in method.

FieldRequiredExampleNotes
Project IDyesmyproject-123456The project Dashies runs its queries in, as on the key form.
Service account emailyesdashies@myproject-123456.iam.gserviceaccount.comThe account Dashies acts as. Its address ends in .iam.gserviceaccount.com: the Compute Engine default account, which ends in developer.gserviceaccount.com, is refused.
Pool project numberyes123456789012The project NUMBER, not its ID, of the project that holds the workload identity pool. It is usually the same project.
Pool IDyesdashies4 to 32 characters of lower case letters, digits and hyphens, not starting with gcp-.
Provider IDyesdashiesThe same rules as the pool ID.
Display namenoAnalytics warehouseUp to 120 characters.

Click Create and show setup. The data source is saved as pending and the dialog shows the Google Cloud commands for it. Nothing has been asked of Google yet.

2. Run the setup commands in Google Cloud

Copy the commands into Google Cloud Shell, signed in as someone who can change IAM in those projects, and run them. In order, they:

  1. Look up the pool project's ID from its number.
  2. Turn on the IAM, IAM Service Account Credentials, Security Token Service and Cloud Resource Manager APIs in the pool's project and the service account's.
  3. Create the workload identity pool, and an OIDC provider in it that trusts this data source's issuer, https://dashies.ai/wif/<issuer id>/, and only a token whose tenant is this data source.
  4. Create the service account, if it does not exist yet, and grant it the three roles in Before you start.
  5. Let this data source, and nothing else, act as the service account, by granting Workload Identity User to its principal: principal://iam.googleapis.com/projects/<pool project number>/locations/global/workloadIdentityPools/<pool id>/subject/<issuer id>.

A command that creates something that already exists fails and changes nothing, so running the commands again is safe, and the service account can be one you made earlier.

Give the provider a pool of its own

A pool's subjects are shared by every provider in it. A pool that also holds a provider whose users can choose their own subject could claim this data source's, so the commands create a pool for Dashies alone, and that is the one to use.

The commands, the issuer URL, the attribute condition and the principal stay available: edit the data source to see them again.

3. Test it

Press Test data source in the dialog, or later from the data source's menu. Dashies signs a short-lived token, Google's token service exchanges it, and Dashies then acts as the service account to list the datasets and check the roles, exactly as the key path does. A pass makes the data source active.

Google applies a new IAM grant after a short delay, so a Test run straight after the commands can be refused at the last step and pass a little later.

The messages below are the add dialog's. A Test pressed from the data source's menu names the same step in the card's own words, which also send you to Edit data source to correct the setup while it has never signed in.

MessageMeaning
Google did not accept this data source's sign-in. ...The provider's issuer URL is not exactly this data source's, or the provider limits its audiences.
The workload identity provider's attribute condition refused this data source's sign-in. ...The provider's attribute condition is not exactly the one in the commands.
Google has no active workload identity provider with that pool project number, pool ID, and provider ID. ...One of the three is wrong, or the provider is disabled.
Google has no service account with that email. ...The service account email is wrong, or the account does not exist.
Dashies signed in but may not act as the service account. ...The Workload Identity User grant is missing, names another principal, or has not taken effect yet.
An API this sign-in needs is turned off. ...The IAM Service Account Credentials API or the Security Token Service API is off.

The role check and its messages are the key path's; see The role check.

Correcting the setup

Until the data source first signs in, its setup can be corrected: edit the data source, change the identifiers and save. Its issuer URL stays the same, so a provider that already names it keeps working. From its first sign-in the setup is fixed, and a different setup is a new data source.

Revoking access

Either side can end it, and neither needs the other:

  • In Google Cloud: remove the Workload Identity User grant, or delete the provider. Every sign-in after that is refused.
  • In Dashies: delete the data source. Its signing key is deleted with it, and its issuer URL stops answering once the copy cached for five minutes expires, so nothing can sign in as it again.

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

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.

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 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.

LimitValueWhat happens past it
Rows per dataset query100,000Refused before any page is fetched.
Bytes scanned per query20 GiB billedQuery errors.
Query timeout30 secondsQuery errors.
Compiled dashboard body5,242,880 bytesPublish 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.

Dialect notes

Cube SQL is GoogleSQL.

  • Table references are backtick-quoted and fully qualified: from `myproject-123456.analytics.orders`.

  • Bucket dates in your time zone, and give the zone to date as well:

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

    timestamp_trunc's third argument sets the bucket and returns the instant of local midnight; date's second argument reads that instant's day in the same zone. A bare date() reads it in UTC, which is the day before for any zone east of UTC. The result is a DATE, which a type: date dimension needs: a timestamp there is refused at publish.

  • 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:

    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), 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.

Rotating the key

Edit the data source and paste a new key under Service account key. A data source that signs in without a key has none to rotate.

Check it worked

  1. The data source reads active on 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.