From raw landing to tested metric marts.
The full pipeline behind the content & games warehouse: heterogeneous sources land with ingestion-level quality gates, then a SQL transformation project takes over — sources → staging views → conformed dimensions, facts, and metric marts, every model tested. The project is real (its build output is summarised below), not a mockup.
How the data lands
Five sources, each with its own format and cadence, land as the raw layer — checked at the door. Failures quarantine loudly; nothing bad promotes into the models below. (Ingestion design; the build that follows is real.)
| Source | Format | Cadence | Landing gate |
|---|---|---|---|
| streaming_events | JSON · event stream | continuous | idempotency key on (user, title, event_ts) — replayed events dropped |
| content_spend | CSV · monthly finance export | monthly | row-count reconciliation · rows missing units quarantined |
| games_telemetry | REST API | hourly | schema type-cast & null-handling — bad rows quarantined |
| title_catalog | CDC | on change | SCD-2 effective-dated rows — catalog history preserved |
| users | CRM extract | daily | key null/uniqueness checks · freshness SLA (< 3h) |
Lineage
Raw sources are staged into clean views, then modeled into conformed dimensions, facts, and metric marts. Facts reference dimensions — enforced by relationships tests.
Models
| Model | Layer | Materialization | Description |
|---|
Tests — 17 / 17 passing
Data quality is codified as tests and enforced on every build.
A model: content_finance_summary.sql
The executive FP&A metric — planned vs. actual with variance and running totals, defined once.