A Guide to Maximize Your dbt Productivity in Visual Studio Code

Bas Nederkoorn

·

Follow

Published in

Towards Data Science

·

9 min read

·

Apr 5, 2023

--

3

Listen

Share

If you are struggling to get VS Code and dbt to work well together, you are not alone. Integrating them can be challenging, but it will improve your modeling efficiency. That is why I am sharing the setup that has worked for me.

In this article, I’ll cover topics like upgrading your terminal so you can quickly recall commands, making use of extensions that allow you to build models faster, and setting up formatting and linting to clean up your code and spot bugs in SQL.

If you implement the tips in this article, you will reduce the time you lose on typing code, running models, cleaning code, and searching for bugs. This gained time you can spend on creating data models that add value. While I’m writing with Mac users in mind, many of these tips will also be relevant to Windows users.

What you need to get started:

• Visual Studio Code
• The homebrew package manager. Installation instructions here
• Python 3.7 or higher and a virtual environment with dbt installed. Refer to the official documentation for this.
• A dbt project. This article does not go into detail on how to set up a dbt project. If you do not have experience with this, I recommend you first complete the official dbt jaffle shop tutorial.

Let’s dive in!

1. Optimizing your Terminal

You can improve your internal VS Code terminal to look and behave more like iTerm. Installing Oh-My-Zsh and some additional packages can unlock the following benefits:

• Auto-completion, valuable when you want to quickly call dbt run -m {specific_model_you_ran_a_while_ago}
• Git branch visualization
• Syntax highlighting, e.g. checking if commands you type exist in $PATH

If you don’t have zsh (newer Macs have it pre-installed) run:

brew install zsh

Run the following command to install Oh-My-Zsh:

sh -c "$(curl -fsSL https://raw.github.com/ohmyzsh/ohmyzsh/master/tools/install.sh)"

Now you can select a theme. My favorite is Agnoster because it has a good git branch visualization. Selecting a theme can be done from the zsh configuration file in your home folder. Open this file by running:

code ~/.zshrc

Modify ZSH_THEME to set your preferred theme. Save and close. Changes in .zshrc will show up when you reboot your terminal.

Note
To refresh the settings of your current terminal window, you can run: source ~/.zshrc

The Git branch visualization uses some particular icons, which you can display in the terminal using a so-called “Nerd Font”. I like the Dejavu Sans Mono Nerd Font, you can install it using homebrew [1]:

brew tap homebrew/cask-fonts && brew install --cask font-dejavu-sans-mono-nerd-font

Next, make sure your VS Code terminal uses the new font. Go to the command palette (Cmd + Shift + P) and open settings.json by typing open settings. Add the following line to the file:

  "terminal.integrated.fontFamily": "DejaVuSansMono Nerd Font",

Note:
Make sure to select the general User Setting file and not the project specific Workspace Setting file. Later on, when configuring settings specific to dbt, we will use the Workspace settings.

Your VS Code terminal should now look something like this:

The final step is to install the zsh-autocompletion and zsh-syntax-highlighting packages [2], [3]. Install them by running:

git clone https://github.com/zsh-users/zsh-autosuggestions ${ZSH_CUSTOM:-~/.oh-my-zsh/custom}/plugins/zsh-autosuggestions && \

git clone https://github.com/zsh-users/zsh-syntax-highlighting.git ${ZSH_CUSTOM:-~/.oh-my-zsh/custom}/plugins/zsh-syntax-highlighting

Add them to the plugins variable in the ~/.zshrc file:

plugins=(git zsh-autosuggestions zsh-syntax-highlighting)

Final Note on Terminal:
I briefly mentioned iTerm, this is an alternative for the default macOS terminal . If you don’t have it installed yet, I recommend trying it out.

2. Extensions

So far we have covered some optimizations for a general VS Code setup. Now it’s time to discuss some more dbt-specific configurations, starting with a very powerful VS Code extension called dbt Power User.

The main features of this extension that I regularly use are:

• Previewing compiled SQL (Cmd + ‘)
• Generating base models from a source yml
• Autocompletion of model references
• Executing model-specific tests

SQL & Jijnja Templating

One of the reasons why dbt is such a strong data modeling tool is that it supports templating for SQL code using Jijna. It allows you to use Python-like logic (e.g. if statements or for loops) to create your SQL queries. In practice a templated SQL file might look like this:

-- Set Variable
{% set min_date = '2021-01-01' %}

-- Use Variable
select *
from {{ ref('model_x') }}
where date > '{{min_date}}'

The compiler returns the following compiled SQL file:

select *
from `your_project`.`your_dataset`.`model_x`
where date > '2021-01-01'

The resulting compiled query might look different depending on the database you are using. Templating allows you to build complex SQL models with just a few lines of code.

Templating in dbt is a whole topic in itself, to learn more about how you can use templating in your models refer to the official documentation.

vscode-dbt

A tool that will help you speed up writing Jijna templated SQL code is vscode-dbt. This extension adds a couple of useful Jinja snippets to your toolset. For example: for,ref and source.

For the snippets to be recognized, you have to add the following line to your workspace settings.json:

"files.associations": {
    "*.sql": "jinja-sql"
},

Adding custom snippets

If you find yourself typing the same thing again and again, it will be faster to create your own custom snippets. For example; At the top of each model, I like to import all the dependencies as a CTE, similar to what you would do in Python code.

