How we set up our computers for working on dbt projects

#1

At Fishtown Analytics, we spend a lot of our time working on dbt projects. As such, we’ve settled on our favorite way to set up our computers to make working on dbt projects as easy as possible.

Note that we are all on MacBooks – if you’re on a PC, some parts of this may not apply to you!

This article won’t cover installing dbt – read these instructions if that’s what you’re looking for.

Also have a read of @eli_kastelein’s comment below to see a script for this!

Terminal

We use iTerm2 instead of the default Mac Terminal – you can definitely use the default Mac Terminal, but using iTerm will get you more cred with your engineering team :wink:.

We also customize our terminals so that they work best for us, but this is definitely a matter of personal preference. If you’re new to using the terminal, I suggest asking to pair with an engineer at your organization to learn how they set up their terminal.

Here are some things that we find useful in customizing your terminal:

  • :rotating_light:Install the dbt-completion script:rotating_light: : See instructions here
  • Install the git-completion script: See instructions here.
  • Use a dark theme: See instructions here, I like Dracula and Adventure Time! (I use these themes wherever I can!)
  • Change your terminal prompt: I like to have information about the status of my git repo – relevant script here. Here’s my (quite verbose!) bash prompt:
  • Change the default code editor: Since I use Atom, I added this to my .bashrc file (which is called by my .bash_profile file). It stops me from having to remember that :wq is the way to save and close a Vim window.
export EDITOR="atom --wait"

Code editor

We use Atom as our code editor – our specific setup is below.

You should use whichever code editor you like best/are most comfortable in – other popular alternatives include:

There are a few things we like to do with our editors:

  • Ensure that Jinja-flavored SQL is highlighted correctly
  • Add useful Jinja and dbt-specific snippets
  • Show files that are not included in version control (e.g. compiled files in the target/ directory) – some editors hide these by default.
  • Grey out the text of files that have compiled code in them, to help prevent you from editing compiled SQL (as opposed to your actual model).

Atom setup

  • Ensure that compiled files are shown in the tree view (they are hidden by default since they are included in .gitignore :no_good_woman:). Settings > Packages > tree-view > Untick Hide VCS Ignored Files
  • Install the atom-dbt package. This package provides highlighting for jinja-flavored SQL, Markdown and YAML, as well as adds a number of dbt-specific snippets.
  • Install the file-types package, and configure it to render compiled SQL as plain text (useful if you ever find yourself editing compiled SQL!). Add this to your config.cson file:
"*":  # Be sure to put "file-types" under the "*" key 
  "file-types":
    "**/target/**": "text.plain"
  • Turn on the indent guide (especially useful for yaml files). Settings > Editor > Show Indent Guide
    51%20PM

VSCode setup

  • Install the better-jinja extension
  • Add some file association settings to your settings.json file (the target file association greys out compiled SQL).
{
   "some_setting": custom_value,
   ...
   "files.associations":{  
      "*.sql":"jinja-sql",
      "**/target/**":""
   }
}
  • Add dbt snippets (coming soon!)

Vim setup
One of our community members (@jesse) has a repo for how he sets up his Vim.
If you want to grey out your compiled SQL, add this following to your .vimrc file:

au BufNewFile,BufRead */target/*.sql set ft=text

SQL query runner

Having a query runner is useful when you want to see what the results of your SELECT SQL statements are, or when running statements outside of dbt (e.g. creating new users). We use a query runner when building models (often writing queries in pure SQL first, and then moving them over to dbt), inspecting the results of a model, and debugging things that don’t look right!

Again, we’ve gone for the open source option here, of SQL Workbench. Personally, I don’t love this application, and would welcome any feedback in the comments if you have strong opinions on an alternative. Popular alternatives include:

If you’re using Snowflake or BigQuery, both provide pretty good web UIs for running queries, so you may not need a query runner on your computer!

SQL formatting/linting

A SQL formatter will format your SQL to match a particular coding convention, while a linter will return errors when your SQL doesn’t match those conventions (rather than updating the code).

While there are a few SQL formatters and linters available, so far none of them play nicely with jinja-flavored SQL, so won’t work with your dbt project.

So for now, we just format by hand! Here is our style guide – it’s a good idea to have one for your organization too!

Global .gitignore

This was something I didn’t know for a really long time!
You can set a global .gitignore for your computer, which means you’ll never accidentally commit system files like those pesky .DS_Store files. This tutorial walks through the steps involved.

6 Likes
#2

Great write-up!

I would suggest exploring using ZSH as your shell as an alternative to Bash, along with the Prezto extension.

Prezto provides a lot of awesome integrations with many of the tools we use on a daily basis (git, conda, etc.) and even shortcuts to better integrate the shell with OS X. For instance, the OS X module of Prezto adds a new shortcut called “cdf” which will open in your terminal the last folder you have opened in Finder.

I’d recommend installing ZSH using Homebrew (rather than using the one that comes with OS X as its a few versions behind), and then installing Prezto:

The key is to check each module, and read the different configuration options. It takes a bit of time, but it’s worth it! The modules are here, each with a README on usage/benefits.

Look at this beautiful shell, with clear indicators for git: which branch, and the status of that branch :

  • Arrow pointing up means I am ahead
  • The 6 pointed star means I have modified files
  • The square means I have untracked changes

The (base) is an indication of which Python/conda environment I’m in.

Prezto is as great as dbt, which means its pretty awesome :heart_eyes:

4 Likes
#3

For you VSCode people, I like to use the PostgreSQL add-in by Chris Kolkman.
Brings SQL into VSCode.

  • Password in MacOS keychain
  • Default to the database of your choice

Looking forward to DBT snippets!

#4

On GitLab’s Data Team we were inspired by this post from @claire to make an onboarding script that incorporates many of the mentioned tools and techniques. So now as part of our onboarding, we ask new hires to run the script right when they get access to their new machine. This leads to 2 main benefits:
1. Time is Saved. A lot of the tasks in the script would need to be done on a new machine anyways (e.g. installing dbt). By putting all of the scripts together in one place, we can save new team members from having to do all of these steps individually. When a new hire has all of their dev software installed and configured “out of the box”, they can theoretically start contributing to our projects immediately.
2. Consistency. Because each member of the team has a similar setup on their local machine, collaboration within our data team becomes much easier. When I recently ran into errors running dbt tests locally, @emilie was able to diagnose my problems quickly, in part because she knew that our local environments were nearly identical. On other teams that I’ve been on in the past, these collaborative debugging sessions would always cause headaches because each individual had their own unique dev environment (ex: mismatching python installations, different path variables).

The nice thing about our script is that it’s meant to be a set of guidelines, not rules. For example, the script installs Atom as the default code editor, but I’m personally more productive in VS Code. After running the script, I still installed VS Code and set it as my default, and that was totally okay. The benefits of everybody using the exact same stack are outweighed by the productivity gains that come from people using the tools they’re the most productive in, so everyone is allowed to customize their environment how they see fit.

Since GitLab is open source, both the script and our onboarding issue template are available to the public. We’d love to incorporate any feedback into improving these. Thanks to Claire for the inspiration!

1 Like