sqlfluff

SQLFluff is a dialect-flexible and configurable SQL linter designed for ELT applications that works with Jinja templating and dbt. It provides comprehensive SQL code analysis including syntax checking, style validation, and formatting with support for multiple SQL dialects.

Key Features:

Multi-Dialect Support: Compatible with ANSI SQL, BigQuery, Snowflake, Postgres, MySQL, SparkSQL, and more SQL dialects
Jinja Templating: Native support for Jinja templating commonly used in dbt and other ELT frameworks
Auto-Fix Capability: Automatically repairs most linting errors and formatting issues with --fix option
Configurable Rules: Extensive rule set with granular configuration options for team-specific standards
dbt Integration: Seamless integration with dbt projects including macro and model analysis
Detailed Reporting: Comprehensive error reporting with line numbers, positions, and fix suggestions
Custom Rules: Extensible architecture allowing custom rule development for specific requirements
Performance Optimized: Fast parsing and analysis even for large SQL codebases

sqlfluff documentation

Version in MegaLinter: 4.2.2
Visit Official Web Site
See How to configure sqlfluff rules
- If custom .sqlfluff config file isn't found, .sqlfluff will be used
See Index of problems detected by sqlfluff

Configuration in MegaLinter

Enable sqlfluff by adding SQL_SQLFLUFF in ENABLE_LINTERS variable
Disable sqlfluff by adding SQL_SQLFLUFF in DISABLE_LINTERS variable

Enable autofixes by adding SQL_SQLFLUFF in APPLY_FIXES variable

Variable	Description	Default value
SQL_SQLFLUFF_ARGUMENTS	User custom arguments to add in linter CLI call Ex: `-s --foo "bar"`
SQL_SQLFLUFF_COMMAND_REMOVE_ARGUMENTS	User custom arguments to remove from command line before calling the linter Ex: `-s --foo "bar"`
SQL_SQLFLUFF_FILTER_REGEX_INCLUDE	Custom regex including filter Ex: `(src\\|lib)`	Include every file
SQL_SQLFLUFF_FILTER_REGEX_EXCLUDE	Custom regex excluding filter Ex: `(test\\|examples)`	Exclude no file
SQL_SQLFLUFF_CLI_LINT_MODE	Override default CLI lint mode - `file`: Calls the linter for each file - `list_of_files`: Call the linter with the list of files as argument - `project`: Call the linter from the root of the project	`list_of_files`
SQL_SQLFLUFF_FILE_EXTENSIONS	Allowed file extensions. `"*"` matches any extension, `""` matches empty extension. Empty list excludes all files Ex: `[".py", ""]`	`[".sql"]`
SQL_SQLFLUFF_FILE_NAMES_REGEX	File name regex filters. Regular expression list for filtering files by their base names using regex full match. Empty list includes all files Ex: `["Dockerfile(-.+)?", "Jenkinsfile"]`	Include every file
SQL_SQLFLUFF_PRE_COMMANDS	List of bash commands to run before the linter	None
SQL_SQLFLUFF_POST_COMMANDS	List of bash commands to run after the linter	None
SQL_SQLFLUFF_UNSECURED_ENV_VARIABLES	List of env variables explicitly not filtered before calling SQL_SQLFLUFF and its pre/post commands	None
SQL_SQLFLUFF_CONFIG_FILE	sqlfluff configuration file name Use `LINTER_DEFAULT` to let the linter find it	`.sqlfluff`
SQL_SQLFLUFF_RULES_PATH	Path where to find linter configuration file	Workspace folder, then MegaLinter default rules
SQL_SQLFLUFF_DISABLE_ERRORS	Run linter but consider errors as warnings	`false`
SQL_SQLFLUFF_DISABLE_ERRORS_IF_LESS_THAN	Maximum number of errors allowed	`0`
SQL_SQLFLUFF_CLI_EXECUTABLE	Override CLI executable	`['sqlfluff']`

MegaLinter Flavors

This linter is available in the following flavors

