CODEWITHSUNDEEP
jinja2databricksdbtpre-commitsqlfluff
pauloutboy
pauloutboy

Reputation: 51

sqlfluff: Undefined jinja template variable: 'dbt_utils'

Running pre-commit with sqlfluff returns the error: enter image description here

Sqlfluff is in the root folder along with pre-commit-config.yaml. I have tried moving it to /dbt but the error persists.

I have tried changing templater = jinja to templater = dbt and it said: enter image description here

But after pip installing sqlfluff-templater-dbt, restarting the terminal and VS code, the same error remains.

Here is how I am using dbt_utils:

select
{{ dbt_utils.generate_surrogate_key([
        'date',          
        'start',
        'end'
    ])
}} as hash
from {{ source('bronze', 'test') }}

This is my sqlfluff:

[sqlfluff]
dialect = databricks
templater = jinja
exclude_rules = structure.column_order

[sqlfluff:templater:jinja]
apply_dbt_builtins = true

[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='') %}item{% endmacro %}
dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}

I have dbt/dependencies.yml as (and ran dbt deps prior):

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Also, everything worked fine prior to my usage of dbt_utils so it's something to do with it. Appreciate the help!

Upvotes: 1

Views: 2354

Answers (3)

Melissa
Melissa

Reputation: 1

Use -- noqa: disable=TMP before the block and -- noqa: enable=TMP after. I had this same issue and after much researching this was the best I found.

Upvotes: 0

Slipstream
Slipstream

Reputation: 407

The reason that the error occurs is that you're using the jinja templater, which isn't (by default) configured with all of the available dbt macros. A few like ref and source are included as a convenience (which is likely why you didn't notice until you started using dbt_utils), but it would be virtually impossible to keep up with mimicking all of the available dbt packages within SQLFluff. As per the error message you've seen one solution to that is to use the SQLFLuff dbt templater. The pros and cons for this are listed in this article in the SQLFluff docs. The biggest upside is that it uses the compilation environment of your dbt project and so all of the same macros are available.

The reason the error message remains after installing the python package for sqlfluff-templater-dbt (by running pip install sqlfluff-templater-dbt) is that you need to configure SQLFluff to use it, now that it's installed (it doesn't just kick in because it's available in the environment). You can just set it on the command line (by running sqlfluff lint ... --templater dbt), but it would be better to use your pre-existing .sqlfluff configuration file, and set the templater value to dbt. That would make the start of your config file be:

[sqlfluff]
dialect = databricks
templater = dbt
...

After that the dbt templater should take over. You can find more information on installation and configuration in the SQLFluff docs.

Upvotes: 1

David Pickering
David Pickering

Reputation: 11

I had this same issue in my DBT Project using SQLFluff.

DBT code:

{{ dbt_utils.generate_surrogate_key(['column1', 'column2']) }} as column_sk,

SQLfluff output

L: 11 | P: 8 | TMP | Undefined jinja template variable: 'dbt_utils'

To my .sqlfluff file I added the following:

ignore = templating

That resolved my error.

However, I recognize that there is probably a better way to solve. The sqlfluff configuration page references some settings (e.g., [tool.sqlfluff.core] templater = "jinja" and [tool.sqlfluff.templater.jinja] apply_dbt_builtins = true that can probably be configured to correctly skip these types of errors while still retaining the ability to format templating code.

Upvotes: 0

Related Questions