Course contents

The Analytics Engineer’s Field Guidedbt 3: ship it like a teamStep 11: Formatting (sqlfmt)

Formatting (sqlfmt)

Part 2 · dbt · step 11

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:

⌨️
Your laptop
sqlfmt .
→
pre-commit
formats on commit
→
MR → pre_prod
CI check_format
→
pro
deploy only, no check

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.

Why pin every version?
Five sqlfmt versions produce five different "correct" formats, and the diff wars come back. Pinned, the output is identical for everyone, CI included.

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_project.yml)
[tool.sqlfmt]
line_length = 90                                 # the only style knob (default 88)
exclude = ["**/target/**/*", "**/dbt_packages/**/*"]   # never touch build output or packages

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 Orders and orders are 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/fct_orders.sql formatted.

# what CI runs: report only, change nothing
sqlfmt --check .

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.

Read that output like a health check
"259 files left unchanged" is the goal state: the project already agrees with the style. The first run on an existing project can reformat hundreds of files. Make that one dedicated commit ("format everything, no logic change"), so the noise never mixes with real changes.

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.yaml, at the project root
repos:
  - repo: https://github.com/tconbeer/sqlfmt
    rev: v0.27.0                        # same version as requirements-dev.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.yml, at the project root
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]==0.27.0"
    - sqlfmt --check .
  rules:                       # only on merge requests into pre_prod
    - if: '$CI_MERGE_REQUEST_TARGET_BRANCH_NAME == "pre_prod"'
  allow_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.yml on that platform: include the job instead of writing it
include:
  - project: 'data-platform/astro-ci-cd-pipelines'
    ref: 'v0.0.2'               # pinned tag: upgrades are explicit
    file: 'check_format.yml'    # holds a check_format job like the one above

stages: [test, build, deploy]   # build: pre_prod (step 15); deploy: part 3

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

merge into
stages that run
why
pre_prod
test → build on the open MR, deploy after the merge
the review door: format is checked here, while a human is already looking
pro
deploy
a promotion of already-checked code, re-checking would only slow the release
Roll it out as a warning first
With allow_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.
sqlfluff still has a seat
sqlfmt answers "what shape is this SQL?". sqlfluff, the linter in 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.
Where it sits
Formatting is the floor: small diffs, reviews about logic. The next step, dbt-score, grades what sits above it (descriptions, tests, naming) as a number you can enforce.

Lesson quiz

  1. What does sqlfmt change in a model?

    1. Only its layout: indentation, casing, commas and line breaks
    2. Its layout and its logic, rewriting slow patterns as faster SQL
    3. Nothing: it lists style problems for you to fix by hand
    4. Its column names, renamed to match the project's conventions
  2. The team wants uppercase keywords and leading commas. What do you add under [tool.sqlfmt] in pyproject.toml?

    1. A .sqlfmtrc file at the root, which overrides pyproject
    2. keyword_case = "upper" and comma_style = "leading"
    3. Nothing: the style isn't configurable beyond line_length
    4. dialect = "snowflake", to switch to Snowflake's house style
  3. Alice's merge request into pre_prod shows check_format in red, yet GitLab still lets her merge. Given the lesson's CI file, why?

    1. pre-commit already formatted the files, so CI's result is ignored
    2. sqlfmt --check prints a diff but always exits with 0
    3. The job has allow_failure: true, so it warns instead of blocking
    4. check_format only blocks merge requests into the pro branch