Introducing PopSQL + dbt. The first SQL editor with built-in dbt supportRead more →

dbt Integration

dbt is a popular framework for analytics engineering. PopSQL's dbt integration lets you create reusable pieces of SQL called macros, and lets you create data models to make analysis easier.

Setup

  1. Since our dbt integration is in beta, please email hi@popsql.com to get access
  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 git@github.com: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 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 %}
    
  6. 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
  6. Hit the Save button
  7. 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.

Known Limitations

Our dbt 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. No ability for branching; everything is on the main branch
  2. No ability to customize git commit messages
  3. Only one target (production) is supported for dbt run
  4. Cannot specify dbt run arguments
  5. Cannot rename or delete dbt files from within PopSQL

Ready for a modern SQL editor?