Cash transfer monitoring

Payment records lived in the finance system, beneficiary records in KoBo, and verification calls in a spreadsheet a field officer maintained by hand. Reconciling the three took most of a week each month, and by the time it was done the numbers were already out of date.

The programme could report how much money had moved. It could not reliably report how many distinct households had received it, which is the number the donor actually asked for.

I modelled the three sources into a single warehouse layer, keyed on a household identifier agreed with the field team rather than assumed from the data. Where the identifier was missing, the pipeline flags the record for review instead of guessing.

Transfers, verifications and grievances then hang off that spine as separate fact tables, so a payment can be traced end to end without joining across systems by hand.

Data-quality tests run on every load: duplicate identifiers, transfers without a matching registration, and verification dates that precede the payment.

Monthly reconciliation became a dashboard refresh. The field team now sees failed and unverified transfers while there is still time to act on them, rather than in a report written six weeks later.