Flavor	Description	Embedded linters
all	Default MegaLinter Flavor	136
c_cpp	Optimized for pure C/C++ projects	59
cupcake	MegaLinter for the most commonly used languages	92
documentation	MegaLinter for documentation projects	52
dotnet	Optimized for C, C++, C# or VB based projects	67
dotnetweb	Optimized for C, C++, C# or VB based projects with JS/TS	76
go	Optimized for GO based projects	54
java	Optimized for JAVA based projects	57
javascript	Optimized for JAVASCRIPT or TYPESCRIPT based projects	62
php	Optimized for PHP based projects	57
python	Optimized for PYTHON based projects	69
ruby	Optimized for RUBY based projects	53
rust	Optimized for RUST based projects	53
salesforce	Optimized for Salesforce based projects	59
swift	Optimized for SWIFT based projects	53
terraform	Optimized for TERRAFORM based projects	56

Behind the scenes

How are identified applicable files

Activated only if one of these files is found: .sqlfluff
File extensions: .sql

How the linting is performed

sqlfluff is called once with the list of files as arguments (list_of_files CLI lint mode)

Example calls

sqlfluff myfile.sql

sqlfluff --config .sqlfluff myfile.sql myfile2.sql

Help content

Usage: sqlfluff [OPTIONS] COMMAND [ARGS]...

  SQLFluff is a modular SQL linter for humans.

Options:
  --version   Show the version and exit.
  -h, --help  Show this message and exit.

Commands:
  dialects  Show the current dialects available.
  fix       Fix SQL files.
  format    Autoformat SQL files.
  lint      Lint SQL files via passing a list of files or using stdin.
  parse     Parse SQL files and just spit out the result.
  render    Render SQL files and just spit out the result.
  rules     Show the current rules in use.
  version   Show the version of sqlfluff.

  Examples:

  .. code-block:: sh

     sqlfluff lint --dialect postgres .

     sqlfluff lint --dialect mysql --rules ST05 my_query.sql

     sqlfluff fix --dialect sqlite --rules LT10,ST05 src/queries

     sqlfluff parse --dialect duckdb --templater jinja path/my_query.sql

Installation on mega-linter Docker image

Dockerfile commands :

# renovate: datasource=pypi depName=sqlfluff
ARG PIP_SQLFLUFF_VERSION=4.2.2

PIP packages (Python):
- sqlfluff==4.2.2

Known errors and resolutions

When this linter fails for a known non-lint reason (remote service unavailable, malformed config, missing credentials, etc.), MegaLinter detects the pattern below in the linter output and surfaces the matching guidance.

SQL_SQLFLUFF_ERROR_DIALECT_NOT_SET

Detection pattern (regex):

(Must specify '--dialect' or specify dialect in config|No dialect was specified)

Resolution guidance:

sqlfluff requires an explicit SQL dialect to parse files. Without one, it cannot lint anything.
Resolutions:
  - Add a `.sqlfluff` config at the repository root with the appropriate dialect, e.g.:
      [sqlfluff]
      dialect = postgres
  - Or pass `--dialect <name>` via `SQL_SQLFLUFF_ARGUMENTS` (e.g. ansi, postgres, snowflake, bigquery, mysql, tsql).

SQL_SQLFLUFF_ERROR_TEMPLATING_FAILED

Detection pattern (regex):

(templating/parsing errors found|Unrecoverable failure in Jinja templating|dbt compilation [Ee]rror|Have you configured your variables\?)

Resolution guidance:

sqlfluff could not render a templated SQL file (Jinja, dbt, Python templater).
This is a templating/environment issue, not a lint finding.
Resolutions:
  - For dbt projects, pre-install dbt and prepare the dbt project via pre-commands in your .mega-linter.yml:
      SQL_SQLFLUFF_PRE_COMMANDS:
        - command: "pip install dbt-core dbt-postgres"
          venv: sqlfluff
          continue_if_failed: false
        - command: "dbt deps && dbt compile"
          cwd: "workspace"
          continue_if_failed: false
  - For Jinja, define missing variables under `[sqlfluff:templater:jinja:context]` in `.sqlfluff`.
  - For files that should not be templated, set `templater = raw` in `.sqlfluff`.

SQL_SQLFLUFF_ERROR_CONFIG_INVALID

Detection pattern (regex):

(Error loading config|Configuration (error|failed)|Unable to parse \.sqlfluff)

Resolution guidance:

sqlfluff failed to load its configuration file.
Resolutions:
  - Validate `.sqlfluff` syntax (INI format) and check for typos in section headers like `[sqlfluff]` or `[sqlfluff:rules]`.
  - Ensure rule references (e.g. `exclude_rules`, `rules`) point to existing rule codes for the installed sqlfluff version.