dbt Core Integration

Overview

The dbt™ platform is a popular tool for analytics engineering. PopSQL integrates with the dbt Core™ functionality, letting you create reusable pieces of SQL called macros and models to make analysis easier. Check out the dbt page for more information.

Demo video

Check out this five minute demo to see the latest features of the integration (updated July 7, 2023).

---

Before you start

Let's make sure your dbt Core™ setup will work with PopSQL's integration.

PopSQL supports dbt Core™ versions: 0.19 - 1.5.

Connection prerequisites

Using dbt Core™ in PopSQL requires a cloud connection since dbt will be running on PopSQL's server and needs to communicate with your database. If your database is within a virtual network, it's still possible to set up a cloud connection through a bridge connector.

🚧

You will need to add the PopSQL dbt server IP address (100.26.75.95) to your git server and database allowlists. If you haven't already, the PopSQL query server IPs (23.20.131.72, 54.211.234.135) also need to be allowed.

Supported database clients:

• Athena (only for dbt 1.0+)
• BigQuery
• PostgreSQL
• Redshift
• Snowflake (Snowflake OAuth is limited to one Snowflake role at a time due to limitations in Snowflake's OAuth, but connecting with username/password allows all available roles to be used)

If you need support for a database client not listed above, please let us know.

Known limitations

Below is a list of current limitations. If any of these are critical for you, please let us know and we can prioritize them:

1. Only one dbt project can be connected to PopSQL per organization.
2. Autocomplete works for columns and tables, but not for dbt references like models and macros.

---

Getting started

Here's a quick video walk through of how to install dbt and setup targets:

1. Once you've been granted access, go to your preferences, then click on dbt™ Integration under your Organization. You can also get to this page from the dbt tab in PopSQL by clicking on Install dbt Core Integration.
2. Click Install dbt Core integration to generate a git deploy key and copy they key to your clipboard.
   1. test

3. Navigate to your dbt git repo and go to Settings > Deploy keys. Click Add deploy key and paste in the deploy key from your clipboard. Make sure you check the box to Allow write access and press Add key.

   If you don't already have a dbt git repo, see the Sample dbt git repo section below.

4. Copy the git URL for your repo (eg [email protected]:popsql/dbt.git) and paste into PopSQL.

5. Select the name of your repo's main branch (e.g. main, master).
6. Add a project subdirectory if your dbt project is not located at the root of your git repo, otherwise leave this field blank.
7. Hit Submit and you're done 💥 you'll land on the dbt Core™ Integration organization preferences.

Sample dbt git repo

If you don't already have a dbt git repo, we have a repository template you can use to quickly get started. You can find the template here, and then click the Use this template button.

Organization dbt preferences

On the dbt Integration page in the preferences tab under Organization you can adjust the dbt Core™ version PopSQL runs for your organization, set a target schema, and uninstall the integration.

Connection setup for your organization

Adding targets in the organization setting will create a shared target that can be used throughout the organization. A target is comprised of the name, connection, and schema and will be used to generate the profile for any dbt commands.

To add a new target click the + New Target button and input its name, the connection you wish to choose, and the schema to run on.

User dbt preferences

Much like organization level settings, user-level dbt preferences can be set up to have targets that are used only by the specific user. This option is found under the dbt tab on the user preferences page. The process for adding personal targets is the same, add a name, connection and, schema and this will be used to generate the profile for any dbt commands.

Making changes to your dbt project

Choosing a branch

Before making changes, you'll want to create a new branch or switch to an existing branch. You can do both from PopSQL from the git branches list at the top of your dbt project file tree. Either choose an existing branch, or type the name of your new branch and choose Create branch.

When you're done making changes to the branch, you'll see all pending changes at the top. You can write a commit message, then press Commit and sync to save your changes to the git repo.

Click on the meatballs menu to the right of the sync icon and you'll see a link to Open branch in GitHub, since at this point most people switch over to GitHub to create a pull request.

Adding files and folders

If your changes require you to create a new file, click the plus icon next to the folder and type the name of your file, with the file extension at the end (.sql for dbt model and macro files or .yml for project files).

You can create a folder by typing the name of the folder, then your file name, like this:

After saving, that becomes (the A icon stands for "added" to make it clear this is a new file):

Editing files

When you open up a file from your dbt project in PopSQL, you'll see a text editor. Just like PopSQL's regular SQL editor, the dbt text editor autosaves all your changes as you work.

You can discard all pending changes by clicking the Discard changes button at the top of the editor:

Searching for dbt files

You can search for dbt files using PopSQL's global search (keyboard shortcut cmd+k). A basic search will scan file names for your search text. In this example you can see the results include the database view defined by dbt, and also the dbt model file itself:

Click on Search within queries and dbt files... to expand your search to scan the text of your files. You can use this advanced search to look for places where a specific model or macro is referenced, like this:

There is also a filter available when you hover over the dbt file tree. That filter will update the tree to only show files that match the text you input (see gif below).

Autocomplete

The dbt text editor supports the same autocomplete as the SQL editor (columns, tables, views, etc.) but it does not yet support completion for dbt references like macros and models. Here's a quick video showing how it works:

Previewing results

When you open a dbt model file, you can hit Preview to run the model as a query. We suggest checking the Limit 100 option from the menu under the preview button to limit results.

Your previewed results will show up in the same way that results appear in the SQL editor. Most of PopSQL's query results features are available, like exploring the results, filtering or sorting the results, and even creating a chart.

It can also be helpful to toggle to the SQL tab of the results to view the compiled version of your model. That way you can see exactly what PopSQL ran against your database and confirm your references and macros are compiling like you expect.

dbt commands

dbt commands are used to execute various actions on your dbt project. PopSQL will respect your organization preferences and users preferences when executing dbt commands.

You'll need to make sure the connection you're using for dbt has access to the right schemas and tables in your database. It will also require write permission for some actions like dbt run that create views and/or tables.

dbt action button

The most common way people trigger dbt commands in PopSQL is through the action button at the top of model files. That button gives you the option to trigger dbt compile, dbt run, dbt test, or dbt build against:

• Current applies to the model file you're looking at
• All applies to every single model in your project
• Upstream applies to the model you're looking at, plus any models referenced in that model, all the way back until you get to sources
• Downstream applies to the model you're looking at, plus any models that reference it, all the way forward until you get to models that are not referenced by any other models
• Upstream + Downstream applies to both Upstream and Downstream definitions above

CLI commands

You can run all supported dbt commands for your dbt version from the PopSQL CLI. You'll see all the same logs you'd expect as the commands run.

You can hover over commands on the left side to see metadata associated with that command:

Canceling dbt commands

You can cancel runs and other dbt commands by clicking the X to the right of a running command in the dbt console:

dbt Packages

dbt packages are supported in PopSQL. All you need to do is add them to the packages.yml file and PopSQL will make them available, no need to run dbt deps (but you can run it in the PopSQL dbt CLI if you really want to).

dbt in the SQL editor

If your organization is using the dbt integration, you can also enable dbt compiling in PopSQL's SQL editor. That allows you to copy-paste dbt models and run them as regular SQL, without manually swapping out references. You can also use helpful dbt macros when writing one-off SQL queries.

You can Enable dbt in the SQL editor from the query menu (see image below). When dbt is enabled in the SQL editor, query variables are disabled since they use a similar templating language that conflicts with dbt references.

dbt docs in PopSQL Data Catalog

You can sync dbt docs information to PopSQL's Data Catalog. This will show table and column descriptions from dbt docs in PopSQL schema pages, when hovering over tables and columns in the schema tree, and in your autocomplete.

Set up data catalog sync

Under your Organization dbt preferences, you'll see a Data Catalog sync section:

Make sure the target you select is the correct target for your production dbt docs information, then click Sync to Data Catalog to start the sync. Each time you sync, the current data catalog values in PopSQL will be overwritten with the latest information from dbt docs.

When the sync is complete, you'll see a notification letting you know how many descriptions for tables and columns were updated in PopSQL. Only added or changed descriptions are counted.

Please contact us if you'd like to try it out! You can use the chat widget in the bottom-right of this page, or email [email protected].

Trademarks

dbt, dbt Core, and the dbt logo are trademarks of dbt Labs, Inc.