BigQuery Adapter

Generate BigQuery Standard SQL from your model.

What the adapter emits

  • Identifiers in `backticks`; bare column names in expressions are lowercased.
  • Date literals as DATE '2026-01-01'; grain truncation as DATE_TRUNC(x, MONTH) (BigQuery’s argument order); parts with EXTRACT(YEAR FROM x), EXTRACT(DAYOFWEEK FROM x).
  • CTEs for the per-fact subqueries (no temp tables) and a FULL OUTER JOIN when a query blends measures from two facts.
  • BigQuery’s own aggregates are recognized as aggregates in measure expressions: any_value, countif, logical_and, logical_or, array_concat_agg, approx_quantiles, approx_top_count, approx_top_sum, on top of the standard list.

The same monthly revenue projection as on the Postgres page, in BigQuery’s dialect:

SELECT
	DATE_TRUNC(T1.`d_date`, MONTH) AS `Month(Date)`,
	sum(T0.`ss_net_paid`) AS `Store Net Paid`
FROM
	`analytics.tpcds.store_sales` T0
	JOIN `analytics.tpcds.date_dim` T1
		ON T0.ss_sold_date_sk = T1.d_date_sk
GROUP BY
	DATE_TRUNC(T1.`d_date`, MONTH)

Configuration

bq:
  adapter: bigquery
  name: BigQuery
  tier: warm
  project: my-gcp-project
  dataset: analytics
  location: US

Connection fields

The server never connects to BigQuery: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.

zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It takes a key_file, GOOGLE_APPLICATION_CREDENTIALS, or application-default credentials; project and location say where, and dataset is read as the schema. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips private_key and api_key from the file if present. See Connecting to a warehouse.

Notes

  • Qualify tables in physical_name. Write project.dataset.table or dataset.table; the value is emitted verbatim in FROM.
  • Expressions are BigQuery SQL. countif(status = 'done'), approx_count_distinct(user_id) and SAFE_DIVIDE are fine in expression.sql.

Next Steps