> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adriel.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data reference

> Field type mapping, query model, filters, and limits for the Google BigQuery data source in Adriel.

## 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-sources/g-n/google-bigquery/how-to-connect).

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

1. **Projects** — every GCP project the connected identity can access.
2. **Datasets** — every dataset within the chosen project.
3. **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:

```sql theme={null}
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.

<Note>
  **Each query is billed by Google Cloud**

  Every 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.
</Note>

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

<Note>
  **How to read the columns**

  The **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.
</Note>

<Note>
  **`_id` columns are always text**

  Any 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.
</Note>

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.

## API references (Google Cloud)

* [BigQuery product overview](https://cloud.google.com/bigquery/docs)
* [BigQuery REST API reference](https://cloud.google.com/bigquery/docs/reference/rest)
* [Standard SQL query reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)
* [Data types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)
* [Pricing (on-demand query cost model)](https://cloud.google.com/bigquery/pricing)

## See also

* [How to connect Google BigQuery](/data-sources/g-n/google-bigquery/how-to-connect) (paired how-to)
* Google BigQuery (Customer Journey) data reference — GA4 event-stream variant for the Customer Journey feature
* [Redshift data reference](/data-sources/o-z/redshift/data-reference) — alternative cloud data warehouse
* [PostgreSQL data reference](/data-sources/o-z/postgres/data-reference) — alternative SQL database