Code quality

The editor checks every statement as you type: syntax, unknown columns, aggregation mistakes, and the BigQuery cost traps a dry run won't tell you about. Problems get a squiggle, a hover explanation, and usually a one-keystroke fix.

SELECT *
FROM `ace-analytics.warehouse.fact_mot_test`
WHERE completed_date >= '2024-01-01'
LIMIT 10

Reading a squiggle

Hover an underline for the message; every message ends with the rule id in parentheses. ⌥F8 (AltF8 on Windows and Linux) jumps to the next problem. Hint-level rules draw nothing; the lightbulb in the gutter is the only sign of them.

Some problems are found as you type; others come back from BigQuery’s dry run when you pause. Dry-run errors, like a missing required partition filter, also land in the status bar:

Fix with AI hands the error and the query to Prism AI; Explain asks it to explain the message. A syntax error shows the dry-run wording, Syntax error: Unexpected identifier "SELEC" at [1:1], with No quick fixes available.

Quick fixes

Put the cursor on the underlined code and press ⌘. (Ctrl. on Windows and Linux), or click the lightbulb. Enter applies the highlighted fix.

WITH tests AS (
SELECT make, test_result
FROM `ace-analytics.warehouse.fact_mot_test`
),
vehicles AS (
SELECT make, model
FROM `ace-analytics.warehouse.dim_vehicle`
)
SELECT make, test_result
FROM tests
JOIN vehicles USING (make)

The menu always ends with two entries: Disable ’…’ rule, which switches that rule off in settings (not offered for syntax errors or rules your organization locks), and Fix with Prism AI, which sends the problems and their suggested fixes to the agent.

Fixes you’ll meet most:

The same menu also lists refactorings that don’t need a warning: expand t.*, extract a subquery to a CTE, inline a CTE, convert CROSS JOIN to INNER JOIN, flip JOIN sides, CASE → COALESCE / NULLIF, convert pipe syntax to standard SQL. Keyword casing and backtick cleanup live on the editor toolbar instead.

Suggest QUALIFY

The subquery + WHERE rn = 1 idiom has a shorter form. Offered for ROW_NUMBER(), RANK() and DENSE_RANK():

SELECT * FROM (
SELECT vehicle_id, test_result,
  ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY completed_date DESC) AS rn
FROM `ace-analytics.warehouse.fact_mot_test`
)
WHERE rn = 1

SELECT vehicle_id, test_result
FROM `ace-analytics.warehouse.fact_mot_test`
QUALIFY ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY completed_date DESC) = 1

Suggest LIKE ANY

WHERE make LIKE 'FORD%' OR make LIKE 'VAUXHALL%' OR make LIKE 'MINI%'

becomes WHERE make LIKE ANY ('FORD%', 'VAUXHALL%', 'MINI%'). NOT LIKE … AND NOT LIKE … becomes NOT LIKE ANY.

Tuning thresholds and switching rules off

Settings → Code Quality (also listed under Settings). Two sections: Cost Optimization holds the thresholds, Diagnostic Rules the on/off switch for every rule.

The size choices are 100 MB / 1 GB / 10 GB (10 GB / 100 GB / 1 TB for the scan threshold) and Never warn. Sizes are decimal (1 GB = 10⁹ bytes) and compare against the table’s stored size.

The Diagnostic Rules section lists the rules below with a switch each, Enable All / Disable All per group, and an Enable All Rules button with a N rules disabled badge. Severities are fixed. In an organization, an admin can lock rules on (they show an Org policy badge), raise a rule’s severity, and set thresholds; the stricter of yours and the org’s applies.

Rules by category

All rules are on by default. The groups match the Diagnostic Rules section in settings.

Performance

Code Quality

Correctness

Semantic

Parser & Engine holds the syntax error itself and ON After Comma Cross-Join. The Charts (ggsql) and Graph (GQL) groups hold the rules described on the Charts and Graph queries pages.

Reusable column aliases

BigQuery can let a SELECT alias be read by later items and by the WHERE of the same query, but only when the project option reusable_column_aliases_mode is on. The editor asks BigQuery whether it is on for the project and location of the active tab, with a free dry run, and follows the answer. With the option on, SELECT x * 2 AS y, y + 1 is not flagged, completions and hover know the aliases, and the cases BigQuery refuses (a duplicate alias that is read, an alias in the WHERE of a grouped query) are errors. With it off, the read is an unrecognized name. See Reusable column aliases in BigQuery for how to turn the option on and what it allows.

What the checks need

• Schema loaded for the tables in FROM; column rules and size thresholds are silent otherwise
• dbt and Dataform files are checked on the resolved SQL, so ref() and {{ … }} don’t count as syntax errors