Formatting with sqlfmt: one style, zero debate
Stop arguing about commas. sqlfmt rewrites every model to the same shape, on your laptop and in CI.
Alice's project now has five contributors, and every review keeps snagging on style. Tabs or spaces, leading or trailing commas, uppercase or lowercase keywords. None of it changes what the query does, but all of it shows up in code review and clutters every diff. A formatter ends the debate: it reformats SQL to one agreed style automatically, so a pull request only shows the lines that actually changed. This lesson sets up sqlfmt. You install the package shandy-sqlfmt, and the command it gives you is sqlfmt.
What sqlfmt buys you
One style, everywhere
Every model reads the same, whoever wrote it. Onboarding gets easier and diffs stop lying about what changed.
Zero human effort
It rewrites the file. Nobody spends a review comment re-indenting a JOIN ever again.
Jinja included
The jinjafmt extra formats {{ ref() }} and {% if %} blocks too, not just the SQL around them.
Checked in CI
A CI job flags unformatted SQL on every merge request (GitLab's word for a pull request), so the standard holds without anyone policing it. Warn first, block once the project passes.
The flow, end to end
One tool, three checkpoints. You format locally, pre-commit catches what you forgot, and CI checks the merge request:
On this team, pre_prod and pro are Git branches as well as dbt targets, and each branch goes with the target of the same name. You open a merge request from your own branch into pre_prod. While it's open, CI checks it and builds it into target pre_prod. After the merge, a deploy job ships the code (part 3). Merging pre_prod into pro later releases it, and Airflow runs target pro. Steps 12 to 15 follow this rule.
Install it
Formatting tools are dev dependencies: laptops and CI need them, production doesn't. So keep two lists. Both files sit at the project root, next to dbt_project.yml. Step 1 ran git init there, so that's also the repo root:
# requirements.txt: what production installs dbt-core~=1.10.0 # any 1.10.x: the adapter alone would allow a newer dbt-core dbt-snowflake==1.10.2
# requirements-dev.txt: production's list, plus the dev tools -r requirements.txt # pulls in dbt-core and dbt-snowflake, pinned pre-commit==4.3.0 # runs checks on every commit sqlfluff==3.0.7 # a linter: flags likely mistakes (last box) shandy-sqlfmt[jinjafmt]==0.27.0 # sqlfmt, plus the Jinja formatter dbt-score==0.19.0 # step 12 dbt-duckdb==1.10.1 # local and CI runs, step 15
pip install -r requirements-dev.txt installs both lists. The square brackets ask pip for an extra, an optional add-on of the package: here, the Jinja formatter. An Airflow repo (part 3) adds its Python linters, like ruff, to the same dev file.
The config: a few lines in pyproject.toml
sqlfmt is opinionated on purpose: the style itself isn't configurable. What you can set lives under [tool.sqlfmt] in pyproject.toml, a small settings file in TOML format that many Python tools read. It sits at the project root too, and dbt-score adds its own section to the same file in the next step:
# pyproject.toml, at the project root (next to dbt_ [tool.project. yml) sqlfmt] line_length = 90 # the only style knob (default 88) exclude = ["**/target/ , "**/**/ *" dbt_ ] # never touch build output or packagespackages/ **/ *"
Every key matches a command-line flag (--line-length, --exclude), and a flag wins over the file. There's also --dialect, the SQL flavour, but the default handles Snowflake, so you don't set it. What sqlfmt enforces, always:
- Lowercase keywords and names. Safe on Snowflake: unquoted names ignore case, so
Ordersandordersare the same table. Quoted names like"Name"stay untouched. - Trailing commas and four-space indentation.
- Short lines merged: anything that fits within the line length goes on one line.
Lowercase and trailing commas aren't objectively right. The point is that nobody reopens the question: the tool answered it, once, for everyone.
Same query, before and after
A rushed model, then the same logic after sqlfmt. The query is too long for one line, so it splits at each clause. The select list fits in 90 characters, so it stays on one line. A longer one would get one column per line, each ending with a comma:
-- before: works, but every reviewer reads it differently SELECT id,Name, sum(amount) AS Total FROM Orders o JOIN customers c on o.cust_ id= c. id WHERE status='open' group by 1,2
-- after: one shape the whole project shares select id, name, sum(amount) as total from orders o join customers c on o.cust_id = c.id where status = 'open' group by 1, 2
Run it locally
sqlfmt rewrites files in place, with no prompt, so commit your work first. Then, from the project root:
# format every .sql file under this folder (models, macros, tests...), minus the excludes sqlfmt . # what you'll see: # 1 file formatted. # 259 files left unchanged. # models/marts/ # what CI runs: report only, change nothing sqlfmt --check .fct_ orders. sql formatted.
Every command ends with an exit code: 0 means success, anything else means failure. --check exits with 1 when a file needs formatting, and CI fails any job whose command exits non-zero.
Switch on pre-commit
pre-commit runs checks on the files you're about to commit. Tell it about sqlfmt in one more file at the project root:
# .pre-commit-config. repos: - repo: https:yaml, at the project root / rev: v0.27.0 # same version as requirements-dev./ github. com/ tconbeer/ sqlfmt txt hooks: - id: sqlfmt additional_dependencies : [".[jinjafmt]" ] # "." = sqlfmt itself, plus the extra
Then run pre-commit install once per clone. From then on, git commit runs sqlfmt on your staged files. If it reformats one, the commit stops: git add the file and commit again.
Wire it into CI
Local commands rely on people remembering. The pipeline doesn't forget. Here's a complete GitLab CI file with one job. GitLab reads .gitlab-ci.yml from the repo root:
# .gitlab-ci. stages: - test # stages run in order; jobs in one stage run together check_format: stage: test image: python:3.12 script: - pip install "shandy-sqlfmt[jinjafmt]=yml, at the project root = - sqlfmt --check . rules: # only on merge requests into pre_prod - if: '$CI_0. 27. 0" MERGE_ allow_REQUEST_ TARGET_ BRANCH_ NAME == "pre_prod"' failure : true # warn, don't block, during rollout
On GitHub, the same two script lines go in a step of an Actions workflow.
On a bigger platform, nobody writes this job by hand. A platform team keeps it in one shared, versioned CI project, and every repo includes it. Part 3's platform calls that project astro-ci-cd-pipelines (nothing here depends on it). A repo there looks like this:
# .gitlab-ci. include: - project: 'data-platform/yml on that platform: include the job instead of writing it astro-ci-cd-pipelines' ref: 'v0.0.2' # pinned tag: upgrades are explicit file: 'check_format. # holds a check_format job like the one above stages: [test, build, deploy] # build: pre_prod (step 15); deploy: part 3 check_format: allow_yml' failure : true # keys you set here override the included job
Where the check actually fires
Quality gates sit where humans review. The check runs on the merge request into pre_prod. The later promotion from pre_prod to pro ships code that already passed, so it only deploys. In GitLab, Build → Pipelines shows it:
pre_prodproallow_failure: true, an unformatted file shows a warning on the merge request instead of blocking it, so nobody is stopped mid-release. Once sqlfmt --check . passes on the whole project (the job turns green), the team lead removes that line, and the check blocks.requirements-dev.txt, answers "is this SQL any good?": unused columns, ambiguous references, dialect mistakes. This guide doesn't configure it. If you add it, switch off its layout rules (sqlfmt's docs show how), so the two never argue about shape.
