Organising models.
WHAT LAYERS TO USE
- Sources: declared references to raw tables
- Staging: one model per source table, lightly cleaned
- Intermediate: reusable logic combining staging models
- Marts: models for consumption, by subject area
WHAT STAGING SHOULD DO
Rename columns to conventions Cast types Handle nulls Deduplicate Nothing else
WHY NOTHING ELSE
Keeping it mechanical means it never needs business discussion.
WHAT INTERMEDIATE MODELS ARE FOR
Logic used by several marts, defined once.
WHAT MARTS SHOULD BE
Shaped for how people actually query them.
WHAT NAMING CONVENTION TO USE
A prefix indicating layer, then the subject.
WHY
So dependencies are obvious from names alone.
WHAT TO AVOID
Marts referencing raw tables directly Circular dependencies Very deep chains nobody can follow Logic duplicated across marts
WHAT TO EXTRACT WHEN LOGIC REPEATS
An intermediate model.
WHAT TO REVIEW
The dependency graph, periodically.
WHAT IT REVEALS
Models nobody uses, and logic that should be shared.
WHAT TO DELETE
Models nothing depends on and nobody queries.
