dbt Core Integration

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.

Demo

Setup

1. Sign up for the beta here
2. Once you've been granted access, go to PopSQL > Preferences > dbt
3. Click Install dbt
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 (eg master or main)
11. Select which connection you want us to use when you do 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 (ie views and tables) in. You can tell PopSQL what connection to use in Preferences > dbt.

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

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, you can 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.

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 target (production) is supported for dbt run
2. We only have available one version of dbt
3. Cannot run multiple dbt rpc instances for a given repo

Trademarks

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