dbt-strategy · git:20260606.52c8642 · 2026-06-06 · sha256 3666eff5e4200345
dbt-strategy git:20260606.52c8642A
Immutable. This exact content is served forever at /api/v1/blob/3666eff5e4200345.
---
name: dbt-strategy
description: Use when creating or modifying dimensional dbt models in warehouse-backed analytics projects. Covers a four-layer warehouse architecture (sources/staging/core/marts), naming conventions, no-alias SQL rule, surrogate-key and missing-record patterns, incremental strategies, deduplication, and common project macros. Use when building fact tables, dimension tables, staging models, writing SQL, or designing tests.
---
# 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:
```sql
-- ❌ 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`:
```sql
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
```
## Core Patterns
### Pattern 1: Dimension Table with Surrogate Key + Missing Record
Every dimension includes a `union all` missing record sentinel.
**Key naming rule** (per CLAUDE.md and Kimball):
- Surrogate key: `<object>_sk` — e.g., `team_sk`
- Natural key: `<object>_id` — e.g., `team_id`
> Note: Older models in the project use `id` / `natural_id` — this is legacy. New models must use `<object>_sk` / `<object>_id`.
```sql
{% set missing_team = "'Missing Team'" %}
with team_snapshots as (
select * from {{ ref('src_snapshot_app_teams') }}
),
latest_state as (
{{ dbt_utils.deduplicate(
relation='team_snapshots',
partition_by='team_id',
order_by='state_valid_from desc'
) }}
),
final as (
select
-- Surrogate key
{{ dbt_utils.generate_surrogate_key(['team_id']) }} as team_sk,
-- Natural key
team_id,
-- Attributes
latest_state.name,
latest_state.plan_code,
latest_state.created_at,
latest_state.deleted_at
from latest_state
union all
select
{{ missing_record_id() }} as team_sk,
'-1' as team_id,
{{ missing_team }} as name,
{{ missing_team }} as plan_code,
cast(null as timestamp) as created_at,
cast(null as timestamp) as deleted_at
)
select * from final
```
**BigQuery null casts**: `cast(null as int64)`, `cast(null as bool)`, `cast(null as timestamp)`, `cast(null as string)`
### Pattern 2: Fact Table with Foreign Keys
Fact table surrogate key follows the same `<object>_sk` rule. Foreign keys to dimensions reference the dimension's surrogate key (`<dim>_sk`):
```sql
with enrollments as (
select * from {{ ref('stg_academy_student_enrollments') }}
),
dim_courses as (
select * from {{ ref('dim_academy_courses') }}
),
dim_users as (
select * from {{ ref('dim_users') }}
),
final as (
select
-- Surrogate key
{{ dbt_utils.generate_surrogate_key(['enrollments.enrollment_id']) }} as enrollment_sk,
-- Natural key
enrollments.enrollment_id,
-- Foreign keys (reference dimension surrogate keys)
{{ get_id_null('dim_courses.course_sk') }} as course_sk,
{{ get_id_null('dim_users.user_sk') }} as user_sk,
{{ get_date_id('enrollments.enrolled_at') }} as enrolled_date_sk,
-- Measures
enrollments.enrolled_at
from enrollments
left join dim_courses
on enrollments.course_id = dim_courses.course_id
left join dim_users
on enrollments.user_id = dim_users.user_id
)
select * from final
```
Join dimensions using the **natural key** (`<object>_id`). Store the dimension's **surrogate key** (`<object>_sk`) as the FK column in the fact.
Use `{{ get_id_null(...) }}` for nullable FK references to dimension surrogate keys.
Use `{{ get_date_id(...) }}` for foreign keys to `dim_date`.
### Pattern 3: Deduplication
Use `dbt_utils.deduplicate` — never use `QUALIFY`:
```sql
deduplicated as (
{{ dbt_utils.deduplicate(
relation='source_cte',
partition_by='team_id',
order_by='updated_at desc'
) }}
),
```
### Pattern 4: BigQuery Incremental Model
For large or event tables, use `insert_overwrite` with `partition_by`:
```sql
{{
config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "event_date",
"data_type": "date",
"granularity": "day"
}
)
}}
with events as (
select * from {{ source('snowplow', 'events') }}
{% if is_incremental() %}
where date(collector_tstamp) >= date_sub(current_date, interval 3 day)
{% endif %}
),
final as (
select
events.event_id,
date(events.collector_tstamp) as event_date,
events.collector_tstamp
from events
)
select * from final
```
### Pattern 5: Source Definition (BigQuery)
```yaml
# models/sources/app/_app__sources.yml
version: 2
sources:
- name: app
description: Core application database
database: <warehouse_project>
schema: app
tables:
- name: teams
description: Raw team records
columns:
- name: id
data_tests:
- unique
- not_null
```
Note: use `data_tests:` (not `tests:`) in YAML files in this project.
### Pattern 6: Project Variables
```sql
-- Sentinel values defined in dbt_project.yml
coalesce(teams.plan_code, '{{ var("unknown") }}') as plan_code
coalesce(users.deleted_at, cast('{{ var("the_distant_future") }}' as timestamp)) as deleted_at
```
### Pattern 7: Documentation with Docblocks
Shared descriptions live in `.md` files as Jinja docblocks:
```markdown
<!-- models/core/shared/docs_shared.md -->
{% docs team_id %}
The unique identifier for a team entity in the application.
{% enddocs %}
```
Reference in YAML:
```yaml
columns:
- name: team_id
description: "{{ doc('team_id') }}"
data_tests:
- unique
- not_null
```
## Available Macros
| Macro | Purpose |
|-------|---------|
| `missing_record_id()` | Returns the ID used for missing record sentinels |
| `get_id_null(expr)` | Safe FK — returns `missing_record_id()` if null |
| `get_date_id(expr)` | Converts timestamp to `dim_date` FK |
| `deletion_status_field()` | Adds `deletion_status` derived from `deleted_at` |
| `dbt_utils.generate_surrogate_key([...])` | Generates MD5 surrogate key |
| `dbt_utils.deduplicate(relation, partition_by, order_by)` | Deduplicates a CTE |
Accepted values macros live in `macros/accepted_values/` — use these in YAML tests instead of hardcoding enum values.
## Datasets
| Environment | Dataset |
|-------------|---------|
| Production | `<project>.dbt_production` |
| Development | `dbt_<username>` e.g., `dbt_johndoe` |
Use production for reading, development dataset for writing during development.
## MCP dbt Tools
Before modifying any model, check its context:
```
get_model_details(unique_id) # Compiled SQL and metadata
get_model_parents(unique_id) # Upstream dependencies
get_model_children(unique_id) # Downstream impact
get_all_models() # Browse all models with metadata
```
## High-Impact Models (Most Connected)
These god nodes have the most downstream dependencies — changes cascade widely:
| Model | Edges | Action before change |
|-------|-------|----------------------|
| `dim_teams` | 46 | `get_model_children()` |
| `dim_users` | 36 | `get_model_children()` |
| `util_user_dimensions` | 32 | `get_model_children()` |
| `fct_team_members` | 22 | `get_model_children()` |
## dbt Commands
```bash
dbt run -s <model> # Run specific model
dbt test -s <model> # Test specific model
dbt build -s <model> # Run + test
dbt run -s +<model> # Model and all upstream
dbt run -s <model>+ # Model and all downstream
dbt compile -s <model> # Compile without running
```
## Verification
- [ ] Model sits in the correct layer with the matching prefix (src_/stg_/dim_/fct_/mart_); new models default to core
- [ ] No table aliases anywhere — full CTE names referenced; the final statement is select * from final
- [ ] Dimensions use the surrogate + natural key naming (`<object>_sk` / `<object>_id`) and include the missing-record sentinel
- [ ] Facts join dimensions on natural keys and store surrogate-key FKs via get_id_null / get_date_id
- [ ] Deduplication uses dbt_utils.deduplicate — no QUALIFY
- [ ] YAML uses data_tests: and accepted-values macros instead of hardcoded enum values
- [ ] get_model_children() was checked before touching any high-impact model
- [ ] Writes target the development dataset; production is read-only