How we set up our computers for working on dbt projects

Archive
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.

image
image.png1704×238 19.6 KB

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

6 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!

13 Likes
5

Here’s a little Atom tip to optimize your workflow.

To “jump” directly from a model to it’s compiled equivalent:

  1. Install the related plugin (https://atom.io/packages/related)
  2. Navigate to related patterns config (Packages -> related -> Edit Related Patterns)
  3. Paste the following at the bottom:
'^(.*[/\\\\])?(.+)(\\.sql)$': [
    '**/compiled/**/$2.sql'
]

  1. From inside any model, just press ctrl+shift+R and hit return.

  2. Bonus from @joshtemple: Open your related Package settings and check the box “Open Single Item Automatically” to prevent need for redundant return key!

No more drilling-down-down-down!

5 Likes
6 
'^(.*[/\\\\])?(.+)(\\.sql)': [
   '**/compiled/**/$2.sql'
]

A variation on this which allows you to jump back and forth between the model and compiled SQL:

'^(.*/)?models/(.*/)?(.+\\.sql)$': [
    '**/compiled/**/$3'
]
'^(.*/)?compiled/(.*/)?(.+\\.sql)$': [
    '**/models/**/$3'
]
7

If someones wants to get to the actually run SQL instead of Compiled SQL.

# To "jump" between source / compiled / build files

'^(.*/)?models/(.*/)?(.+\\.sql)$': [
    '**/compiled/**/$3'
]
'^(.*/)?compiled/(.*/)?(.+\\.sql)$': [
    '**/run/**/$3'
]

'^(.*/)?run/(.*/)?(.+\\.sql)$': [
    '**/models/**/$3'
]
8

Are there any plans to add something like a ‘dbt lint’ command that would address the issues with jinja? I was thinking of trying to adapt an existing SQL linter but figured I should check to see if there is anything already in the works first.

1 Like
9

Not at present! I know that Alan from Tails.com is working on a linter: https://github.com/alanmcruickshank/sqlfluff

1 Like
10

I was able to get https://marketplace.visualstudio.com/items?itemName=eamodio.find-related working for VSCode using the following ruleset (hat tip to @hui-zheng b/c these are direct copy and past from him).

    "findrelated.rulesets": [
    {
        "name": "sql",
        "rules": [
            {
                "pattern": "^(.*/)?models/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/compiled/**/$3"
                ]
            },
            {
                "pattern": "^(.*/)?compiled/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/run/**/$3"
                ]
            },
            {
                "pattern": "^(.*/)?run/(.*/)?(.+\\.sql)$",
                "locators": [
                    "**/models/**/$3"
                ]
            }
        ]
    }
],

Also, https://github.com/bastienboutonnet/vscode-dbt works great for vscode autocomplete.

4 Likes
11

@hui-zheng @sean-rose @jars , so what’s your workflow?
work in atom on a query that has jinja,
then drop to shell and dbt compile and then go back to Atom, and jump to the compiled?

12

A bit of an update on the comment from @eli_kastelein and @emilie at GitLab, as MacOS now have zsh as their default shell we decided to update our onboarding scripts to use zsh. There were more compatibility issues than we were expecting, and quite a few of the old tools just did not work any more, however we were able to find some great plugins as replacements for all of the old tools, so this script now works providing exactly the same functionality as the old one, in a shiny new zsh shell.

Here is the new zsh script, in case anyone would like to migrate over to zsh with us.

Something which helped make the migration quite a bit less painful was finding oh-my-zsh (a plugin manager for zsh), which includes some great themes and tools, and made installing new tools much easier. There are quite a few plugin manager options available at the moment so if you are using zsh, would really like to hear, are you using a plugin mananger and if so, which one?

13

Hey folks,

If you are using vscode. Well it could even be a reason to start using vscode.

I suggest to install our extension: dbt Power User - Visual Studio Marketplace

Main features:

  • Go to the definition of any models, macro’s and sources.
  • Autocompletion of models, macros and sources
  • Ability to run a model through the play button of the document
  • dbt install / update
  • dbt logs viewer (force tailing)

Autocomplete model

If you have any feature requests or bugs, just let us know on Issues · innoverio/vscode-dbt-power-user · GitHub

6 Likes
14

@mdesmet this is AWESOME! :exploding_head:

2 Likes
16

Hi there,

I use SQLTools in VSCode for SQL.

But, be careful setting up your SSL settings and do it via your profile, not the default form which is dangerous.

Love the ease of use though.

17

This is great, thank you.

In addition to that I also had to enable the rule set by adding the following to my vscode config…

    "findrelated.applyRulesets": [
        "sql"
    ]
2 Likes
18

Also want to add the for VSCode folks who use anaconda environments or virtual environments to contain the dbt package, a long-awaited update to the VSCode Python extension is coming next month. Now the Python extension will auto activate along with your chosen python environment. You just need to have a requirements.txt in the repo.

19

The author of the DBT poweruser VSCode extension has just added Show compiled SQL and Show run SQL commands.

https://marketplace.visualstudio.com/items?itemName=innoverio.vscode-dbt-power-user&ssr=false

I have mapped them to the alt+c and alt+r shortcuts and can now quickly see the compiled SQL.

Highly recommended!

5 Likes
20

I wrote a set up guide specifc guide to VSCode. I’d love y’all’s feedback! Huge kudos obvs to Claire for the original post.

1 Like
21

Hello everyone, I just came up with a way to search for dbt models interactively directly in the terminal.

Here’s a quick demo:
fzf-dbt_demo

You can learn more about it and how to set it up in the github repo:

1 Like
22

@claire - vscode-dbt is now in the marketplace: vscode-dbt - Visual Studio Marketplace
(decided to @ ping you since I can’t fork & PR in discourse afaik :slight_smile: )

In the “VSCode setup” section, suggest updating the part where it says “hopefully this gets onto the marketplace soon!”

Or maybe just link to this post: Setting up VSCode to use with the dbt CLI