Data reference

Introduction

Google BigQuery is Google Cloud’s serverless data warehouse. Customers store analytics, event, and business data in BigQuery tables and query it with standard SQL. The Adriel BigQuery connector binds one Adriel data source to one BigQuery table, discovers that table’s columns, and lets those columns be used as metrics and breakdowns in widgets. When a widget loads, the connector auto-generates a BigQuery SQL statement from the widget configuration, executes it against Google Cloud, and returns the result. As a database connector, the available fields are not a fixed catalog — they are derived from the schema of the bound table. Data types are mapped from BigQuery native types to Adriel field types, and any column that is numeric becomes a metric, while non-numeric columns can serve as either a metric or a breakdown. To connect this data source, see How to connect Google BigQuery to Adriel.

Data refresh strategy

BigQuery data is fetched on demand. There is no incremental sync — every dashboard, widget, or report load issues a live query against the bound table, so results always reflect the latest committed data in the source. Identical-SQL cache. To reduce redundant scan cost, identical SQL statements are served from a 10-minute result cache. Once the cache expires, the next request re-runs the query against BigQuery. Schema cache. The table’s column list is memoized for 10 minutes per data source; the V3 field-metadata cache uses a 24-hour TTL. New columns added on the BigQuery side become visible after the cache refreshes.

Architecture levels

Google Cloud project → Dataset → Table. Discovery walks these three levels via the BigQuery metadata API:

Projects — every GCP project the connected identity can access.
Datasets — every dataset within the chosen project.
Tables — every table within the chosen dataset.

One Adriel data source binds to one BigQuery table. To expose a second table, create a second data source.

Date range limits

There is no platform-imposed date range limit. Query bounds are set by the widget’s date-range control combined with the date column mapped in Blend Data settings. To minimize scanned bytes and Google Cloud query cost, partitioned tables with a mapped date column are strongly recommended.

Query model

Widget queries are auto-generated by the shared SQL query service in the BigQuery flavor. Each request produces one SQL statement of the form:

SELECT <metrics>, <breakdowns>
FROM `<datasetId>.<tableId>` AS t
WHERE <widget filters> [AND <customFilters>]
GROUP BY <breakdowns>
ORDER BY <sort>
LIMIT <sqlLimit>

Key BigQuery-flavor rules applied by the query builder:

Backtick-quoted table reference. The dataset.table reference is wrapped in backticks.
t. breakdown qualifier. Every breakdown and filter field is prefixed with the t. table alias to avoid ambiguity.
Non-ASCII field names. Field names containing non-ASCII characters are wrapped in backticks.
STRING aggregation cast. Applying sum, min, or max to a STRING column auto-wraps it as CAST(x AS FLOAT64).
Inclusive date range. The end date is incremented by one day and formatted as yyyy-MM-dd so the range filter is inclusive.
LIMIT. Rows are capped at sqlLimit (default 1,000).

Queries execute directly through the Google Cloud BigQuery SDK — there is no HTTP proxy layer.

Each query is billed by Google CloudEvery widget load that misses the 10-minute cache issues a live BigQuery query, which counts against the connected Google Cloud project’s billing account. Fewer widgets, fewer manual refreshes, and tighter filters translate directly to fewer BigQuery queries. Pairing a date column mapped through Blend Data with a customFilters or filterColumn pre-filter narrows the scanned partition — the single biggest lever for controlling BigQuery cost.

Filters

Widget filters translate into BigQuery WHERE conditions using a whitelisted operator set. Data-source-level pre-filters narrow the scan before widget filters apply.

Supported filter operators

Only three operators are enabled for widget filters on BigQuery data sources:

IN
LIKE
REGEXP

For anything richer, apply a pre-filter on the data source or expose a view on the BigQuery side.

Data-source pre-filters (mutually exclusive)

