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

1. Add the data source

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

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

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.

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.

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

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.