BigQuery SQL style and best practices: a short, tested standard

Names

• snake_case for everything: datasets, tables, columns, CTEs. BigQuery column names are not case-sensitive, so OrderDate and orderdate collide.
• Datasets by stage and domain: raw_google_ads, staging_crm, reporting_marketing. The prefix says how far the data has been processed; the rest says what it is.
• Tables singular, by grain: order, session, daily_channel_spend. The name answers “one row is one what”.
• Columns say their unit: amount_usd, cost_micros, duration_seconds, is_paid for booleans, _at for TIMESTAMP and _date for DATE.
• IDs end in _id and keep the same name in every table, so a join reads USING (customer_id).

Reserved words and backticks

SELECT 1 AS order;

fails with “Syntax error: Unexpected keyword ORDER”. ORDER, SELECT, GROUP, FROM and other reserved keywords cannot be used as plain names. With backticks they can: SELECT 1 AS `order` runs.

Not every SQL keyword is reserved in BigQuery. SELECT 2 AS date, 3 AS record ran without backticks. The safe rule is still to avoid them: a column called date reads ambiguously next to the DATE type and function. Use order_date, event_date.

Project IDs with a hyphen are a special case. In FROM, BigQuery accepts them unquoted: FROM my-project.dataset.table passed a dry run in our test. Outside FROM the same path fails with a syntax error, and other names with a hyphen need quoting anyway. Backtick the whole path, `my-project.dataset.table`, and the question never comes up.

Layout

SELECT
  customer_id,
  COUNT(*) AS orders,
  SUM(amount_usd) AS revenue_usd
FROM reporting_marketing.order
WHERE order_date >= '2026-09-01'
  AND status = 'paid'
GROUP BY customer_id
ORDER BY revenue_usd DESC;

One clause per line, one column per line, trailing commas avoided, conditions aligned under WHERE. The point is diffs: when a column is added, one line changes. Name every aggregate with AS; an unnamed one becomes f0_, which nobody downstream can read. Use CTEs for steps instead of nested subqueries; see the CTE guide.

Comments

All three styles work in BigQuery:

SELECT 1 AS n  -- a note at the end of a line
# also a single-line comment
/* a block
   comment */

Use -- for single lines; # works but is unusual outside BigQuery and confuses readers coming from other SQL. Comment the why, not the what: -- refunds are excluded because finance books them separately is useful; -- filter status is noise.

Strings

SELECT
  """He said "it's fine"
on two lines""" AS triple,
  r'\d+' AS raw_regex;

• Triple quotes hold quotes and line breaks without escaping: useful for JSON, long text and SQL inside SQL.
• Raw strings, r'...', keep backslashes as typed. Write every regular expression as a raw string, or \d has to become \\d. The string functions guide has the regex functions.

SAFE functions

The SAFE. prefix turns an error into a NULL for most functions, and SAFE_DIVIDE, SAFE_CAST and others are their own functions. Use them where a NULL is an acceptable answer, and count the NULLs they produce; otherwise a broken input is replaced by silence. Not every function accepts the prefix: SAFE.DATE(string) fails with “SAFE with function … is not supported”. Details are in CAST and SAFE_CAST.

Cost rules

1. Name the columns. On-demand queries are billed by the columns read; SELECT * on a wide table can cost hundreds of times more.
2. Filter on the partition column with constants, so BigQuery prunes partitions. See partitioned tables.
3. Check the estimate the editor shows before running. LIMIT does not lower it.
4. There is no EXPLAIN. EXPLAIN SELECT 1 fails with “Statement not supported: ExplainStatement”. Use the dry-run estimate before, and the job’s Execution details after, to see where time and bytes went.

Prices and the billing model are in BigQuery pricing.

Enforce it with tools

• SQLFluff, with its BigQuery dialect, checks layout and naming in CI.
• Version control for every query a report depends on, so a change has a diff and an author.
• A review rule: no query reaches a shared report without a second reader.

One definition per metric

Style makes a query readable. It does not stop two readable queries from defining “active customer” differently, and that is what makes reports disagree. The last rule of a standard is about ownership: each metric is written once and read everywhere. In OWOX Data Marts that query is a Data Mart with an owner and a description per column, and every sheet and dashboard reads it. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data. More SQL foundations are in the BigQuery SQL guide.

Google BigQueryBigQuery Date Functions: Syntax, Examples and Common ErrorsOctober 6, 2026→Google BigQueryHow to Connect GA4 to BigQuery: Setup, Limits and First QueriesOctober 6, 2026→Google BigQueryBigQuery String Functions: Syntax and Tested ExamplesOctober 6, 2026→

See all articles →