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
- Sign up for the beta here
- Once you've been granted access, go to your preferences, then click on
dbt™ Integration
under your Organization. - Click
Install dbt Core integration
- 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 (e.g.
master
ormain
) - Select which connection you want us to use by default when you
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 (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:
- 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, 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:
- Only one project can be connected to PopSQL per organization.
- Each project can only have one target, however users can freely change their connection for dbt.
- The integration currently only works with cloud connections for Athena, BigQuery, PostgreSQL, Redshift, Snowflake.
- You can only use service accounts for BigQuery.
- You can only use username/password for PostgreSQL, Redshift, Snowflake.
- OAuth support is coming soon.
- Autocomplete does not work with dbt syntax.
- Cannot cancel an in-progress dbt command.
- 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.
Updated 1 day ago