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

0sql never connects to BigQuery. project, dataset and location are carried as metadata for your own application, which runs the SQL it gets back with its own client and credentials. 0sql never uses them. Keep service-account keys out of the project entirely; if a key does land in datasources.yml, zsql deploy strips private_key and api_key before building the archive.

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