dbt Core Integration (Beta)

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.

❗️
This integration is currently in beta, so you'll need to request access from your customer success manager or PopSQL support (chat bubble on this page or [email protected]).

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

PopSQL's dbt Core™ integration is in beta, so there are some things that we're still working through. Below is a list of current limitations. If any of these are critical for you, please let us know and we can prioritize them:

Only one dbt project can be connected to PopSQL per organization.
Each project can only have one target. However, users can freely change their connection and target schema on an individual level.
Autocomplete does not work with dbt syntax.
Cannot cancel an in-progress dbt command.
Viewers can edit dbt files and run dbt commands.

Getting started

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.
Click Install dbt Core integration to generate a git deploy key and copy they key to your clipboard.
1. test

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.

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

Select the name of your repo's main branch (e.g. main, master).
Add a project subdirectory if your dbt project is not located at the root of your git repo, otherwise leave this field blank.
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 this page you can adjust the dbt Core™ version PopSQL runs for your organization, set a default connection and target schema, and uninstall the integration.

Connection setup for your organization

There are two options:

Provide a default connection means you will set up a shared connection and default target schema to be used for dbt previews and commands. Users can still override these defaults in their user dbt preferences. If you choose a default connection that some users don't have access to, they will not be able to use dbt previews or dbt commands unless they add a user-level connection.
Require members to set up a connection means there is no default connection or target schema. Users will not be able to use dbt previews or commands until they have set up their user dbt preferences. They can still use the git directory features and use dbt in the SQL editor.

User dbt preferences

User-level dbt preferences can be set up to override defaults in your organization dbt preferences. If your organization is using Require members to set up a connection, this step is required to use dbt previews and dbt commands. This can also be useful if you need to change your connection and/or target schema on the fly between 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:

Autocomplete

The dbt text editor does not support autocomplete for columns, tables, etc. That will be added in the future.

The autocomplete menu has a library of dbt shortcuts to save you time when creating models and macros. Access them by typing two underscores __. When you choose one, PopSQL will fill in a text block so you don't have to look up syntax in dbt documentation.

For example, if you choose __config_incremental PopSQL will add this block of code for you to work from:

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:

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

As part of our Data Catalog feature, we are working on an integration to show your dbt model descriptions in your Data Catalog. This will allow the descriptions you write in dbt docs to show up in PopSQL's autocomplete and schema browser. Please contact us if you're interested in this feature.

Trademarks

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