Structuring the whole.
WHAT LAYERS TO BUILD
- Raw: data as received, unmodified
- Staging or cleaned: typed, deduplicated, standardised
- Modelled: dimensional or otherwise, ready for use
- Serving: aggregates and views for specific consumers
WHY KEEP RAW DATA UNMODIFIED
So processing can be corrected and rerun without re-extracting.
WHY THAT MATTERS ENORMOUSLY
Sources frequently cannot be re-read, and a transformation bug otherwise loses the original.
WHAT THE CLEANED LAYER DOES
Applies types, handles nulls, standardises codes, removes duplicates.
WHAT THE MODELLED LAYER DOES
Applies business definitions and structure.
WHY SEPARATE THOSE
Technical cleaning and business logic change for different reasons.
WHAT THE SERVING LAYER PROVIDES
Pre-aggregated or simplified structures for particular consumers.
WHAT NAMING CONVENTIONS TO ESTABLISH
Table and column naming Prefixes indicating layer How dates and times are named
WHY
Consistency is what makes a warehouse navigable by someone who did not build it.
WHAT TO DOCUMENT
Every table, its grain, its source, and its owner.
WHAT TO AVOID
Business logic duplicated in several places.