You can add snippets by opening the command palette (Cmd + Shift + P) and navigating to Configure User Snippets

After adding the snippet, the file looks like this:

{
   "Generate Import" : {
      "prefix" : "import",
      "body" : "$1 as (select * from {{ ref($2) }})"
     }
}

This is a simple example for illustration purposes, I encourage you to create your own snippets wherever you see fit.

3. Jinja-SQL formatting: sqlfmt

To format Jinja-SQL, I use sqlfmt, a formatter specifically designed for dbt logic that is integrated with dbt Power User. sqlfmt is an opinionated formatter, you can adjust the maximum line length but there is not much room for customizing the formatting behavior. This is a good thing, as it leaves less room for discussion about code aesthetics. This simplicity also positively affects the reliability and speed of the formatting.

The easiest way to install sqlfmt(as recommended in [4]) is by using pipx.

If you don’t have pipx , install it by running:

brew install pipx

The ensurepath command adds the pipx bin to your PATH, so that you can call sqlfmt from anywhere → In our case from dbt Power User.

pipx ensurepath

To install sqlfmt run:

pipx install 'shandy-sqlfmt[jijnafmt]'

Resulting in sqlfmt being installed and exposed on your $PATH:

The last step is to configure the default formatter for Jinja SQL in VS Code. Add these lines to the workspace settings.json:

"[jijna-sql]": {
  "editor.defaultFormatter": "innoverio.vscode-dbt-power-user" 
  "editor.formatOnSave": false //Optional, I prefer to format manually.
}  

Note:
If you read the manual of sqlfmt, you will find the option to exclude certain parts of the code by using # fmt: off / on or define folders in the to exclude.

4. Linting with SQLFluff

What is Linting?

Linting is the process of analyzing code to find errors, bugs, and style mistakes. You could consider a linter a grammar checker for code.

Formatting and linting are two different techniques that can be used together to improve your code. Formatting will improve code appearance while linting will help you catch more complex errors and mistakes in your code.

The linter I use is SQLFluff. It works for most SQL dialects and most importantly; it is compatible with Jinja templated SQL. At the time of writing, I found this to be the most advanced SQL linter out there.

Install it by running the following command:

brew install sqlfluff

To integrate SQLFluff with VS Code you can download the sqlfluff extension. To avoid conflicts between sqlfluff, sqlfmt, and dbt Jinja logic, add the following lines to your workspace settings.json:

   
    "sqlfluff.dialect": "bigquery", // Depends on your database
    "sqlfluff.executablePath": "/usr/local/bin/sqlfluff", // Check your path by running `which sqlfluff`
    "sqlfluff.linter.diagnosticSeverity": "error",
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.config": "${workspaceFolder}/.sqlfluff",

On top of that, create a .sqlfuff file in your workspace folder. Add the following lines:

[sqlfluff]
# Double, but needed if you run from terminal
dialect = bigquery

exclude_rules = L003, L018, L022, L036,

# Conflict sqlfmt:
# L003 : Indentation
# L018 : CTE closing bracket position
# L036 : Amount of columns in one line (1 vs. how many fit within line length)

# Personal Preference
# L022 : Blank line after CTE 
# (conflicts with creating select * cte's of dependencies at the top of a file)


[sqlfluff:rules]
# Use the same linelength as sqlfmt to prevent conflicts
max_line_length = 100
capitalisation_policy = lower
extended_capitalisation_policy = lower

[sqlfluff:indentation]
indented_joins = False
indented_using_on = True
template_blocks_indent = False

[sqlfluff:rules:L052]
multiline_newline = True

Note :
The above-mentioned .sqlfluff is based on [5] but slightly extended. How you set up the linting rules is up to your personal (and your team’s) preference.

You won’t need to lint from the command line once you have installed the vscode-sqlfluff extension. However, I still prefer calling sqlfluff lint {path_to_model_sub_folder} from the terminal, as it provides a clear overview of any issues in your code:

Besides the lint command, SQLFluff has a formatter-likefix command. You can use this to fix some of the issues in your code raised by SQLFluff. However, I recommend using this feature with some caution, as it can also break your code. As mentioned before, I prefer using less the complex sqlfmt for formatting.

To ignore linting errors due to templated dbt functions, such as the one below, you can add ignore = templating to your .sqlfluff file.

L:  25 | P:  15 |  TMP | Undefined jinja template variable: 'dbt_utils'

To further customize the linting behavior, refer to the official documentation [6].

Done!

Conclusion

If you have followed the outlined steps and made it this far, you should now have an optimal workspace to work with dbt! We’ve covered topics like upgrading your VS Code terminal, which extensions to use, and how to format and lint Jinja templated SQL. These tips will save you time and effort, allowing you to focus on building better data models.

The goal is to continuously increase efficiency. If you’re looking for the next, more advanced step, I recommend exploring macros for dbt. If you have any questions or ideas for future articles, feel free to reach out to me.

Thank you for reading, and happy modeling!

Enjoyed this Article?

If you found this article helpful, be sure to follow me to stay up-to-date with my latest articles on data modeling, coding, and more.

References

[1] Installing Fonts through homebrew | | link

[2] Installing zsh-syntax-higlighting | | link

[3] Installing zsh-autocomplete | | link

[4] Installing sqlfmt | | link

[5] Integrating sqlfmt and sqlfluff | | link

[6] Default Config SQLFluff | | link