sql-analytics-and-dbt

SQL Analytics and dbt

Overview

Analytical SQL fails quietly: a join that fans out, a NULL that drops rows from a NOT IN, a running total over the wrong frame. The query runs, the dashboard shows a number, the number is wrong. dbt gives you the tools to make SQL testable — use them the way you use pytest.

Core principle: every model declares its grain and tests it; logic is proven with a dbt unit test before it is trusted with a real table.

1. Detect the project's conventions

Read dbt_project.yml, packages.yml (is dbt_utils installed?), the adapter in profiles.yml or CI (Postgres, Snowflake, BigQuery, Databricks, DuckDB), any .sqlfluff config, and the existing folder and naming scheme (staging/stg_<source>__<entity>, intermediate/, marts/fct_*, dim_*). Follow them. SQLMesh projects follow SQLMesh's own model and audit conventions — never mix the two.

2. Model structure

with

orders as (
    select * from {{ ref('stg_shop__orders') }}
),

order_lines as (
    select * from {{ ref('stg_shop__order_lines') }}
),

line_totals as (
    select
        order_id,
        sum(amount) as order_value,
        count(*) as line_count
    from order_lines
    where status != 'cancelled'
    group by order_id
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        orders.ordered_at,
        coalesce(line_totals.order_value, 0) as order_value,
        coalesce(line_totals.line_count, 0) as line_count
    from orders
    left join line_totals using (order_id)
)

select * from final

• ref() and source() only — never a hard-coded schema.table.
• Aggregate to the join key before joining (line_totals), so the join stays one-to-one.
• One transformation per CTE, named for what it holds; explicit column lists in the final select.

3. Window functions without surprises

-- latest row per key, deterministic tie-break
select *
from events
qualify row_number() over (partition by event_id order by loaded_at desc, _row_id desc) = 1

-- running total: say ROWS, or ties on order_date are summed together
sum(amount) over (
    partition by customer_id
    order by order_date, order_id
    rows between unbounded preceding and current row
) as running_spend

-- previous value
lag(order_date) over (partition by customer_id order by order_date, order_id) as previous_order_date