SQL fluff linting breaks when using DBT variables #3728
Description
Search before asking
- I searched the issues and found no similar issues.
What Happened
Within Quantile, we are using for a project Snowflake, Meltano and DBT to build a data warehouse. We added to our Github actions some linting testing/automation using SQL Fluff. Which works in general super smooth!
Yesterday I tried adding some a SQL CASE like this to one of our DBT files:
-- Add the time of the day segment as a dictionary
day_segment_start_and_end_time AS (
SELECT
*,
CASE
WHEN
favorite_day_segment_for_an_appointment = 'morning'
THEN {{ var("day_segments")["morning"] }}
WHEN
favorite_day_segment_for_an_appointment = 'afternoon'
THEN {{ var("day_segments")["afternoon"] }}
WHEN
favorite_day_segment_for_an_appointment = 'evening'
THEN {{ var("day_segments")["evening"] }}
END AS favorite_day_segment_start_and_end_time
FROM
favorite_time_for_an_appointment
)
Since the command does work, I wanted to check the SQL fluff linting since this is integrated within our Github actions. When running the SQL fluff linting command, I receive the following problem:
❯ meltano invoke sqlfluff:lint
2022-08-05T11:47:40.981271Z [info ] Environment 'dev' is active
=== [dbt templater] Sorting Nodes...
file rpt_customer_information.sql: 0%| | 0/36 [00:00<?, ?it/s]11:47:49 Unable to do partial parsing because config vars, config profile, or config target have changed
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
file rpt_customer_information.sql: 0%| | 0/36 [00:02<?, ?it/s]11:47:51 Unable to do partial parsing because config vars, config profile, or config target have changed
11:47:51 Unable to do partial parsing because env vars used in profiles.yml have changed
11:47:51 Unable to do partial parsing because config vars, config profile, or config target have changed
11:47:51 Unable to do partial parsing because env vars used in profiles.yml have changed
11:47:51 Unable to do partial parsing because config vars, config profile, or config target have changed
11:47:51 Unable to do partial parsing because env vars used in profiles.yml have changed
11:47:51 Unable to do partial parsing because config vars, config profile, or config target have changed
11:47:51 Unable to do partial parsing because env vars used in profiles.yml have changed
== [/COMPANY-NAME-ANONYMIZED/meltano/transform/models/reporting/customer_contact/rpt_customers_favorite_day_time.sql] FAIL
L: 79 | P: 13 | PRS | Line 79, Position 13: Found unparsable section: 'WHEN \n
| favorite_day_segme...'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished :scroll: :tada:!
I tried adding the dbt variables to my .sqlfluff config file, but that didn't seem to solve the problem.
[sqlfluff]
dialect = snowflake
templater = dbt
output_line_length = 80
ignore_templated_areas = True
runaway_limit = 100
exclude_rules = L001, L009, L014, L016, L034
[sqlfluff:templater:dbt]
project_dir = transform
profiles_dir = transform/profiles/snowflake
profile = meltano
target = default
[sqlfluff:templater:jinja:context]
day_segments = {morning: {start: 0, end: 12}, afternoon: {start: 12,end: 17}, evening: {start: 17,end: 23}}
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
[sqlfluff:rules:L014]
extended_capitalisation_policy = lower
[sqlfluff:rules:L036]
wildcard_policy = multiple
Expected Behaviour
Since DBT build works, the code actually works. But SQL linting says it isn't able to parse it.
Observed Behaviour
It says that the code can't be parsed. So I tried replacing the DBT variables with just arbitrary values. This does work! So I observed that the problem is that it breaks due to the CASE SQL statement in combination with the DBT variables.
How to reproduce
Use DBT variables in the THEN part of a CASE statement.
Dialect
snowflake
Version
Installed via Meltano utility functions -> pip_url: sqlfluff[dbt]
Configuration
.sqlfluff
[sqlfluff]
dialect = snowflake
templater = dbt
output_line_length = 80
ignore_templated_areas = True
runaway_limit = 100
exclude_rules = L001, L009, L014, L016, L034
[sqlfluff:templater:dbt]
project_dir = transform
profiles_dir = transform/profiles/snowflake
profile = meltano
target = default
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 120
indent_unit = space
comma_style = trailing
[sqlfluff:rules:L014]
extended_capitalisation_policy = lower
[sqlfluff:rules:L036]
wildcard_policy = multiple
.meltano/
Are you willing to work on and submit a PR to address the issue?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct