SQLFluff, i.e. keeping SQL queries clean

https://github.com/bartosz25/databricks-playground/tree/main/sqlfluff-dab

Surprisingly it may sound, keeping code formatted correctly is not only the matter of Python. If you are more of a SQL user, you can also implement additional linting controls with a library called SQLFluff.

4-day workshop · In-person or online

What would it take for you to trust your Databricks pipelines in production?

A 3-day bug hunt on a 3-person team costs up to €7,200 in lost engineering time. This workshop teaches you to prevent that — unit tests, data tests, and integration tests for PySpark and Databricks Lakeflow, including Spark Declarative Pipelines.

Unit, data & integration tests

Medallion architecture & Lakeflow SDP

Max 10 participants · production-ready templates

See the full curriculum → €7,000 flat fee · cohort of up to 10

Bartosz
Konieczny

SQLFluff 101

In a nutshell, SQLFluff is a linter for SQL designed for ELT workflows. Its main features include:

• Many database dialects making it a choice you may find in various data projects. One of the supported dialects is of course Apache Spark SQL, so inherently Databricks platform.
• Customized rules that you can implement as simple Python files.
• Support for Jinja templates. You can keep your queries as templates and still leverage the linting for the not dynamic parts of the queries.
• Auto-fix capability that automatically corrects linting issues.
• Native integration to the pre-commit library with the linting and auto-fixing hooks.

Configuration

SQLFluff has many configuration entries but the documentation recommends keeping things simple, which is a great recommendation for bootstrapping the projects. Consequently, the properties to look at are:

• dialect to know how SQLFluff should understand and lint your queries
• templater if you use a templating engine for queries
• exclude_rules to define rules that should be ignored during the linting or rules to explicitly enable list of rules to use
• sqlfluff:rules:capitalisation:(keywords,identifiers,functions,literals,types) to, respectively, define the capitalisation strategy for keywords,identifiers,functions,literals and types
• warnings to transform blocking issues into warning notifications.
• max_line_length to set the max line number, exactly like you do for Python files to keep them readable
• ignore_comment_lines to ignore comments either from the indentation or from the lines length check rules

config = FluffConfig(overrides={"dialect": "databricks"})

linter_result = Linter(config=config).lint_string(get_active_users_query(table_name=table_for_linting))

A minimal configuration file for Apache Spark SQL/Databricks could then look like that:

[sqlfluff]
dialect = None
templater = raw
max_line_length = 80
exclude_rules = LT08, RF02

SQLFluff and Declarative Automation Bundles

Due to the file formats limitation - after all you won't be able to easily validate SQL queries written in your Python project - SQLFluff easily integrates with SQL or dbt projects on Lakeflow Jobs. Here is how you can define a Poe task to validate the SQL queries either as a pre-commit hook, or/and as part of your CI/CD process:

[tool.poe.tasks.validate_code]
help ="Lints and checks for formatting issues."
shell = """
sqlfluff lint queries/
"""

If you run it against the queries present in the Github repo for this blog post, you should see a few linting errors:

If so far you have been thinking that linting SQL queries is just a dream, SQLFluff shows it's not.

Data Engineering Design Patterns

Looking for a book that defines and solves most common data engineering problems? I wrote one on that topic! You can read it online on the O'Reilly platform, or get a print copy on Amazon.

I also help solve your data engineering problems contact@waitingforcode.com 📩