[PB-016] Data Warehouse (medallion + dbt + Synapse)

[ma7moudalysalem]

PB-016 — Data Warehouse (medallion + dbt + Synapse)

Owners: @ma7moudalysalem (lead) + @yousra-elnoby (moderate stories) • Priority: Medium • Iteration: 3 • Est: 55 pts

Spec folder: .specify/specs/PB-016-data-warehouse/
SRS section: docs/SRS.md section 8.3 + FR-226..FR-245
Architecture: docs/ANALYTICS.md — sections 3-5

Goal

Replace DirectQuery (PB-015) with a proper medallion pipeline. SQL Server CDC → ADF → Data Lake Bronze → dbt Silver → dbt Marts (Gold star schema) → Synapse Serverless → Power BI.

User stories + split

Story	Owner
US-166 Enable CDC on 15 tables	@yousra-elnoby
US-167 Bronze ingestion pipeline (ADF every 15 min)	@ma7moudalysalem
US-168 Silver dbt models (clean + dedup + explode JSON)	@yousra-elnoby
US-169 Gold star schema (5 facts + 5 dims, 2 SCD2)	@ma7moudalysalem
US-170 Power BI cutover to Gold	@TasneemShaaban
US-171 Data-quality assertions (≥ 20)	@yousra-elnoby
US-172 dbt docs site	@yousra-elnoby
US-173 Pipeline as code (ADF ARM + dbt CI)	@ma7moudalysalem

Blocking dependencies

• PB-015 (Power BI dashboards must exist before cutover)
• INFRA (Azure Data Lake + Synapse workspace provisioning)
• PB-017 data-model additions (RecommendationClickEvent, etc) must also flow into Gold

Ready for review

Done when every Power BI dashboard reads from Gold, dbt tests are ≥ 95% pass, docs/ANALYTICS.md runbooks are current.