## 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](https://popsql.com/dbt) 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](https://docs.popsql.com/docs/cloud-direct-connections#cloud-connections) 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](https://docs.popsql.com/docs/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](https://docs.popsql.com/docs/connecting-to-athena) (only for dbt 1.0+) * [BigQuery](https://docs.popsql.com/docs/connecting-to-bigquery) * [PostgreSQL](https://docs.popsql.com/docs/connecting-to-postgresql) * [Redshift](https://docs.popsql.com/docs/connecting-to-redshift) * [Snowflake](https://docs.popsql.com/docs/connecting-to-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 theSample dbt git repo section below.*

4. Copy the git URL for your repo (eg `git@github.com: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](#organization-dbt-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](https://github.com/popsql/dbt-template), 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. ![](https://files.readme.io/db4c020-image.png) ## 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](/docs/query-results) are available, like exploring the results, filtering or sorting the results, and even [creating a chart](/docs/creating-charts). 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](#organization-dbt-preferences) and [users preferences](#user-dbt-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](https://docs.getdbt.com/reference/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](https://docs.popsql.com/docs/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](doc: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.

dbt docs in schema tree and autocomplete

See table and column descriptions on table page. ### Set up data catalog sync Under your [Organization dbt preferences](https://app.popsql.com/preferences/dbt), 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. ![](https://files.readme.io/10796d8-image.png) 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 [hi@popsql.com.](mailto:hi@popsql.com.) ## Trademarks dbt, dbt Core, and the dbt logo are trademarks of [dbt Labs, Inc](https://www.getdbt.com/).