Under the hood
How queries run and what they cost
From a chart on screen to a BigQuery job and its bill.
GOAT answers every chart on a dashboard from BigQuery.
From a chart to one query
- The viewer's browser names a saved chart and sends the page's filter choices. It never sends a query. A viewer can run only charts on dashboards that are published and that their tenant can open.
- GOAT reads the chart's saved definition and builds the SQL itself, on the server.
- If the dataset has a tenant key, GOAT adds a filter on that column for the tenant named in the viewer token. The tenant's id is passed to BigQuery as a query parameter, never written into the SQL text. If the token names no tenant, the query matches no rows.
- GOAT runs one BigQuery query for the chart. That one query returns the chart's values, its totals, and how many groups there are.
A chart groups by at most two fields and returns at most 10,000 cells. When a chart has more groups than it shows, GOAT says so.
Datasets with no tenant key
A dataset with no tenant key adds no tenant filter, so every tenant's charts read all of its rows unless an Author turns its sharing off. See Security model.
Operators
Operators, the Admins and Authors who sign in to the app, query with no tenant filter unless they choose a tenant with Preview as, and can try queries of their own while building. See Security model.
Caches
A chart is answered from the first of these that has it:
| Layer | What it keeps | How long |
|---|---|---|
| GOAT's answer cache | Each chart's answer, per tenant, per filter choice, per version of the chart and the data. It lives in each Cloud Run instance's memory, 64 MiB by default. | Until the data changes. A load into one of GOAT's managed copies (the tables GOAT loads your data into) starts a new version, so an older answer is never used again. For data GOAT reads where it lives, without copying, an answer is reused for a window, 5 minutes by default, set per dataset. |
| BigQuery's own result cache | Results of identical queries. | As BigQuery keeps them. |
| A new query |
A kept answer runs no BigQuery job and costs nothing. Because the cache is in each instance's memory, a newly started instance begins empty and answers more slowly until it fills. Viewers cannot skip the caches; operators can, for testing.
The cost cap per query
Every chart query runs with a cap on the bytes BigQuery may bill: 10 GiB by default for the install, and a dataset may set its own, higher, lower, or none. A query that would read more fails instead of billing. The previews an Author sees while building a chart, and reads of the rows behind a chart, are checked first with a dry run, which costs nothing, and are refused before they run if they would pass the cap.
The cap limits one query. To cap a day's total, set a BigQuery custom quota on the project. See Costs.
What drives the bill
- The bytes each query reads. On BigQuery's on-demand pricing, a query is billed by the data it reads, with a minimum of 10 MB. A project on capacity pricing pays for reserved capacity instead. Partitioning and clustering a table on the columns your filters use cuts what a query reads. GOAT's managed copies keep the layout of their source, or one GOAT picks.
- How often queries miss the caches. Repeat views of the same chart, tenant, and filters are free while the answer is kept.
- One query per chart. A dashboard of ten charts opened cold runs about ten queries.
Every BigQuery job GOAT runs carries labels saying what it was for, such as a chart, a load, or a filter's values, and which dataset, chart, and tenant it served. Use them to break your BigQuery bill down in your own billing reports.
Request ceilings
Each viewer tenant, and each operator, has a ceiling on queries that miss the caches, and filter value lists have their own. Limits gives the figures. A request over a ceiling is answered 429 with Retry-After, and the embedded dashboard retries up to twice. The ceilings are kept per Cloud Run instance, so they are approximate when several instances run.