Configuration

SQLFluff accepts configuration either through the command line or through configuration files. There is rough parity between the two approaches with the exception that templating configuration must be done via a file, because it otherwise gets slightly complicated.

For details of what’s available on the command line check out the CLI Reference.

For file based configuration SQLFluff will look for the following files in order. Later files will (if found) will be used to overwrite any vales read from earlier files.

  • setup.cfg

  • tox.ini

  • pep8.ini

  • .sqlfluff

  • pyproject.toml

Within these files, the first four will be read like a cfg file, and SQLFluff will look for sections which start with sqlfluff, and where subsections are delimited by a semicolon. For example the jinjacontext section will be indicated in the section started with [sqlfluff:jinjacontext].

For example, a snippet from a .sqlfluff file (as well as any of the supported cfg file types):

[sqlfluff]
templater = "jinja"
sql_file_exts = ".sql,.sql.j2,.dml,.ddl"

[sqlfluff:indentation]
indented_joins = false
indented_using_on = true
template_blocks_indent = false

[sqlfluff:templater]
unwrap_wrapped_queries = true

[sqlfluff:templater:jinja]
apply_dbt_builtins = true

For the pyproject.toml file, all valid sections start with tool.sqlfluff and subsections are delimited by a dot. For example the jinjacontext section will be indicated in the section started with [tool.sqlfluff.jinjacontext].

For example, a snippet from a pyproject.toml file:

[tool.sqlfluff.core]
templater = "jinja"
sql_file_exts = ".sql,.sql.j2,.dml,.ddl"

[tool.sqlfluff.indentation]
indented_joins = false
indented_using_on = true
template_blocks_indent = false

[tool.sqlfluff.templater]
unwrap_wrapped_queries = true

[tool.sqlfluff.templater.jinja]
apply_dbt_builtins = true

Nesting

SQLFluff uses nesting in its configuration files, with files closer overriding (or patching, if you will) values from other files. That means you’ll end up with a final config which will be a patchwork of all the values from the config files loaded up to that path. The exception to this is the value for templater, which cannot be set in config files in subdirectories of the working directory. You don’t need any config files to be present to make SQLFluff work. If you do want to override any values though SQLFluff will use files in the following locations in order, with values from later steps overriding those from earlier:

  1. […and this one doesn’t really count] There’s a default config as part of the SQLFluff package. You can find this below, in the Default Configuration section.

  2. It will look in the user’s os-specific app config directory. On OSX this is ~/Library/Preferences/sqlfluff, Unix is ~/.config/sqlfluff, Windows is <home>\AppData\Local\sqlfluff\sqlfluff, for any of the filenames above in the main Configuration section. If multiple are present, they will patch/override each other in the order above.

  3. It will look for the same files in the user’s home directory (~).

  4. It will look for the same files in the current working directory.

  5. [if parsing a file in a subdirectory of the current working directory] It will look for the same files in every subdirectory between the current working dir and the file directory.

  6. It will look for the same files in the directory containing the file being linted.

This whole structure leads to efficient configuration, in particular in projects which utilise a lot of complicated templating.

Rule Configuration

Rules can be configured with the .sqlfluff config files.

Common rule configurations can be set in the [sqlfluff:rules] section.

For example:

[sqlfluff:rules]
tab_space_size = 4
max_line_length = 80
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all

Rule specific configurations are set in rule specific subsections.

For example, enforce that keywords are upper case by configuring the rule L010:

[sqlfluff:rules:L010]
# Keywords
capitalisation_policy = upper

All possible options for rule sections are documented in Rules Reference.

For an overview of the most common rule configurations that you may want to tweak, see Default Configuration (and use Rules Reference to find the available alternatives).

Enabling and Disabling Rules

To disable individual rules, set exclude_rules in the top level section of sqlfluff configuration. The value is a comma separated list of rule ids.

For example, to disable the rules L022 and L027:

[sqlfluff]
exclude_rules = L022, L027

To enable individual rules, configure rules, respectively.

For example, to enable L027:

[sqlfluff]
rules = L027

