BigQuery SQL Formatter

BigQuery SQL Formatter

Visual Studio Code extension providing BigQuery SQL syntax checking

The BigQuery SQL Formatter extension for Visual Studio Code provides syntax checking and formatting for BigQuery SQL queries. This extension aims to enhance the development experience for users working with BigQuery by offering features such as:

• Syntax Checking: Automatically checks the syntax of BigQuery SQL queries to ensure they are correct and conform to best practices.
• Formatting: Provides tools to format SQL queries according to predefined styles, making the code more readable and maintainable.
• Language Server Integration: Utilizes a language server to offer real-time feedback and suggestions as users write SQL queries.
• GoogleSQL Support: Checks BigQuery's SQL dialect, including BigQuery-specific query features.
• Best Practices Enforcement: Encourages the use of best practices in SQL query writing, such as preferring LEFT JOIN over RIGHT JOIN for better readability and maintainability.

This extension is particularly useful for developers and data analysts who frequently write and maintain BigQuery SQL queries, providing them with tools to improve code quality and productivity.

Diagnostics update as you type, including after edits to multiline strings and comments. Rules ignore SQL-like text inside strings, quoted identifiers, and comments. Style and best-practice diagnostics default to warnings. Quick fixes and formatting preserve your file's line endings; linting does not rewrite files.

Installation

Install BigQuery SQL Formatter from the Visual Studio Code Marketplace. Requires Visual Studio Code 1.91 or newer. Open a BigQuery-specific file or select GoogleSQL as the document language to start linting.

Rules

See the rule reference for all aliasing, ambiguous, capitalisation, convention, layout, references, and structure rules. The references (RF) group checks identifier naming and usage. RF02 requires source-qualified columns in multi-source queries, including STRUCT field paths. Its ignore_words and subqueries_ignore_external_references options apply in editor and project settings. RF03 enforces STRUCT-aware single-source consistency with quick fixes, Fix All and formatting; single_table_references accepts consistent (default), qualified or unqualified. RF04 is enabled by default and warns about keywords used as unquoted aliases. Quoted names are accepted by default; identifier policies and ignore_words are configurable. RF05 checks quoted identifiers for special characters, with exceptions for dashed project IDs and INFORMATION_SCHEMA region qualifiers. RF06 removes unnecessary backticks from columns, aliases and CTE names. Both are enabled by default; RF06 leaves table paths quoted unless check_table_paths is enabled. The 0.4.0 rules add explicit UNION modifiers and INNER JOINs, consistent GROUP BY/ORDER BY references, matching set-query column counts, statement terminators, and CTE layout.

UNION modifier checks now use AM02 instead of CV12. Move any UNION-specific CV12 settings and noqa suppressions to AM02. CV12 continues checking EXCEPT and INTERSECT.

Pipe and temporal queries

Pipe queries (including FROM-only inputs) and FOR SYSTEM_TIME AS OF support RF01-RF06, AL01, AL02, AL04, AL05, AL06, AL08, AL09, AM04, AM05, AM06 and AM07. Checks follow each stage's visible table aliases, output aliases and JOIN USING columns. RF01 remains disabled by default. Quick fixes, Fix All and formatting preserve pipe placement and line endings.

MATCH_RECOGNIZE, GRAPH_TABLE and unmodelled pipe operators (such as pipe WITH) still skip structural checks in their own block and descendants; enclosing queries and sibling blocks continue to be checked. CP01-CP05, CV01, CV02, CV04, CV05, CV13, LT01, LT12 and LT13 remain active throughout. Other layout and structure rules do not yet apply inside pipe queries.

Rule configuration

Configure rules in your Visual Studio Code user, workspace, or workspace-folder settings:

{
  "bigquerySQLFormatter.rules": {
    "CV05": { "enabled": true, "severity": "error" },
    "LT02": { "enabled": false },
    "exclude": ["CV01"],
    "groups": { "capitalisation": false }
  }
}

Rules default to enabled with warning severity unless documented otherwise. An opt-in rule with a disabled default requires an explicit "enabled": true; setting a severity, options, or enabling its group is not sufficient. RF01 is Disabled by default because schema-free checking cannot distinguish unknown table qualifiers from STRUCT field access. Enable it with "RF01": { "enabled": true }. Supported severities are error, warning, information, hint, and off. Disabling a rule removes its diagnostics and fixes, including fix-on-save. Exclusions, disabled groups, and off take precedence over an individual enabled: true setting. Group names are aliasing, ambiguous, capitalisation, convention, layout, references, and structure. Use uppercase rule codes in configuration, including the RF prefix for references rules.

Rules with declared style options accept an options object. For example:

{
  "bigquerySQLFormatter.rules": {
    "CP04": { "options": { "capitalisation_policy": "upper" } }
  }
}

CP01 (keywords), CP02 (unquoted identifiers), CP03 (functions), CP04 (literals), and CP05 (data types) accept lower (the default), upper, or consistent. consistent follows the first matching token in the file; a mixed-case first token selects lowercase. Strings, quoted identifiers, and comments are ignored. Group settings remain boolean toggles, not shared option defaults.

Option declarations also support lists of strings, including RF04's case-insensitive ignore_words. Lists must contain only strings; an empty list is valid. Project lists replace the corresponding editor list rather than being concatenated.

For shared project settings, create .bqsqlformatter.json:

{
  "maxNumberOfProblems": 100,
  "rules": {
    "CV05": { "severity": "error" },
    "LT02": { "enabled": false }
  }
}

The nearest config between the SQL file and its workspace-folder root overrides Visual Studio Code settings field by field. Only that config is used; parent project configs are not merged. Exclusion arrays replace rather than extend the editor list. Multi-root workspaces remain isolated. Without a workspace, only the SQL file's directory is checked. Settings and project-file changes revalidate open documents automatically. Options use the same rules.CODE.options shape in project files, and project values override individual option fields, retaining omitted editor values. Invalid configuration produces a visible error. Unknown options or invalid option values reset that rule's entire options object to its declared defaults, without disabling other settings or rules.

SQL comments can suppress a line (-- noqa or -- noqa: CV05, LT02) or a file/region (-- noqa: disable=CV05, LT02 followed by -- noqa: enable=CV05). Put a disable directive at the top for file-wide suppression; use all for all rules. Enable directives cannot override configured-disabled rules.

See Configuration for precedence and examples. Rule-specific style options and .sqlfluff compatibility are not yet supported.

Configuration

File associations

GoogleSQL activates automatically for .bqsql, .bqddl, and .bqdml files. Generic .sql, .ddl, and .dml files are left to your other SQL extensions. To enable BigQuery linting for them, select GoogleSQL from the language mode picker or add explicit associations to your workspace's .vscode/settings.json:

{
  "files.associations": {
    "*.sql": "googlesql",
    "*.ddl": "googlesql",
    "*.dml": "googlesql"
  }
}

Only documents in GoogleSQL language mode are linted. Existing associations to the legacy sql-bigquery ID should be changed to googlesql.

bigquerySQLFormatter.maxNumberOfProblems defaults to 100 and limits published diagnostics on both opening and editing a document, after noqa suppression. Set it to 0 to hide diagnostics. Settings are resolved per document, including for code actions, and changing configuration revalidates open documents.

Format Document and Format Selection

Use Format Document or Format Selection in GoogleSQL language mode. This extension is the default formatter for GoogleSQL unless you have chosen another formatter. To enable formatting on save:

{
  "[googlesql]": {
    "editor.defaultFormatter": "sean-conkie.bigquerysqlformatter",
    "editor.formatOnSave": true
  }
}

Formatting applies enabled, automatically fixable rules repeatedly until the result is stable. It respects editor/project rule settings and noqa directives, preserves line endings, and is not limited by maxNumberOfProblems. Selection formatting only applies fixes wholly contained in the selection; text outside it is unchanged. Rules without automatic fixes remain diagnostics. Formatting uses the rules' existing styles and configured capitalisation policies; custom indentation and comma-position options are not yet supported. Cycling fixes or more than 100 passes report an error without applying partial formatting.

Fix All and fix on save

Fix All combines fixes across the whole document into one action, even when only some diagnostics are visible. Like formatting, it repeats safe, non-overlapping fixes until stable, including issues exposed by earlier fixes. Individual quick fixes remain available.

As an alternative to formatting on save, add this to your settings.json:

{
  "[googlesql]": {
    "editor.codeActionsOnSave": {
      "source.fixAll.bigquerySQLFormatter": "explicit"
    }
  }
}

The generic "source.fixAll": "explicit" setting also includes this extension.

Disable Rules

In-line directive

To disable all rules for a line add comment -- noqa. Directives are case-insensitive and accept zero or more spaces after -- (for example --NOQA or -- noqa). Only actual line comments are directives, not text inside strings or block comments.

select case when cc.enddate is null then 1 else null end as is_current -- noqa
  from dataset.table cc;

To disable specific rules for a line add comment -- noqa followed by a comma separated list of rules. Rule codes are matched exactly, ignoring case: -- noqa: LT01, CV05 disables only those rules, while -- noqa: LT0 does not disable LT01.

select case when cc.enddate is null then 1 else null end as is_current -- noqa: ST01
  from dataset.table cc;

Contributing

For development, testing, adding rules, and release guidance, see Contributing.