dbt-strategy

dbt Strategy

Patterns for building dbt models in warehouse-backed analytics projects using Kimball-style dimensional modeling.

Layer Architecture

sources/     Source views — raw data from app DB, event stream, billing, CRM, LMS
    ↓
staging/     Intermediate transformations (keep minimal — new models go directly to core/)
    ↓
core/        Fact and dimension tables (main transformation layer)   → tables
    ↓
marts/       Business aggregations built on top of core             → tables

Put new models in core/ directly. Use staging/ only when complex intermediate joins are truly necessary.

Naming Conventions

Layer	Prefix	Example
Sources	src_	src_app_teams, src_events
Staging	stg_	stg_teams, stg_billing_customers
Core dim	dim_	dim_teams, dim_users
Core fct	fct_	fct_team_members, fct_team_budgets
Marts	mart_	mart_team_overview, mart_creation_overview

Domain subdirectories in core/: academy/, analytics/, finance/, product/, sales/, scoring/, shared/

Critical SQL Rules

No Aliases

Always reference the full CTE name — never use aliases:

-- ❌ WRONG
select u.id, t.name
from users u
join teams t on u.team_id = t.id

-- ✅ CORRECT
select users.id, teams.name
from users
join teams on users.team_id = teams.id

Standard CTE Structure

Every model uses clear CTEs. The final SELECT is always select * from final:

with source_cte as (
    select * from {{ ref('src_app_teams') }}
),

transformed as (
    select
        -- Primary key
        source_cte.team_id,

        -- Attributes
        source_cte.name as team_name,
        source_cte.created_at
    from source_cte
),

final as (
    select * from transformed
)

select * from final