If both exclude_rules and rules have non-empty value, then the excluded rules are removed from the rules list. This allows for example enabling common rules on top level but excluding some on subdirectory level.

Additionally, some rules have a special force_enable configuration option, which allows to enable the given rule even for dialects where it is disabled by default. The rules that support this can be found in the Rules Reference.

The default values can be seen in Default Configuration.

See also: Ignoring Errors & Files.

Jinja Templating Configuration

When thinking about Jinja templating there are two different kinds of things that a user might want to fill into a templated file, variables and functions/macros. Currently functions aren’t implemented in any of the templaters.

Variable Templating

Variables are available in the jinja, python and placeholder templaters. By default the templating engine will expect variables for templating to be available in the config, and the templater will be look in the section corresponding to the context for that templater. By convention, the config for the jinja templater is found in the sqlfluff:templater:jinja:context section, the config for the *python templater is found in the sqlfluff:templater:python:context section, the one for the placeholder templater is found in the sqlfluff:templater:placeholder:context section

For example, if passed the following .sql file:

SELECT {{ num_things }} FROM {{ tbl_name }} WHERE id > 10 LIMIT 5

…and the following configuration in .sqlfluff in the same directory:

[sqlfluff:templater:jinja:context]
num_things=456
tbl_name=my_table

…then before parsing, the sql will be transformed to:

SELECT 456 FROM my_table WHERE id > 10 LIMIT 5

Note

If there are variables in the template which cannot be found in the current configuration context, then this will raise a SQLTemplatingError and this will appear as a violation without a line number, quoting the name of the variable that couldn’t be found.

Placeholder templating

Libraries such as SQLAlchemy or Psycopg use different parameter placeholder styles to mark where a parameter has to be inserted in the query.

For example a query in SQLAlchemy can look like this:

SELECT * FROM table WHERE id = :myid

At runtime :myid will be replace by a value provided by the application and escaped as needed, but this is not standard SQL and cannot be parsed as is.

In order to parse these queries is then necessary to replace these placeholders with sample values, and this is done with the placeholder templater.

Placeholder templating can be enabled in the config using:

[sqlfluff]
templater = placeholder

A few common styles are supported:

 -- colon
 WHERE bla = :my_name

 -- numeric_colon
 WHERE bla = :2

 -- pyformat
 WHERE bla = %(my_name)s

 -- dollar
 WHERE bla = $my_name

 -- question_mark
 WHERE bla = ?

 -- numeric_dollar
 WHERE bla = $3

 -- percent
 WHERE bla = %s

 -- ampersand
 WHERE bla = &s or WHERE bla = &{s} or USE DATABASE MARK_{ENV}

These can be configured by setting param_style to the names above:

[sqlfluff:templater:placeholder]
param_style = colon
my_name = 'john'

then it is necessary to set sample values for each parameter, like my_name above. Notice that the value needs to be escaped as it will be replaced as a string during parsing.

When parameters are positional, like question_mark, then their name is simply the order in which they appear, starting with 1.

[sqlfluff:templater:placeholder]
param_style = question_mark
1 = 'john'

In case you need a parameter style different from the ones above, you can pass a custom regex.

[sqlfluff:templater:placeholder]
param_regex = __(?P<param_name>[\w_]+)__
my_name = 'john'

N.B. quotes around param_regex in the config are interpreted literally by the templater. e.g. param_regex=’__(?P<param_name>[w_]+)__’ matches ‘__some_param__’ not __some_param__

the named parameter param_name will be used as the key to replace, if missing, the parameter is assumed to be positional and numbers are used insead.

Also consider making a pull request to the project to have your style added, it may be useful to other people and simplify your configuration.

Complex Variable Templating

Two more advanced features of variable templating are case sensitivity and native python types. Both are illustrated in the following example:

[sqlfluff:templater:jinja:context]
my_list = ['a', 'b', 'c']
MY_LIST = ("d", "e", "f")
my_where_dict = {"field_1": 1, "field_2": 2}
SELECT
    {% for elem in MY_LIST %}
        '{{elem}}' {% if not loop.last %}||{% endif %}
    {% endfor %} as concatenated_list
