Setting up VSCode to use with the dbt CLI

Show and Tell
, ,
4

Thank you for the great article.

Making sqlfluff work

To make sqlfluff work I also had to do a few extra things in addition to what you described.

Sqlfluff has multiple ways deal with jinja-sql:

  1. A dbt based templater that uses dbt to compile your jinja-sql. This generates more accurate sql and automatically uses all your macros and variables, but is fairly slow.
  2. Their own jinja templater, which requires extra work to make dbt work, but is very fast

In addition to that, the vscode sqlfuff extension doesn’t work with the dbt templater.

But since the dbt templater is quite slow, it’s probably not the best choice for use inside of vscode anyways.

But it also means you have to use some workarounds to make your dbt variables, macros and packages work.

Configure sqlfluff

To configure sqlfluff I had to create two files:

.sqlfluff:

[sqlfluff]
# Run `sqlfluff dialects` to get a list of all dialects
dialect = tsql

# Use the dbt templater
# templater = dbt

# Use jinja templater instead of dbt
templater = jinja

[sqlfluff:templater:jinja]
# Make dbt default macros like available like `ref`
apply_dbt_builtins = true

# Load custom macros from the macro directory
load_macros_from_path = ./macros/

# Allow mocking dbt_utils and other packages through
# python files placed in this dir
library_path = ./sqlfluff_libs/

# Mock variables that are used in dbt
[sqlfluff:templater:jinja:context]
target = prod

.sqlfluffignore

target/
dbt_modules/
dbt_packages/
macros/

Make dbt_utils work

The above config worked, but when I ran sqlfluff against a model that used dbt_utils.surrogate_key(), I received this error:

L:   1 | P:   1 |  TMP | Unrecoverable failure in Jinja templating: 'dbt_utils'
                       | is undefined. Have you configured your variables?
                       | https://docs.sqlfluff.com/en/latest/configuration.html
L:  17 | P:  12 |  TMP | Undefined jinja template variable: 'dbt_utils'

I was able to fix it by creating a python library that mocks dbt_utils package macros:

sqlfluff_libs/dbt_utils.py:

def surrogate_key(field_list):
    return "'0xMockKey'"

To mock more complex and nested libraries, you can refer to:
https://docs.sqlfluff.com/en/stable/configuration.html#library-templating

A broken link…

I also noticed the link a gist for an example .vscode directory doesn’t seem to work, because you changed your github name.

The working link is: .vscode folder for getting started with dbt in VSCode · GitHub

2 Likes