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

# 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

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

# 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

# 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

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

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

# 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

[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

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

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

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

[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

-- 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)

-- 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)

-- 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)

-- 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:

[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)

-- 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)

-- 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:

[sqlfluff:layout:type:comma]
line_position = trailing    # or "leading"

References (RF)

-- 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)

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

-- 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)

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

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

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

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

# .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\`.`,
              '',
              '<details><summary>Full lint output</summary>',
              '',
              '```',
              output,
              '```',
              '</details>'
            ].join('\n');
            await github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body
            });

      - name: Fail workflow if violations found
        if: steps.lint.outputs.exit_code != '0'
        run: |
          echo "SQLFluff found violations. See annotations above."
          exit 1

Workflow 2: Lint + Autofix (commit style fixes back to PR branch)

This workflow detects violations, applies sqlfluff fix, and pushes the fixed files back to the PR branch as a new commit. Use only if you trust auto-fixes and your branch protection rules allow workflow commits.

# .github/workflows/sqlfluff-autofix.yml
name: SQLFluff Autofix

on:
  pull_request:
    branches: [main]
    paths:
      - 'dbt/models/**/*.sql'
      - 'dbt/macros/**/*.sql'

permissions:
  contents: write               # required to push the fix commit
  pull-requests: write

concurrency:
  group: sqlfluff-fix-${{ github.ref }}
  cancel-in-progress: true

jobs:
  autofix:
    name: SQLFluff Autofix
    runs-on: ubuntu-24.04
    timeout-minutes: 20

    steps:
      # Check out the HEAD commit of the PR branch (not the merge commit)
      - uses: actions/checkout@v4
        with:
          ref: ${{ github.head_ref }}
          # Use a PAT or GitHub App token if your branch protection requires
          # signed commits or prevents GITHUB_TOKEN from pushing
          token: ${{ secrets.GITHUB_TOKEN }}

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"
          cache: pip

      - name: Install sqlfluff
        run: |
          pip install --upgrade pip
          pip install "sqlfluff==3.3.0" "sqlfluff-templater-dbt==3.3.0" dbt-trino==1.8.5

      - name: Install dbt packages
        working-directory: dbt
        run: dbt deps

      - name: sqlfluff fix
        working-directory: dbt
        run: |
          sqlfluff fix models/ macros/ \
            --dialect trino \
            --force \
            --nocolor

      # Commit and push only if files were changed
      - name: Commit and push fixes
        uses: stefanzweifel/git-auto-commit-action@v5
        with:
          commit_message: "style: sqlfluff autofix [skip ci]"
          file_pattern: "dbt/models/**/*.sql dbt/macros/**/*.sql"
          commit_author: "github-actions[bot] <github-actions[bot]@users.noreply.github.com>"
          # [skip ci] prevents this commit from triggering another workflow run

Matrix strategy — lint multiple dialects / dbt targets

# .github/workflows/sqlfluff-matrix.yml
name: SQLFluff Multi-Dialect

on:
  pull_request:
    paths: ['sql/**/*.sql', '.sqlfluff']

jobs:
  lint:
    name: "Lint — ${{ matrix.dialect }}"
    runs-on: ubuntu-24.04
    timeout-minutes: 10

    strategy:
      fail-fast: false          # run all dialects even if one fails
      matrix:
        include:
          - dialect: trino
            path: sql/trino/
            sqlfluff_config: .sqlfluff.trino
          - dialect: postgres
            path: sql/postgres/
            sqlfluff_config: .sqlfluff.postgres
          - dialect: clickhouse
            path: sql/clickhouse/
            sqlfluff_config: .sqlfluff.clickhouse

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
          cache: pip

      - name: Install sqlfluff
        run: pip install "sqlfluff==3.3.0"

      - name: Lint ${{ matrix.dialect }}
        run: |
          sqlfluff lint ${{ matrix.path }} \
            --dialect ${{ matrix.dialect }} \
            --config ${{ matrix.sqlfluff_config }} \
            --format github-annotation-native \
            --annotation-level warning

Caching pip dependencies

      # actions/setup-python with cache: pip caches the pip download cache
      # For faster installs, also cache the virtual environment
      - name: Cache pip venv
        uses: actions/cache@v4
        with:
          path: ~/.cache/pip
          key: sqlfluff-${{ runner.os }}-pip-${{ hashFiles('**/requirements*.txt') }}
          restore-keys: |
            sqlfluff-${{ runner.os }}-pip-

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.11"
          cache: pip            # built-in pip cache in setup-python

VS Code Integration

Install the extension

Install the SQLFluff extension by dbt Labs (extension ID: dbt-labs.sqlfluff), or the SQLFluff extension by RobertOstrowskiSmith (older, community-maintained).

From the command palette: ext install dbt-labs.sqlfluff

.vscode/settings.json

{
    // SQLFluff extension settings
    "sqlfluff.executablePath": "${workspaceFolder}/.venv/bin/sqlfluff",
    "sqlfluff.linter.run": "onSave",           // "onSave" | "onType" | "off"
    "sqlfluff.linter.lintEntireProject": false, // lint only the open file
    "sqlfluff.config": "${workspaceFolder}/.sqlfluff",

    // Dialect (overrides .sqlfluff for the extension)
    "sqlfluff.dialect": "trino",

    // Templater (use "jinja" for VS Code to avoid needing dbt connection)
    "sqlfluff.linter.arguments": ["--templater", "jinja"],

    // Format on save using sqlfluff fix
    "[sql]": {
        "editor.defaultFormatter": "dbt-labs.sqlfluff",
        "editor.formatOnSave": true
    },

    // Show problems in the Problems panel
    "sqlfluff.linter.diagnosticSeverity": "error",      // error | warning | info | hint
    "sqlfluff.linter.diagnosticSeverityByRule": {
        "LT05": "warning",    // long lines — warning, not error
        "ST06": "info"        // SELECT * — informational
    },

    // File associations — lint .sql.j2 files as SQL
    "files.associations": {
        "*.sql.j2": "sql",
        "*.sql": "sql"
    }
}

