# Sqlfluff
> SQLFluff SQL linter — .sqlfluff config, dialect selection (ansi/bigquery/clickhouse/duckdb/hive/postgres/snowflake/sparksql/trino), rule sets, templating (Jinja/dbt), fix command, VS Code integration, pre-commit hook, CI/CD GitHub Actions, custom rules, noqa inline suppression
- Skill: `ivanshamaev/sqlfluff` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ivanshamaev/sqlfluff`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ivanshamaev/sqlfluff/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: ivanshamaev (https://skillmd.com/u/ivanshamaev)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/ivanshamaev/sqlfluff
---
# SQLFluff — SQL Linter and Auto-Formatter
## When to Use
Load this skill when the user needs to:
- Enforce consistent SQL style across a team or codebase (keyword case, indentation, comma placement, alias conventions)
- Set up CI quality gates that reject non-conforming SQL before merge
- Lint a dbt project's `.sql` model files using the dbt templater (primary use case)
- Automatically fix style violations with `sqlfluff fix`
- Clean up legacy SQL that has grown inconsistent over time
- Write a `.sqlfluff` configuration file or tune specific rules
- Configure a pre-commit hook to lint SQL locally before push
- Create a GitHub Actions workflow that annotates PRs with inline SQL violations
- Suppress specific rule violations inline with `-- noqa` comments
- Write a custom SQLFluff rule as a plugin
---
## Installation and Basic Usage
### Install
```bash
# Core linter (ANSI dialect, Jinja templater)
pip install sqlfluff
# dbt templater (required for dbt projects — separate package)
pip install sqlfluff-templater-dbt
# With the dbt adapter for your warehouse (needed by the dbt templater)
pip install sqlfluff-templater-dbt dbt-trino==1.8.5
pip install sqlfluff-templater-dbt dbt-postgres==1.8.2
pip install sqlfluff-templater-dbt dbt-bigquery==1.8.2
pip install sqlfluff-templater-dbt dbt-snowflake==1.8.4
# Pin sqlfluff version for reproducible CI
pip install "sqlfluff==3.3.0" "sqlfluff-templater-dbt==3.3.0"
```
### List available dialects
```bash
sqlfluff dialects
# ansi, athena, bigquery, clickhouse, databricks, db2, duckdb, exasol,
# greenplum, hive, materialize, mysql, oracle, postgres, redshift,
# snowflake, soql, sparksql, sqlite, teradata, trino, tsql, vertica
```
### Lint a file or directory
```bash
# Lint a single file
sqlfluff lint path/to/query.sql
# Lint a directory recursively
sqlfluff lint models/
# Specify dialect on the command line
sqlfluff lint models/ --dialect trino
# Lint and emit GitHub PR annotations (for CI)
sqlfluff lint models/ --format github-annotation-native --annotation-level failure
# Human-readable output (default)
sqlfluff lint models/ --format human
# Machine-readable output
sqlfluff lint models/ --format json
sqlfluff lint models/ --format yaml
# Exit codes:
# 0 — no violations
# 1 — lint violations found
# 2 — linter error (parse error, config issue)
```
Sample output:
```
== [models/staging/stg_orders.sql] FAIL
L: 5 | P: 1 | LT02 | Expected 4 spaces of indentation, found 2. [layout.indent]
L: 8 | P: 12 | AL01 | Implicit/explicit aliasing of table. [aliasing.table]
L: 15 | P: 5 | CP01 | Keywords must be consistently upper case. [capitalisation.keywords]
All Finished! 📛 3 violations found in 1 out of 1 files.
```
### Fix violations automatically
```bash
# Fix safe violations only (default)
sqlfluff fix models/staging/stg_orders.sql
# Fix all violations including potentially unsafe ones (e.g. whitespace inside strings)
sqlfluff fix models/ --force
# Preview what would be fixed without writing (dry run)
sqlfluff fix models/ --check
# Fix only specific rules
sqlfluff fix models/ --rules LT01,LT02,CP01
```
**Safe vs unsafe fixes:** By default `sqlfluff fix` only applies fixes that are guaranteed to be semantically equivalent (whitespace, casing, trailing newlines). Fixes that could change semantics (e.g. rewriting `!=` to `<>`) are marked unsafe and require `--force` or individual rule confirmation.
### Parse tree inspection
```bash
# Dump the parse tree — useful when writing custom rules or debugging parse errors
sqlfluff parse models/staging/stg_orders.sql
# Output as JSON
sqlfluff parse models/staging/stg_orders.sql --format json
```
---
## `.sqlfluff` Configuration File
Place `.sqlfluff` at the root of your project (or dbt project directory). SQLFluff walks up the directory tree to find the nearest config file.
```ini
# .sqlfluff — complete annotated configuration for a dbt + Trino project
[sqlfluff]
# --- Dialect ---
# Required. Must match your warehouse. Controls keyword recognition and parsing.
# Options: ansi | bigquery | clickhouse | duckdb | hive | postgres | snowflake |
# sparksql | trino | tsql | vertica (see `sqlfluff dialects`)
dialect = trino
# --- Templater ---
# "dbt" — uses dbt to render Jinja; requires sqlfluff-templater-dbt + dbt adapter
# "jinja" — renders Jinja directly without dbt; use for non-dbt projects
# "python"— Python string.Template; rarely used
# "raw" — no templating (fastest, for plain SQL files)
templater = dbt
# --- Line length ---
max_line_length = 120
# --- Indentation ---
# "space" or "tab"
indent_unit = space
tab_space_size = 4
# --- File extensions to lint ---
sql_file_exts = .sql,.sql.j2
# --- Rules to run ---
# Default: all rules. Specify a comma-separated allowlist to run only these rules.
# rules = AL01,AL02,CP01,LT01,LT02,LT04,LT05
# --- Rules to exclude globally ---
# RF04: reserved words as identifiers — noisy in many real-world schemas
# CV03: COUNT(*) vs COUNT(1) — team preference varies
# AM04: COUNT DISTINCT syntax — dialect-dependent
exclude_rules = RF04,CV03
# --- Output format ---
# human | json | yaml | github-annotation-native | github-annotation
output_line_format = {code} {description}
# --- Parallel execution (see Performance section) ---
# processes = 4
# --- Ignore files / directories (glob patterns) ---
ignore = dbt_packages, target, logs, .venv
[sqlfluff:indentation]
# Whether JOIN clauses are indented relative to SELECT
indented_joins = false
# Whether CTEs are indented inside the WITH block
indented_ctes = false
# Whether the ON / USING clause of a JOIN is indented
indented_using_on = true
# Whether Jinja/dbt template blocks ({% if %}, {% for %}) contribute to indentation
# Set false for dbt to avoid double-indenting SQL inside Jinja blocks
template_blocks_indent = false
[sqlfluff:layout:type:comma]
# "trailing" — comma at end of line (most common in SQL)
# "leading" — comma at start of next line (some teams prefer this)
line_position = trailing
spacing_before = touch
spacing_after = single
[sqlfluff:layout:type:comparison_operator]
# Force comparison operators to have spaces on both sides
spacing_before = single
spacing_after = single
[sqlfluff:rules:capitalisation.keywords]
# "upper" — SELECT, FROM, WHERE ...
# "lower" — select, from, where ...
# "capitalise" — Select, From, Where ...
# "consistent" — match whatever is used first in the file
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.identifiers]
# How column/table/alias identifiers should be cased
# "lower" is typical for most warehouses; "consistent" lets teams mix
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
# Built-in function names: COALESCE, COUNT, SUM, etc.
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.literals]
# NULL, TRUE, FALSE
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
# Data type keywords: INT, VARCHAR, TIMESTAMP, etc.
capitalisation_policy = upper
[sqlfluff:rules:aliasing.table]
# "explicit" — require AS keyword in aliases: FROM orders AS o
# "implicit" — forbid AS keyword: FROM orders o
# "consistent"— match whatever style appears first
aliasing = explicit
[sqlfluff:rules:aliasing.column]
# Same options as aliasing.table
aliasing = explicit
[sqlfluff:rules:aliasing.expression]
# Require aliases on complex expressions in SELECT
# "explicit" | "implicit" | "consistent"
aliasing = explicit
[sqlfluff:rules:ambiguous.column_references]
# Forbid bare column references in GROUP BY / ORDER BY (use column names, not numbers)
# "consistent" | "qualified" | "unqualified"
group_by_and_order_by_style = consistent
[sqlfluff:rules:references.from]
# Require every column reference in SELECT to be qualified with table alias
# when more than one table is in scope
# "consistent" | "qualified" | "unqualified"
# force_enable = false # set true to always enforce qualification
[sqlfluff:rules:convention.not_equal]
# Which NOT EQUAL operator to enforce
# "consistent" | "<>" | "!="
preferred_not_equal_style = consistent
[sqlfluff:rules:convention.count_rows]
# "consistent" | "zero" (COUNT(0)) | "star" (COUNT(*)) | "expression" (COUNT(1))
preferred_count_rows_expression = star
[sqlfluff:templater:jinja]
# Context variables for standalone Jinja templating (templater = jinja).
# Not used when templater = dbt — dbt provides its own context.
[sqlfluff:templater:jinja:context]
# Define Jinja variables that appear in your SQL templates
# my_date = '2024-01-01'
# env_name = 'prod'
```
---
## dbt Integration
The dbt templater is the most common production use case. It uses dbt's own Jinja engine to render templates before linting, so `{{ ref() }}`, `{{ source() }}`, `{{ config() }}`, and custom macros all resolve correctly.
### Installation
```bash
# Both packages must be on the same minor version
pip install "sqlfluff==3.3.0" "sqlfluff-templater-dbt==3.3.0" "dbt-trino==1.8.5"
# Always run dbt deps before linting — the templater needs dbt packages
dbt deps
```
### `.sqlfluff` for a dbt project
```ini
[sqlfluff]
dialect = trino
templater = dbt
max_line_length = 120
indent_unit = space
tab_space_size = 4
# Rules commonly excluded in dbt projects (explained below)
exclude_rules = RF04,ST06,AM04
[sqlfluff:indentation]
indented_joins = false
indented_ctes = false
template_blocks_indent = false # CRITICAL for dbt — prevents double-indent inside {% if %}
[sqlfluff:templater:dbt]
# Path to the dbt project root (contains dbt_project.yml)
# Default: current directory
project_dir = ./
# Path to profiles.yml
# Default: ~/.dbt/
profiles_dir = ./
# Which profile name to use (must exist in profiles.yml)
# Default: value of "profile:" in dbt_project.yml
profile = my_project
# Which target in the profile to use for linting
# Use a lightweight "dev" or "ci" target that doesn't need a live connection
# (sqlfluff only renders templates; it doesn't execute SQL)
target = dev
```
### Running sqlfluff on a dbt project
```bash
# From the dbt project root
sqlfluff lint models/
# Lint specific subdirectories
sqlfluff lint models/staging/ models/marts/
# Lint a single model
sqlfluff lint models/marts/finance/fct_orders.sql
# Pass dialect explicitly (overrides .sqlfluff)
sqlfluff lint models/ --dialect trino
# Fix style issues in all models
sqlfluff fix models/ --force
# Lint only changed files (useful locally, before commit)
git diff --name-only HEAD | grep '\.sql$' | xargs sqlfluff lint --dialect trino
```
### How dbt templating works with SQLFluff
SQLFluff invokes dbt's compilation engine to render each model before parsing. This means:
| Template construct | Behavior |
|---|---|
| `{{ ref('stg_orders') }}` | Resolved to the fully-qualified table name |
| `{{ source('raw', 'orders') }}` | Resolved to the source table reference |
| `{{ config(materialized='table') }}` | Block is stripped before linting |
| `{% if target.name == 'prod' %}` | **Rendered** — both branches are linted if `execute=True` |
| `{{ my_macro(...) }}` | Expanded via dbt macro resolution |
| `{{ var('my_var') }}` | Requires the variable to be defined in `dbt_project.yml` or passed via `--vars` |
**What doesn't work:**
- Macros from packages not installed (`dbt deps` not run)
- `execute` context functions (only available at runtime, not compile)
- Models with `{{ run_query() }}` inside macros that produce SQL
### Example: well-linted dbt model
```sql
-- models/marts/finance/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
)
}}
WITH source AS (
SELECT *
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
),
enriched AS (
SELECT
o.order_id,
o.customer_id,
o.status,
o.amount_usd,
o.created_at,
c.country_code
FROM source AS o
LEFT JOIN {{ ref('dim_customers') }} AS c
ON o.customer_id = c.customer_id
)
SELECT *
FROM enriched
```
### Common dbt-specific rule exclusions and why
```ini
[sqlfluff]
exclude_rules =
RF04, # Reserved words as identifiers — dbt uses "ref", "source" etc. as identifiers
ST06, # SELECT wildcards — dbt staging models legitimately use SELECT *
AM04, # COUNT(*) with DISTINCT — dialect-specific; trino supports it natively
CV10, # SELECT * forbidden — same as ST06; use whichever rule name your version uses
LT13 # Start of file — dbt models start with {{ config(...) }}, not a SQL statement
```
**Rule-specific explanation:**
- **RF04** (`reserved_words`): dbt macros like `{{ ref(...) }}` get rendered as identifiers that sometimes match reserved words depending on the table name.
- **ST06 / CV10** (`select_wildcards`): Staging models intentionally use `SELECT *` to pass through all source columns. Suppress at the model level or globally.
- **LT13** (`start_of_file`): dbt models start with a Jinja `{{ config(...) }}` block, which SQLFluff sees as a non-SQL start. This fires LT13 on every model.
- **template_blocks_indent = false**: Without this, SQL inside `{% if is_incremental() %}` gets double-indented. Always set this in dbt projects.
### Selective inline suppression in dbt models
```sql
-- Suppress SELECT * on a specific staging model
SELECT * -- noqa: ST06
FROM {{ ref('raw_events') }}
-- Suppress a specific rule on a JOIN that legitimately uses implicit style
FROM orders o -- noqa: AL01
LEFT JOIN customers c ON o.customer_id = c.customer_id -- noqa: AL01
```
---
## Rule Reference
Rules were renamed from `L001`-style codes to semantic names in SQLFluff 2.0. Always use the new names. The old `L0XX` codes still work as aliases but are deprecated.
### Aliasing (AL)
| Rule | Name | Violation | Fix |
|---|---|---|---|
| AL01 | `aliasing.table` | `FROM orders o` | `FROM orders AS o` |
| AL02 | `aliasing.column` | `SELECT id + 1 order_id` | `SELECT id + 1 AS order_id` |
| AL03 | `aliasing.expression` | `SELECT 1 + 1` (no alias) | `SELECT 1 + 1 AS two` |
| AL04 | `aliasing.unique` | Two CTEs or tables with same alias | Rename one alias |
| AL05 | `aliasing.unused` | CTE defined but never referenced | Remove the CTE |
| AL06 | `aliasing.length` | Table alias shorter than configured min | Lengthen alias |
| AL07 | `aliasing.forbid` | Aliases forbidden when configured | Remove alias |
| AL08 | `aliasing.column.wildcard` | `SELECT t.*` — qualified wildcard | Configure per project |
| AL09 | `aliasing.self_alias.column` | Column aliased to its own name: `id AS id` | Remove the alias |
```sql
-- AL01 violation
SELECT o.id FROM orders o;
-- AL01 fix
SELECT o.id FROM orders AS o;
-- AL05 violation — unused CTE
WITH unused_cte AS (SELECT 1),
main AS (SELECT * FROM orders)
SELECT * FROM main;
-- AL05 fix — remove the unused CTE
WITH main AS (SELECT * FROM orders)
SELECT * FROM main;
```
### Ambiguous (AM)
| Rule | Name | Description |
|---|---|---|
| AM01 | `ambiguous.distinct` | `SELECT DISTINCT COUNT(x)` — DISTINCT inside aggregate |
| AM02 | `ambiguous.union` | Bare `UNION` (not `UNION ALL`) is almost always a mistake |
| AM03 | `ambiguous.order_by` | Numeric ORDER BY column references |
| AM04 | `ambiguous.column_count` | `COUNT(DISTINCT ...)` — style enforcement |
| AM05 | `ambiguous.join` | JOIN without explicit type (INNER/LEFT/RIGHT/CROSS) |
| AM06 | `ambiguous.select_star_then_whitelisted` | `SELECT *, col` — mixing * with named columns |
```sql
-- AM02 violation: UNION removes duplicates, which is usually unintended
SELECT id FROM a
UNION
SELECT id FROM b;
-- AM02 fix: be explicit
SELECT id FROM a
UNION ALL
SELECT id FROM b;
-- AM05 violation: ambiguous join type
SELECT * FROM a JOIN b ON a.id = b.id;
-- AM05 fix
SELECT * FROM a INNER JOIN b ON a.id = b.id;
```
### Capitalisation (CP)
| Rule | Name | Description |
|---|---|---|
| CP01 | `capitalisation.keywords` | SELECT, FROM, WHERE, JOIN, etc. |
| CP02 | `capitalisation.identifiers` | table_name, column_name |
| CP03 | `capitalisation.functions` | COALESCE, COUNT, SUM, UPPER |
| CP04 | `capitalisation.literals` | NULL, TRUE, FALSE |
| CP05 | `capitalisation.types` | INT, VARCHAR, TIMESTAMP |
```sql
-- CP01 violation
select id, name from orders where status = 'active';
-- CP01 fix
SELECT id, name FROM orders WHERE status = 'active';
-- CP03 violation
SELECT coalesce(name, 'unknown') FROM customers;
-- CP03 fix
SELECT COALESCE(name, 'unknown') FROM customers;
```
Configure policy per rule:
```ini
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper # SELECT, FROM, WHERE → uppercase
[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower # table_name, column_name → lowercase
[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = upper # COALESCE, COUNT → uppercase
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper # NULL, TRUE, FALSE → uppercase
[sqlfluff:rules:capitalisation.types]
capitalisation_policy = upper # INT, VARCHAR → uppercase
```
### Convention (CV)
| Rule | Name | Description |
|---|---|---|
| CV01 | `convention.not_equal` | Enforce `<>` vs `!=` consistently |
| CV02 | `convention.coalesce` | Prefer COALESCE over NVL/IFNULL/IIF |
| CV03 | `convention.count_rows` | COUNT(*) vs COUNT(1) vs COUNT(0) |
| CV04 | `convention.is_null` | Use `IS NULL` not `= NULL` |
| CV05 | `convention.casting_style` | CAST(x AS type) vs x::type (dialect-specific) |
| CV06 | `convention.statement_terminator` | Require/forbid trailing semicolons |
| CV07 | `convention.select_union_order` | SELECT before UNION ORDER BY |
| CV08 | `convention.left_join` | Prefer LEFT JOIN over RIGHT JOIN |
| CV09 | `convention.inner_join` | Require INNER keyword (or forbid it) |
| CV10 | `convention.select_wildcards` | Forbid `SELECT *` |
| CV11 | `convention.unnecessary_else` | `CASE WHEN x THEN y ELSE NULL END` → remove ELSE NULL |
```sql
-- CV04 violation
SELECT * FROM orders WHERE cancelled_at = NULL;
-- CV04 fix
SELECT * FROM orders WHERE cancelled_at IS NULL;
-- CV11 violation
SELECT
CASE
WHEN status = 'active' THEN 1
ELSE NULL
END AS is_active
FROM orders;
-- CV11 fix (ELSE NULL is implicit)
SELECT
CASE
WHEN status = 'active' THEN 1
END AS is_active
FROM orders;
-- CV02 violation
SELECT NVL(name, 'unknown') FROM customers;
-- CV02 fix
SELECT COALESCE(name, 'unknown') FROM customers;
```
### Layout (LT)
| Rule | Name | Description |
|---|---|---|
| LT01 | `layout.trailing_whitespace` | Trailing spaces on a line |
| LT02 | `layout.indent` | Indentation depth/character |
| LT03 | `layout.operators` | Spacing around operators |
| LT04 | `layout.commas` | Trailing vs leading comma position |
| LT05 | `layout.long_lines` | Lines exceeding `max_line_length` |
| LT06 | `layout.functions` | Space before function parenthesis |
| LT07 | `layout.cte_bracket` | Spacing around CTE `AS (` |
| LT08 | `layout.cte_newline` | Blank lines between CTEs |
| LT09 | `layout.select_targets` | Each SELECT expression on its own line |
| LT10 | `layout.select_modifiers` | DISTINCT/ALL placement in SELECT |
| LT11 | `layout.set_operators` | UNION/INTERSECT/EXCEPT spacing |
| LT12 | `layout.end_of_file` | Trailing newline at end of file |
| LT13 | `layout.start_of_file` | No leading blank lines at file start |
```sql
-- LT04 violation: leading comma (if trailing configured)
SELECT
id
, name
, status
FROM orders;
-- LT04 fix: trailing comma
SELECT
id,
name,
status
FROM orders;
-- LT09 violation: multiple targets on one line
SELECT id, name, status FROM orders;
-- LT09 fix: each target on its own line
SELECT
id,
name,
status
FROM orders;
```
Configure comma position:
```ini
[sqlfluff:layout:type:comma]
line_position = trailing # or "leading"
```
### References (RF)
| Rule | Name | Description |
|---|---|---|
| RF01 | `references.from` | Referenced tables must exist in FROM clause |
| RF02 | `references.qualification` | Column references must be qualified in multi-table queries |
| RF03 | `references.consistent` | Qualification must be consistent |
| RF04 | `references.keywords` | Avoid reserved words as identifiers |
| RF05 | `references.quoting` | Consistent use of quoting |
| RF06 | `references.special_chars` | Unnecessary quoting of identifiers |
```sql
-- RF02 violation: ambiguous column reference with two tables in scope
SELECT id, name
FROM orders
JOIN customers ON orders.customer_id = customers.id;
-- RF02 fix: qualify all references
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;
-- Or use aliases
SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id;
```
### Structure (ST)
| Rule | Name | Description |
|---|---|---|
| ST01 | `structure.else_null` | Same as CV11 — redundant ELSE NULL |
| ST02 | `structure.simple_case` | `CASE WHEN x = TRUE THEN y` → `CASE WHEN x THEN y` |
| ST03 | `structure.unused_cte` | CTE defined but not referenced (same as AL05) |
| ST04 | `structure.nested_case` | Nested CASE can be flattened |
| ST05 | `structure.subquery` | Subquery in WHERE/FROM could be a CTE |
| ST06 | `structure.select_wildcards` | SELECT * is ambiguous |
| ST07 | `structure.using` | USING clause ambiguity |
| ST08 | `structure.distinct` | DISTINCT in aggregate |
| ST09 | `structure.join_condition_order` | JOIN conditions in consistent order |
```sql
-- ST02 violation: CASE WHEN comparing to TRUE
SELECT
CASE
WHEN is_active = TRUE THEN 'yes'
ELSE 'no'
END
FROM customers;
-- ST02 fix
SELECT
CASE
WHEN is_active THEN 'yes'
ELSE 'no'
END
FROM customers;
-- ST04 violation: nested CASE
SELECT
CASE
WHEN a > 0 THEN
CASE
WHEN b > 0 THEN 'both'
ELSE 'only a'
END
ELSE 'neither'
END
FROM t;
-- ST04 fix: flatten with AND
SELECT
CASE
WHEN a > 0 AND b > 0 THEN 'both'
WHEN a > 0 THEN 'only a'
ELSE 'neither'
END
FROM t;
```
---
## Inline Suppression
Use `-- noqa` comments to suppress violations on specific lines. Use sparingly — prefer fixing the underlying issue.
### Syntax
```sql
-- Suppress ALL rules on this line
SELECT * FROM orders -- noqa
-- Suppress specific rules by code (comma-separated)
SELECT * FROM orders -- noqa: ST06,AL01
-- Suppress on the next line (useful for multi-line expressions)
-- noqa: LT05
SELECT very_long_column_name_1, very_long_column_name_2, very_long_column_name_3 FROM t
-- Block suppression: disable rules for a range of lines
-- noqa: disable=all
SELECT
* -- this is intentional in a staging passthrough model
FROM {{ ref('raw_events') }}
-- noqa: enable=all
-- Block suppression for specific rules
-- noqa: disable=ST06,AL01
SELECT * FROM source_table
-- noqa: enable=ST06,AL01
```
### When to use vs fixing the issue
**Use `-- noqa` when:**
- The violation is a genuine false positive (e.g., a dbt macro expands to a reserved word)
- The pattern is intentional by design (e.g., `SELECT *` in a staging passthrough)
- A rule is overly strict for a specific exceptional case
- Generated SQL that cannot be reformatted
**Fix the underlying issue when:**
- The rule catches a real style inconsistency
- The `-- noqa` would need to be placed on dozens of lines (configure globally instead)
- The violation indicates a potential logic error (CV04, AM02, AM05)
```sql
-- Bad: noqa used to mask a real problem
SELECT * FROM a JOIN b ON a.id = b.id -- noqa: AM05
-- Better: fix the root cause
SELECT * FROM a INNER JOIN b ON a.id = b.id
-- Acceptable: dbt staging passthrough where SELECT * is intentional
SELECT * -- noqa: ST06
FROM {{ source('raw', 'orders') }}
```
---
## Pre-commit Hook
Add SQLFluff to `.pre-commit-config.yaml` to lint (and optionally fix) SQL before every commit.
```yaml
# .pre-commit-config.yaml
repos:
- repo: https://github.com/sqlfluff/sqlfluff
rev: 3.3.0 # pin to a specific release tag
hooks:
# Lint only — fails if violations found, does NOT modify files
- id: sqlfluff-lint
name: SQLFluff Lint
args:
- --dialect=trino
# If using dbt templater, pass these args:
# - --templater=dbt
# - --config=.sqlfluff
additional_dependencies:
# Required for dbt templater:
- sqlfluff-templater-dbt==3.3.0
- dbt-trino==1.8.5
files: '^dbt/models/.*\.sql$' # only lint model files
exclude: '^dbt/target/|dbt_packages/'
# Fix and re-stage — automatically rewrites style violations
- id: sqlfluff-fix
name: SQLFluff Fix
args:
- --dialect=trino
- --force # apply unsafe fixes too
additional_dependencies:
- sqlfluff-templater-dbt==3.3.0
- dbt-trino==1.8.5
files: '^dbt/models/.*\.sql$'
exclude: '^dbt/target/|dbt_packages/'
```
**Important notes for dbt + pre-commit:**
- The dbt templater requires `dbt deps` to have been run before the hook executes. Add a pre-commit hook to run `dbt deps` if needed, or use the Jinja templater instead for pre-commit to avoid this dependency.
- The `additional_dependencies` list installs packages into the pre-commit isolated environment — they must match the versions in your project.
- If `dbt deps` is too slow for pre-commit, consider using `templater = jinja` with stub context variables for the pre-commit hook, and `templater = dbt` in CI only.
```yaml
# Lightweight pre-commit config using jinja templater (no dbt dep required)
repos:
- repo: https://github.com/sqlfluff/sqlfluff
rev: 3.3.0
hooks:
- id: sqlfluff-lint
args: [--dialect=trino, --templater=jinja]
files: '^dbt/models/.*\.sql$'
exclude: '^dbt/target/|dbt_packages/'
```
Install and run hooks:
```bash
# Install pre-commit (once per machine)
pip install pre-commit
# Install hooks into your repo
pre-commit install
# Run manually against all files
pre-commit run sqlfluff-lint --all-files
# Run on staged files only (what happens on git commit)
pre-commit run sqlfluff-lint
```
---
## CI/CD GitHub Actions
### Workflow 1: Lint-only with Native GitHub Annotations
This workflow runs on every PR that touches SQL files and produces inline code annotations on changed lines — no additional GitHub App required.
```yaml
# .github/workflows/sqlfluff-lint.yml
name: SQLFluff Lint
on:
pull_request:
branches: [main]
paths:
- 'dbt/models/**/*.sql'
- 'dbt/macros/**/*.sql'
- 'dbt/tests/**/*.sql'
- '.sqlfluff'
- '.github/workflows/sqlfluff-lint.yml'
permissions:
contents: read
pull-requests: write # required to post PR comments
concurrency:
group: sqlfluff-${{ github.ref }}
cancel-in-progress: true
env:
SQLFLUFF_VERSION: "3.3.0"
DBT_ADAPTER: "dbt-trino==1.8.5"
jobs:
lint:
name: SQLFluff Lint
runs-on: ubuntu-24.04
timeout-minutes: 15
steps:
- uses: actions/checkout@v4
- name: Set up Python
uses: actions/setup-python@v5
with:
python-version: "3.11"
cache: pip
- name: Install sqlfluff + dbt templater
run: |
pip install --upgrade pip
pip install \
"sqlfluff==${{ env.SQLFLUFF_VERSION }}" \
"sqlfluff-templater-dbt==${{ env.SQLFLUFF_VERSION }}" \
"${{ env.DBT_ADAPTER }}"
- name: Install dbt packages
working-directory: dbt
run: dbt deps
# github-annotation-native emits GitHub Actions workflow commands
# that the runner converts into inline PR annotations — no action needed
- name: SQLFluff lint (annotate PR)
id: lint
working-directory: dbt
continue-on-error: true # collect all violations before deciding to fail
run: |
sqlfluff lint models/ macros/ tests/ \
--format github-annotation-native \
--annotation-level failure \
--dialect trino \
2>&1 | tee /tmp/sqlfluff-output.txt
echo "exit_code=${PIPESTATUS[0]}" >> "$GITHUB_OUTPUT"
# Post a collapsible summary comment on the PR
- name: Comment lint results on PR
if: steps.lint.outputs.exit_code != '0'
uses: actions/github-script@v7
with:
script: |
const fs = require('fs');
const raw = fs.readFileSync('/tmp/sqlfluff-output.txt', 'utf8');
// Truncate to GitHub comment size limit (65536 chars)
const output = raw.length > 60000 ? raw.slice(0, 60000) + '\n... (truncated)' : raw;
const body = [
'## SQLFluff Lint Results',
'',
`Found SQL style violations. Fix them with \`sqlfluff fix dbt/models/ --force\`.`,
'',
'Full lint output
',
'',
'```',
output,
'```',
'