FROM tbl
WHERE
    {% for field, value in my_where_dict.items() %}
        {{field}} = {{value}} {% if not loop.last %}and{% endif %}
    {% endfor %}

…will render as…

SELECT
    'd' || 'e' || 'f' as concatenated_list
FROM tbl
WHERE
    field_1 = 1 and field_2 = 2

Note that the variable was replaced in a case sensitive way and that the settings in the config file were interpreted as native python types.

Macro Templating

Macros (which also look and feel like functions are available only in the jinja templater. Similar to Variable Templating, these are specified in config files, what’s different in this case is how they are named. Similar to the context section above, macros are configured separately in the macros section of the config. Consider the following example.

If passed the following .sql file:

SELECT {{ my_macro(6) }} FROM some_table

…and the following configuration in .sqlfluff in the same directory (note the tight control of whitespace):

[sqlfluff:templater:jinja:macros]
a_macro_def = {% macro my_macro(n) %}{{ n }} + {{ n * 2 }}{% endmacro %}

…then before parsing, the sql will be transformed to:

SELECT 6 + 12 FROM some_table

Note that in the code block above, the variable name in the config is a_macro_def, and this isn’t apparently otherwise used anywhere else. Broadly this is accurate, however within the configuration loader this will still be used to overwrite previous values in other config files. As such this introduces the idea of config blocks which could be selectively overwritten by other configuration files downstream as required.

In addition to macros specified in the config file, macros can also be loaded from files or folders. This is specified in the config file:

[sqlfluff:templater:jinja]
load_macros_from_path = my_macros

load_macros_from_path is a comma-separated list of .sql files or folders. Locations are relative to the config file. For example, if the config file above was found at /home/my_project/.sqlfluff, then SQLFluff will look for macros in the folder /home/my_project/my_macros/ (but not subfolders). Any macros defined in the config will always take precedence over a macro defined in the path.

  • .sql files: Macros in these files are available in every .sql file without requiring a Jinja include or import.

  • Folders: To use macros from the .sql files in folders, use Jinja include or import as explained below.

Note: The load_macros_from_path setting also defines the search path for Jinja include or import. Unlike with macros (as noted above), subdirectories are supported. For example, if load_macros_from_path is set to my_macros, and there is a file my_macros/subdir/my_file.sql, you can do:

{% include 'subdir/include_comment.sql' %}

Note

Throughout the templating process whitespace will still be treated rigorously, and this includes newlines. In particular you may choose to provide dummy macros in your configuration different from the actual macros used in production.

REMEMBER: The reason SQLFluff supports macros is to enable it to parse templated sql without it being a blocker. It shouldn’t be a requirement that the templating is accurate - it only needs to work well enough that parsing and linting are helpful.

Builtin Macro Blocks

One of the main use cases which inspired SQLFluff as a project was dbt. It uses jinja templating extensively and leads to some users maintaining large repositories of sql files which could potentially benefit from some linting.

Note

SQLFluff has now a tighter integration with dbt through the “dbt” templater. It is the recommended templater for dbt projects. If used, it eliminates the need for the overrides described in this section.

To use the dbt templater, go to dbt Project Configuration.

SQLFluff anticipates this use case and provides some built in macro blocks in the Default Configuration which assist in getting started with dbt projects. In particular it provides mock objects for:

  • ref: The mock version of this provided simply returns the model reference as the name of the table. In most cases this is sufficient.

  • config: A regularly used macro in dbt to set configuration values. For linting purposes, this makes no difference and so the provided macro simply returns nothing.

Note

If there are other builtin macros which would make your life easier, consider submitting the idea (or even better a pull request) on github.

Library Templating

If using SQLFluff for dbt with jinja as your templater, you may have library function calls within your sql files that can not be templated via the normal macro templating mechanisms:

SELECT foo, bar FROM baz {{ dbt_utils.group_by(2) }}

To template these libraries, you can use the sqlfluff:jinja:library_path config option:

[sqlfluff:templater:jinja]
library_path = sqlfluff_libs

This will pull in any python modules from that directory and allow sqlfluff to use them in templates. In the above example, you might define a file at sqlfluff_libs/dbt_utils.py as:

def group_by(n):
    return "GROUP BY 1,2"

If an __init__.py is detected, it will be loaded alongside any modules and submodules found within the library path.

SELECT
   {{ custom_sum('foo', 'bar') }},
   {{ foo.bar.another_sum('foo', 'bar') }}
FROM
   baz

sqlfluff_libs/__init__.py:

def custom_sum(a: str, b: str) -> str:
    return a + b

sqlfluff_libs/foo/__init__.py:

# empty file

sqlfluff_libs/foo/bar.py:

def another_sum(a: str, b: str) -> str:
   return a + b

dbt Project Configuration

Note

From sqlfluff version 0.7.0 onwards, the dbt templater has been moved to a separate plugin and python package. Projects that were already using the dbt templater may initially fail after an upgrade to 0.7.0+. See the installation instructions below to install the dbt templater.

dbt templating is still a relatively new feature added in 0.4.0 and is still in very active development! If you encounter an issue, please let us know in a GitHub issue or on the SQLFluff slack workspace.

dbt is not the default templater for SQLFluff (it is jinja). dbt is a complex tool, so using the default jinja templater will be simpler. You should be aware when using the dbt templater that you will be exposed to some of the complexity of dbt. Users may wish to try both templaters and choose according to how they intend to use SQLFluff.

A simple rule of thumb might be:

  • If you are using SQLFluff in a CI/CD context, where speed is not critical but accuracy in rendering sql is, then the dbt templater may be more appropriate.

  • If you are using SQLFluff in an IDE or on a git hook, where speed of response may be more important, then the jinja templater may be more appropriate.

Pros:

  • Most (potentially all) macros will work

Cons:

  • More complex, e.g. using it successfully may require deeper understanding of your models and/or macros (including third-party macros)

    • More configuration decisions to make

    • Best practices are not yet established or documented

  • If your dbt model files access a database at compile time, using SQLFluff with the dbt templater will also require access to a database.

    • Note that you can often point SQLFluff and the dbt templater at a test database (i.e. it doesn’t have to be the production database).

  • Runs slower

Installation & Configuration

In order to get started using SQLFluff with a dbt project you will first need to install the relevant dbt adapter for your dialect and the sqlfluff-templater-dbt package using your package manager of choice (e.g. pip install dbt-postgres sqlfluff-templater-dbt) and then will need the following configuration:

In .sqlfluff:

[sqlfluff]
templater = dbt

In .sqlfluffignore:

target/
# dbt <1.0.0
dbt_modules/
# dbt >=1.0.0
dbt_packages/
macros/

You can set the dbt project directory, profiles directory and profile with:

[sqlfluff:templater:dbt]
project_dir = <relative or absolute path to dbt_project directory>
profiles_dir = <relative or absolute path to the directory that contains the profiles.yml file>
profile = <dbt profile>
target = <dbt target>

Note

If the profiles_dir setting is omitted, SQLFluff will look for the profile in the default location, which varies by operating system. On Unix-like operating systems (e.g. Linux or macOS), the default profile directory is ~/.dbt/. On Windows, you can determine your default profile directory by running dbt debug –config-dir.

Known Caveats

  • To use the dbt templater, you must set templater = dbt in the .sqlfluff config file in the directory where sqlfluff is run. The templater cannot be changed in .sqlfluff files in subdirectories.

  • In SQLFluff 0.4.0 using the dbt templater requires that all files within the root and child directories of the dbt project must be part of the project. If there are deployment scripts which refer to SQL files not part of the project for instance, this will result in an error. You can overcome this by adding any non-dbt project SQL files to .sqlfluffignore.

CLI Arguments

You already know you can pass arguments (--verbose, --exclude-rules, etc.) through the CLI commands (lint, fix, etc.):

$ sqlfluff lint my_code.sql -v --exclude-rules L022,L027

You might have arguments that you pass through every time, e.g rules you always want to ignore. These can also be configured:

[sqlfluff]
verbose = 1
exclude_rules = L022,L027

Note that while the exclude_rules config looks similar to the above example, the verbose config has an integer value. This is because verbose is stackable meaning there are multiple levels of verbosity that are available for configuration. See CLI Reference for more details about the available CLI arguments. For more details about rule exclusion, see Enabling and Disabling Rules.

Ignoring Errors & Files

Ignoring individual lines

Similar to flake8’s ignore, individual lines can be ignored by adding -- noqa to the end of the line. Additionally, specific rules can be ignored by quoting their code or the category.

-- Ignore all errors
SeLeCt  1 from tBl ;    -- noqa

-- Ignore rule L014 & rule L030
SeLeCt  1 from tBl ;    -- noqa: L014,L030

-- Ignore all parsing errors
SeLeCt from tBl ;       -- noqa: PRS

Ignoring line ranges

Similar to pylint’s “pylint” directive”, ranges of lines can be ignored by adding -- noqa:disable=<rule>[,...] | all to the line. Following this directive, specified rules (or all rules, if “all” was specified) will be ignored until a corresponding – noqa:enable=<rule>[,…] | all directive.

-- Ignore rule L012 from this line forward
SELECT col_a a FROM foo -- noqa: disable=L012

-- Ignore all rules from this line forward
SELECT col_a a FROM foo -- noqa: disable=all

-- Enforce all rules from this line forward
SELECT col_a a FROM foo -- noqa: enable=all

Ignoring types of errors

General categories of errors can be ignored using the --ignore command line option or the ignore setting in .sqlfluffignore. Types of errors that can be ignored include:

  • lexing

  • linting

  • parsing

  • templating

.sqlfluffignore

Similar to Git’s .gitignore and Docker’s .dockerignore, SQLFluff supports a .sqlfluffignore file to control which files are and aren’t linted. Under the hood we use the python pathspec library which also has a brief tutorial in their documentation.

An example of a potential .sqlfluffignore placed in the root of your project would be:

# Comments start with a hash.

# Ignore anything in the "temp" path
/temp/

# Ignore anything called "testing.sql"
testing.sql

# Ignore any ".tsql" files
*.tsql

Ignore files can also be placed in subdirectories of a path which is being linted and the sub files will also be applied within that subdirectory.

Default Configuration

The default configuration is as follows, note the Builtin Macro Blocks in section [sqlfluff:templater:jinja:macros] as referred to above.

  1[sqlfluff]
  2# verbose is an integer (0-2) indicating the level of log output
  3verbose = 0
  4# Turn off color formatting of output
  5nocolor = False
  6# Supported dialects https://docs.sqlfluff.com/en/stable/dialects.html
  7# Or run 'sqlfluff dialects'
  8dialect = ansi
  9# One of [raw|jinja|python|placeholder]
 10templater = jinja
 11# Comma separated list of rules to check, or None for all
 12rules = None
 13# Comma separated list of rules to exclude, or None
 14exclude_rules = None
 15# The depth to recursively parse to (0 for unlimited)
 16recurse = 0
 17# Below controls SQLFluff output, see max_line_length for SQL output
 18output_line_length = 80
 19# Number of passes to run before admitting defeat
 20runaway_limit = 10
 21# Ignore errors by category (one or more of the following, separated by commas: lexing,linting,parsing,templating)
 22ignore = None
 23# Ignore linting errors found within sections of code coming directly from
 24# templated code (e.g. from within Jinja curly braces. Note that it does not
 25# ignore errors from literal code found within template loops.
 26ignore_templated_areas = True
 27# can either be autodetect or a valid encoding e.g. utf-8, utf-8-sig
 28encoding = autodetect
 29# Ignore inline overrides (e.g. to test if still required)
 30disable_noqa = False
 31# Comma separated list of file extensions to lint
 32# NB: This config will only apply in the root folder
 33sql_file_exts = .sql,.sql.j2,.dml,.ddl
 34# Allow fix to run on files, even if they contain parsing errors
 35# Note altering this is NOT RECOMMENDED as can corrupt SQL
 36fix_even_unparsable = False
 37
 38[sqlfluff:indentation]
 39# See https://docs.sqlfluff.com/en/stable/indentation.html
 40indented_joins = False
 41indented_ctes = False
 42indented_using_on = True
 43template_blocks_indent = True
 44
 45[sqlfluff:templater]
 46unwrap_wrapped_queries = True
 47
 48[sqlfluff:templater:jinja]
 49apply_dbt_builtins = True
 50
 51[sqlfluff:templater:jinja:macros]
 52# Macros provided as builtins for dbt projects
 53dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %}
 54dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
 55dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
 56dbt_var = {% macro var(variable, default='') %}item{% endmacro %}
 57dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}
 58
 59# Some rules can be configured directly from the config common to other rules
 60[sqlfluff:rules]
 61tab_space_size = 4
 62max_line_length = 80
 63indent_unit = space
 64comma_style = trailing
 65allow_scalar = True
 66single_table_references = consistent
 67unquoted_identifiers_policy = all
 68
 69# Some rules have their own specific config
 70[sqlfluff:rules:L007]
 71operator_new_lines = after
 72
 73[sqlfluff:rules:L010]
 74# Keywords
 75capitalisation_policy = consistent
 76# Comma separated list of words to ignore for this rule
 77ignore_words = None
 78
 79[sqlfluff:rules:L011]
 80# Aliasing preference for tables
 81aliasing = explicit
 82
 83[sqlfluff:rules:L012]
 84# Aliasing preference for columns
 85aliasing = explicit
 86
 87[sqlfluff:rules:L014]
 88# Unquoted identifiers
 89extended_capitalisation_policy = consistent
 90# Comma separated list of words to ignore for this rule
 91ignore_words = None
 92
 93[sqlfluff:rules:L016]
 94# Line length
 95ignore_comment_lines = False
 96ignore_comment_clauses = False
 97
 98[sqlfluff:rules:L026]
 99# References must be in FROM clause
