# 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.

Source: https://docs.querylab.io/writing-sql/code-quality/

**Query 1**

```sql
SELECT «warn:*»
FROM `ace-analytics.warehouse.fact_mot_test`
WHERE completed_date >= '2024-01-01'
LIMIT 10
```

SELECT * from large table 'ace-analytics.warehouse.fact_mot_test' (506.7GB).
    Consider selecting specific columns to reduce costs.{" "}
    <code>sql-best-practices(select-star-large-table)</code>
    <br />
    <br />
    View Problem (Alt+F8) · Quick Fix… (Ctrl+.)

## Reading a squiggle

| Underline | Severity | Means                                                           |
| --------- | -------- | --------------------------------------------------------------- |
| Red       | Error    | BigQuery will reject this                                       |
| Orange    | Warning  | Runs, but costs more or does something you probably didn't mean |
| Blue      | Info     | Worth knowing; no action required                               |
| None      | Hint     | Shown only in the quick-fix menu when the cursor is on the code |

Hover an underline for the message; every message ends with the rule id in parentheses. `⌥ F8` (`Alt F8` 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.

**Quick Fix**

```sql
WITH tests AS (
  SELECT make, test_result
  FROM `ace-analytics.warehouse.fact_mot_test`
),
vehicles AS (
  SELECT make, «err: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:

| Rule                                                      | Fix inserted                                                                             |
| --------------------------------------------------------- | ---------------------------------------------------------------------------------------- |
| SELECT \* on Large Table                                  | Expand SELECT \* to explicit columns                                                     |
| Missing Partition Filter / Missing Cluster Filter         | Add `column` filter: a WHERE condition on the partition or cluster column                |
| Missing LIMIT on Large Table                              | Add LIMIT 1000 (or `\|> LIMIT 1000` in pipe syntax)                                      |
| Wildcard Table Scan Without Filter                        | Add `_TABLE_SUFFIX` filter: today only, last 7 days, last 30 days                        |
| Large Table Scan                                          | Add TABLESAMPLE SYSTEM (1 PERCENT); see [TABLESAMPLE](https://docs.querylab.io/writing-sql/tablesample/)         |
| Undefined Column / Table Alias / CTE                      | Did you mean '…'? (nearest name, STRUCT fields marked)                                   |
| Ambiguous Column Reference                                | Qualify with 'alias.column'                                                              |
| Non-Aggregated Column                                     | Add 'column' to GROUP BY · Use GROUP BY ALL                                              |
| Aggregate in WHERE                                        | Change WHERE to HAVING · Move aggregate condition to existing HAVING                     |
| NULL Comparison with Equals                               | Replace with IS NULL / IS DISTINCT FROM                                                  |
| COUNT(DISTINCT) Performance                               | Replace with APPROX_COUNT_DISTINCT                                                       |
| Cross Join Detected                                       | Use explicit CROSS JOIN · Move condition to ON clause                                    |
| Unused CTE                                                | Remove unused CTE 'name'                                                                 |
| Suggest QUALIFY / LIKE ANY / GROUP BY ALL / UNION BY NAME | Convert to QUALIFY clause · Replace with LIKE ANY · Use GROUP BY ALL · Use UNION BY NAME |

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()`:

```sql
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
```

```sql
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

```sql
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`.

## Thresholds and disabling rules

Settings → Code Quality (also listed under [Settings](https://docs.querylab.io/settings/#code-quality)). Two sections: **Cost Optimization** holds the thresholds, **Diagnostic Rules** the on/off switch for every rule.

<div class="set-g">Query Pattern Warnings</div>
  - Warn about SELECT * on tables larger than: 1 GB. Show warnings when using SELECT * on large tables that could scan excessive data.
  - Warn about missing LIMIT on tables larger than: 1 GB. Show warnings when queries lack LIMIT clause on large tables.
  - Warn about scanning tables larger than: 100 GB. Show warnings when querying very large tables without filters.
  <div class="set-g">Partition &amp; Clustering</div>
  - Warn about missing partition filters (selected): Alert when querying partitioned tables without filtering the partition column (critical for BigQuery cost optimization).
  - Warn about missing cluster filters (selected): Alert when querying clustered tables without filtering cluster columns (improves query performance).

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

| Rule                               | Severity | Catches                                                            |
| ---------------------------------- | -------- | ------------------------------------------------------------------ |
| SELECT \* on Large Table           | Warning  | `SELECT *` on a table above the threshold                          |
| Missing Partition Filter           | Warning  | Partitioned table with no filter on the partition column           |
| Missing Cluster Filter             | Warning  | Clustered table with no filter on a cluster column                 |
| Missing LIMIT on Large Table       | Info     | No LIMIT on a large table                                          |
| ORDER BY Without LIMIT             | Warning  | Sorting a large table with no LIMIT, the "Resources exceeded" case |
| Wildcard Table Scan Without Filter | Warning  | `table_*` with no `_TABLE_SUFFIX` filter                           |
| Large Table Scan                   | Hint     | Table above the scan threshold, no filter, no sampling             |
| Join with OR Condition             | Warning  | `ON a = b OR c = d`                                                |
| Cross Join Detected                | Info     | Explicit or comma cross join                                       |
| COUNT(DISTINCT) Performance        | Hint     | `COUNT(DISTINCT x)` where an approximation would do                |
| Clustering Order Suboptimal        | Hint     | WHERE conditions out of clustering-key order                       |

### Code Quality

| Rule                   | Severity | Catches                                               |
| ---------------------- | -------- | ----------------------------------------------------- |
| Unused CTE             | Warning  | A CTE nothing references                              |
| Duplicate CTE          | Error    | Two CTEs with one name                                |
| Duplicate Column Alias | Warning  | The same alias twice in a SELECT list                 |
| Suggest QUALIFY Clause | Hint     | Subquery wrapper around a window function             |
| Suggest GROUP BY ALL   | Hint     | A GROUP BY that uses position numbers                 |
| Suggest UNION BY NAME  | Hint     | UNION arms with the same columns in a different order |
| Suggest LIKE ANY       | Hint     | Chained `LIKE … OR LIKE …` on one column              |

### Correctness

| Rule                           | Severity | Catches                                        |
| ------------------------------ | -------- | ---------------------------------------------- |
| Non-Aggregated Column          | Error    | Column neither grouped nor aggregated          |
| Aggregate in WHERE             | Error    | `WHERE SUM(x) > 0`                             |
| Nested Aggregate               | Error    | `SUM(COUNT(x))`                                |
| Non-Boolean WHERE              | Error    | `WHERE user_id`                                |
| NULL Comparison with Equals    | Error    | `= NULL`, `<> NULL`                            |
| Missing Join ON                | Error    | JOIN with no ON                                |
| Missing Join Condition         | Error    | JOIN whose condition doesn't relate the tables |
| ORDER BY Not in SELECT         | Error    | With DISTINCT                                  |
| ORDER BY Invalid Position      | Error    | `ORDER BY 0`                                   |
| ORDER BY Position Out of Range | Error    | `ORDER BY 5` with four columns                 |
| CASE Type Inconsistency        | Warning  | Branches of different types                    |
| Conflicting Column Alias       | Warning  | Alias that shadows a column                    |
| Multi-level aggregation        | Error    | The forms BigQuery rejects                     |

### Semantic

| Rule                         | Severity | Catches                                       |
| ---------------------------- | -------- | --------------------------------------------- |
| Undefined Column             | Error    | Column not in any table in scope              |
| Undefined Table Alias        | Error    | `x.col` where `x` isn't declared              |
| Ambiguous Column Reference   | Warning  | Unqualified name present in two joined tables |
| Ambiguous Star Expansion     | Warning  | Bare `*` over a JOIN                          |
| Duplicate Column             | Warning  | Two result columns with one name              |
| String in Numeric Comparison | Warning  | `odometer > '1000'`                           |
| Invalid Hierarchical Name    | Error    | Not `project.dataset.table`                   |

**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](https://docs.querylab.io/writing-sql/charts/) and [Graph queries](https://docs.querylab.io/writing-sql/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](https://docs.querylab.io/bigquery/column-aliases/) 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