Killing SQL one step at a time

Writing and running one-off queries every time you need an answer simply doesn't scale: someone has to remember the logic exists, find it in the platform, and re-run it.

However, this still triggers the question: are you going to write every SQL query that you need and then execute on-demand?

Introducing dbt

dbt is one of those tools in your toolkit that you'll use over and over again. It's a framework that turns your queries into a versioned, tested pipeline. It enables you to scale by creating a data modelling factory instead of running it on-demand.

dbt is, in essence, a way of versioning all your end-result queries and, most importantly, all the data transformation needed to go from your systems' data structure to your desired one.

Cost to Serve as an example

Let's try to provide a cost-to-serve view at the customer level, translating customer support expenses into an average cost to serve per customer.

Cost to Serve is a metric defined as Customer Success (and Support) Costs per Customer. What do we need to calculate it?

We need, in the same time period:

• Customer Support and Customer Success Management costs
• Number of customers

If we were to use plain SQL, we would (1) define the SQL queries for each, (2) save it in the platform, (3) run it on-demand.

Instead with dbt, we will commit that entire code to GitHub. The code is then triggered to run automatically every time new data lands in the warehouse.

Notice the {{ref('pnl_rollup')}} calls below. Instead of copy pasting the entire CTE from another query, dbt lets this model reference another model. If pnl_rollup ever changes, this model automatically picks it up.

dbt also knows how to run things in the right order.

The code below does three things:

• Pulls in a PnL rollup and customer count via ref()
• Filters the PnL entries for the customer support entries only (GL ID 4030)
• Joins the two to compute the cost per customer

with pnl_rollup as (
    select * from {{ ref('pnl_rollup') }}
),
 
customer_count_view as (
    select * from {{ref('customer_count_view')}}
),
 
customer_support_costs as (
    select
        date,
        gl_id,
        gl_account,
        value_type,
        amount
    from pnl_rollup
    where gl_id = 4030
)
 
select
    css.date,
    css.value_type,
    css.amount as customer_support_cost,
    ccv.ending_balance as nr_of_customers,
    COALESCE(css.amount / ccv.ending_balance,0) as cost_to_serve
from customer_support_costs css
left join customer_count_view ccv on css.date = ccv.date and css.value_type = ccv.value_type

Bottom Line

From here, this model gets version-controlled in GitHub, tested, and scheduled to run automatically as new data lands.

No one has to remember it, no one has to re-derive it, and the number is the same whether finance, support, or the CEO pulls it.