Section 11
System Design Flow
The canonical pipeline shape used in every deployment in this playbook.
Every system has the same five stages. Only the technology underneath changes.
Source
API / DB / files
Ingestion
raw landing
Bronze
raw_*
Silver
_clean
Gold
_summary
Outputs
BI / API / files
The layers
Source
External systems you don't control: APIs, OLTP databases, SFTP drops, flat files, webhooks. Treat as read-only.
Ingestion
A thin job that pulls from source and lands raw bytes/rows in the bronze layer. Zero transformation. Stamp every row with _loaded_at and _source.
Bronze — raw
Append-only mirror of source. Same column names, no business logic.
Silver — cleaned & conformed
Type-cast, deduplicated, joined to reference data, business keys resolved. One row per logical entity.
Gold — business-ready
Aggregates, KPIs, dimensional models. The only layer your dashboards and APIs read from.
Outputs
BI tools, REST APIs, Excel exports, reverse-ETL into operational systems.
Rule of thumb
If a stakeholder asks "where does this number come from?", you should be able to trace gold → silver → bronze → source in under a minute.