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.
Note: This integration is currently in Beta.
Demo
Setup
- Sign up for the beta here
- Once you've been granted access, go to
PopSQL > Preferences > dbt
- Click
Install dbt
- Copy the public key
- 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 - Click
New deploy key
- Paste the public key from step 4, check
Allow write access
, and hitAdd key
- Copy the git URL for your repo (eg
[email protected]:popsql/dbt.git
)
- Back in PopSQL, paste the git URL
- Select the name of your repo's main branch (eg
master
ormain
) - Select which connection you want us to use when you do
dbt run
- 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:
- Click on dbt in the left menu
- Right click on
macros
- Click
New file
- Name the file
to_pt.sql
- Set the file contents to this:
{% macro to_pt(column_name) %}
{{column_name}} at time zone 'utc' at time zone 'pdt'
{% endmacro %}
- 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.
- Click on dbt in the left menu
- Right click on
models
- Click
New file
- Name the file
activated_users.sql
- 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
- Hit the
Save
button - Click the
dbt
button in the top bar and hitdbt run
. This will createactivated_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:
- Only one target (production) is supported for
dbt run
- We only have available one version of dbt
- Cannot run multiple dbt rpc instances for a given repo
Trademarks
dbt, dbt Core, and the dbt logo are trademarks of dbt Labs, Inc.
Updated 8 months ago