dbt Core Integration (Beta)

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 data models to make analysis easier. See our /dbt page for more information.

PopSQL currently supports dbt versions: 0.19 - 1.4.

Demo

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

Setup

1. Sign up for the beta here
2. Once you've been granted access, go to your preferences, then click on dbt™ Integration under your Organization.
3. Click Install dbt Core integration
4. Copy the public key
   
5. Navigate to your dbt git repo > Settings > Deploy keys. If you don't already have a dbt git repo, see the dbt git repo section below
6. Click New deploy key
   
7. Paste the public key from step 4, check Allow write access, and hit Add key
   
8. Copy the git URL for your repo (eg [email protected]:popsql/dbt.git)
   
9. Back in PopSQL, paste the git URL
10. Select the name of your repo's main branch (e.g. master or main)
11. Select which connection you want us to use by default when you dbt run
12. Hit Submit and you're done 💥
    

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.

dbt run connection

When you do dbt run in PopSQL, dbt needs to know what connection to create your models (i.e. views and tables) in. You can tell PopSQL what connection to use in your user Preferences under dbt™.

In your database, you'll need to create a dbt schema, create a user, and grant it permissions to create views and tables.

Since dbt commands are running on PopSQL's server, you may need to add the PopSQL dbt IP address to your safe list: 100.26.75.95

dbt Packages

Packages in dbt are supported, 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 console if you really want to).

Macros

Think of a piece of SQL that you commonly have to write, or maybe copy/paste from query to query. For example, converting a UTC timezone to PDT:

select
  id,
  created_at at time zone 'utc' at time zone 'pdt'
from users
limit 10

Let's convert that to a macro:

1. Click on dbt in the left menu
2. Right click on macros
3. Click New file
4. Name the file to_pt.sql
5. Set the file contents to this:

{% macro to_pt(column_name) %}
  {{column_name}} at time zone 'utc' at time zone 'pdt'
{% endmacro %}

1. Hit the Save button

Now back in your PopSQL query, you can do:

select
  id,
  {{ to_pt('created_at') }}
from users
limit 10

🥳 Using macros, you can eliminate repetitive lines of SQL so you don't have to repeat yourself anymore!

Models

Think of a CTE or subquery that you often copy/paste between queries. For example, finding activated users (ie placed their first order):

with activated_users as (
  select
    users.id as user_id,
    count(orders.id) as num_orders
  from users
  left join orders on orders.user_id = users.id
  group by 1
  having count(orders.id) > 0
)

select *
from events e
inner join activated_users au on au.user_id = e.user_id

Instead of copying/pasting that from query to query, let's create a dbt model so it can be referenced and have one central home.

1. Click on dbt in the left menu
2. Right click on models
3. Click New file
4. Name the file activated_users.sql
5. Set the file contents to your CTE/subquery:

select
  users.id as user_id,
  count(orders.id) as num_orders
from users
left join orders on orders.user_id = users.id
group by 1
having count(orders.id) > 0

1. Hit the Save button
2. Click the dbt button in the top bar and hit dbt run. This will create activated_users as a view in your database

Now back in your PopSQL query, to use your dbt macros or models, you first need to enable dbt under the three dot menu:

After enabling dbt, you can now do:

select *
from events e
inner join {{ ref('activated_users') }} au on au.user_id = e.user_id

🥳 Using models, you can stop copy/pasting CTEs between queries, and have a shared definition for complicated calculations like activated users, churned users, etc. Please note that when using dbt within a query, you will currently not be able to use query variables.

dbt Commands

You can run all supported dbt commands available in your version from the PopSQL CLI. You'll see all the logs you'd expect as the commands run, and clear feedback for running vs passed vs failed commands.

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

Integration to Data Catalog

As part of our Data Catalog feature, we support a sync of your dbt models' descriptions to your organization's Data Catalog. This will allow the descriptions you write in dbt show up inline within PopSQL, namely through our autocomplete and in our schema browser. Please contact us if you'd like this feature to be enabled for your organization.

Known Limitations

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

1. Only one project can be connected to PopSQL per organization.
2. Each project can only have one target, however users can freely change their connection for dbt.
3. The integration currently only works with cloud connections for Athena, BigQuery, PostgreSQL, Redshift, Snowflake.
   1. You can only use service accounts for BigQuery.
   2. You can only use username/password for PostgreSQL, Redshift, Snowflake.
   3. OAuth support is coming soon.
4. Autocomplete does not work with dbt syntax.
5. Cannot cancel an in-progress dbt command.
6. Viewers are able to edit dbt files and run dbt commands.

Trademarks

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