Let's try: dbt part 11 - SQLFluff & Pre-Commit
format script and check before deploying
To align all scripts in our codebase into one standard, we integrate an SQL formatter to format them and do the checks before deploying.
SQLFluff the formatter
SQLfluff is a great tool to format SQL scripts. Once setup, it will format every SQL files to improve readability and we are also able to configure to align with standards of the team.
Installation
Because we are using dbt, then SQLFluff and dbt formatter version of SQLfluff should be installed via:
1
2
3
4
5
6
7
# pip
pip install sqlfluff sqlfluff-templater-dbt
sqlfluff --version
# uv
uv add sqlfluff sqlfluff-templater-dbt
uv run sqlfluff --version
If the installation is complete, the version number must be showed properly.
Basic usage
Now we have sqlfluff command. There are several commands sqlfluff provides but I usually use lint and fix.
lintis to check if the format is correct.1 2 3 4 5
# sqlfluff installed via pip sqlfluff lint # check any errors in sql format # sqlfuff installed via uv uv run sqlfluff lint # check any errors in sql format
Here is the example output of
lint.1 2 3 4 5 6 7 8 9 10
$ uv run sqlfluff lint models/mart/weekly_avg_cases_per_country.sql === [dbt templater] Sorting Nodes... === [dbt templater] Compiling dbt project... === [dbt templater] Project Compiled. == [models/mart/weekly_avg_cases_per_country.sql] FAIL L: 4 | P: 5 | LT05 | Line is too long (131 > 80). | [layout.long_lines] L: 4 | P: 30 | LT01 | Unexpected whitespace before 'partition' keyword. | [layout.spacing] All Finished 📜 🎉!
The Line number, Position, LT rule codes and description are displayed.
fixwill find and format the files.1 2 3 4 5
# sqlfluff installed via pip sqlfluff fix # format the sql files # sqlfuff installed via uv uv run sqlfluff fix # format the sql files
1 2 3 4 5 6 7 8 9 10 11 12
$ uv run sqlfluff fix models/mart/weekly_avg_cases_per_country.sql ==== finding fixable violations ==== === [dbt templater] Sorting Nodes... === [dbt templater] Compiling dbt project... === [dbt templater] Project Compiled. == [models/mart/weekly_avg_cases_per_country.sql] FAIL L: 4 | P: 5 | LT05 | Line is too long (130 > 80). | [layout.long_lines] L: 4 | P: 30 | LT01 | Unexpected whitespace before 'partition' keyword. | [layout.spacing] == [models/mart/weekly_avg_cases_per_country.sql] FIXED 2 fixable linting violations found
All fixable violations now are fixed.
Configure SQLFluff
We can configure format and rule for sqlfluff in the project.
Let’s say the project folders look like this.
1
2
3
4
5
6
7
8
9
10
11
12
.
├── .sqlfluff <<-- SQLfluff configs
├── .sqlfluffignore <<-- SQLFluff ignore files
├── README.md
└─── dbt-bq
├── analyses
├── logs
├── macros
├── models
├── seeds
├── snapshots
└── tests
The content of .sqlfluff can be written as below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[sqlfluff]
dialect = bigquery
templater = dbt
runaway_limit = 10
max_line_length = 80
indent_unit = space
large_file_skip_byte_limit = 30000
[sqlfluff:indentation]
tab_space_size = 4
[sqlfluff:layout:type:comma]
spacing_before = touch
line_position = trailing
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower
[sqlfluff:rules:aliasing.table]
aliasing = explicit
[sqlfluff:rules:aliasing.column]
aliasing = explicit
[sqlfluff:rules:aliasing.expression]
allow_scalar = False
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower
[sqlfluff:rules:ambiguous.column_references] # Number in group by
group_by_and_order_by_style = implicit
[sqlfluff:templater:dbt]
project_dir = dbt-bq
profile_dir = dbt-bq
And also able to ignore files and folders from sqlfluff by creating .sqlfluffignore.
1
2
3
4
5
dbt-bq/reports
dbt-bq/target
dbt-bq/dbt_packages
dbt-bq/macros
.venv
Pre-commit the checkers
And the second, pre-commit is a checker tool that verify all necessary assets before we push to the repo.
I have posted the introduction of pre-commit here.
Example configs
Here the example uses dbt-checkpoint and sqlfluff to ensure all assets and formats are in check.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
# yaml-language-server: $schema=https://www.schemastore.org/pre-commit-config.json
repos:
- repo: https://github.com/dbt-checkpoint/dbt-checkpoint
rev: v2.0.6
hooks:
- id: check-script-has-no-table-name
files: ^dbt_bq/models/
- id: check-script-ref-and-source
files: ^dbt_bq/models/
- id: check-model-has-properties-file
files: ^dbt_bq/models/
- id: check-model-columns-have-desc
files: ^dbt_bq/models/
- id: check-model-has-description
files: ^dbt_bq/models/
- id: check-model-has-contract
files: ^dbt_bq/models
- id: check-macro-has-description
files: ^dbt_bq/macros/
- id: check-macro-arguments-have-desc
files: ^dbt_bq/macros/
- repo: https://github.com/sqlfluff/sqlfluff
rev: "3.4.1"
hooks:
- id: sqlfluff-lint
additional_dependencies:
- "dbt-bigquery"
- "sqlfluff-templater-dbt"
args:
- --config=.sqlfluff
- id: sqlfluff-fix
additional_dependencies:
- "dbt-bigquery"
- "sqlfluff-templater-dbt"
args:
- --config=.sqlfluff
Wrap up
sqlfluffto format the SQL scripts.sqlfluff lintto check any errorssqlfluff fixto correct the errors.
pre-committo check changes before push to repo.dbt-checkpointto check dbt assets.sqlfluffto check SQL format.