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

1. Add the data source

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

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

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.

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:

  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:

  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.

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