sqlfluff ignores everything when jinja code present

Question

I noticed a strange behavior of sqlfluff in dbt. It doesn't show warnings when I add jinja code for incremental refresh.

For example for this code sqlfluff shows warnings (e.g. capitalized commands, no explicit table aliasing):

SELECT *
FROM orders o
LEFT JOIN {{ ref('cars') }} c ON c.car_id = o.car_id

And for this it doesn't:

SELECT *
FROM orders o
LEFT JOIN {{ ref('cars') }} c ON c.car_id = o.car_id

{% if is_incremental() -%}

    where o.updated_at > (select max(updated_at) from {{ this }})

{%- endif %}

My .sqlfluff file for reference:

[sqlfluff]
dialect = snowflake
templater = dbt
exclude_rules = ST06
runaway_limit = 10
max_line_length = 0
indent_unit = space

[sqlfluff:indentation]
tab_space_size = 4

[sqlfluff:layout:type:comma]
spacing_before = touch
line_position = trailing

[sqlfluff:rules:capitalisation.keywords] 
capitalisation_policy = lower

[sqlfluff:rules:aliasing.table]
aliasing = explicit

[sqlfluff:rules:aliasing.column]
aliasing = explicit

[sqlfluff:rules:aliasing.expression]
allow_scalar = False

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:ambiguous.column_references]
group_by_and_order_by_style = explicit

When I change templater from dbt to jinja this works fine but it shows this warning for my macro:

Line 6, Position 5: Found unparsable section: ',\n    ,\n    o.change,\n    o.cost,\n ...' sqlfluff (PRS)
Undefined jinja template variable: 'my_macro_name' sqlfluff (TMP)

I read this document but I haven't found an answer there: sqlfluff: dbt-templater

Answer 1

I use vscode with dbt-power-user for IDE and find debugging sqlfluff errors difficult through the IDE. For unexpected behaviour i recommend running sqlfluff from the command line interface (cli). Sqlfluff also has several layers of verbosity you can enable running from cli.

To lint from cli enter sqlfluff lint relative/path/to/model.sql and compare the results for your two different file versions. If that does not give you anything try enabling verbosity with -v https://docs.sqlfluff.com/en/stable/cli.html#cmdoption-sqlfluff-dialects-v

I hope this will help you!