project set up

1. sign up to cloud.getdbt.com

2. link dbt to your github account https://docs.getdbt.com/docs/cloud/git/connect-github

3. create a new project

   1. in project set subdirectory to dbt_proj

   

4. sign into your supabase DB

   1. you will need to use the settings from the session pooler connection string

1. To double check that the project is set up correctly we should be able to see the lineage of the my_first_dbt_model like:

Lets build an example:

first we want to select from the customer table and our orders table

in dbt create new models in a staging folder:

firstly in the staging folder we can create a new schema.yml and fill it in:

version: 2 

sources:
  - name: shop
    database: postgres 
    schema: < your schema name>
    tables:
      - name: order
      - name: customer
    

then we can create the 2 models for these sources

these models will look something like this

with orders as (
    select * from {{ source('shop', 'orders') }}
)

select * from orders 

Now lets join these in an intermediate model

in this intermediate model we want to join the customer and order data together using a left join

for this i have selected some columns so that in our mart we can calculate so kpis

with orders as ( 
    select * from {{ ref('stg_order') }}
), 
customer as (
    select * from {{ ref('stg_customer') }}
),
joined_data as ( 
    select 
    c.customer_id,
    c.city,
    o.order_date,
    o.total_amount
    from 
    customer c
    left join orders o
    on c.customer_id = o.customer_id
)

select * from joined_data

Now for the mart

in this we want to use some of the aggregate functions we used in earlier to calculate some kpis

you can go back and use some of that sql, and create a model referencing our customer_order model in the from clause

now we have built a small workflow we can run dbt docs generate

the command can be ran at the bottom