100# Disabled for some dialects (e.g. bigquery)
101force_enable = False
102
103[sqlfluff:rules:L028]
104# References must be consistently used
105# Disabled for some dialects (e.g. bigquery)
106force_enable = False
107
108[sqlfluff:rules:L029]
109# Keywords should not be used as identifiers.
110unquoted_identifiers_policy = aliases
111quoted_identifiers_policy = none
112# Comma separated list of words to ignore for this rule
113ignore_words = None
114
115[sqlfluff:rules:L030]
116# Function names
117extended_capitalisation_policy = consistent
118# Comma separated list of words to ignore for this rule
119ignore_words = None
120
121[sqlfluff:rules:L031]
122# Avoid table aliases in from clauses and join conditions.
123# Disabled for some dialects (e.g. bigquery)
124force_enable = False
125
126[sqlfluff:rules:L038]
127# Trailing commas
128select_clause_trailing_comma = forbid
129
130[sqlfluff:rules:L040]
131# Null & Boolean Literals
132capitalisation_policy = consistent
133# Comma separated list of words to ignore for this rule
134ignore_words = None
135
136[sqlfluff:rules:L042]
137# By default, allow subqueries in from clauses, but not join clauses
138forbid_subquery_in = join
139
140[sqlfluff:rules:L047]
141# Consistent syntax to count all rows
142prefer_count_1 = False
143prefer_count_0 = False
144
145[sqlfluff:rules:L051]
146# Fully qualify JOIN clause
147fully_qualify_join_types = inner
148
149[sqlfluff:rules:L052]
150# Semi-colon formatting approach
151multiline_newline = False
152require_final_semicolon = False
153
154[sqlfluff:rules:L054]
155# GROUP BY/ORDER BY column references
156group_by_and_order_by_style = consistent
157
158[sqlfluff:rules:L057]
159# Special characters in identifiers
160unquoted_identifiers_policy = all
161quoted_identifiers_policy = all
162allow_space_in_identifier = False
163additional_allowed_characters = ""
164ignore_words = None
165
166[sqlfluff:rules:L059]
167# Policy on quoted and unquoted identifiers
168prefer_quoted_identifiers = False
169ignore_words = None
170
171[sqlfluff:rules:L062]
172# Comma separated list of blocked words that should not be used
173blocked_words = None