Each data source can carry one of two optional pre-filter modes; setting both fails validation. Option A — customFilters (free-form WHERE). A raw SQL fragment appended to every query as AND <customFilters>. The fragment is validated before it is stored: it must begin with WHERE (the keyword is stripped before storage), cannot contain semicolons, DML/DDL keywords (INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, EXECUTE, GRANT, REVOKE), SQL comments (--, /*, #), the dangerous functions (EXEC, EXECUTE, PG_SLEEP, PG_TERMINATE_BACKEND, PG_CANCEL_BACKEND), system-table references (INFORMATION_SCHEMA, PG_, SYS.), or ? placeholders. Keyword checks are case-insensitive. Option B — filterColumn (form-driven). A column, operator (filterType, one of the supported filter operators), and value (filterValue). Expands into an IN / LIKE / REGEXP WHERE condition. Column lookup requires exactly one bound table.

Date-range filtering

Date-range filtering is applied automatically to the column mapped as the date field in Blend Data settings. The end date is incremented by one day for inclusive-range semantics.

Field type mapping

BigQuery has no fixed metric or breakdown catalog. The available fields are the columns of the bound table, resolved at query time from the table schema.

How to read the columnsThe Data type column uses the platform’s ten-value vocabulary: Number, Currency, Percentage, Ratio, Duration, Date, Text, URL, Array, Boolean. The Field role column indicates whether the field can serve as a metric, a breakdown, or both.

_id columns are always textAny column whose name ends in _id is exposed as a text field regardless of its BigQuery type, so numeric identifiers behave as breakdowns rather than metrics. This override is checked first, before the BigQuery type switch below.

Each BigQuery native type maps to an Adriel field type as follows.

BigQuery type	Adriel field type	Data type	Field role
`INTEGER`, `NUMERIC`, `FLOAT`, `INT64`, `FLOAT64`	`number`	Number	Metric
`STRING`	`string`	Text	Metric or breakdown
`BOOLEAN`, `BOOL`	`boolean`	Boolean	Metric or breakdown
`TIMESTAMP`, `DATE`, `DATETIME`	`date`	Date	Metric or breakdown; used for date-range filtering when mapped through Blend Data
`GEOGRAPHY`	`string`	Text	Serialized as a WKT string.
`STRUCT`	Flattened	—	Child fields exposed via `parent.child` path notation.
`ARRAY`	`array`	Array	Joined for display.
Any other BigQuery type	`string`	Text	Metric or breakdown
Field name ends in `_id`	`string` (override)	Text	Metric or breakdown

Adriel-added fields

Adriel adds one synthetic field on top of the table’s own columns.

Field	Description	Data type	API Key
Row count	Total number of rows in the bound table (or in the widget-filtered subset). Resolves to `COUNT(*)` in the generated SQL.	Number	`bigQuery:rowCount`

Aggregation defaults

When no explicit aggregation is set on a field, the query builder chooses one based on the BigQuery type:

BigQuery type	Default aggregation
`INTEGER`, `NUMERIC`, `FLOAT`	`SUM(x)`
`STRING`	Single-value collapse — returns the value when only one distinct value exists in the group, otherwise empty.
`BOOLEAN`, `TIMESTAMP`, `DATE`, `DATETIME`	Single-value collapse — returns the value cast to string when only one distinct value exists in the group, otherwise NULL.
Field name prefixed with `count_`	`COUNT(field_without_prefix)`

When an explicit aggregation of sum, min, or max is set on a STRING field, BigQuery-flavor generation wraps the value in CAST(x AS FLOAT64) first.

Limitations

Based on the current connector:

1,000-row cap per widget query by default (sqlLimit). Configurable per data source, but capping widget results is intentional for stability and cost. Results that hit the cap are flagged as potentially incomplete (TOO_MUCH_DATA_REQUESTED_INCOMPLETE).
One table per data source. Multi-table joins are not supported — they must be pre-joined into a BigQuery view.
No arbitrary SQL from widget builder. Widgets can only select from the bound table; free-form SQL is limited to the customFilters WHERE fragment.
Filter operator whitelist. Only IN, LIKE, and REGEXP are available in widget filters.
10-minute query-result cache. Two identical SQL statements issued within a 10-minute window are served from cache without hitting BigQuery. Rows changed in BigQuery within that window may still return the previous cached result until the cache expires. deleteCache() is a no-op on this connector.
24-hour V3 field-metadata cache. New columns added on the BigQuery side become visible only after the field cache refreshes (up to 24 hours).
SUM overflow. Integer overflow in SUM aggregation surfaces as SUM_TOO_BIG_TO_AGGREGATE — the connector does not auto-cast to FLOAT64 for numeric columns.
Custom-filter validator restrictions. Free-form WHERE fragments cannot use semicolons, DML/DDL, SQL comments, INFORMATION_SCHEMA, or ? placeholders (see Filters).
Dataset region alignment required. The bound table must reside in the region reported by BigQuery for the dataset; a region mismatch triggers auto-disconnect (Table ... was not found in location ...).
Auto-disconnect on table, region, or auth loss. The data source is automatically disconnected when BigQuery returns Not found for the bound table or dataset, when the table is found in a different region, or when the Google authorization has expired (invalid_rapt) — see the paired how-to for re-authorization steps.

Introduction

Data refresh strategy

Architecture levels

Date range limits

Query model

Filters

Supported filter operators

Data-source pre-filters (mutually exclusive)

Date-range filtering

Field type mapping

Adriel-added fields

Aggregation defaults

Limitations

API references (Google Cloud)

See also

​Introduction

​Data refresh strategy

​Architecture levels

​Date range limits

​Query model

​Filters

​Supported filter operators

​Data-source pre-filters (mutually exclusive)

​Date-range filtering

​Field type mapping

​Adriel-added fields

​Aggregation defaults

​Limitations

​API references (Google Cloud)

​See also

Introduction

Data refresh strategy

Architecture levels

Date range limits

Query model

Filters

Supported filter operators

Data-source pre-filters (mutually exclusive)

Date-range filtering

Field type mapping

Adriel-added fields

Aggregation defaults

Limitations

API references (Google Cloud)

See also