💻 Coding
SQLFluff Config Builder: Team SQL Style Guide to .sqlfluff Rules, dbt Templater Setup, and a Pre-commit Hook
Translate a written SQL style guide into a working SQLFluff setup for a dbt project: a .sqlfluff file with dialect, templater, capitalisation, comma, aliasing, and line length rules, a .sqlfluffignore, a pre-commit hook pinned to your version, and a rollout plan that will not flood an old repo with thousands of lint errors.
0Reviews
Prompt
Act as an analytics engineer who maintains SQLFluff for dbt projects and has rolled linting out to teams without stalling their pull requests. You turn style guide sentences into exact SQLFluff rule codes and config keys, and you say so when a guideline has no rule behind it. Inputs: - Warehouse and SQL dialect: [Dialect] - SQLFluff version pinned in the repo: [SqlfluffVersion] - dbt adapter, project_dir, profiles_dir, and target used in CI: [DbtSetup] - The team's style guide text, pasted: [StyleGuide] - Folders and models that should be ignored or linted later: [LegacyPaths] - Current lint count or a sample failing model, if known: [BaselineErrors] - Output format: [Format] Generate: 1. Mapping table: each StyleGuide sentence, the SQLFluff rule code and name (for example CP01 capitalisation.keywords, LT04 layout.commas, AL01 aliasing.table, AM03 ambiguous.column_references), the config key and value, and NO RULE when nothing in SQLFluff enforces it. 2. A complete .sqlfluff file: [sqlfluff] core with dialect, templater = dbt, max_line_length, and exclude_rules only for guidelines the team rejected; [sqlfluff:templater:dbt] with project_dir, profiles_dir, profile, and target from DbtSetup; [sqlfluff:indentation]; [sqlfluff:layout:type:comma] line_position; and one [sqlfluff:rules:<rule>] section per configured rule. 3. A .sqlfluffignore covering target/, dbt_packages/, and LegacyPaths. 4. A .pre-commit-config.yaml block using the sqlfluff repo at the pinned SqlfluffVersion, the sqlfluff-lint hook, and additional_dependencies for the dbt adapter and sqlfluff-templater-dbt. Note that the dbt templater needs a compilable project and profile in CI, and offer the jinja templater as a faster fallback with its tradeoff. 5. Rollout plan for an existing repo: run sqlfluff lint to get a baseline, apply sqlfluff fix to one folder per pull request, and lint only changed files in CI until the backlog is clear. 6. Three before and after SQL snippets from the StyleGuide so reviewers can see what each rule changes. Constraints: - Only use rule codes and config keys that exist in the pinned SqlfluffVersion; flag anything you are unsure of as VERIFY. - Never claim a rule enforces naming conventions it does not check. - No em dashes.
Instructions
Replace every [bracket] with your details before running. Works on ChatGPT, Claude, and Gemini.
Generated Output
This image was generated using the prompt above.
Examples
Example Input
Dialect: Snowflake SqlfluffVersion: 3.2.5 DbtSetup: dbt-snowflake 1.8, project_dir ., profiles_dir ./ci, profile analytics, target ci StyleGuide: lowercase keywords and functions; lowercase identifiers; leading commas; always alias tables with AS; no SELECT *; group by column names not numbers; max 100 characters per line; CTEs named with a verb like filter_orders LegacyPaths: models/legacy/ and analyses/ BaselineErrors: about 4,100 violations on the last manual run Format: sections 1 to 6 with code blocks
Example Output
1. Mapping table
| Guideline | Rule | Config |
| lowercase keywords | CP01 capitalisation.keywords | capitalisation_policy = lower |
| lowercase functions | CP03 capitalisation.functions | extended_capitalisation_policy = lower |
| lowercase identifiers | CP02 capitalisation.identifiers | extended_capitalisation_policy = lower |
| leading commas | LT04 layout.commas | [sqlfluff:layout:type:comma] line_position = leading |
| alias tables with AS | AL01 aliasing.table | aliasing = explicit |
| no SELECT * | AM04 ambiguous.column_count | keep it enabled; VERIFY it fits dbt staging models that select from sources |
| group by names | AM06 ambiguous.column_references | group_by_and_order_by_style = explicit |
| 100 characters | LT05 layout.long_lines | max_line_length = 100 |
| CTE verb names | NO RULE | enforce in code review or a custom plugin |
2. .sqlfluff
[sqlfluff]
dialect = snowflake
templater = dbt
max_line_length = 100
[sqlfluff:templater:dbt]
project_dir = .
profiles_dir = ./ci
profile = analytics
target = ci
[sqlfluff:indentation]
indent_unit = space
tab_space_size = 4
[sqlfluff:layout:type:comma]
line_position = leading
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower
[sqlfluff:rules:aliasing.table]
aliasing = explicit
[sqlfluff:rules:ambiguous.column_references]
group_by_and_order_by_style = explicit
3. .sqlfluffignore
target/
dbt_packages/
models/legacy/
analyses/
4. .pre-commit-config.yaml
repos:
- repo: https://github.com/sqlfluff/sqlfluff
rev: 3.2.5
hooks:
- id: sqlfluff-lint
additional_dependencies: ['dbt-snowflake==1.8.*', 'sqlfluff-templater-dbt==3.2.5']
Note: the dbt templater compiles every model, so CI needs the ci profile and warehouse credentials. If that is too slow, switch to templater = jinja and accept that dbt macros may lint less precisely.
5. Rollout plan
- Week 1: run sqlfluff lint models/ --format github-annotation to record the 4,100 baseline by folder.
- Weeks 2 to 4: one pull request per folder running sqlfluff fix, reviewed for logic changes only.
- CI meanwhile lints only files changed in the pull request, so new work is clean from day one.
- Remove models/legacy/ from .sqlfluffignore when its folder pass is merged.
6. Before and after
Before: SELECT ORDER_ID, SUM(AMOUNT) FROM ORDERS o GROUP BY 1
After:
select
order_id
, sum(amount) as total_amount
from orders as o
group by order_id