Multi-Bank Digital Lending ETL Orchestration

Problem Statement

A digital lending platform needed automated, reliable ingestion of loan account and repayment data from 5 Ethiopian partner bank Metabase instances into a centralized data lake - with consistent scheduling, per-bank isolation, and analytics-ready output for portfolio risk monitoring.

Technical Approach

Built 5 parallel bank pipelines in Mage AI, each following a 3-block pattern: (1) custom API block authenticates with the bank's Metabase instance and discovers credit and integration hub table metadata; (2) parallel data loader blocks query loan account and customer data; (3) S3 exporter writes dated JSON to the RAW layer partitioned by bank, source, table, and date. A separate aggregation pipeline merges all banks and applies a transformer for PAR bucket computation, loan cycle counting, and product classification before writing to the application layer. A companion read-only Athena query wrapper package exposes cleansed and cleansed_layer_2 databases via SQL with pagination and write-operation blocking.

Key Results

• →5 per-bank pipelines running on a 6-hour ingestion schedule with isolated RAW layer partitions in S3
• →Per-bank RAW layer in S3 partitioned by source, table, and date
• →PAR bucket segmentation (Current, 30-90, 90-179, 180-364, 365+ days) computed in aggregation transformer
• →Loan product classification (15-day vs 30-day) and loan cycle number derived per customer
• →Read-only Athena query wrapper with pagination (100 rows/page), write-op blocking, and deduplication on updated_on/updated_at
• →Experimented with multi-interface access: Spark SQL, PySpark, Presto, Trino, Zeppelin/Jupyter using EMR, and also with self-deployed or managed Trino with Glue data cataloging Hive and Iceberg format to change the AWS Athena-based Lakehouse architecture - all built on top of a well-architected, partitioned medallion architecture (Bronze, Silver, Gold) in AWS S3 as the underlying data lake