Digital Lending Dimensional Data Modeling
End-to-end dimensional modeling project for a digital lending platform - from conceptual and logical design through physical Star Schema implementation (fact and dimension tables) in Amazon Redshift - covering Transient Staging, Core Canonical Data Model, and a Semantic Layer with Data Contracts for analytics, credit risk, and BI consumption
Tech Stack
Problem Statement
Raw data ingested from conventional banking, sharia-compliant, operational, and portfolio risk sources arrives with heterogeneous schemas, inconsistent naming conventions, and varying data quality. Analytics teams, credit risk models, and Power BI dashboards need a unified, business-ready view of lending entities - loans, repayments, customers, collateral - across all partner banks and source systems.
Technical Approach
Followed a full dimensional modeling lifecycle: (1) Conceptual Modeling - identified core business processes (loan origination, repayment, customer onboarding) and defined grain for each subject area; (2) Logical Modeling - designed Star Schemas with fact tables (fact_loan_disbursement, fact_repayment, fact_portfolio_snapshot) and conformed dimension tables (dim_customer, dim_product, dim_branch, dim_date, dim_source_system); (3) Physical Implementation - built the 3-layer stack in Amazon Redshift using dbt Core: Transient Staging (per-source normalization, type casting, deduplication), Core Canonical Data Model (cross-bank unified entities with surrogate keys and SCD handling), and Semantic Layer plus Data Contracts (business-facing views, metric definitions, and enforced schema agreements between producers and consumers).
Key Results
- →Conceptual model covering loan origination, repayment collection, and portfolio snapshot business processes across all source systems
- →Star Schema design: fact tables (fact_loan_disbursement, fact_repayment, fact_portfolio_snapshot) with conformed dimensions (dim_customer, dim_product, dim_branch, dim_date, dim_source_system)
- →Transient Staging layer per source system - type casting, deduplication, and null handling before canonical promotion
- →Core Canonical Data Model unifying loan, repayment, customer, and collateral entities across conventional banks (5 partners), sharia-compliant partners, operational data, and portfolio risk sources with surrogate keys and SCD handling
- →Semantic Layer exposing business metrics and KPIs used directly by Power BI dashboards and portfolio analytics teams
- →Data Contracts enforcing schema agreements - breaking changes caught at model boundary before reaching downstream consumers
- →dbt tests (not-null, unique, accepted-values, referential integrity) validating data quality at every modeling layer
- →In progress - Data Vault modeling layer: introducing an agile, auditable Data Vault design to decouple raw history from business rules - enabling KPI definitions (e.g. National Bank of Ethiopia DPD loan status: current, pass, doubtful, substandard, loss) to change without breaking or rebuilding the underlying warehouse model
- →99.9% system uptime achieved via Kubernetes (EKS) containerization across all pipeline services
- →CI/CD: GitHub Actions + GitLab CI/CD pipelines for automated deployments - zero-touch promotion from dev to production
- →Data compliance: GDPR-compliant hashing and anonymization implemented across all PII fields at ingestion boundary
- →Alerting: Microsoft Teams webhook alerts via Apache Airflow for pipeline failures and data quality breaches (currently being refactored for improved reliability)