Workspace-level settings for a dbt project

// .vscode/settings.json — dbt project with dbt Power User extension
{
    "sqlfluff.executablePath": "${workspaceFolder}/.venv/bin/sqlfluff",
    "sqlfluff.dialect": "trino",

    // Use jinja templater in VS Code — dbt templater requires a live dbt environment
    // which is impractical for real-time linting
    "sqlfluff.linter.arguments": [
        "--templater", "jinja",
        "--exclude-rules", "LT13"
    ],

    "[jinja-sql]": {
        "editor.defaultFormatter": "dbt-labs.sqlfluff",
        "editor.formatOnSave": true,
        "editor.formatOnSaveMode": "file"
    },

    // dbt Power User extension — set dbt path
    "dbt.dbtPythonPathOverride": "${workspaceFolder}/.venv/bin/python"
}

Problem matcher for inline error display

If you run sqlfluff via a VS Code task, use this problem matcher to show violations inline:

// .vscode/tasks.json
{
    "version": "2.0.0",
    "tasks": [
        {
            "label": "SQLFluff: Lint",
            "type": "shell",
            "command": "${workspaceFolder}/.venv/bin/sqlfluff lint ${file} --dialect trino",
            "group": "test",
            "presentation": { "reveal": "always", "panel": "shared" },
            "problemMatcher": {
                "owner": "sqlfluff",
                "fileLocation": ["absolute"],
                "pattern": {
                    "regexp": "^\\s+L:\\s*(\\d+)\\s+\\|\\s+P:\\s*(\\d+)\\s+\\|\\s+(\\w+)\\s+\\|\\s+(.*)$",
                    "file": 0,
                    "line": 1,
                    "column": 2,
                    "code": 3,
                    "message": 4
                }
            }
        },
        {
            "label": "SQLFluff: Fix",
            "type": "shell",
            "command": "${workspaceFolder}/.venv/bin/sqlfluff fix ${file} --dialect trino --force",
            "group": "test"
        }
    ]
}

Custom Rules

SQLFluff supports plugin-based custom rules. Rules are Python classes that walk the parse tree and emit LintResult objects.

Plugin structure

my_sqlfluff_plugin/
├── setup.cfg              (or pyproject.toml)
├── my_sqlfluff_plugin/
│   ├── __init__.py
│   └── rules.py

setup.cfg

[metadata]
name = my_sqlfluff_plugin
version = 0.1.0

[options]
packages = find:
install_requires =
    sqlfluff>=3.0.0

# Register the rule plugin via entry_points
[options.entry_points]
sqlfluff = my_sqlfluff_plugin = my_sqlfluff_plugin.rules

my_sqlfluff_plugin/rules.py

"""Custom SQLFluff rules for our data engineering team."""
from typing import Optional

from sqlfluff.core.rules import BaseRule, LintResult, RuleContext
from sqlfluff.core.rules.crawlers import SegmentSeekerCrawler


class Rule_MY01(BaseRule):
    """CTEs must have a preceding comment explaining their purpose.

    **Anti-pattern**

    .. code-block:: sql

        WITH raw_orders AS (
            SELECT * FROM orders
        )
        SELECT * FROM raw_orders

    **Best practice**

    .. code-block:: sql

        -- raw_orders: source data from the orders table before any transformations
        WITH raw_orders AS (
            SELECT * FROM orders
        )
        SELECT * FROM raw_orders
    """

    name = "custom.cte_comment"
    aliases = ("MY01",)
    groups = ("all", "custom")
    # The crawler determines which node types this rule visits
    crawl_behaviour = SegmentSeekerCrawler({"cte_definition"})
    is_fix_compatible = False  # this rule cannot be auto-fixed

    def _eval(self, context: RuleContext) -> Optional[LintResult]:
        """Check that each CTE definition is preceded by a comment."""
        segment = context.segment

        # Walk backwards through siblings to find a comment before this CTE
        raw_stack = context.raw_stack
        for prior_segment in reversed(raw_stack):
            if prior_segment.is_type("comment", "block_comment", "inline_comment"):
                return None  # comment found → pass
            if prior_segment.is_type("cte_definition", "with_compound_statement"):
                break  # hit another CTE without finding a comment → fail
            if not prior_segment.is_whitespace and not prior_segment.is_newline:
                break  # non-whitespace non-comment found → fail

        return LintResult(
            anchor=segment,
            description=(
                f"CTE '{segment.get_child('cte_definition').raw}' "
                "must be preceded by a comment explaining its purpose."
            ),
        )


class Rule_MY02(BaseRule):
    """SELECT * is forbidden in mart-layer models (models/marts/**).

    Only applies when the file path contains 'marts/'. Staging models
    may use SELECT * as a passthrough pattern.

    **Anti-pattern** (in models/marts/)

    .. code-block:: s

…(truncated)