visual-studio-codepathformattingdbtsqlfluff

handle dbt variables while using sqlfluff

I'm using VS Code on macOS. I installed sqlfluff locally using pip. I also installed it via the extensions UI.

I am trying to format my code manually via the UI. When I select my code, do a left click and then opt for options like "SQLFluff Format Selection" or "Format Selection -> sqlfluff", I get this error:

Command failed to execute, check logs for details

The sqlfluff executable was not found. Use the 'Executable Path' setting to configure the location of the executable, or add it to your PATH.

I could make it work via the terminal with

sqlfluff fix filepath

but the issue is that I use some variables within my dbt sql code.

While running sqlfluff, I would get this error now

Skipped file xxx.sql because dbt raised a fatal exception during compilation: Compilation Error in model xxx (xxx.sql)
  Required var 'backfill_date_start' not found in config:
  Vars supplied to xxx = {} 
WARNING    Tried to denylist unknown rules references: ['AL09']

Normally, when running the dbt models, I run them like this:

dbt run --select xxx --vars '{backfill_date_start: "2025-07-17", backfill_date_end: "2025-07-20"}'

Due to other dependencies, I can't change how variables are handled at the moment. Is there any way to make sqlfluff work meanwhile?

Solution

  • Using dbt templater

    dbt is not the default templater for SQLFluff (it is jinja). dbt is a complex tool, so using the default jinja templater will be simpler.

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

    In .sqlfluff:

    [sqlfluff]
templater = dbt

    And variables:

    If your project requires that you pass variables to dbt through command line, you can specify them in template:dbt:context section of .sqlfluff. See below configuration and its equivalent dbt command:

    [sqlfluff:templater:dbt:context]
my_variable = 1

dbt run --vars '{"my_variable": 1}'