.\" Man page generated from reStructuredText. . . .nr rst2man-indent-level 0 . .de1 rstReportMargin \\$1 \\n[an-margin] level \\n[rst2man-indent-level] level margin: \\n[rst2man-indent\\n[rst2man-indent-level]] - \\n[rst2man-indent0] \\n[rst2man-indent1] \\n[rst2man-indent2] .. .de1 INDENT .\" .rstReportMargin pre: . RS \\$1 . nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin] . nr rst2man-indent-level +1 .\" .rstReportMargin post: .. .de UNINDENT . RE .\" indent \\n[an-margin] .\" old: \\n[rst2man-indent\\n[rst2man-indent-level]] .nr rst2man-indent-level -1 .\" new: \\n[rst2man-indent\\n[rst2man-indent-level]] .in \\n[rst2man-indent\\n[rst2man-indent-level]]u .. .TH "SQLFLUFF" "1" "Jan 29, 2023" "" "SQLFluff" .SH NAME sqlfluff \- SQLFluff 1.4.5 .sp Bored of not having a good SQL linter that works with whichever dialect you\(aqre working with? Fluff is an extensible and modular linter designed to help you write good SQL and catch errors and bad SQL before it hits your database. .sp Notable changes: .INDENT 0.0 .IP \(bu 2 \fB0.1.x\fP involved a major re\-write of the parser, completely changing the behaviour of the tool with respect to complex parsing. .IP \(bu 2 \fB0.2.x\fP added templating support and a big restructure of rules and changed how users might interact with SQLFluff on templated code. .IP \(bu 2 \fB0.3.x\fP drops support for python 2.7 and 3.4, and also reworks the handling of indentation linting in a potentially not backward compatible way. .IP \(bu 2 \fB0.4.x\fP dropped python 3.5, added the dbt templater, source mapping and also introduced the python API. .IP \(bu 2 \fB0.5.x\fP introduced some breaking changes to the API. .IP \(bu 2 \fB0.6.x\fP introduced parallel processing, which necessitated a big re\-write of several innards. .IP \(bu 2 \fB0.7.x\fP extracted the dbt templater to a separate plugin and removed the \fBexasol_fs\fP dialect (now merged in with the main \fBexasol\fP). .IP \(bu 2 \fB0.8.x\fP an improvement to the performance of the parser, a rebuild of the Jinja Templater, and a progress bar for the CLI. .IP \(bu 2 \fB0.9.x\fP refinement of the Simple API, dbt 1.0.0 compatibility, and the official SQLFluff Docker image. .IP \(bu 2 \fB0.10.x\fP removed support for older dbt versions < 0.20 and stopped \fBfix\fP attempting to fix unparsable SQL. .IP \(bu 2 \fB0.11.x\fP rule L030 changed to use \fBextended_capitalisation_policy\fP\&. .IP \(bu 2 \fB0.12.x\fP dialect is now mandatory, the \fBspark3\fP dialect was renamed to \fBsparksql\fP and datatype capitalisation was extracted from L010 to it\(aqs own rule L063. .IP \(bu 2 \fB0.13.x\fP new rule for quoted literals, option to remove hanging indents in rule L003, and introduction of \fBignore_words_regex\fP\&. .IP \(bu 2 \fB1.0.0\fP first \fIstable\fP release, no major changes to take advantage of a point of relative stability. .UNINDENT .sp Want to see where and how people are using SQLFluff in their projects? Head over to \fI\%SQLFluff in the Wild\fP for inspiration. .SH GETTING STARTED .sp To get started just install the package, make a sql file and then run SQLFluff and point it at the file. For more details or if you don\(aqt have python or pip already installed see \fI\%Getting Started\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pip install sqlfluff $ echo \(dq SELECT a + b FROM tbl; \(dq > test.sql $ sqlfluff lint test.sql \-\-dialect ansi == [test.sql] FAIL L: 1 | P: 1 | L050 | Files must not begin with newlines or whitespace. L: 1 | P: 3 | L003 | First line has unexpected indent L: 1 | P: 11 | L039 | Unnecessary whitespace found. L: 1 | P: 14 | L039 | Unnecessary whitespace found. L: 1 | P: 27 | L001 | Unnecessary trailing whitespace. .ft P .fi .UNINDENT .UNINDENT .SH CONTENTS .SS Getting Started .sp To get started with \fISQLFluff\fP you\(aqll need python and pip installed on your machine, if you\(aqre already set up, you can skip straight to \fI\%Installing sqlfluff\fP\&. .SS Installing Python .sp How to install \fIpython\fP and \fIpip\fP depends on what operating system you\(aqre using. In any case, the python wiki provides up to date \fI\%instructions for all platforms here\fP\&. .sp There\(aqs a chance that you\(aqll be offered the choice between python versions. Support for python 2 was dropped in early 2020, so you should always opt for a version number starting with a 3. As for more specific options beyond that, \fISQLFluff\fP aims to be compatible with all current python versions, and so it\(aqs best to pick the most recent. .sp You can confirm that python is working as expected by heading to your terminal or console of choice and typing \fBpython \-\-version\fP which should give you a sensible read out and not an error. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ python \-\-version Python 3.9.1 .ft P .fi .UNINDENT .UNINDENT .sp For most people, their installation of python will come with \fBpip\fP (the python package manager) preinstalled. To confirm this you can type \fBpip \-\-version\fP similar to python above. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pip \-\-version pip 21.3.1 from ... .ft P .fi .UNINDENT .UNINDENT .sp If however, you do have python installed but not \fBpip\fP, then the best instructions for what to do next are \fI\%on the python website\fP\&. .SS Installing SQLFluff .sp Assuming that python and pip are already installed, then installing \fISQLFluff\fP is straight forward. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ pip install sqlfluff .ft P .fi .UNINDENT .UNINDENT .sp You can confirm its installation by getting \fISQLFluff\fP to show its version number. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff version 1.4.5 .ft P .fi .UNINDENT .UNINDENT .SS Basic Usage .sp To get a feel for how to use \fISQLFluff\fP it helps to have a small \fB\&.sql\fP file which has a simple structure and some known issues for testing. Create a file called \fBtest.sql\fP in the same folder that you\(aqre currently in with the following content: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a+b AS foo, c AS bar from my_table .ft P .fi .UNINDENT .UNINDENT .sp You can then run \fBsqlfluff lint test.sql \-\-dialect ansi\fP to lint this file. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff lint test.sql \-\-dialect ansi == [test.sql] FAIL L: 1 | P: 1 | L034 | Select wildcards then simple targets before calculations | and aggregates. L: 1 | P: 1 | L036 | Select targets should be on a new line unless there is | only one select target. L: 1 | P: 9 | L006 | Missing whitespace before + L: 1 | P: 9 | L006 | Missing whitespace after + L: 1 | P: 11 | L039 | Unnecessary whitespace found. L: 2 | P: 1 | L003 | Expected 1 indentations, found 0 [compared to line 01] L: 2 | P: 10 | L010 | Keywords must be consistently upper case. .ft P .fi .UNINDENT .UNINDENT .sp You\(aqll see that \fISQLFluff\fP has failed the linting check for this file. On each of the following lines you can see each of the problems it has found, with some information about the location and what kind of problem there is. One of the errors has been found on \fIline 1\fP, \fIposition * (as shown by :code:\(gaL: 1 | P: 9\(ga) and it\(aqs a problem with rule *L006\fP (for a full list of rules, see \fI\%Rules Reference\fP). From this (and the following error) we can see that the problem is that there is no space either side of the \fB+\fP symbol in \fBa+b\fP\&. Head into the file, and correct this issue so that the file now looks like this: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a + b AS foo, c AS bar from my_table .ft P .fi .UNINDENT .UNINDENT .sp Rerun the same command as before, and you\(aqll see that the original error (violation of \fIL006\fP) no longer shows up. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff lint test.sql \-\-dialect ansi == [test.sql] FAIL L: 1 | P: 1 | L034 | Select wildcards then simple targets before calculations | and aggregates. L: 1 | P: 1 | L036 | Select targets should be on a new line unless there is | only one select target. L: 1 | P: 13 | L039 | Unnecessary whitespace found. L: 2 | P: 1 | L003 | Expected 1 indentations, found 0 [compared to line 01] L: 2 | P: 10 | L010 | Keywords must be consistently upper case. .ft P .fi .UNINDENT .UNINDENT .sp To fix the remaining issues, we\(aqre going to use one of the more advanced features of \fISQLFluff\fP, which is the \fIfix\fP command. This allows more automated fixing of some errors, to save you time in sorting out your sql files. Not all rules can be fixed in this way and there may be some situations where a fix may not be able to be applied because of the context of the query, but in many simple cases it\(aqs a good place to start. .sp For now, we only want to fix the following rules: \fIL003\fP, \fIL009\fP, \fIL010\fP .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff fix test.sql \-\-rules L003,L009,L010 \-\-dialect ansi ==== finding violations ==== == [test.sql] FAIL L: 2 | P: 1 | L003 | Expected 1 indentations, found 0 [compared to line 01] L: 2 | P: 10 | L010 | Keywords must be consistently upper case. ==== fixing violations ==== 2 fixable linting violations found Are you sure you wish to attempt to fix these? [Y/n] .ft P .fi .UNINDENT .UNINDENT .sp \&...at this point you\(aqll have to confirm that you want to make the changes by pressing \fBy\fP on your keyboard... .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C Are you sure you wish to attempt to fix these? [Y/n] ... Attempting fixes... Persisting Changes... == [test.sql] PASS Done. Please check your files to confirm. .ft P .fi .UNINDENT .UNINDENT .sp If we now open up \fBtest.sql\fP, we\(aqll see the content is now different. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a + b AS foo, c AS bar FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp In particular: .INDENT 0.0 .IP \(bu 2 The second line has been indented to reflect being inside the \fBSELECT\fP statement. .IP \(bu 2 The \fBFROM\fP keyword has been capitalised to match the other keywords. .IP \(bu 2 A final newline character has been added at the end of the file (which may not be obvious in the snippet above). .UNINDENT .sp We could also fix \fIall\fP of the fixable errors by not specifying \fB\-\-rules\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff fix test.sql \-\-dialect ansi ==== finding violations ==== == [test.sql] FAIL L: 1 | P: 1 | L034 | Select wildcards then simple targets before calculations | and aggregates. L: 1 | P: 1 | L036 | Select targets should be on a new line unless there is | only one select target. L: 1 | P: 13 | L039 | Unnecessary whitespace found. ==== fixing violations ==== 3 fixable linting violations found Are you sure you wish to attempt to fix these? [Y/n] ... Attempting fixes... Persisting Changes... == [test.sql] PASS Done. Please check your files to confirm. .ft P .fi .UNINDENT .UNINDENT .sp If we now open up \fBtest.sql\fP, we\(aqll see the content has been updated again. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT c AS bar, a + b AS foo FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp The SQL statement is now well formatted according to all the rules defined in SQLFluff. .sp The \fB\-\-rules\fP argument is optional, and could be useful when you or your organisation follows a slightly different convention than what we have defined. .SS Custom Usage .sp So far we\(aqve covered the stock settings of \fISQLFluff\fP, but there are many different ways that people style their sql, and if you or your organisation have different conventions, then many of these behaviours can be configured. For example, given the example above, what if we actually think that indents should only be two spaces, and rather than uppercase keywords, they should all be lowercase? .sp To achieve this we create a configuration file named \fB\&.sqlfluff\fP and place it in the same directory as the current file. In that file put the following content: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] dialect = ansi [sqlfluff:rules] tab_space_size = 2 [sqlfluff:rules:L010] capitalisation_policy = lower .ft P .fi .UNINDENT .UNINDENT .sp Then rerun the same command as before. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff fix test.sql \-\-rules L003,L009,L010,L034,L036,L039 .ft P .fi .UNINDENT .UNINDENT .sp Then examine the file again, and you\(aqll notice that the file has been fixed accordingly. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C select c as bar, a + b as foo from my_table .ft P .fi .UNINDENT .UNINDENT .sp For a full list of configuration options check out \fI\%Default Configuration\fP\&. To see how these options apply to specific rules check out the \(dqConfiguration\(dq section within each rule\(aqs documentation in \fI\%Rules Reference\fP\&. .SS Going further .sp From here, there are several more things to explore. .INDENT 0.0 .IP \(bu 2 To understand how \fISQLFluff\fP is interpreting your file explore the \fBparse\fP command. You can learn more about that command and more by running \fBsqlfluff \-\-help\fP or \fBsqlfluff parse \-\-help\fP\&. .IP \(bu 2 To start linting more than just one file at a time, experiment with passing SQLFluff directories rather than just single files. Try running \fBsqlfluff lint .\fP (to lint every sql file in the current folder) or \fBsqlfluff lint path/to/my/sqlfiles\fP\&. .IP \(bu 2 To find out more about which rules are available, see \fI\%Rules Reference\fP\&. .IP \(bu 2 To find out more about configuring \fISQLFluff\fP and what other options are available, see \fI\%Configuration\fP\&. .UNINDENT .sp One last thing to note is that \fISQLFluff\fP is a relatively new project and you may find bugs or strange things while using it. If you do find anything, the most useful thing you can do is to \fI\%post the issue on GitHub\fP where the maintainers of the project can work out what to do with it. The project is in active development and so updates and fixes may come out regularly. .SS SQL in the Wild .sp SQL has been around for a long time, as a language for communicating with databases, like a communication protocol. More recently with the rise of \fIdata\fP as a business function, or a domain in its own right SQL has also become an invaluable tool for defining the \fIstructure\fP of data and analysis \- not just as a one off but as a form of \fI\%infrastructure as code\fP\&. .sp As \fIanalytics\fP transitions from a profession of people doing one\-offs, and moves to building stable and reusable pieces of analytics, more and more principles from software engineering are moving in the analytics space. One of the best articulations of this is written in the \fI\%viewpoint section of the docs for the open\-source tool dbt\fP\&. Two of the principles mentioned in that article are \fI\%quality assurance\fP and \fI\%modularity\fP\&. .SS Quality assurance .sp The primary aim of \fISQLFluff\fP as a project is in service of that first aim of \fI\%quality assurance\fP\&. With larger and larger teams maintaining large bodies of SQL code, it becomes more and more important that the code is not just \fIvalid\fP but also easily \fIcomprehensible\fP by other users of the same codebase. One way to ensure readability is to enforce a \fI\%consistent style\fP, and the tools used to do this are called \fI\%linters\fP\&. .sp Some famous \fI\%linters\fP which are well known in the software community are \fI\%flake8\fP and \fI\%jslint\fP (the former is used to lint the \fISQLFluff\fP project itself). .sp \fBSQLFluff\fP aims to fill this space for SQL. .SS Modularity .sp SQL itself doesn\(aqt lend itself well to \fI\%modularity\fP, so to introduce some flexibility and reusability it is often \fI\%templated\fP\&. Typically this is done in the wild in one of the following ways: .INDENT 0.0 .IP 1. 3 Using the limited inbuilt templating abilities of a programming language directly. For example in python this would be using the \fI\%format string syntax\fP: .INDENT 3.0 .INDENT 3.5 .sp .nf .ft C \(dqSELECT {foo} FROM {tbl}\(dq.format(foo=\(dqbar\(dq, tbl=\(dqmytable\(dq) .ft P .fi .UNINDENT .UNINDENT .sp Which would evaluate to: .INDENT 3.0 .INDENT 3.5 .sp .nf .ft C SELECT bar FROM mytable .ft P .fi .UNINDENT .UNINDENT .IP 2. 3 Using a dedicated templating library such as \fI\%jinja2\fP\&. This allows a lot more flexibility and more powerful expressions and macros. See the \fI\%Jinja Templating Configuration\fP section for more detail on how this works. .INDENT 3.0 .IP \(bu 2 Often there are tools like \fI\%dbt\fP or \fI\%apache airflow\fP which allow \fI\%templated\fP sql to be used directly, and they will implement a library like \fI\%jinja2\fP under the hood themselves. .UNINDENT .UNINDENT .sp All of these templating tools are great for \fI\%modularity\fP but they also mean that the SQL files themselves are no longer valid SQL code, because they now contain these configured \fIplaceholder\fP values, intended to improve modularity. .sp SQLFluff supports both of the templating methods outlined above, as well as \fI\%dbt\fP projects, to allow you to still lint these \(dqdynamic\(dq SQL files as part of your CI/CD pipeline (which is great 🙌), rather than waiting until you\(aqre in production (which is bad 🤦, and maybe too late). .sp During the CI/CD pipeline (or any time that we need to handle \fI\%templated\fP code), SQLFluff needs additional info in order to interpret your templates as valid SQL code. You do so by providing dummy parameters in SQLFluff configuration files. When substituted into the template, these values should evaluate to valid SQL (so SQLFluff can check its style, formatting, and correctness), but the values don\(aqt need to match actual values used in production. This means that you can use \fImuch simpler\fP dummy values than what you would really use. The recommendation is to use \fIthe simplest\fP possible dummy value that still allows your code to evaluate to valid SQL so that the configuration values can be as streamlined as possible. .SS Vision for SQLFluff .sp SQLFluff has a few components: .INDENT 0.0 .IP 1. 3 A generic parser for SQL which aims to be able to unify SQL written in different dialects into a comparable format. The \fIparser\fP\&. .IP 2. 3 A mechanism for measuring written SQL against a set of rules, with the added ability to fix any violations found. The \fIlinter\fP\&. .IP 3. 3 An opinionated set of guidelines for how SQL should be structured and formatted. The \fIrules\fP\&. .UNINDENT .sp The core vision [1] for SQLFluff is to be really good at being the \fIlinter\fP\&. The reasoning for this is outlined in \fI\%SQL in the Wild\fP\&. .sp Most of the codebase for SQLFluff is the \fIparser\fP, mostly because at the point of developing SQLFluff, there didn\(aqt appear to be a good option for a whitespace\-aware parser that could be used instead. .sp With regards to the \fIrules\fP, SQLFluff aims to be opinionated but it also accepts that many organisations and groups have pre\-existing strong conventions around how to write SQL and so ultimately SQLFluff should be flexible enough to support whichever rule set a user wishes to. .sp Notes .IP [1] 5 Credit to \fI\%this article\fP for highlighting the importance of a good vision. .SS Rolling out SQLFluff with a new team .sp Rolling out SQLFluff, like rolling out any other linter or style guide, is not just about the \fItechnical\fP rollout, but also how you introduce the tool to the team and organisation around you. .INDENT 0.0 .INDENT 3.5 \fIThe effect of SQLFluff should be to change your behaviours, not\fP \fIjust your SQL\fP\&. .UNINDENT .UNINDENT .sp With that in mind, it\(aqs worth reminding ourselves what we\(aqre trying to achieve with a tool like this. A set of potential success criteria might be: .INDENT 0.0 .IP 1. 3 \fBFaster comprehension and collaboration\fP by the team on a shared codebase. This includes more effective (and more enjoyable) code review on top of code which is easy to review and build upon. .IP 2. 3 \fBEasier and faster onboarding\fP for new team members. By adopting a style which is clean and \fIconsistent with other organisations\fP we make it easier for new people to join the team. .IP 3. 3 \fBImproved adoption of shared SQL\fP from other sources. If the SQL found in open source projects is easy to read and \fIlooks familiar\fP then you\(aqre more likely to use it. This means more reusable code across the industry. .IP 4. 3 \fBProductive discussions around style\fP\&. By defining your organisation\(aqs style guide in code, it means you can version control it, discuss changes and ultimately give a concrete output to discussions over style. .sp \fIYou like leading commas? Make a PR to .sqlfluff and let\(aqs\fP \fIdiscuss with the team what the implications would be\fP\&. .UNINDENT .sp Consider which of these success measures is most important and most desirable for your team. \fIWrite that down\fP\&. .sp The following steps are a guide, which you should adapt to your organisation, and in particular its level of data maturity. .SS 1. Assess the situation .sp This step is done by you, or a small group of people who \fIalready\fP \fIthink that linting is a good idea\fP\&. .INDENT 0.0 .IP \(bu 2 Run \fBsqlfluff lint\fP on your project with the stock configuration to find out how things work \fIout of the box\fP\&. .IP \(bu 2 Set up your \fI\%Configuration\fP so that things run and that you can get a readout of the errors which you would want the team to see and \fInot the ones you don\(aqt\fP\&. Great tools for this are to use \fI\%Ignoring types of errors\fP, \fB\-\-exclude\-rules\fP or \fB\-\-ignore\fP in the CLI (see \fI\%CLI Reference\fP). .IP \(bu 2 Identify which areas of your project are the worst and which are the tidiest. In particular, any areas which are particularly tidy already will be particularly useful in the next phase. .UNINDENT .SS 2. Make a plan .sp There are three sensible rollout phases: .INDENT 0.0 .IP 1. 3 \fBPre CI/CD\fP\&. .IP 2. 3 \fBSoft CI/CD\fP (warnings but no strict fails). .IP 3. 3 \fBHard CI/CD\fP (violations mean deployments fail). .UNINDENT .sp In each of these phases you have three levers to play with: .INDENT 0.0 .IP 1. 3 Areas of the project in which to apply rules. .IP 2. 3 Depth of rules enforced (this might also include whether to ignore parsing errors or not). .IP 3. 3 Whether to just lint changes (\fI\%Using SQLFluff on changes using diff\-quality\fP), or to lint all the existing code as well. .UNINDENT .sp Work out a sensible roadmap of how hard you want to go in each phase. Be clear who is responsible for changes at each phase. An example plan might look like this: .INDENT 0.0 .IP 1. 3 \fBPre CI/CD\fP we get the tidiest area of a project to a stage that it fully passes the rules we eventually want to enforce. The core project team will do this. Liberal use of \fBsqlfluff fix\fP can be a lifesaver in this phase. .IP 2. 3 \fBSoft CI/CD\fP is applied to the whole project, team members are encouraged to write tidy SQL, but not \fIrequired\fP to. .IP 3. 3 \fBHard CI/CD\fP is applied to the tidy areas of the project and also to any changes to the whole project. Anyone making changes is \fIrequired\fP to write SQL which passes check. .IP 4. 3 \fBHard CI/CD\fP is applied to the whole project on not just changes, with only a few particularly problematic files explicitly ignored using \fI\%Ignoring types of errors\fP\&. .UNINDENT .SS 3. Build the need .sp Bring your team together to introduce both linting as a concept and also SQLFluff as a tool. At this stage it\(aqs \fBreally important\fP \fBthat the team understand *why* this is a good thing\fP\&. .sp Consider whether to discuss the whole plan from step 2, or whether to only talk about the first few steps. Aim to make this an empowering experience that everyone can get involved with rather than \fIanother piece of admin they need to do\fP\&. .sp At this stage, you might also want to consider other tools in the SQLFluff ecosystem such as the \fI\%SQLFluff pre\-commit hook\fP and the \fI\%SQLFluff VSCode plugin\fP or \fI\%SQLFluff online formatter\fP\&. .SS 4. Do, Review & Reassess .sp Once the plan is in motion, make sure to start putting in place norms and rituals around how you change the rules. In particular: .INDENT 0.0 .IP \(bu 2 How would someone suggest changing the style guide or enabling/disabling a rule? .IP \(bu 2 How do we assess whether the changes are working for the team or whether some are creating unnecessary stress? .UNINDENT .sp It\(aqs normal for your usage of tools like SQLFluff to change and evolve over time. It\(aqs important to expect this change in advance, and welcome it when it happens. Always make sure you\(aqre driving toward the success measures you decided up front, rather than just resisting the change. .SS 5. Spread the word 😁 .sp Did it work? If so, spread the word. Tell a friend about SQLFluff. .sp If you\(aqre lucky they might share your views on comma placement 🤷‍♀️. .SS Let\(aqs talk about whitespace .sp If there is one part of building a linter that is going to be controversial it\(aqs going to be \fBwhitespace\fP (closely followed by \fBcApiTaLiSaTiOn\fP 😁). .sp More specifically, \fBwhitespace\fP divides into three key themes: .INDENT 0.0 .IP 1. 3 \fBSpacing\fP: The amount of whitespace between elements on the same line. .IP 2. 3 \fBLine Breaks\fP: The choice of where within the code it is inappropriate, appropriate or even compulsory to have a line break. .IP 3. 3 \fBIndentation\fP: Given a line break, how much whitespace should precede the first code element on that line. .UNINDENT .sp \fISQLFluff\fP aims to be \fIopinionated\fP on this theme, but also \fIconfigurable\fP (see \fI\%Configuring Layout\fP). The tool will have a default viewpoint and will aim to have views on all of the important aspects of SQL layout, but if you (or your organisation) don\(aqt like those views then we aim to allow enough configuration that you can lint in line with your views, and still use \fISQLFluff\fP\&. For more information on how to configure rules to your own viewpoint see \fI\%Configuration\fP\&. .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 This section of the docs handles the intent and reasoning behind how layout is handled by SQLFluff. For a deeper look at how this is achieved internally see \fI\%Reflow Internals\fP\&. .UNINDENT .UNINDENT .SS Spacing .sp Of the different elements of whitespace, spacing is likely the least controversial. By default, all elements are separated by a single space character. Except for very specific circumstances (see section on \fI\%Aligned elements\fP), any additional space between elements is usually unwanted and a distraction for the reader. There are however several common cases where \fIno whitespace\fP is more appropriate, which fall into two cases (for more details on where to configure these see \fI\%Configuring layout and spacing\fP). .INDENT 0.0 .IP 1. 3 \fINo whitespace but a newline is allowed.\fP This option is configured using the \fBtouch\fP option in the \fBspacing_*\fP configuration settings. The most common example of this is the spacing around commas. For example \fBSELECT a , b\fP would be unusual and more normally be written \fBSELECT a, b\fP\&. Inserting a newline between the \fBa\fP and comma would not cause issues and may even be desired, for example: .INDENT 3.0 .INDENT 3.5 .sp .nf .ft C SELECT col_a , col_b \-\- Newline present before column , col_c \-\- When inline, comma should still touch element before. , GREATEST(col_d, col_e) as col_f FROM tbl_a .ft P .fi .UNINDENT .UNINDENT .IP 2. 3 \fINo whitespace and a newline is not allowed.\fP This option is configured using the \fBinline\fP option in the \fBspacing_*\fP configuration settings. The most common example of this is spacing within the parts of qualified identifier e.g. \fBmy_schema.my_table\fP\&. If a newline were present between the \fB\&.\fP and either \fBmy_schema\fP or \fBmy_table\fP, then the expression would not parse and so no newlines should be allowed. .UNINDENT .SS Aligned elements .sp A special case of spacing is where elements are set to be aligned within some limits. This is not enabled by default, but can be be configured to achieve layouts like: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a AS first_column, b AS second_column, (a + b) / 2 AS third_column FROM foo AS bar .ft P .fi .UNINDENT .UNINDENT .sp In this example, the alias expressions are all aligned with each other. To configure this, SQLFluff needs to know what elements to align and how far to search to find elements which should be aligned with each other. The configuration to achieve this layout is: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:layout:type:alias_expression] # We want non\-default spacing _before_ the alias expressions. spacing_before = align # We want to align them within the next outer select clause. # This means for example that alias expressions within the FROM # or JOIN clause would _not_ be aligned with them. align_within = select_clause # The point at which to stop searching outward for siblings, which # in this example would likely be the boundary of a CTE. Stopping # when we hit brackets is usually a good rule of thumb for this # configuration. align_scope = bracketed .ft P .fi .UNINDENT .UNINDENT .sp Of these configuration values, the \fBalign_scope\fP is potentially the least obvious. The following example illustrates the impact it has. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- With \-\- align_scope = bracketed \-\- align_within = select_clause WITH foo as ( SELECT a, b, c AS first_column d + e AS second_column ) SELECT a AS first_column, (a + b) / 2 AS third_column FROM foo AS bar; \-\- With \-\- align_scope = bracketed \-\- align_within = statement WITH foo as ( SELECT a, b, c AS first_column d + e AS second_column ) SELECT a AS first_column, (a + b) / 2 AS third_column FROM foo AS bar \-\- Now the FROM alias is also aligned. \-\- With \-\- align_scope = file \-\- align_within = select_clause WITH foo as ( SELECT a, b, c AS first_column \-\- Now the aliases here are aligned d + e AS second_column \-\- with the outer query. ) SELECT a AS first_column, (a + b) / 2 AS third_column FROM foo AS bar \-\- With \-\- align_scope = file \-\- align_within = statement WITH foo as ( SELECT a, b, c AS first_column d + e AS second_column ) SELECT a AS first_column, (a + b) / 2 AS third_column FROM foo AS bar .ft P .fi .UNINDENT .UNINDENT .SS Line Breaks .sp When controlling line breaks, we are trying to achieve a few different things: .INDENT 0.0 .IP 1. 3 Do we have \fIenough\fP line breaks that \fIline length\fP doesn\(aqt become excessive. Long lines are hard to read, especially given that readers may be on varying screen sizes or have multiple windows open. This is (of course) configurable, but the default is 80 characters (in line with the \fI\%dbt Labs SQL style guide\fP\&.) .IP 2. 3 Is the positioning of \fIblank lines\fP (i.e. lines with nothing other than whitespace on them) appropriate. There are some circumstances where a blank line is \fIdesired\fP (e.g. between CTEs). There are others where they are not, in particular \fImultiple blank lines\fP, for example at the beginning of a file. .IP 3. 3 Where we do have line breaks, are they positioned appropriately and consistently with regards to other elements around them. This is most common when it comes to \fIcommas\fP, and whether they should be \fIleading\fP (e.g. \fB, my_column\fP) or \fItrailing\fP (e.g. \fBmy_column,\fP). In less common cases, it may also be desirable for some elements to have both a line break \fIbefore and after\fP (e.g. a set operator such as \fIUNION\fP). .UNINDENT .SS Indentation .sp Lastly, given we have multiple lines of SQL, to what extent should we indent some lines to provide visual cues to the structure of that SQL. It\(aqs important to note that SQL is \fInot\fP whitespace sensitive in its interpretation and that means that any principles we apply here are entirely for the benefit of humans. \fIYour database doesn\(aqt care\fP\&. .sp The indentation therefore should be treated as a \fIhint\fP to the reader of the structure of the code. This explains the common practice within most languages that nested elements (for example the contents of a set of brackets in a function call) should be indented one step from the outer elements. It\(aqs also convention that elements \fIwith the same level\fP in a nested structure should have \fIthe same indentation\fP, at least with regards to their local surroundings. As an example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT nested_within_select AS first_column, some_function( nested_within_function, also_nested_within_function ) AS indented_the_same_as_opening_bracket FROM indented_the_same_as_select .ft P .fi .UNINDENT .UNINDENT .SS Comment Indents .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 The notes here about block comments are not implemented prior to 2.0.x. They should be coming in that release or soon after. .UNINDENT .UNINDENT .sp \fBComments\fP are dealt with differently, depending on whether they\(aqre \fIblock\fP comments (\fB/* like this */\fP), which might optionally include newlines, or \fIinline\fP comments (\fB\-\- like this\fP) which are necessarily only on one line. .INDENT 0.0 .IP \(bu 2 \fIBlock comments\fP cannot share a line with any code elements (so in effect they must start on their own new line), they cannot be followed by any code elements on the same line (and so in effect must be followed by a newline, if we are to avoid trailing whitespace). None of the lines within the block comment may have an indent less than the first line of the block comment (although additional indentation within a comment is allowed), and that first line should be aligned with the first code element \fIfollowing\fP the block comment. .INDENT 2.0 .INDENT 3.5 .sp .nf .ft C SELECT /* This is a block comment starting on a new line which contains a newline (continuing with at least the same indent. \- potentially containing greater indents \- having no other code following it in the same line \- and aligned with the line of code following it */ this_column as what_we_align_the_column_to FROM my_table .ft P .fi .UNINDENT .UNINDENT .IP \(bu 2 \fIInline comments\fP can be on the same line as other code, but are subject to the same line\-length restrictions. If they don\(aqt fit on the same line (or if it just looks nicer) they can also be the only element on a line. In this latter case, they should be aligned with the first code element \fIfollowing\fP the comment. .INDENT 2.0 .INDENT 3.5 .sp .nf .ft C SELECT \-\- This is fine this_column as what_we_align_to, another_column as something_short, \-\- Is ok case \-\- This is aligned correctly with below when indented then take_care else try_harder end as the_general_guidance \-\- Even here we align with the line below FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 2.0 .INDENT 3.5 When fixing issues with comment indentation, SQLFluff will attempt to keep comments in their original position but if line length concerns make this difficult, it will either abandon the fix, or move \fIsame line\fP comments up and \fIbefore\fP the line they are currently on. This is in line with the assumption that comments on their own line refer to the elements of code which they come \fIbefore\fP, not \fIafter\fP\&. .UNINDENT .UNINDENT .UNINDENT .SS Hanging Indents .sp One approach to indenting nested elements is a layout called a \fIhanging indent\fP\&. In this layout, there is no line break before the first nested element, but subsequent elements are indented to match the line position of that first element. Two examples might be: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- A select statement with two hanging indents: SELECT no_line_break_before_me, indented_to_match_the_first, 1 + (a + b) AS another_more_complex_example FROM my_table; \-\- This TSQL example is also in essence a hanging indent: DECLARE @prv_qtr_1st_dt DATETIME, @last_qtr INT, @last_qtr_first_mn INT, @last_qtr_yr INT; .ft P .fi .UNINDENT .UNINDENT .sp In some circumstances this layout can be quite neat (the \fBDECLARE\fP statement is a good example of this), however once indents are nested or indentation styles are mixed it can rapidly become confusing (as partially shown in the first example). Additionally, unless the leading element of the first line is very short, hanging indents use much \fIlarger indents\fP than a traditional simple indent where a line break is used before the first element. .sp Hanging indents have been supported in SQLFluff up to the 1.x versions, however \fBthey will no longer by supported from 2.0.0\fP onwards. This is due to the ambiguity which they bring to fixing poorly formatted SQL. Take the following code: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT this_is, badly_formatted, code_and, not_obvious, what_was, intended FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp Given the lack of line break between \fBSELECT\fP and \fBthis_is\fP, it would appear that the user is intending a hanging indent, however it is also plausible that they did not and they just forgot to add a line break between them. This ambiguity is unhelpful, both for SQLFluff as a tool, but also for people who write SQL that there two ways of indenting their SQL. Given SQLFluff aims to provide consistency in SQL layout and remove some of the burden of needing to make choices like this \- and that it would be very unusual to keep \fIonly hanging indents and disable traditional ones\fP \- the only route left to consistency is to \fBnot allow hanging indents\fP\&. Starting in 2.0.0, any hanging indents detected will be converted to traditional indents. .SS Templated Indents .sp SQLFluff supports templated elements in code, such as those offered by jinja2 (or dbt which relies on it). For simple cases, templated elements are handled as you would expect by introducing additional indents into the layout. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, {% for n in [\(aqb\(aq, \(aqc\(aq, \(aqd\(aq] %} \-\- This section is indented relative to \(aqa\(aq because \-\- it is inside a jinja for loop. {{ n }}, {% endfor %} e FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp This functionality can be turned off if you wish using the \fBtemplate_blocks_indent\fP option in your \fI\%Configuration\fP\&. .sp It\(aqs important to note here, that SQLFluff lints the code after it has been rendered, and so only has access to code which is still present after that process. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, {% if False %} \-\- This section of the code cannot be linted because \-\- it is never rendered due to the \(gaif False\(ga condition. my + poorly + spaced \- and/indented AS section_of_code {% endif %} e FROM my_table .ft P .fi .UNINDENT .UNINDENT .sp More complex templated cases are usually characterised by templated tags \fIcutting across the parse tree\fP\&. This more formally is where the opening and closing tags of a templated section exist at different levels in the parsed structure. Starting in version 2.x, these will be treated differently (Prior to version 2.x, situations like this were sometimes handled inconsistently or incorrectly). .sp Indentation should act as a visual cue to the structure of the written SQL, and as such, the most important thing is that template tags belonging to the same block structure use the same indentation. In the example below, this is the opening and closing elements of the second \fBif\fP statement. If treated as a simple case, these tags would have different indents, because they are at different levels of the parse tree and so clearly there is a conflict to be resolved. .sp The view SQLFluff takes on how to resolve this conflict is to pull all of the tags in this section down to the indent of the \fIleast indented\fP (in the example below that would be the closing \fBendif\fP tag). This is similar to the treatment of \fI\%C Preprocessor Directives\fP, which are treated somewhat as being outside the structure of the rest of the file. In these cases, the content is also \fInot further indented\fP as in the simple case because it makes it harder to line up elements within the affected section and outside (in the example below the \fBSELECT\fP and \fBFROM\fP are a good illustration). .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, {% if True %} \-\- This is a simple case. The opening and closing tag are \-\- both at the same level within the SELECT clause. simple_case AS example, {% endif %} b, {% if True %} \-\- This is a complex case. The opening tag is within the SELECT \-\- clause, but the closing tag is outside the statement \-\- entirely. complex_case AS example FROM table_option_one {% else %} complex_case_two AS example FROM table_option_two {% endif %} .ft P .fi .UNINDENT .UNINDENT .SS Configuring Layout .sp Configuration for layout is spread across three places: .INDENT 0.0 .IP 1. 3 Indent behavior for particular dialect elements is controlled by the parser. This is because in the background SQLFluff inserts \fBIndent\fP and \fBDedent\fP tokens into the parse tree where those things are expected. For more detail see \fI\%Configuring indent locations\fP\&. .IP 2. 3 Configuration for the spacing and line position of particular types of element (such as commas or operators) is set in the \fBlayout\fP section of the config file. For more detail see \fI\%Configuring layout and spacing\fP\&. .IP 3. 3 Some elements of layout are still controlled by rules directly. These are usually very specific cases, see \fI\%Rules Reference\fP for more details. .UNINDENT .SS Configuring indent locations .sp One of the key areas for this is the indentation of the \fBJOIN\fP expression, which we\(aqll use as an example. .sp Semantically, a \fBJOIN\fP expression is part of the \fBFROM\fP expression and therefore would be expected to be indented. However according to many of the most common SQL style guides (including the \fI\%dbt Labs SQL style guide\fP and the \fI\%Mozilla SQL style guide\fP) the \fBJOIN\fP keyword is expected to at the same indent as the \fBFROM\fP keyword. By default, \fISQLFluff\fP sides with the current consensus, which is to \fInot\fP indent the \fBJOIN\fP keyword, however this is one element which is configurable. .sp By setting values in the \fBsqlfluff:indentation\fP section of your config file you can control how this is parsed. .sp For example, the default indentation would be as follows: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 .ft P .fi .UNINDENT .UNINDENT .sp By setting your config file to: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:indentation] indented_joins = True .ft P .fi .UNINDENT .UNINDENT .sp Then the expected indentation will be: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 .ft P .fi .UNINDENT .UNINDENT .sp There is a similar \fBindented_using_on\fP config (defaulted to \fBTrue\fP) which can be set to \fBFalse\fP to prevent the \fBUSING\fP or \fBON\fP clause from being indented, in which case the original SQL would become: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 .ft P .fi .UNINDENT .UNINDENT .sp There is also a similar \fBindented_on_contents\fP config (defaulted to \fBTrue\fP) which can be set to \fBFalse\fP to align any \fBAND\fP subsections of an \fBON\fP block with each other. If set to \fBFalse\fP the original SQL would become: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 .ft P .fi .UNINDENT .UNINDENT .sp These can also be combined, so if \fBindented_using_on\fP config is set to \fBFalse\fP, and \fBindented_on_contents\fP is also set to \fBFalse\fP then the SQL would become: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 .ft P .fi .UNINDENT .UNINDENT .sp There is also a similar \fBindented_ctes\fP config (defaulted to \fBFalse\fP) which can be set to \fBTrue\fP to enforce CTEs to be indented within the \fBWITH\fP clause: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C WITH some_cte AS ( SELECT 1 FROM table1 ), some_other_cte AS ( SELECT 1 FROM table1 ) SELECT 1 FROM some_cte .ft P .fi .UNINDENT .UNINDENT .sp By default, \fISQLFluff\fP aims to follow the most common approach to indentation. However, if you have other versions of indentation which are supported by published style guides, then please submit an issue on GitHub to have that variation supported by \fISQLFluff\fP\&. .SS Configuring layout and spacing .sp The \fB[sqlfluff:layout]\fP section of the config controls the treatment of spacing and line breaks across all rules. The syntax of this section is very expressive; however in normal use, only very small alterations should be necessary from the \fI\%Default Configuration\fP\&. .sp The syntax of the section headings here select by \fItype\fP, which corresponds to the \fBtype\fP defined in the dialect. For example the following section applies to elements of the \fItype\fP \fBcomma\fP, i.e. \fB,\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:layout:type:comma] spacing_before = touch line_position = trailing .ft P .fi .UNINDENT .UNINDENT .sp Within these configurable sections there are a few key elements which are available: .INDENT 0.0 .IP \(bu 2 \fBSpacing Elements\fP: \fBspacing_before\fP, \fBspacing_after\fP and \fBspacing_within\fP\&. For each of these options, there are a few possible settings: .INDENT 2.0 .IP \(bu 2 The default spacing for all elements is \fBsingle\fP unless otherwise specified. In this state, elements will be spaced with a single space character unless there is a line break between them. .IP \(bu 2 The value of \fBtouch\fP allows line breaks, but if no line break is present, then no space should be present. A great example of this is the spacing before commas (as shown in the config above), where line breaks may be allowed, but if not they should \fItouch\fP the element before. .IP \(bu 2 The value of \fBinline\fP is effectively the same as \fBtouch\fP but in addition, no line breaks are allowed. This is best illustrated by the spacing found in a qualified identifier like \fBmy_schema.my_table\fP\&. .UNINDENT .IP \(bu 2 \fBLine Position\fP: set using the \fBline_position\fP option. By default this is unset, which implies no particular line position requirements. The available options are: .INDENT 2.0 .IP \(bu 2 \fBtrailing\fP and \fBleading\fP, which are most common in the placement of commas. Both of these settings \fIalso\fP allow the option of a comma on its own on a line, or in the middle of a line, \fIbut\fP if there is a line break on \fIeither side\fP then they make sure it\(aqs on the \fIcorrect side\fP\&. By default we assume \fItrailing\fP commas, but if you (or your organisation) have settled on \fIleading\fP commas then you should add the following section to your config: .INDENT 2.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:layout:type:comma] line_position = leading .ft P .fi .UNINDENT .UNINDENT .IP \(bu 2 \fBalone\fP, which means if there is a line break on either side, then there must be a line break on \fIboth sides\fP (i.e. that it should be the only thing on that line. .IP \(bu 2 All of the above options can be qualified with the \fB:strict\fP modifier \- which prevents the \fIinline\fP case. For example: .INDENT 2.0 .INDENT 3.5 .sp .nf .ft C \-\- Setting line_position to just \(gaalone\(ga \-\- within [sqlfluff:layout:type:set_operator] \-\- would not allow: SELECT a UNION SELECT b; \-\- ...or... SELECT a UNION SELECT b; \-\- but *would* allow both of the following: SELECT a UNION SELECT b; SELECT a UNION SELECT b; \-\- However the default is set to \(gaalone:strict\(ga \-\- then the *only* acceptable configuration is: SELECT a UNION SELECT b; .ft P .fi .UNINDENT .UNINDENT .UNINDENT .UNINDENT .SS Rules Reference .sp \fIRules\fP in \fISQLFluff\fP are implemented as \fIcrawlers\fP\&. These are entities which work their way through the parsed structure of a query to evaluate a particular rule or set of rules. The intent is that the definition of each specific rule should be really streamlined and only contain the logic for the rule itself, with all the other mechanics abstracted away. .SS Core Rules .sp Certain rules belong to the \fBcore\fP rule group. In order for a rule to be designated as \fBcore\fP, it must meet the following criteria: .INDENT 0.0 .IP \(bu 2 Stable .IP \(bu 2 Applies to most dialects .IP \(bu 2 Could detect a syntax issue .IP \(bu 2 Isn’t too opinionated toward one style (e.g. the \fBdbt\fP style guide) .UNINDENT .sp Core rules can also make it easier to roll out SQLFluff to a team by only needing to follow a \(aqcommon sense\(aq subset of rules initially, rather than spending time understanding and configuring all the rules, some of which your team may not necessarily agree with. .sp We believe teams will eventually want to enforce more than just the core rules, and we encourage everyone to explore all the rules and customize a rule set that best suites their organization. .sp See the \fI\%Configuration\fP section for more information on how to enable only \fBcore\fP rules by default. .SS Specific Rules .SS Inline Ignoring Errors .sp \fISQLFluff\fP features inline error ignoring. For example, the following will ignore the lack of whitespace surrounding the \fB*\fP operator. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C a.a*a.b AS bad_1 \-\- noqa: L006 .ft P .fi .UNINDENT .UNINDENT .sp Multiple rules can be ignored by placing them in a comma\-delimited list. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C a.a * a.b AS bad_2, \-\- noqa: L007, L006 .ft P .fi .UNINDENT .UNINDENT .sp It is also possible to ignore non\-rule based errors, and instead opt to ignore templating (\fBTMP\fP) & parsing (\fBPRS\fP) errors. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C WHERE col1 = 2 AND dt >= DATE_ADD(CURRENT_DATE(), INTERVAL \-2 DAY) \-\- noqa: PRS .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 It should be noted that ignoring \fBTMP\fP and \fBPRS\fP errors can lead to incorrect \fBsqlfluff lint\fP and \fBsqfluff fix\fP results as \fISQLFluff\fP can misinterpret the SQL being analysed. .UNINDENT .UNINDENT .sp Should the need arise, not specifying specific rules to ignore will ignore all rules on the given line. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C a.a*a.b AS bad_3 \-\- noqa .ft P .fi .UNINDENT .UNINDENT .SS Ignoring line ranges .sp Similar to \fI\%pylint\(aqs \(dqpylint\(dq directive\(dq\fP, ranges of lines can be ignored by adding \fB\-\- noqa:disable=[,...] | all\fP to the line. Following this directive, specified rules (or all rules, if \(dqall\(dq was specified) will be ignored until a corresponding \fI\-\- noqa:enable=[,...] | all\fP directive. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- 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 .ft P .fi .UNINDENT .UNINDENT .SS Dialects Reference .sp SQLFluff is designed to be flexible in supporting a variety of dialects. Not all potential dialects are supported so far, but several have been implemented by the community. Below are a list of the currently available dialects. Each inherits from another, up to the root \fIansi\fP dialect. .sp For a canonical list of supported dialects, run the \fBsqlfluff dialects\fP command, which will output a list of the current dialects available on your installation of SQLFluff. .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 For technical users looking to add new dialects or add new features to existing ones, the dependent nature of how dialects have been implemented is to try and reduce the amount of repetition in how different elements are defined. As an example, when we say that the \fI\%Redshift\fP dialect \fIinherits\fP from the \fI\%PostgreSQL\fP dialect this is not because there is an agreement between those projects which means that features in one must end up in the other, but that the design of the \fI\%Redshift\fP dialect was heavily \fIinspired\fP by the postgres dialect and therefore when defining the dialect within sqlfuff it makes sense to use \fI\%PostgreSQL\fP as a starting point rather than starting from scratch. .sp Consider when adding new features to a dialect: .INDENT 0.0 .IP \(bu 2 Should I be adding it just to this dialect, or adding it to a \fIparent\fP dialect? .IP \(bu 2 If I\(aqm creating a new dialect, which dialect would be best to inherit from? .IP \(bu 2 Will the feature I\(aqm adding break any \fIdownstream\fP dependencies within dialects which inherit from this one? .UNINDENT .UNINDENT .UNINDENT .SS ANSI .sp This is the base dialect which holds most of the definitions of common SQL commands and structures. If the dialect which you\(aqre actually using isn\(aqt specifically implemented by SQLFluff, using this dialect is a good place to start. .sp This dialect doesn\(aqt intend to be brutal in adhering to (and only to) the ANSI SQL spec \fI(mostly because ANSI charges for access to that spec)\fP\&. It aims to be a representation of vanilla SQL before any other project adds their spin to it, and so may contain a slightly wider set of functions than actually available in true ANSI SQL. .SS Athena .sp The dialect for \fI\%Amazon Athena\fP\&. .SS BigQuery .sp The dialect for \fI\%Google BigQuery\fP\&. .SS ClickHouse .sp The dialect for \fI\%ClickHouse\fP\&. .SS Databricks .sp The dialect \fI\%Databricks\fP is an alias for the \fI\%SparkSQL\fP\&. .sp Since Databricks \fI\%builds on top of\fP Apache Spark, the Spark SQL dialect holds most of the definitions of common commands and structures. .sp Specifics to Databricks, such as Delta Live Table syntax, are added to the Spark SQL dialect to simplify implementation and prevent code duplication for minor syntax updates. This follows SQLFluff\(aqs philosophy of not being strict in adhering to dialect specifications to permit slightly wider set of functions than actually available in a given dialect. .SS Db2 .sp The dialect for \fI\%Db2\fP\&. .SS Exasol .sp The dialect for \fI\%Exasol\fP\&. .SS Hive .sp The dialect for \fI\%Hive\fP\&. .SS Materialize .sp The dialect for \fI\%Materialize\fP\&. .SS MySQL .sp The dialect for \fI\%MySQL\fP\&. .SS Oracle .sp The dialect for \fI\%Oracle\fP SQL. Note: this does not include PL/SQL. .SS PostgreSQL .sp This is based around the \fI\%PostgreSQL spec\fP\&. Many other SQL instances are often based on PostreSQL syntax. If you\(aqre running an unsupported dialect, then this is often the dialect to use (until someone makes a specific dialect). .SS Redshift .sp The dialect for \fI\%Amazon Redshift\fP\&. .SS Snowflake .sp The dialect for \fI\%Snowflake\fP, which has much of its syntax inherited from \fI\%PostgreSQL\fP\&. .SS SOQL .sp The dialect for \fI\%SOQL\fP (Salesforce Object Query Language). .SS SparkSQL .sp The dialect for Apache \fI\%Spark SQL\fP\&. It inherits from \fI\%ANSI\fP and includes relevant syntax from \fI\%Hive\fP for commands that permit Hive Format. Spark SQL extensions provided by the \fI\%Delta Lake\fP project are also implemented in this dialect. .sp This implementation focuses on the \fI\%Ansi Compliant Mode\fP introduced in Spark3, instead of being Hive Compliant. The introduction of ANSI Compliance provides better data quality and easier migration from traditional DBMS. .sp Versions of Spark prior to 3.x will only support the Hive dialect. .SS SQLite .sp The dialect for \fI\%SQLite\fP\&. .SS T\-SQL .sp The dialect for \fI\%T\-SQL\fP (aka Transact\-SQL). .SS Teradata .sp The dialect for \fI\%Teradata\fP\&. .SS Production Usage .sp SQLFluff is designed to be used both as a utility for developers but also to be part of \fI\%CI/CD\fP pipelines. .SS Using SQLFluff on a whole sql codebase .sp The \fI\%exit code\fP provided by SQLFluff when run as a command line utility is designed to assist usefulness in deployment pipelines. If no violations are found then the \fI\%exit code\fP will be 0. If violations are found then a non\-zero code will be returned which can be interrogated to find out more. .INDENT 0.0 .IP \(bu 2 At the moment all error states related to linting return \fI65\fP\&. .IP \(bu 2 An error as a result of a SQLFluff internal error will return \fI1\fP\&. .UNINDENT .SS Using SQLFluff on changes using \fIdiff\-quality\fP .sp For projects with large amounts of (potentially imperfect) SQL code, the full SQLFluff output could be very large, which can be distracting \-\- perhaps the CI build for a one\-line SQL change shouldn\(aqt encourage the developer to fix lots of unrelated quality issues. .sp To support this use case, SQLFluff integrates with a quality checking tool called \fIdiff\-quality\fP\&. By running SQLFluff using \fIdiff\-quality\fP (rather than running it directly), you can limit the the output to the new or modified SQL in the branch (aka pull request or PR) containing the proposed changes. .sp Currently, \fBdiff\-quality\fP requires that you are using \fBgit\fP for version control. .sp NOTE: Installing SQLFluff automatically installs the \fIdiff_cover\fP package that provides the \fIdiff\-quality\fP tool. .SS Adding \fIdiff\-quality\fP to your builds .sp In your CI build script: .sp 1. Set the current working directory to the \fBgit\fP repository containing the SQL code to be checked. .INDENT 0.0 .IP 2. 3 Run \fIdiff\-quality\fP, specifying SQLFluff as the underlying tool: .UNINDENT .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ diff\-quality \-\-violations sqlfluff .ft P .fi .UNINDENT .UNINDENT .sp The output will look something like: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\-\-\-\-\-\-\-\-\-\-\-\- Diff Quality Quality Report: sqlfluff Diff: origin/master...HEAD, staged and unstaged changes \-\-\-\-\-\-\-\-\-\-\-\-\- sql/audience_size_queries/constraints/_postcondition_check_gdpr_compliance.sql (0.0%): sql/audience_size_queries/constraints/_postcondition_check_gdpr_compliance.sql:5: Unquoted Identifiers must be consistently upper case. \-\-\-\-\-\-\-\-\-\-\-\-\- Total: 1 line Violations: 1 line % Quality: 0% \-\-\-\-\-\-\-\-\-\-\-\-\- .ft P .fi .UNINDENT .UNINDENT .sp These messages are basically the same as those provided directly by SQLFluff, although the format is a little different. Note that \fIdiff\-quality\fP only lists the line _numbers_, not the character position. If you need the character position, you will need to run SQLFluff directly. .sp For more information on \fIdiff\-quality\fP, see the \fI\%documentation\fP\&. It covers topics such as: .INDENT 0.0 .IP \(bu 2 Generating HTML reports .IP \(bu 2 Controlling which branch to compare against (i.e. to determine new/changed lines). The default is \fIorigin/master\fP\&. .IP \(bu 2 Configuring \fIdiff\-quality\fP to return an error code if the quality is too low .IP \(bu 2 Troubleshooting .UNINDENT .SS Using \fI\%pre\-commit\fP .sp \fI\%pre\-commit\fP is a framework to manage git \(dqhooks\(dq triggered right before a commit is made. .sp A \fI\%git hook\fP is a git feature to \(dqfire off custom scripts\(dq when specific actions occur. .sp Using \fI\%pre\-commit\fP with SQLFluff is a good way to provide automated linting to SQL developers. .sp With \fI\%pre\-commit\fP, you also get the benefit of only linting/fixing the files that changed. .sp SQLFluff comes with two \fI\%pre\-commit\fP hooks: .INDENT 0.0 .IP \(bu 2 sqlfluff\-lint: returns linting errors. .IP \(bu 2 sqlfluff\-fix: attempts to fix rule violations. .UNINDENT .sp \fBWARNING:\fP .INDENT 0.0 .INDENT 3.5 For safety reasons, \fBsqlfluff\-fix\fP by default will not make any fixes in files that had templating or parse errors, even if those errors were ignored using \fBnoqa\fP or \fI\-\-ignore\(ga\fP\&. .sp Although it is not advised, you \fIcan\fP tell SQLFluff to try and fix these files by overriding the \fBfix_even_unparsable\fP setting in \fB\&.sqlfluff\fP config file or using the \fBsqlfluff fix \-\-FIX\-EVEN\-UNPARSABLE\fP command line option. .sp \fIOverriding this behavior may break your SQL. If you use this override, always be sure to review any fixes applied to files with templating or parse errors to verify they are okay.\fP .UNINDENT .UNINDENT .sp You should create a file named \fI\&.pre\-commit\-config.yaml\fP at the root of your git project, which should look like this: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C repos: \- repo: https://github.com/sqlfluff/sqlfluff rev: 1.4.5 hooks: \- id: sqlfluff\-lint # For dbt projects, this installs the dbt \(dqextras\(dq. # You will need to select the relevant dbt adapter for your dialect # (https://docs.getdbt.com/docs/available\-adapters): # additional_dependencies: [\(aq\(aq, \(aqsqlfluff\-templater\-dbt\(aq] \- id: sqlfluff\-fix # Arbitrary arguments to show an example # args: [\-\-rules, \(dqL003,L014\(dq] # additional_dependencies: [\(aq\(aq, \(aqsqlfluff\-templater\-dbt\(aq] .ft P .fi .UNINDENT .UNINDENT .sp When trying to use the \fI\%dbt templater\fP, uncomment the \fBadditional_dependencies\fP to install the extras. This is equivalent to running \fBpip install sqlfluff\-templater\-dbt\fP\&. .sp You can specify the version of \fBdbt\-adapter\fP used in \fI\%pre\-commit\fP, for example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C additional_dependencies : [\(aqdbt\-bigquery==1.0.0\(aq, \(aqsqlfluff\-templater\-dbt\(aq] .ft P .fi .UNINDENT .UNINDENT .sp See the list of available \fI\%dbt\-adapters\fP\&. .sp Note that you can pass the same arguments available through the CLI using \fBargs:\fP\&. .SS Using \fI\%GitHub Actions\fP to Annotate PRs .sp There are two way to utilize SQLFluff to annotate Github PRs. .INDENT 0.0 .IP 1. 3 When \fIsqlfluff lint\fP is run with the \fI\-\-format github\-annotation\-native\fP option, it produces output formatted as \fI\%Github workflow commands\fP which are converted into pull request annotations by Github. .IP 2. 3 When \fIsqlfluff lint\fP is run with the \fI\-\-format github\-annotation\fP option, it produces output compatible with this \fI\%action\fP\&. Which uses Github API to annotate the SQL in \fIGitHub pull requests\fP\&. .UNINDENT .sp For more information and examples on using SQLFluff in GitHub Actions, see the \fI\%sqlfluff\-github\-actions repository\fP\&. .SS Configuration .sp SQLFluff accepts configuration either through the command line or through configuration files. There is \fIrough\fP parity between the two approaches with the exception that \fItemplating\fP configuration must be done via a file, because it otherwise gets slightly complicated. .sp For details of what\(aqs available on the command line check out the \fI\%CLI Reference\fP\&. .sp For file based configuration \fISQLFluff\fP will look for the following files in order. Later files will (if found) will be used to overwrite any values read from earlier files. .INDENT 0.0 .IP \(bu 2 \fBsetup.cfg\fP .IP \(bu 2 \fBtox.ini\fP .IP \(bu 2 \fBpep8.ini\fP .IP \(bu 2 \fB\&.sqlfluff\fP .IP \(bu 2 \fBpyproject.toml\fP .UNINDENT .sp Within these files, the first four will be read like a \fI\%cfg file\fP, and \fISQLFluff\fP will look for sections which start with \fBsqlfluff\fP, and where subsections are delimited by a semicolon. For example the \fIjinjacontext\fP section will be indicated in the section started with \fB[sqlfluff:jinjacontext]\fP\&. .sp For example, a snippet from a \fB\&.sqlfluff\fP file (as well as any of the supported cfg file types): .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [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 .ft P .fi .UNINDENT .UNINDENT .sp For the \fI\%pyproject.toml file\fP, all valid sections start with \fBtool.sqlfluff\fP and subsections are delimited by a dot. For example the \fIjinjacontext\fP section will be indicated in the section started with \fB[tool.sqlfluff.jinjacontext]\fP\&. .sp For example, a snippet from a \fBpyproject.toml\fP file: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [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 .ft P .fi .UNINDENT .UNINDENT .SS Nesting .sp \fBSQLFluff\fP uses \fBnesting\fP in its configuration files, with files closer \fIoverriding\fP (or \fIpatching\fP, if you will) values from other files. That means you\(aqll 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 \fItemplater\fP, which cannot be set in config files in subdirectories of the working directory. You don\(aqt \fBneed\fP any config files to be present to make \fISQLFluff\fP 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: .INDENT 0.0 .IP 0. 4 \fI[...and this one doesn\(aqt really count]\fP There\(aqs a default config as part of the SQLFluff package. You can find this below, in the \fI\%Default Configuration\fP section. .IP 1. 4 It will look in the user\(aqs os\-specific app config directory. On OSX this is \fI~/Library/Preferences/sqlfluff\fP, Unix is \fI~/.config/sqlfluff\fP, Windows is \fI\eAppData\eLocal\esqlfluff\esqlfluff\fP, for any of the filenames above in the main \fI\%Configuration\fP section. If multiple are present, they will \fIpatch\fP/\fIoverride\fP each other in the order above. .IP 2. 4 It will look for the same files in the user\(aqs home directory (~). .IP 3. 4 It will look for the same files in the current working directory. .IP 4. 4 \fI[if parsing a file in a subdirectory of the current working directory]\fP It will look for the same files in every subdirectory between the current working dir and the file directory. .IP 5. 4 It will look for the same files in the directory containing the file being linted. .UNINDENT .sp This whole structure leads to efficient configuration, in particular in projects which utilise a lot of complicated templating. .SS In\-File Configuration Directives .sp In addition to configuration files mentioned above, SQLFluff also supports comment based configuration switching in files. This allows specific SQL file to modify a default configuration if they have specific needs. .sp When used, these apply to the whole file, and are parsed from the file in an initial step before the rest of the file is properly parsed. This means they can be used for both rule configuration and also for parsing configuration. .sp To use these, the syntax must start as an \fIinline sql comment\fP beginning with \fBsqlfluff\fP (i.e. \fB\-\- sqlfluff\fP). The line is then interpreted as a colon\-seperated address of the configuation value you wish to set. A few common examples are shown below: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- Set Indented Joins \-\- sqlfluff:indentation:indented_joins:true \-\- Set a smaller indent for this file \-\- sqlfluff:indentation:tab_space_size:2 \-\- Set keywords to be capitalised \-\- sqlfluff:rules:L010:capitalisation_policy:upper SELECT * FROM a JOIN b USING(c) .ft P .fi .UNINDENT .UNINDENT .sp We recommend only using this configuration approach for configuration that applies to one file in isolation. For configuration changes for areas of a project or for whole projects we recommend \fI\%Nesting\fP of configuration files. .SS Rule Configuration .sp Rules can be configured with the \fB\&.sqlfluff\fP config files. .sp Common rule configurations can be set in the \fB[sqlfluff:rules]\fP section. .sp For example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:rules] tab_space_size = 4 max_line_length = 80 indent_unit = space allow_scalar = True single_table_references = consistent unquoted_identifiers_policy = all .ft P .fi .UNINDENT .UNINDENT .sp Rule specific configurations are set in rule specific subsections. .sp For example, enforce that keywords are upper case by configuring the rule \fBL010\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:rules:L010] # Keywords capitalisation_policy = upper .ft P .fi .UNINDENT .UNINDENT .sp All possible options for rule sections are documented in \fI\%Rules Reference\fP\&. .sp For an overview of the most common rule configurations that you may want to tweak, see \fI\%Default Configuration\fP (and use \fI\%Rules Reference\fP to find the available alternatives). .SS Enabling and Disabling Rules .sp To disable individual rules, set \fBexclude_rules\fP in the top level section of sqlfluff configuration. The value is a comma separated list of rule ids. .sp For example, to disable the rules \fBL022\fP and \fBL027\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] exclude_rules = L022, L027 .ft P .fi .UNINDENT .UNINDENT .sp To enable individual rules, configure \fBrules\fP, respectively. .sp For example, to enable \fBL027\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] rules = L027 .ft P .fi .UNINDENT .UNINDENT .sp If both \fBexclude_rules\fP and \fBrules\fP 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. .sp Rules can also be enabled/disabled by their grouping. Right now, the only rule grouping is \fBcore\fP\&. This will enable (or disable) a select group of rules that have been deemed \(aqcore rules\(aq. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] rules = core .ft P .fi .UNINDENT .UNINDENT .sp More information about \(aqcore rules\(aq can be found in the \fI\%Rules Reference\fP\&. .sp Additionally, some rules have a special \fBforce_enable\fP 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 \fI\%Rules Reference\fP\&. .sp The default values can be seen in \fI\%Default Configuration\fP\&. .sp See also: \fI\%Ignoring Errors & Files\fP\&. .SS Downgrading rules to warnings .sp To keep displaying violations for specific rules, but not have those issues lead to a failed run, rules can be downgraded to \fIwarnings\fP\&. Rules set as \fIwarnings\fP won\(aqt cause a file to fail, but will still be shown in the CLI to warn users of their presence. .sp The configuration of this behaves very like \fBexclude_rules\fP above: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] warnings = L019, L007 .ft P .fi .UNINDENT .UNINDENT .sp With this configuration, files with no other issues (other than those set to warn) will pass. If there are still other issues, then the file will still fail, but will show both warnings and failures. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C == [test.sql] PASS L: 2 | P: 9 | L006 | WARNING: Missing whitespace before + == [test2.sql] FAIL L: 2 | P: 8 | L014 | Unquoted identifiers must be consistently upper case. L: 2 | P: 11 | L006 | WARNING: Missing whitespace before + .ft P .fi .UNINDENT .UNINDENT .sp This is particularly useful as a transitional tool when considering the introduction on new rules on a project where you might want to make users aware of issues without blocking their workflow (yet). .SS Layout & Spacing Configuration .sp The \fB[sqlfluff:layout]\fP section of the config controls the treatment of spacing and line breaks across all rules. To understand more about this section, see the section of the docs dedicated to layout: \fI\%Configuring Layout\fP\&. .SS Jinja Templating Configuration .sp When thinking about Jinja templating there are two different kinds of things that a user might want to fill into a templated file, \fIvariables\fP and \fIfunctions/macros\fP\&. Currently \fIfunctions\fP aren\(aqt implemented in any of the templaters. .SS Variable Templating .sp Variables are available in the \fIjinja\fP, \fIpython\fP and \fIplaceholder\fP 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 \fIjinja\fP templater is found in the \fIsqlfluff:templater:jinja:context section, the config for the *python\fP templater is found in the \fIsqlfluff:templater:python:context\fP section, the one for the \fIplaceholder\fP templater is found in the \fIsqlfluff:templater:placeholder:context\fP section .sp For example, if passed the following \fI\&.sql\fP file: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT {{ num_things }} FROM {{ tbl_name }} WHERE id > 10 LIMIT 5 .ft P .fi .UNINDENT .UNINDENT .sp \&...and the following configuration in \fI\&.sqlfluff\fP in the same directory: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja:context] num_things=456 tbl_name=my_table .ft P .fi .UNINDENT .UNINDENT .sp \&...then before parsing, the sql will be transformed to: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT 456 FROM my_table WHERE id > 10 LIMIT 5 .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 If there are variables in the template which cannot be found in the current configuration context, then this will raise a \fISQLTemplatingError\fP and this will appear as a violation without a line number, quoting the name of the variable that couldn\(aqt be found. .UNINDENT .UNINDENT .SS Placeholder templating .sp Libraries such as SQLAlchemy or Psycopg use different parameter placeholder styles to mark where a parameter has to be inserted in the query. .sp For example a query in SQLAlchemy can look like this: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT * FROM table WHERE id = :myid .ft P .fi .UNINDENT .UNINDENT .sp At runtime \fI:myid\fP 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. .sp In order to parse these queries is then necessary to replace these placeholders with sample values, and this is done with the placeholder templater. .sp Placeholder templating can be enabled in the config using: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] templater = placeholder .ft P .fi .UNINDENT .UNINDENT .sp A few common styles are supported: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- colon WHERE bla = :my_name \-\- colon_nospaces \-\- (use with caution as more prone to false positives) WHERE bla = table:my_name \-\- numeric_colon WHERE bla = :2 \-\- pyformat WHERE bla = %(my_name)s \-\- dollar WHERE bla = $my_name or WHERE bla = ${my_name} \-\- question_mark WHERE bla = ? \-\- numeric_dollar WHERE bla = $3 or WHERE bla = ${3} \-\- percent WHERE bla = %s \-\- ampersand WHERE bla = &s or WHERE bla = &{s} or USE DATABASE MARK_{ENV} .ft P .fi .UNINDENT .UNINDENT .sp These can be configured by setting \fIparam_style\fP to the names above: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:placeholder] param_style = colon my_name = \(aqjohn\(aq .ft P .fi .UNINDENT .UNINDENT .sp then it is necessary to set sample values for each parameter, like \fImy_name\fP above. Notice that the value needs to be escaped as it will be replaced as a string during parsing. .sp When parameters are positional, like \fIquestion_mark\fP, then their name is simply the order in which they appear, starting with \fI1\fP\&. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:placeholder] param_style = question_mark 1 = \(aqjohn\(aq .ft P .fi .UNINDENT .UNINDENT .sp In case you need a parameter style different from the ones above, you can pass a custom regex. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:placeholder] param_regex = __(?P[\ew_]+)__ my_name = \(aqjohn\(aq .ft P .fi .UNINDENT .UNINDENT .sp N.B. quotes around \fIparam_regex\fP in the config are interpreted literally by the templater. e.g. \fIparam_regex=\(aq__(?P[w_]+)__\(aq\fP matches \fI\(aq__some_param__\(aq\fP not \fI__some_param__\fP .sp the named parameter \fIparam_name\fP will be used as the key to replace, if missing, the parameter is assumed to be positional and numbers are used instead. .sp 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. .SS Complex Variable Templating .sp Two more advanced features of variable templating are \fIcase sensitivity\fP and \fInative python types\fP\&. Both are illustrated in the following example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja:context] my_list = [\(aqa\(aq, \(aqb\(aq, \(aqc\(aq] MY_LIST = (\(dqd\(dq, \(dqe\(dq, \(dqf\(dq) my_where_dict = {\(dqfield_1\(dq: 1, \(dqfield_2\(dq: 2} .ft P .fi .UNINDENT .UNINDENT .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT {% for elem in MY_LIST %} \(aq{{elem}}\(aq {% 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 %} .ft P .fi .UNINDENT .UNINDENT .sp \&...will render as... .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT \(aqd\(aq || \(aqe\(aq || \(aqf\(aq as concatenated_list FROM tbl WHERE field_1 = 1 and field_2 = 2 .ft P .fi .UNINDENT .UNINDENT .sp 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. .SS Macro Templating .sp Macros (which also look and feel like \fIfunctions\fP are available only in the \fIjinja\fP templater. Similar to \fI\%Variable Templating\fP, these are specified in config files, what\(aqs different in this case is how they are named. Similar to the \fIcontext\fP section above, macros are configured separately in the \fImacros\fP section of the config. Consider the following example. .sp If passed the following \fI\&.sql\fP file: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT {{ my_macro(6) }} FROM some_table .ft P .fi .UNINDENT .UNINDENT .sp \&...and the following configuration in \fI\&.sqlfluff\fP in the same directory (note the tight control of whitespace): .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja:macros] a_macro_def = {% macro my_macro(n) %}{{ n }} + {{ n * 2 }}{% endmacro %} .ft P .fi .UNINDENT .UNINDENT .sp \&...then before parsing, the sql will be transformed to: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT 6 + 12 FROM some_table .ft P .fi .UNINDENT .UNINDENT .sp Note that in the code block above, the variable name in the config is \fIa_macro_def\fP, and this isn\(aqt apparently otherwise used anywhere else. Broadly this is accurate, however within the configuration loader this will still be used to overwrite previous \fIvalues\fP in other config files. As such this introduces the idea of config \fIblocks\fP which could be selectively overwritten by other configuration files downstream as required. .sp 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: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja] load_macros_from_path = my_macros .ft P .fi .UNINDENT .UNINDENT .sp \fBload_macros_from_path\fP is a comma\-separated list of \fB\&.sql\fP files or folders. Locations are \fIrelative to the config file\fP\&. For example, if the config file above was found at \fB/home/my_project/.sqlfluff\fP, then SQLFluff will look for macros in the folder \fB/home/my_project/my_macros/\fP (but not subfolders). Any macros defined in the config will always take precedence over a macro defined in the path. .INDENT 0.0 .IP \(bu 2 \fB\&.sql\fP files: Macros in these files are available in every \fB\&.sql\fP file without requiring a Jinja \fBinclude\fP or \fBimport\fP\&. .IP \(bu 2 Folders: To use macros from the \fB\&.sql\fP files in folders, use Jinja \fBinclude\fP or \fBimport\fP as explained below. .UNINDENT .sp \fBNote:\fP The \fBload_macros_from_path\fP setting also defines the search path for Jinja \fI\%include\fP or \fI\%import\fP\&. Unlike with macros (as noted above), subdirectories are supported. For example, if \fBload_macros_from_path\fP is set to \fBmy_macros\fP, and there is a file \fBmy_macros/subdir/my_file.sql\fP, you can do: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C {% include \(aqsubdir/include_comment.sql\(aq %} .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 Throughout the templating process \fBwhitespace\fP will still be treated rigorously, and this includes \fBnewlines\fP\&. In particular you may choose to provide \fIdummy\fP macros in your configuration different from the actual macros used in production. .sp \fBREMEMBER:\fP The reason SQLFluff supports macros is to \fIenable\fP it to parse templated sql without it being a blocker. It shouldn\(aqt be a requirement that the \fItemplating\fP is accurate \- it only needs to work well enough that \fIparsing\fP and \fIlinting\fP are helpful. .UNINDENT .UNINDENT .SS Builtin Macro Blocks .sp One of the main use cases which inspired \fISQLFluff\fP as a project was \fI\%dbt\fP\&. It uses jinja templating extensively and leads to some users maintaining large repositories of sql files which could potentially benefit from some linting. .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 \fISQLFluff\fP has now a tighter integration with dbt through the \(dqdbt\(dq templater. It is the recommended templater for dbt projects. If used, it eliminates the need for the overrides described in this section. .sp To use the dbt templater, go to \fI\%dbt Project Configuration\fP\&. .UNINDENT .UNINDENT .sp \fISQLFluff\fP anticipates this use case and provides some built in macro blocks in the \fI\%Default Configuration\fP which assist in getting started with \fI\%dbt\fP projects. In particular it provides mock objects for: .INDENT 0.0 .IP \(bu 2 \fIref\fP: The mock version of this provided simply returns the model reference as the name of the table. In most cases this is sufficient. .IP \(bu 2 \fIconfig\fP: A regularly used macro in \fI\%dbt\fP to set configuration values. For linting purposes, this makes no difference and so the provided macro simply returns nothing. .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 If there are other builtin macros which would make your life easier, consider submitting the idea (or even better a pull request) on \fI\%github\fP\&. .UNINDENT .UNINDENT .SS Interaction with \fB\-\-ignore=templating\fP .sp Ignoring Jinja templating errors provides a way for users to use SQLFluff while reducing or avoiding the need to spend a lot of time adding variables to \fB[sqlfluff:templater:jinja:context]\fP\&. .sp When \fB\-\-ignore=templating\fP is enabled, the Jinja templater behaves a bit differently. This additional behavior is \fIusually\fP but not \fIalways\fP helpful for making the file at least partially parsable and fixable. It definitely doesn’t \fBguarantee\fP that every file can be fixed, but it’s proven useful for some users. .sp Here\(aqs how it works: .INDENT 0.0 .IP \(bu 2 Within the expanded SQL, undefined variables are automatically \fIreplaced\fP with the corresponding string value. .IP \(bu 2 If you do: \fB{% include query %}\fP, and the variable \fBquery\fP is not defined, it returns a “file” containing the string \fBquery\fP\&. .IP \(bu 2 If you do: \fB{% include \(dqquery_file.sql\(dq %}\fP, and that file does not exist or you haven’t configured a setting for \fBload_macros_from_path\fP, it returns a “file” containing the text \fBquery_file\fP\&. .UNINDENT .sp For example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C select {{ my_variable }} from {% include \(dqmy_table.sql\(dq %} .ft P .fi .UNINDENT .UNINDENT .sp is interpreted as: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C select my_variable from my_table .ft P .fi .UNINDENT .UNINDENT .sp The values provided by the Jinja templater act \fIa bit\fP (not exactly) like a mixture of several types: .INDENT 0.0 .IP \(bu 2 \fBstr\fP .IP \(bu 2 \fBint\fP .IP \(bu 2 \fBlist\fP .IP \(bu 2 Jinja\(aqs \fBUndefined\fP \fI\%class\fP .UNINDENT .sp Because the values behave like \fBUndefined\fP, it\(aqs possible to replace them using Jinja\(aqs \fBdefault()\fP \fI\%filter\fP\&. For example: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C select {{ my_variable | default(\(dqcol_a\(dq) }} from my_table .ft P .fi .UNINDENT .UNINDENT .sp is interpreted as: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C select col_a from my_table .ft P .fi .UNINDENT .UNINDENT .SS Library Templating .sp If using \fISQLFluff\fP 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: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT foo, bar FROM baz {{ dbt_utils.group_by(2) }} .ft P .fi .UNINDENT .UNINDENT .sp To template these libraries, you can use the \fIsqlfluff:jinja:library_path\fP config option: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja] library_path = sqlfluff_libs .ft P .fi .UNINDENT .UNINDENT .sp 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 \fIsqlfluff_libs/dbt_utils.py\fP as: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C def group_by(n): return \(dqGROUP BY 1,2\(dq .ft P .fi .UNINDENT .UNINDENT .sp If an \fI__init__.py\fP is detected, it will be loaded alongside any modules and submodules found within the library path. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C SELECT {{ custom_sum(\(aqfoo\(aq, \(aqbar\(aq) }}, {{ foo.bar.another_sum(\(aqfoo\(aq, \(aqbar\(aq) }} FROM baz .ft P .fi .UNINDENT .UNINDENT .sp \fIsqlfluff_libs/__init__.py\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C def custom_sum(a: str, b: str) \-> str: return a + b .ft P .fi .UNINDENT .UNINDENT .sp \fIsqlfluff_libs/foo/__init__.py\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C # empty file .ft P .fi .UNINDENT .UNINDENT .sp \fIsqlfluff_libs/foo/bar.py\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C def another_sum(a: str, b: str) \-> str: return a + b .ft P .fi .UNINDENT .UNINDENT .SS dbt Project Configuration .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 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. .sp 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. .UNINDENT .UNINDENT .sp \fBdbt\fP is not the default templater for \fISQLFluff\fP (it is \fBjinja\fP). \fBdbt\fP is a complex tool, so using the default \fBjinja\fP templater will be simpler. You should be aware when using the \fBdbt\fP templater that you will be exposed to some of the complexity of \fBdbt\fP\&. Users may wish to try both templaters and choose according to how they intend to use \fISQLFluff\fP\&. .sp A simple rule of thumb might be: .INDENT 0.0 .IP \(bu 2 If you are using \fISQLFluff\fP in a CI/CD context, where speed is not critical but accuracy in rendering sql is, then the \fIdbt\fP templater may be more appropriate. .IP \(bu 2 If you are using \fISQLFluff\fP in an IDE or on a git hook, where speed of response may be more important, then the \fIjinja\fP templater may be more appropriate. .UNINDENT .sp Pros: .INDENT 0.0 .IP \(bu 2 Most (potentially all) macros will work .UNINDENT .sp Cons: .INDENT 0.0 .IP \(bu 2 More complex, e.g. using it successfully may require deeper understanding of your models and/or macros (including third\-party macros) .INDENT 2.0 .IP \(bu 2 More configuration decisions to make .IP \(bu 2 Best practices are not yet established or documented .UNINDENT .IP \(bu 2 If your \fBdbt\fP model files access a database at compile time, using SQLFluff with the \fBdbt\fP templater will \fBalso\fP require access to a database. .INDENT 2.0 .IP \(bu 2 Note that you can often point SQLFluff and the \fBdbt\fP templater at a test database (i.e. it doesn\(aqt have to be the production database). .UNINDENT .IP \(bu 2 Runs slower .UNINDENT .SS Installation & Configuration .sp In order to get started using \fISQLFluff\fP with a dbt project you will first need to install the relevant \fI\%dbt adapter\fP for your dialect and the \fBsqlfluff\-templater\-dbt\fP package using your package manager of choice (e.g. \fBpip install dbt\-postgres sqlfluff\-templater\-dbt\fP) and then will need the following configuration: .sp In \fI\&.sqlfluff\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] templater = dbt .ft P .fi .UNINDENT .UNINDENT .sp In \fI\&.sqlfluffignore\fP: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C target/ # dbt <1.0.0 dbt_modules/ # dbt >=1.0.0 dbt_packages/ macros/ .ft P .fi .UNINDENT .UNINDENT .sp You can set the dbt project directory, profiles directory and profile with: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:dbt] project_dir = profiles_dir = profile = target = .ft P .fi .UNINDENT .UNINDENT .sp \fBNOTE:\fP .INDENT 0.0 .INDENT 3.5 If the \fIprofiles_dir\fP 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 \fI~/.dbt/\fP\&. On Windows, you can determine your default profile directory by running \fIdbt debug \-\-config\-dir\fP\&. .UNINDENT .UNINDENT .sp To use builtin dbt Jinja functions SQLFluff provides a configuration option that enables usage within templates. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja] apply_dbt_builtins = True .ft P .fi .UNINDENT .UNINDENT .sp This will provide dbt macros like \fIref\fP, \fIvar\fP, \fIis_incremental()\fP\&. If the need arises builtin dbt macros can be customised via Jinja macros in \fI\&.sqlfluff\fP configuration file. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:jinja:macros] # Macros provided as builtins for dbt projects dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %} dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %} dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %} dbt_var = {% macro var(variable, default=\(aq\(aq) %}item{% endmacro %} dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %} .ft P .fi .UNINDENT .UNINDENT .sp If your project requires that you pass variables to dbt through command line, you can specify them in \fItemplate:dbt:context\fP section of \fI\&.sqlfluff\fP\&. See below configuration and its equivalent dbt command: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff:templater:dbt:context] my_variable = 1 .ft P .fi .UNINDENT .UNINDENT .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C dbt run \-\-vars \(aq{\(dqmy_variable\(dq: 1}\(aq .ft P .fi .UNINDENT .UNINDENT .SS Known Caveats .INDENT 0.0 .IP \(bu 2 To use the dbt templater, you must set \fItemplater = dbt\fP in the \fI\&.sqlfluff\fP config file in the directory where sqlfluff is run. The templater cannot be changed in \fI\&.sqlfluff\fP files in subdirectories. .IP \(bu 2 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. .UNINDENT .SS CLI Arguments .sp You already know you can pass arguments (\fB\-\-verbose\fP, \fB\-\-exclude\-rules\fP, etc.) through the CLI commands (\fBlint\fP, \fBfix\fP, etc.): .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C $ sqlfluff lint my_code.sql \-v \-\-exclude\-rules L022,L027 .ft P .fi .UNINDENT .UNINDENT .sp You might have arguments that you pass through every time, e.g rules you \fIalways\fP want to ignore. These can also be configured: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] verbose = 1 exclude_rules = L022,L027 .ft P .fi .UNINDENT .UNINDENT .sp Note that while the \fBexclude_rules\fP config looks similar to the above example, the \fBverbose\fP config has an integer value. This is because \fBverbose\fP is \fIstackable\fP meaning there are multiple levels of verbosity that are available for configuration. See \fI\%CLI Reference\fP for more details about the available CLI arguments. For more details about rule exclusion, see \fI\%Enabling and Disabling Rules\fP\&. .SS Ignoring Errors & Files .SS Ignoring individual lines .sp Similar to \fI\%flake8\(aqs ignore\fP, individual lines can be ignored by adding \fB\-\- noqa\fP to the end of the line. Additionally, specific rules can be ignored by quoting their code or the category. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- 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 .ft P .fi .UNINDENT .UNINDENT .SS Ignoring line ranges .sp Similar to \fI\%pylint\(aqs \(dqpylint\(dq directive\(dq\fP, ranges of lines can be ignored by adding \fB\-\- noqa:disable=[,...] | all\fP to the line. Following this directive, specified rules (or all rules, if \(dqall\(dq was specified) will be ignored until a corresponding \fI\-\- noqa:enable=[,...] | all\fP directive. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \-\- 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 .ft P .fi .UNINDENT .UNINDENT .SS Ignoring types of errors .sp General \fIcategories\fP of errors can be ignored using the \fB\-\-ignore\fP command line option or the \fBignore\fP setting in \fB\&.sqlfluffignore\fP\&. Types of errors that can be ignored include: .INDENT 0.0 .IP \(bu 2 \fBlexing\fP .IP \(bu 2 \fBlinting\fP .IP \(bu 2 \fBparsing\fP .IP \(bu 2 \fBtemplating\fP .UNINDENT .SS \&.sqlfluffignore .sp Similar to \fI\%Git\(aqs\fP \fB\&.gitignore\fP and \fI\%Docker\(aqs\fP \fB\&.dockerignore\fP, SQLFluff supports a \fB\&.sqlfluffignore\fP file to control which files are and aren\(aqt linted. Under the hood we use the python \fI\%pathspec library\fP which also has a brief tutorial in their documentation. .sp An example of a potential \fB\&.sqlfluffignore\fP placed in the root of your project would be: .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C # Comments start with a hash. # Ignore anything in the \(dqtemp\(dq path /temp/ # Ignore anything called \(dqtesting.sql\(dq testing.sql # Ignore any \(dq.tsql\(dq files *.tsql .ft P .fi .UNINDENT .UNINDENT .sp 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. .SS Default Configuration .sp The default configuration is as follows, note the \fI\%Builtin Macro Blocks\fP in section \fI[sqlfluff:templater:jinja:macros]\fP as referred to above. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C [sqlfluff] # verbose is an integer (0\-2) indicating the level of log output verbose = 0 # Turn off color formatting of output nocolor = False # Supported dialects https://docs.sqlfluff.com/en/stable/dialects.html # Or run \(aqsqlfluff dialects\(aq dialect = None # One of [raw|jinja|python|placeholder] templater = jinja # Comma separated list of rules to check, default to all rules = all # Comma separated list of rules to exclude, or None exclude_rules = None # The depth to recursively parse to (0 for unlimited) recurse = 0 # Below controls SQLFluff output, see max_line_length for SQL output output_line_length = 80 # Number of passes to run before admitting defeat runaway_limit = 10 # Ignore errors by category (one or more of the following, separated by commas: lexing,linting,parsing,templating) ignore = None # Warn only for rule codes (one of more rule codes, seperated by commas: e.g. L001,L002) # Also works for templating and parsing errors by using TMP or PRS warnings = None # Ignore linting errors found within sections of code coming directly from # templated code (e.g. from within Jinja curly braces. Note that it does not # ignore errors from literal code found within template loops. ignore_templated_areas = True # can either be autodetect or a valid encoding e.g. utf\-8, utf\-8\-sig encoding = autodetect # Ignore inline overrides (e.g. to test if still required) disable_noqa = False # Comma separated list of file extensions to lint # NB: This config will only apply in the root folder sql_file_exts = .sql,.sql.j2,.dml,.ddl # Allow fix to run on files, even if they contain parsing errors # Note altering this is NOT RECOMMENDED as can corrupt SQL fix_even_unparsable = False # Very large files can make the parser effectively hang. # The more efficient check is the _byte_ limit check which # is enabled by default. The previous _character_ limit check # is still present for backward compatibility. This will be # removed in a future version. # Set either to 0 to disable. large_file_skip_char_limit = 0 large_file_skip_byte_limit = 20000 # CPU processes to use while linting. # If positive, just implies number of processes. # If negative or zero, implies number_of_cpus \- specified_number. # e.g. \-1 means use all processors but one. 0 means all cpus. processes = 1 [sqlfluff:indentation] # See https://docs.sqlfluff.com/en/stable/layout.html#configuring\-indent\-locations indented_joins = False indented_ctes = False indented_using_on = True indented_on_contents = True template_blocks_indent = True # Layout configuration # See https://docs.sqlfluff.com/en/stable/layout.html#configuring\-layout\-and\-spacing [sqlfluff:layout:type:comma] spacing_before = touch line_position = trailing [sqlfluff:layout:type:binary_operator] spacing_within = touch line_position = leading [sqlfluff:layout:type:statement_terminator] spacing_before = touch line_position = trailing [sqlfluff:layout:type:end_of_file] spacing_before = touch [sqlfluff:layout:type:set_operator] line_position = alone:strict [sqlfluff:layout:type:start_bracket] spacing_after = touch [sqlfluff:layout:type:end_bracket] spacing_before = touch [sqlfluff:layout:type:start_square_bracket] spacing_after = touch [sqlfluff:layout:type:end_square_bracket] spacing_before = touch [sqlfluff:layout:type:casting_operator] spacing_before = touch spacing_after = touch [sqlfluff:layout:type:comparison_operator] spacing_within = touch line_position = leading [sqlfluff:layout:type:assignment_operator] spacing_within = touch line_position = leading [sqlfluff:layout:type:object_reference] spacing_within = inline [sqlfluff:layout:type:comment] spacing_before = any spacing_after = any # Template loop tokens shouldn\(aqt dictate spacing around them. [sqlfluff:layout:type:template_loop] spacing_before = any spacing_after = any [sqlfluff:templater] unwrap_wrapped_queries = True [sqlfluff:templater:jinja] apply_dbt_builtins = True # Some rules can be configured directly from the config common to other rules [sqlfluff:rules] tab_space_size = 4 # Max line length is set by default to be in line with the dbt style guide. # https://github.com/dbt\-labs/corp/blob/main/dbt_style_guide.md max_line_length = 80 indent_unit = space allow_scalar = True single_table_references = consistent unquoted_identifiers_policy = all # Some rules have their own specific config [sqlfluff:rules:L003] hanging_indents = True [sqlfluff:rules:L010] # Keywords capitalisation_policy = consistent # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L011] # Aliasing preference for tables aliasing = explicit [sqlfluff:rules:L012] # Aliasing preference for columns aliasing = explicit [sqlfluff:rules:L014] # Unquoted identifiers extended_capitalisation_policy = consistent # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L016] # Line length ignore_comment_lines = False ignore_comment_clauses = False [sqlfluff:rules:L027] # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L026] # References must be in FROM clause # Disabled for some dialects (e.g. bigquery) force_enable = False [sqlfluff:rules:L028] # References must be consistently used # Disabled for some dialects (e.g. bigquery) force_enable = False [sqlfluff:rules:L029] # Keywords should not be used as identifiers. unquoted_identifiers_policy = aliases quoted_identifiers_policy = none # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L030] # Function names extended_capitalisation_policy = consistent # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L031] # Avoid table aliases in from clauses and join conditions. # Disabled for some dialects (e.g. bigquery) force_enable = False [sqlfluff:rules:L036] wildcard_policy = single [sqlfluff:rules:L038] # Trailing commas select_clause_trailing_comma = forbid [sqlfluff:rules:L040] # Null & Boolean Literals capitalisation_policy = consistent # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L042] # By default, allow subqueries in from clauses, but not join clauses forbid_subquery_in = join [sqlfluff:rules:L047] # Consistent syntax to count all rows prefer_count_1 = False prefer_count_0 = False [sqlfluff:rules:L051] # Fully qualify JOIN clause fully_qualify_join_types = inner [sqlfluff:rules:L052] # Semi\-colon formatting approach multiline_newline = False require_final_semicolon = False [sqlfluff:rules:L054] # GROUP BY/ORDER BY column references group_by_and_order_by_style = consistent [sqlfluff:rules:L057] # Special characters in identifiers unquoted_identifiers_policy = all quoted_identifiers_policy = all allow_space_in_identifier = False additional_allowed_characters = None ignore_words = None ignore_words_regex = None [sqlfluff:rules:L059] # Policy on quoted and unquoted identifiers prefer_quoted_identifiers = False ignore_words = None ignore_words_regex = None force_enable = False [sqlfluff:rules:L062] # Comma separated list of blocked words that should not be used blocked_words = None blocked_regex = None [sqlfluff:rules:L063] # Data Types extended_capitalisation_policy = consistent # Comma separated list of words to ignore for this rule ignore_words = None ignore_words_regex = None [sqlfluff:rules:L064] # Consistent usage of preferred quotes for quoted literals preferred_quoted_literal_style = consistent # Disabled for dialects that do not support single and double quotes for quoted literals (e.g. Postgres) force_enable = False [sqlfluff:rules:L066] min_alias_length = None max_alias_length = None [sqlfluff:rules:L067] # SQL type casting preferred_type_casting_style = consistent .ft P .fi .UNINDENT .UNINDENT .SS CLI Reference .SS API Reference .sp SQLFluff exposes a public api for other python applications to use. A basic example of this usage is given here, with the documentation for each of the methods below. .INDENT 0.0 .INDENT 3.5 .sp .nf .ft C \(dq\(dq\(dqThis is an example of how to use the simple sqlfluff api.\(dq\(dq\(dq from typing import Any, Dict, Iterator, List, Union import sqlfluff # \-\-\-\-\-\-\-\- LINTING \-\-\-\-\-\-\-\-\-\- my_bad_query = \(dqSeLEct *, 1, blah as fOO from mySchema.myTable\(dq # Lint the given string and return an array of violations in JSON representation. lint_result = sqlfluff.lint(my_bad_query, dialect=\(dqbigquery\(dq) # lint_result = # [ # { # \(dqcode\(dq: \(dqL010\(dq, # \(dqline_no\(dq: 1, # \(dqline_pos\(dq: 1, # \(dqdescription\(dq: \(dqKeywords must be consistently upper case.\(dq, # } # ... # ] # \-\-\-\-\-\-\-\- FIXING \-\-\-\-\-\-\-\-\-\- # Fix the given string and get a string back which has been fixed. fix_result_1 = sqlfluff.fix(my_bad_query, dialect=\(dqbigquery\(dq) # fix_result_1 = \(aqSELECT *, 1, blah AS foo FROM myschema.mytable\en\(aq # We can also fix just specific rules. fix_result_2 = sqlfluff.fix(my_bad_query, rules=[\(dqL010\(dq]) # fix_result_2 = \(aqSELECT *, 1, blah AS fOO FROM mySchema.myTable\(aq # Or a subset of rules... fix_result_3 = sqlfluff.fix(my_bad_query, rules=[\(dqL010\(dq, \(dqL014\(dq]) # fix_result_3 = \(aqSELECT *, 1, blah AS fOO FROM myschema.mytable\(aq # \-\-\-\-\-\-\-\- PARSING \-\-\-\-\-\-\-\-\-\- # Parse the given string and return a JSON representation of the parsed tree. parse_result = sqlfluff.parse(my_bad_query) # parse_result = {\(aqfile\(aq: {\(aqstatement\(aq: {...}, \(aqnewline\(aq: \(aq\en\(aq}} # This JSON structure can then be parsed as required. # An example usage is shown below: def get_json_segment( parse_result: Dict[str, Any], segment_type: str ) \-> Iterator[Union[str, Dict[str, Any], List[Dict[str, Any]]]]: \(dq\(dq\(dqRecursively search JSON parse result for specified segment type. Args: parse_result (Dict[str, Any]): JSON parse result from \(gasqlfluff.fix\(ga. segment_type (str): The segment type to search for. Yields: Iterator[Union[str, Dict[str, Any], List[Dict[str, Any]]]]: Retrieves children of specified segment type as either a string for a raw segment or as JSON or an array of JSON for non\-raw segments. \(dq\(dq\(dq for k, v in parse_result.items(): if k == segment_type: yield v elif isinstance(v, dict): yield from get_json_segment(v, segment_type) elif isinstance(v, list): for s in v: yield from get_json_segment(s, segment_type) # e.g. Retrieve array of JSON for table references. table_references = list(get_json_segment(parse_result, \(dqtable_reference\(dq)) print(table_references) # [[{\(aqidentifier\(aq: \(aqmySchema\(aq}, {\(aqdot\(aq: \(aq.\(aq}, {\(aqidentifier\(aq: \(aqmyTable\(aq}]] # Retrieve raw table name from last identifier in the table reference. for table_reference in table_references: table_name = list(get_json_segment(parse_result, \(dqnaked_identifier\(dq))[\-1] print(f\(dqtable_name: {table_name}\(dq) # table_name: myTable .ft P .fi .UNINDENT .UNINDENT .SS Simple API commands .SS Advanced API usage .sp The simple API presents only a fraction of the functionality present within the core SQLFluff library. For more advanced use cases, users can import the \fBLinter()\fP and \fBFluffConfig()\fP classes from \fBsqlfluff.core\fP\&. As of version 0.4.0 this is considered as \fIexperimental only\fP as the internals may change without warning in any future release. If you come to rely on the internals of SQLFluff, please post an issue on GitHub to share what you\(aqre up to. This will help shape a more reliable, tidy and well documented public API for use. .SS Internals .sp It is recommended that the following is read in conjunction with exploring the codebase. \fIdialect_ansi.py\fP in particular is helpful to understand the recursive structure of segments and grammars. Some more detail is also given on our \fI\%Wiki\fP including a \fI\%Contributing Dialect Changes\fP guide. .SS Architecture .sp At a high level, the behaviour of SQLFluff is divided into a few key stages. Whether calling \fIsqlfluff lint\fP, \fIsqlfluff fix\fP or \fIsqlfluff parse\fP, the internal flow is largely the same. .SS Stage 1, the templater .sp This stage only applies to templated SQL, most commonly Jinja and dbt. Vanilla SQL is sent straight to stage 2, the lexer. .sp In order to lint templated SQL, SQLFluff must first convert the \(aqraw\(aq or pre\-templated code into valid SQL, which can then be parsed. The templater returns both the raw and post\-templated SQL so that any rule violations which occur in templated sections can be ignored and the rest mapped to their original line location for user feedback. .sp \fISQLFluff\fP supports two templating engines: \fI\%Jinja\fP and \fI\%dbt\fP\&. .sp Under the hood dbt also uses Jinja, but in \fISQLFluff\fP uses a separate mechanism which interfaces directly with the dbt python package. .sp For more details on how to configure the templater see \fI\%Jinja Templating Configuration\fP\&. .SS Stage 2, the lexer .sp The lexer takes SQL and separates it into segments of whitespace and code. No meaning is imparted; that is the job of the parser. .SS Stage 3, the parser .sp The parser is arguably the most complicated element of SQLFluff, and is relied on by all the other elements of the tool to do most of the heavy lifting. .INDENT 0.0 .IP 1. 3 The lexed segments are parsed using the specified dialect\(aqs grammars. In SQLFluff, grammars describe the shape of SQL statements (or their components). The parser attempts to apply each potential grammar to the lexed segments until all the segments have been matched. .IP 2. 3 In SQLFluff, segments form a tree\-like structure. The top\-level segment is a \fBFileSegment\fP, which contains zero or more \fBStatementSegment\fPs, and so on. Before the segments have been parsed and named according to their type, they are \(aqraw\(aq, meaning they have no classification other than their literal value. .IP 3. 3 The three key components to the parser are segments, \fBmatch_grammar\fPs and \fBparse_grammar\fPs. A segment can be a leaf in the parse tree, such as a \fBNumericLiteralSegment\fP, which is simply a number, or can contain many other segments, such as a \fBSelectStatementSegment\fP\&. Each segment can specify a \fBparse_grammar\fP, and a \fBmatch_grammar\fP\&. If both a \fBmatch_grammar\fP and \fBparse_grammar\fP are defined in a segment, \fBmatch_grammar\fP is used to quickly prune the tree for branches which do not match segments being parsed, and the \fBparse_grammar\fP is then used to refine the branch identified as correct. If only a \fBmatch_grammar\fP is defined, then it serves the purpose of both pruning and refining. .IP 4. 3 A segment\(aqs \fB\&.parse()\fP method uses the \fBparse_grammar\fP, on which \fB\&.match()\fP is called. The \fImatch\fP method of this grammar will return a potentially refined structure of the segments within the segment in greater detail. In the example of a \fBFileSegment\fP, it first divides up the query into statements and then finishes. .INDENT 3.0 .IP \(bu 2 .INDENT 3.0 .TP .B \fISegments\fP must implement a \fBmatch_grammar\fP\&. When \fB\&.match()\fP is called on a segment, this is the grammar which is used to decide whether there is a match. .UNINDENT .IP \(bu 2 .INDENT 3.0 .TP .B \fIGrammars\fP combine \fIsegments\fP or other \fIgrammars\fP together in a pre\-defined way. For example the \fBOneOf\fP grammar will match if any one of its child elements match. .UNINDENT .UNINDENT .INDENT 3.0 .IP 1. 3 Regardless of whether the \fBparse_grammar\fP was used, the next step is to recursively call the \fB\&.parse()\fP method of each of the child segments of the grammar. This operation is wrapped in a method called \fB\&.expand()\fP\&. In the \fBFileSegment\fP, the first step will have transformed a series of raw tokens into \fBStatementSegment\fP segments, and the \fIexpand\fP step will let each of those segments refine the content within them. .IP 2. 3 During the recursion, the parser eventually reaches segments which have no children (raw segments containing a single token), and so the recursion naturally finishes. .UNINDENT .UNINDENT .sp #. If no match is found for a segment, the contents will be wrapped in an \fBUnparsableSegment\fP which is picked up as a \fIparsing\fP error later. .sp When working on the parser there are a couple of design principles to keep in mind. .INDENT 0.0 .IP \(bu 2 Grammars are contained in \fIdialects\fP, the root dialect being the \fIansi\fP dialect. The ansi dialect is used to host logic common to all dialects, and so does not necessarily adhere to the formal ansi specification. Other SQL dialects inherit from the ansi dialect, replacing or patching any segments they need to. One reason for the \fIRef\fP grammar is that it allows name resolution of grammar elements at runtime and so a \fIpatched\fP grammar with some elements overridden can still rely on lower\-level elements which haven\(aqt been redeclared within the dialect .IP \(bu 2 All grammars and segments attempt to match as much as they can and will return partial matches where possible. It is up to the calling grammar or segment to decide whether a partial or complete match is required based on the context it is matching in. .UNINDENT .SS Stage 4, the linter .sp Given the complete parse tree, rule classes check for linting errors by traversing the tree, looking for segments and patterns of concern. If the rule discovers a violation, it returns a \fBLintResult\fP pointing to the segment which caused the violation. .sp Some rules are able to \fIfix\fP the problems they find. If this is the case, the rule will return a list of fixes, which describe changes to be made to the tree. This can include edits, inserts, or deletions. Once the fixes have been applied, the updated tree is written to the original file. .SS Reflow Internals .sp Many rules supported by SQLFluff involve the spacing and layout of different elements, either to enforce a particular layout or just to add or remove code elements in a way sensitive to the existing layout configuration. The way this is achieved is through some centralised utilities in the \fIsqlfluff.utils.reflow\fP module. .sp This module aims to achieve several things: * Less code duplication by implementing reflow logic in only one place. .INDENT 0.0 .IP \(bu 2 Provide a streamlined interface for rules to easily utilise reflow logic. .INDENT 2.0 .IP \(bu 2 Given this requirement, it\(aqs important that reflow utilities work within the existing framework for applying fixes to potentially templated code. We achieve this by returning \fILintFix\fP objects which can then be returned by each rule wanting to use this logic. .UNINDENT .IP \(bu 2 Provide a consistent way of \fIconfiguring\fP layout requirements. For more details on configuration see \fI\%Configuring Layout\fP\&. .UNINDENT .sp To support this, the module provides a \fBReflowSequence\fP class which allows access to all of the relevant operations which can be used to reformat sections of code, or even a whole file. Unless there is a very good reason, all rules should use this same approach to ensure consistent treatment of layout. .SS Developing Rules .sp \fIRules\fP in \fISQLFluff\fP are implemented as classes inheriting from \fBBaseRule\fP\&. SQLFluff crawls through the parse tree of a SQL file, calling the rule\(aqs \fB_eval()\fP function for each segment in the tree. For many rules, this allows the rule code to be really streamlined and only contain the logic for the rule itself, with all the other mechanics abstracted away. .SS Traversal Options .SS \fBrecurse_into\fP .sp Some rules are a poor fit for the simple traversal pattern described above. Typical reasons include: .INDENT 0.0 .IP \(bu 2 The rule only looks at a small portion of the file (e.g. the beginning or end). .IP \(bu 2 The rule needs to traverse the parse tree in a non\-standard way. .UNINDENT .sp These rules can override \fBBaseRule\fP\(aqs \fBrecurse_into\fP field, setting it to \fBFalse\fP\&. For these rules \fBFalse\fP, \fB_eval()\fP is only called \fIonce\fP, with the root segment of the tree. This can be much more efficient, especially on large files. For example, see rules \fBL050\fP and \fBL009\fP , which only look at the beginning or end of the file, respectively. .SS \fB_works_on_unparsable\fP .sp By default, \fISQLFluff\fP calls \fB_eval()\fP for all segments, even \(dqunparsable\(dq segments, i.e. segments that didn\(aqt match the parsing rules in the dialect. This causes issues for some rules. If so, setting \fB_works_on_unparsable\fP to \fBFalse\fP tells SQLFluff not to call \fB_eval()\fP for unparsable segments and their descendants. .SS Performance\-related Options .sp These are other fields on \fBBaseRule\fP\&. Rules can override them. .SS \fBneeds_raw_stack\fP .sp \fBneeds_raw_stack\fP defaults to \fBFalse\fP\&. Some rules use \fBRuleContext.raw_stack\fP property to access earlier segments in the traversal. This can be useful, but it adds significant overhead to the linting process. For this reason, it is disabled by default. .SS \fBlint_phase\fP .sp There are two phases of rule running. .sp 1. The \fBmain\fP phase is appropriate for most rules. These rules are assumed to interact and potentially cause a cascade of fixes requiring multiple passes. These rules run the \fIrunaway_limit\fP number of times (default 10). .sp 2. The \fBpost\fP phase is for post\-processing rules, not expected to trigger any downstream rules, e.g. capitalization fixes. They are run in a post\-processing loop at the end. This loop is identical to the \fBmain\fP loop, but is only run 2 times at the end (once to fix, and once again to confirm no remaining issues). .sp The two phases add complexity, but they also improve performance by allowing SQLFluff to run fewer rules during the \fBmain\fP phase, which often runs several times. .sp NOTE: \fBpost\fP rules also run on the \fIfirst\fP pass of the \fBmain\fP phase so that any issues they find will be presented in the list of issues output by \fBsqlfluff fix\fP and \fBsqlfluff lint\fP\&. .SS Base Rules .SS \fIbase_rules\fP Module .SS Functional API .sp These newer modules provide a higher\-level API for rules working with segments and slices. Rules that need to navigate or search the parse tree may benefit from using these. Eventually, the plan is for \fBall\fP rules to use these modules. As of December 30, 2021, 17+ rules use these modules. .sp The modules listed below are submodules of \fIsqlfluff.utils.functional\fP\&. .SS \fIsegments\fP Module .SS \fIsegment_predicates\fP Module .SS \fIraw_file_slices\fP Module .SS \fIraw_file_slice_predicates\fP Module .SS Developing Plugins .sp \fISQLFluff\fP is extensible through \(dqplugins\(dq. We use the \fI\%pluggy library\fP to make linting Rules pluggable, which enable users to implement rules that are just too \(dqorganization specific\(dq to be shared, or too platform specific to be included in the core library. .SS Creating a plugin .sp We have an example plugin in \fI\%sqlfluff/plugins/sqlfluff\-plugin\-example\fP which you can use as a template for rules, or the \fI\%sqlfluff/plugins/sqlfluff\-templater\-dbt\fP which you can use as a template for templater plugins. .SS Few things to note about plugins: .sp Currently, only Rules and Templaters can be added through plugins. Over time we expect more elements of SQLFluff will be extensible with plugins. Each plugin can implement multiple Rules or Templaters. .sp We recommend that the name of a plugin should start with \fI\(dqsqlfluff\-\(dq\fP to be clear on the purpose of your plugin. .sp A plugin may need to include a default configuration if its rules are configurable: use plugin default configurations \fBonly for that reason\fP! We advise against overwriting core configurations by using a default plugin configuration, as there is no mechanism in place to enforce precedence between the core library configs and plugin configs, and multiple plugins could clash. .sp A plugin Rule class name should have the structure: \(dqRule_PluginName_L000\(dq. The \(aqL\(aq can be any letter and is meant to categorize rules; you could use the letter \(aqS\(aq to denote rules that enforce security checks for example. .sp An important thing to note when running custom implemented rules: Run \fBpip install \-e .\fP, inside the plugin folder so custom rules in linting are included. .sp A plugin Rule code includes the PluginName, so a rule \(dqRule_L000\(dq in core will have code \(dqL000\(dq, while \(dqRule_PluginName_L000\(dq will have code \(dqPluginName_L000\(dq. Codes are used to display errors, they are also used as configuration keys. .sp We make it easy for plugin developers to test their rules by exposing a testing library in \fIsqlfluff.utils.testing\fP\&. .SS Giving feedback .sp Would you like to have other parts of \fISQLFluff\fP be \(dqpluggable\(dq? Tell us about it in a \fI\%GitHub issue\fP 😄. .SS SQLFluff in the Wild .sp Want to find other people who are using SQLFluff in production use cases? Want to brag about how you\(aqre using it? Just want to show solidarity with the project and provide a testimonial for it? .sp Just add a section below by raising a PR on GitHub by \fI\%editing this file ✏️\fP\&. .INDENT 0.0 .IP \(bu 2 SQLFluff in production \fI\%dbt\fP projects at \fI\%tails.com\fP\&. We use the SQLFluff cli as part of our CI pipeline in \fI\%codeship\fP to enforce certain styles in our SQL codebase (with over 650 models) and keep code quality high. Contact \fI\%@alanmcruickshank\fP\&. .IP \(bu 2 \fI\%Netlify\fP\(aqs data team uses SQLFluff with \fI\%dbt\fP to keep code quality in more than 350 models (and growing). Previously, we had our SQL Guidelines defined in a site hosted with Netlify, and now we\(aqre enforcing these rules in our CI workflow thanks to SQLFluff. .IP \(bu 2 \fI\%Drizly\(aqs\fP analytics team uses SQLFluff with \fI\%dbt\fP for over 700 models as part of our CI checks in GitHub. Before SQLFluff, we had SQL best practices outlined in a google doc and had to manually enforce through PR comments. We\(aqre now able to enforce much of our style guide automatically through SQLFluff. .IP \(bu 2 \fI\%Petal\(aqs\fP data\-eng team runs SQLFluff on our 100+ model \fI\%dbt\fP project. As a pre\-commit hook and as a CI check, SQLFluff helps keep our SQL readable and consistent. .IP \(bu 2 \fI\%Surfline\fP\(aqs Analytics Engineering team implemented SQLFluff as part of our continuous integration (CI) suite across our entire \fI\%dbt\fP project (700+ models). We implement the CI suite using \fI\%GitHub Actions and Workflows\fP\&. The benefits of using SQLFluff at Surfline are: .INDENT 2.0 .IP \(bu 2 The SQL in our dbt models is consistent and easily readable. .IP \(bu 2 Our style guide is maintained as \fBcode\fP, not a README that is rarely updated. .IP \(bu 2 Reduced burden on Analytics Engineers to remember every single style rule. .IP \(bu 2 New Analytics Engineers can quickly see and learn what \(dqgood SQL\(dq looks like at Surfline and start writing it from day 1. .UNINDENT .IP \(bu 2 The \fI\%HTTP Archive\fP uses SQLFluff to automatically check for quality and consistency of code submitted by the many contributors to this project. In particular our annual \fI\%Web Almanac\fP attracts hundreds of volunteers to help analyse our BigQuery dataset and being able automatically lint Pull Requests through GitHub Actions is a fantastic way to help us maintain our growing repository of \fI\%over a thousand queries\fP\&. .IP \(bu 2 \fI\%Brooklyn Data Co\fP has a \fI\%dbt_artifacts\fP dbt package from which runs SQLFluff in CI to lint pull requests automatically. It uses the \fI\%GitHub Actions workflow\fP contributed by Greg Clunies, with annotations on pull requests to make it easy for contributors to see where their SQL has failed any rules. See an \fI\%example pull request with SQLFluff annotations\fP\&. .IP \(bu 2 \fI\%Markerr\fP has tightly integrated SQLFluff into our CI/CD process for data model changes and process improvements. Since adopting SQLFluff across the organization, the clarity of our SQL code has risen dramatically, freeing up review time to focus on deeper data and process\-specific questions. .IP \(bu 2 \fI\%Symend\fP has a microservices platform supporting our SaaS product. We use SQLFLuff in the CI/CD process of several of our data\-oriented microservices. Among other things, it validates our database migration scripts, deployed using \fI\%schemachange\fP and we have near\-term plans to implement it for our \fI\%dbt\fP projects. .IP \(bu 2 At \fI\%CarePay\fP we use SQLFLuff to lint and fix all our dbt models as well as several other SQL heavy projects. Locally we use SQLFluff with pre\-commit and have also integrated it into our CI/CD pipelines. .IP \(bu 2 Core Analytics Team from \fI\%Typeform\fP and \fI\%videoask\fP uses SQLFluff in the production \fI\%dbt\fP project for building our datawarehouse layer for both products: .INDENT 2.0 .IP \(bu 2 We use it locally in our day to day work, helping us to write cleaner code. .IP \(bu 2 We added SQLFluff to our CI processes, so during a PR we can check that any new or modified sql file has a consistent and easy\-to\-read format. .UNINDENT .UNINDENT .SS SQLFluff Slack .sp We have a fast\-growing \fI\%community on Slack\fP, come and join us! .SS SQLFluff on Twitter .sp Follow us On Twitter \fI\%@SQLFluff\fP for announcements and other related posts. .INDENT 0.0 .IP \(bu 2 \fI\%Index\fP .IP \(bu 2 \fI\%Module Index\fP .IP \(bu 2 \fI\%Search Page\fP .UNINDENT .SH AUTHOR Alan Cruickshank .SH COPYRIGHT 2023, Alan Cruickshank .\" Generated by docutils manpage writer. .