sylvia772/pesaflow-analytics-engineering-

Analytics engineering pipeline for PesaFlow — a fictional African digital lending platform across Nigeria, Kenya and Ghana. Turns messy PostgreSQL transactional data into trusted, business-ready analytical models that power credit risk decisions, customer segmentation and collections strategy. # PesaFlow Analytics Engineering Pipeline An end-to-end analytics engineering pipeline for PesaFlow — a fictional African digital lending and BNPL platform operating across Nigeria, Kenya and Ghana (2021-2024). This is the third and most technically advanced project in a series of portfolio projects documenting a self-directed journey into analytics engineering. --- ## The Problem This Solves PesaFlow's operational data lives in PostgreSQL — a transactional database optimised for writing fast, not for answering business questions. The credit risk team needs to know who to approve. The collections team needs to know which defaulted loans are worth chasing. The ML team needs clean, structured features to train a default prediction model. The product team needs to understand which customer segments are growing and which are at risk. None of these questions can be answered reliably from raw transactional tables. This pipeline bridges that gap. --- ## Architecture PostgreSQL (PesaFlow operational database) ↓ Python + SQLAlchemy (extract_to_bigquery.py) ↓ BigQuery: raw_pesaflow (Bronze — raw, untouched) ↓ dbt staging models (Silver — clean, typed, standardised) ↓ BigQuery: staging_pesaflow ↓ dbt mart models (Gold — business-ready, star schema) ↓ BigQuery: mart_pesaflow ↓ Python (export_to_parquet.py) → Parquet + JSON files --- ## Tech Stack | Tool | Purpose | |------|---------| | PostgreSQL | Source — operational lending database | | Python + SQLAlchemy | Extract from PostgreSQL | | pandas | Transform dataframes | | google-cloud-bigquery | Load into BigQuery | | dbt-bigquery | Transform inside BigQuery | | dbt_utils | Surrogate keys and utility functions | | dbt_expectations | Advanced data quality testing | | BigQuery | Cloud analytical warehouse | | Git + GitHub | Version control | --- ## Data 6 source tables across 3 countries: | Table | Rows | Description | |-------|------|-------------| | customers | 505 | Borrower profiles | | loan_applications | 800 …