All jobs

Add SQLFluff Linting to a dbt Project

Yes. An agent wires SQLFluff with the dbt templater into a dbt project's pre-commit hooks and a GitHub Actions job, fixes the first lint's findings, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads what the project already has before it adds a tool. In a project shaped like dbt-labs/jaffle-shop, dbt_project.yml names jaffle_shop version 3.0.0 with require-dbt-version: ">=2.0.0", staging models as views and marts as tables; models/staging holds seven stg_* models and models/marts seven more, each beside a .yml; macros/cents_to_dollars.sql dispatches per adapter with default__, postgres__, bigquery__ and fabric__ variants, and macros/generate_schema_name.sql routes seeds to a raw schema; packages.yml pins dbt_utils 1.4.1 and dbt-audit-helper from git. A .sqlfluff file is already checked in: dialect = snowflake, max_line_length = 80, four-space indents, lowercase keywords, identifiers, functions and literals, explicit table and column aliasing, implicit group by style, and templater = dbt-cloud, a templater only the dbt Cloud IDE provides. .sqlfluffignore skips target, dbt_packages, macros and .venv. But .pre-commit-config.yaml runs only pre-commit-hooks v6.0.0 and ruff-pre-commit v0.14.10, requirements.in lists pre-commit, requests and jafgen, and .github/workflows/ci.yml triggers dbt Cloud PR jobs for Snowflake, BigQuery and Postgres through scripts/dbt_cloud_run_job.py. The README.md says the config was kept to future-proof the repository; nothing runs it.

The agent makes the config runnable outside the IDE. It adds sqlfluff, sqlfluff-templater-dbt and dbt-postgres to requirements.in and regenerates requirements.txt with the uv pip compile requirements.in -o requirements.txt command the file's header records. In .sqlfluff it keeps dialect = snowflake and the rule sections as they are, and adds a [sqlfluff:templater:dbt] block with project_dir = . and profiles_dir = .ci, so a --templater dbt on the command line renders ref, source, dbt.date_trunc and the project's own cents_to_dollars the way dbt does, rather than tripping over them as unknown Jinja. .ci/profiles.yml holds a default profile with a postgres target that reads its host and credentials from environment variables, because the dbt templater compiles the project through a dbt adapter and the project already has a Postgres environment in dbt Cloud. profiles.yml at the root stays gitignored.

Then the two places it runs. .pre-commit-config.yaml gains the sqlfluff/sqlfluff repository with the sqlfluff-lint and sqlfluff-fix hooks, additional_dependencies of sqlfluff-templater-dbt and dbt-postgres, and --templater dbt in args, so pre-commit run --all-files lints models/ the way the README's pre-commit section describes for ruff. .github/workflows/lint.yml runs on pull_request: actions/setup-python 3.12 and uv pip install -r requirements.txt --system, the same two steps ci.yml uses, a postgres service for the templater's connection, dbt deps so dbt_utils resolves, then sqlfluff lint models --templater dbt --format github-annotation-native so each finding lands as an annotation on the changed line. The existing dbt Cloud jobs are not touched.

The first lint is part of the work. The agent runs sqlfluff lint models --templater dbt, reads every rule code it reports, applies sqlfluff fix where the rule is safe to auto-fix, edits by hand where it is not, and re-runs until the models pass. The models already use lowercase keywords, trailing commas and explicit orders.customer_id style aliases, so the pull request lists what the lint found and what changed, model by model, rather than claiming a count up front. dbt parse runs after the fixes to prove the project still compiles.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "codex",
  "input": "Add SQLFluff linting to this dbt project and open a pull request. Read dbt_project.yml, .sqlfluff, .sqlfluffignore, .pre-commit-config.yaml, requirements.in, requirements.txt, packages.yml, macros/, models/staging/stg_orders.sql, models/marts/orders.sql, README.md and .github/workflows/ci.yml first. Add sqlfluff, sqlfluff-templater-dbt and dbt-postgres to requirements.in and regenerate requirements.txt with the uv pip compile command in its header. Keep the existing .sqlfluff rules and dialect; add a [sqlfluff:templater:dbt] section pointing at the project and a .ci/profiles.yml with a postgres target read from environment variables. Add the sqlfluff-lint and sqlfluff-fix hooks with the dbt templater to .pre-commit-config.yaml, and add .github/workflows/lint.yml on pull_request that installs with uv as ci.yml does, runs dbt deps, starts a postgres service and runs sqlfluff lint models --templater dbt with GitHub annotations. Do not change the dbt Cloud workflows. Run the lint, fix every finding with sqlfluff fix or by hand, run dbt parse, and paste the final sqlfluff output and the list of rules fixed per model in the pull request.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that edits requirements.in, requirements.txt, .sqlfluff and .pre-commit-config.yaml, adds .ci/profiles.yml and .github/workflows/lint.yml, and touches whichever model files the first lint flagged. The description shows the final sqlfluff lint models output, a per-model list of rule codes that were fixed, and the dbt parse summary.

On a repository with the existing dbt Cloud CI workflow the pull request still runs the three Cloud jobs, and the new lint job runs beside them with findings as annotations on the diff. The Cloud jobs build and test the models, so a fix that changed a model's meaning would fail there.

A reviewer reads the model diffs for anything beyond layout, checks that .ci/profiles.yml holds no credential, and confirms the dialect stays snowflake for the IDE while the templater runs on Postgres. Then they merge and run pre-commit install locally.

Run it on every dbt project without SQL linting

A team with several dbt projects sends one POST /o/{org}/sessions per repository with this prompt and that repository in resources. Each run reads its own .sqlfluff, or writes one from the project's dominant style when there is none, and its own CI, so a project on BigQuery gets a bigquery target in the templater profile and each opens its own pull request with its own first-lint fixes.

Give the API key a webhook_url and Rush POSTs run.completed when each turn ends, with the session_url to read the pull request and the lint output from.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

Adding a linter is one commit; making the first run green is the afternoon. The dbt templater needs the adapter, the packages and a profile before it renders a single ref, and every rule that fires needs a judgement about whether the auto-fix changes meaning. In a Rush sandbox the agent installs the pinned requirements, runs dbt deps, runs the lint, fixes, re-runs and runs dbt parse, and keeps every command and its output in the run's record, so the pull request shows the lint passing rather than asserting it will.

This repository's checks run in dbt Cloud against three warehouses, which a laptop cannot reproduce. The lint job the agent adds runs on the pull request itself, and the same request body works on every dbt project a team owns.

Frequently asked questions

Why the dbt templater and not jinja?

The models call dbt.date_trunc and the project's own cents_to_dollars, which only resolve when dbt renders the project. The dbt templater compiles through the adapter and sees both; the jinja templater would fail on them as unknown names or render them wrong.

Does the lint need a warehouse?

The dbt templater opens the adapter when it compiles, so the workflow starts a postgres service and points the templater profile at it. No models are run and no data is loaded; the service exists so dbt can compile.

Will it reformat all my models?

Only where a rule fires. The project already follows most of its own .sqlfluff, and the pull request lists each model with the rule codes that changed it, so a reviewer sees exactly which lines moved and why.

Why keep dialect snowflake if the templater uses Postgres?

The dialect decides how SQLFluff parses the rendered SQL; the templater only decides how Jinja renders. The models are written for Snowflake in dbt Cloud, so parsing as Snowflake keeps the rules honest, and the pull request notes the one place a Postgres-only cast would differ.

More dbt jobs

More jobs like this: Hand off a long task

Keep reading