AfriMoney Intelligence Platform

Chapter 01

What is AfriMoney?

The African remittance market and the case for a unified intelligence platform

1.1 The Business Problem

South Africa is home to an estimated 3.5 million foreign nationals from neighbouring countries — primarily Zimbabwe, Mozambique, Zambia, and Malawi. These workers send money home regularly to support families, pay school fees, and fund small businesses. The remittance corridor from South Africa to sub-Saharan Africa moves billions of rand every month.

R 78B+

Annual SA Outbound Volume

Active Corridors Modelled

6–8%

Avg Transfer Cost (World Bank)

Despite this volume, most remittance operators lack a unified data platform. Customer data lives in siloed CRMs, transfer records are in separate operational databases, KYC documents are stored in a different system, and FX rates come from yet another feed. The result: no single view of the customer, the corridor, or the business.

AfriMoney's answer: One Snowflake data platform that ingests from every source system across both brands (Mukuru and Mama Money), standardises the data through a Bronze → Silver → Gold medallion architecture, powers 14 dbt models, and runs 3 Snowpark ML models — all without data leaving Snowflake.

1.2 Mukuru — Africa's Largest Money Transfer Operator

Founded in 2004 in Zimbabwe, Mukuru has grown to serve over 17 million customers across 50+ countries. Its product suite extends well beyond money transfers:

Product	Description	Data Domain
International Remittances	Cash, bank, mobile wallet payouts to 15+ countries	FACT_REMITTANCE_TRANSFER
Mukuru Card	Prepaid Mastercard; salary, spending, cash access	FACT_CARD_TRANSACTION
Mukuru Fast Loan	Short-term credit linked to Card eligibility	FACT_LOAN_APPLICATION
Mukuru Funeral Cover	Micro-insurance with repatriation service	FACT_INSURANCE_POLICY
MukuruPay	Bill payments, merchant payments, cash e-commerce	FACT_BILL_PAYMENT
Dollar Savings	USD-denominated savings product	FACT_USD_SAVINGS

1.3 Mama Money — Wallet-First Fintech Challenger

Founded in 2015 with a focus on transparency and affordability, Mama Money's ISO 9001 certified platform puts the wallet at the centre of everything. Every product — transfers, card, savings — flows through the wallet.

Product	Unique Feature	Data Domain
Mama Money Send	Transfers to 13+ countries via mobile wallet, cash, bank	FACT_REMITTANCE_TRANSFER
Mama Wallet	ZAR digital wallet — hub of all activity	FACT_WALLET_LEDGER
Mama Card	Salary deposits, card spend, airtime, electricity	FACT_CARD_TRANSACTION
Save in USD	USDC-backed stablecoin savings — not a bank account	FACT_USD_SAVINGS
Send More with Mama	Structured limit uplift via document submission	CUSTOMER_LIMIT_PROFILE

Important modelling note: Mama Money's "Save in USD" product is backed by USDC (USD Coin), a regulated stablecoin. The data model must distinguish the displayed currency (USD) from the underlying digital asset (USDC). It carries different regulatory obligations than a standard bank account.

1.4 Why One Platform?

The strongest architecture is one shared Snowflake database with a BUSINESS_KEY dimension (MKR / MMY) distinguishing the two brands. This enables:

Executive reporting across both brands from a single Power BI dataset
Cross-brand fraud signals (a customer flagged on Mukuru is detectable on Mama Money)
Shared KYC cost — one verification framework, two brand touchpoints
Unified customer 360 for product cross-sell analytics
Consistent ML features — fraud and churn models benefit from full transaction history

Chapter 02

Snowflake Account Architecture

Warehouses, databases, schemas, roles, and the internal stage setup

2.1 Why Snowflake?

AfriMoney chose Snowflake as its cloud data platform for four reasons:

Separation of compute and storage — the 40M-row dataset costs nothing when no query is running; warehouses auto-suspend after 60–120 seconds of inactivity.
Zero-copy cloning — the Bronze layer can be cloned instantly for testing without duplicating 500 GB of storage.
Snowpark ML — Python ML code runs inside Snowflake; raw PII never leaves the platform to an external training server.
Time Travel & Fail-Safe — 90-day time travel on all fact tables means any accidental delete or bad load can be recovered with a single UNDROP or SELECT ... AT(OFFSET) query.

2.2 Virtual Warehouse Strategy

Warehouse	Size	Purpose	Auto-Suspend
AFRIMONEY_INGEST_WH	MEDIUM	COPY INTO loads from stage	120s
AFRIMONEY_TRANSFORM_WH	LARGE	dbt runs (Silver + Gold)	60s
AFRIMONEY_ANALYTICS_WH	SMALL	Power BI queries, ad-hoc	300s
AFRIMONEY_ML_WH	X-LARGE	Snowpark ML training (multi-cluster)	60s

Cost tip: The ML warehouse is X-LARGE and multi-cluster (1–4 nodes). It should only be active during scheduled training jobs (Sunday 02:00 SAST). An automated Snowflake Task calls the stored procedure and then the warehouse auto-suspends — typical cost per weekly training run: ~$8–15 USD.

2.3 Database & Schema Layout

-- AFRIMONEY_DB ├── BRONZE -- raw, immutable; COPY INTO lands here ├── SILVER -- dbt staging views + intermediate tables ├── GOLD -- dbt mart tables; Power BI connects here ├── STAGING -- internal stage for CSV uploads └── UTILS -- shared UDFs, macros, stored procedures -- AFRIMONEY_ML_DB ├── FEATURE_STORE -- ML feature tables (refresh daily) ├── EXPERIMENTS -- training run logs + metrics ├── MODEL_REGISTRY -- registered model versions └── PREDICTIONS -- scored output tables

2.4 RBAC — Role-Based Access Control

Snowflake's RBAC system ensures that a Power BI analyst can never modify the Bronze layer, and a data engineer can never see the ML model weights. The role hierarchy looks like this:

ACCOUNTADMIN └── SYSADMIN └── AFRIMONEY_ADMIN ├── AFRIMONEY_ENG -- WRITE on Bronze/Silver, CREATE on Gold ├── AFRIMONEY_ANALYST -- READ on Gold only │ └── AFRIMONEY_VIEWER -- READ Gold (Power BI service account) └── AFRIMONEY_ML_ENG -- WRITE on ML_DB only

Chapter 03

Bronze Layer — Raw Ingestion with COPY INTO

How 40 million rows of synthetic fintech data land in Snowflake, immutably and verifiably

3.1 Source Systems Inventory

The Bronze layer ingests from 12 source system categories. Each source system writes to its own subfolder in the Snowflake internal stage (@STAGING.AFRIMONEY_STAGE/), and a dedicated COPY INTO command loads each table.

Bronze design rule: Bronze tables are append-only and immutable. We never UPDATE or DELETE in Bronze. If a record is wrong, the correction happens in Silver (dbt). This means we always have a full audit trail back to the raw source data.

3.2 The ETL Pipeline — Step by Step

Generate synthetic data (Python)

40M+ rows across 20 table types generated with realistic distributions — lognormal transfer amounts, correlated fraud patterns, seasonal transfer volumes.

Upload CSVs to Snowflake internal stage

PUT file://data/bronze/*.csv @STAGING.AFRIMONEY_STAGE AUTO_COMPRESS=TRUE — Snowflake compresses and stores in its own S3/Azure Blob; you pay storage, not egress.

COPY INTO Bronze tables

The COPY INTO command reads from the stage and loads into the Bronze table. The AFRIMONEY_CSV_FORMAT file format handles nulls, quoted fields, and type casting automatically.

Verify via COPY_HISTORY & INFORMATION_SCHEMA

Snowflake records every COPY INTO in INFORMATION_SCHEMA.COPY_HISTORY. Query it to confirm row counts, check for errors, and audit the load time.

Cluster keys applied automatically

All large fact tables are clustered by BUSINESS_KEY and TO_DATE(created_datetime). Snowflake automatically reclusters over time — queries that filter by date scan only the relevant micro-partitions.

3.3 Snowflake Worksheet — Bronze Query

Snowflake — AfriMoney Intelligence Platform — Worksheets

SELECT business_key, corridor_code, created_month, initiated_count, completed_count, ROUND(success_rate_pct, 2) AS success_rate, ROUND(total_net_revenue_zar/1e6, 2) AS revenue_M_ZAR, avg_fx_spread_pct, median_completion_minutes FROM AFRIMONEY_DB.GOLD.MART_REMITTANCE WHERE created_month BETWEEN '2026-01' AND '2026-06' AND completed_count > 100 ORDER BY revenue_M_ZAR DESC LIMIT 20;

✓ Query succeeded | 18 rows | 0.3s | TRANSFORM_WH (LARGE) COMPLETED

BUSINESS_KEY	CORRIDOR_CODE	CREATED_MONTH	INITIATED	COMPLETED	SUCCESS%	REVENUE_M_ZAR	FX_SPREAD%
MKR	ZA-ZW	2026-06	28,412	22,156	78.0	4.82	5.41
MKR	ZA-MZ	2026-06	18,930	14,772	78.0	3.21	5.38
MMY	ZA-ZW	2026-06	11,240	8,767	78.0	1.91	5.45
MKR	ZA-ZM	2026-06	9,810	7,652	78.0	1.66	5.39
MMY	ZA-MZ	2026-06	7,544	5,884	78.0	1.28	5.42

3.4 COPY INTO Performance

Table	Rows	File Size	Load Time (LARGE WH)
dim_customer	500,000	114 MB	~12s
dim_recipient	1,000,000	120 MB	~15s
fact_remittance_transfer	5,000,000	~900 MB	~65s
fact_wallet_ledger	~10,000,000	~1.6 GB	~120s
fact_transfer_status_history	~7,500,000	~800 MB	~90s
All tables	~40M+	~5 GB	~8 min total

Chapter 04

Silver Layer with dbt

Transforming raw Bronze data into clean, tested, canonical Silver models using dbt on Snowflake

4.1 Why dbt?

dbt (data build tool) is the industry standard for SQL-first data transformation. On Snowflake it compiles your .sql model files into CREATE TABLE AS SELECT or CREATE VIEW AS statements and runs them in the right order based on the DAG (Directed Acyclic Graph) of ref() calls.

Key benefit: Every dbt model is version-controlled in Git, tested automatically, and documented inline. When a business analyst asks "where does the revenue figure in the dashboard come from?", the answer is traceable all the way back to a line in a Bronze CSV.

4.2 Project Structure

afrimoney/ ├── dbt_project.yml # project config — materialisations, warehouses ├── profiles.yml # Snowflake connection (dev + prod targets) ├── models/ │ ├── staging/ # SILVER schema — VIEWs over Bronze │ │ ├── stg_transfers.sql │ │ ├── stg_customers.sql │ │ ├── stg_fx_rates.sql │ │ └── stg_loans.sql │ ├── intermediate/ # SILVER schema — TABLEs with business logic │ │ ├── int_transfer_profitability.sql │ │ ├── int_customer_transfer_stats.sql │ │ └── int_risk_features.sql │ └── marts/ # GOLD schema — TABLEs for Power BI │ ├── mart_remittance.sql │ ├── mart_customer_360.sql │ ├── mart_fx_profitability.sql │ └── mart_risk_compliance.sql ├── tests/ │ └── generic_tests.yml # not_null, unique, accepted_values, relationships └── macros/ └── afrimoney_macros.sql # div0, safe_divide, business_day helpers

4.3 Materialisation Strategy

Layer	Materialisation	Why?
Staging	view	No storage cost; always reads fresh from Bronze. Fast to iterate during development.
Intermediate	table	Heavy joins and aggregations (5M transfer × 500K customer). Pay compute once, reuse many times.
Marts	table	Power BI connects here. Sub-second query response requires pre-materialised tables.

4.4 dbt DAG — Full Pipeline

4.5 dbt Run Output

$ dbt run --select staging+ intermediate+ marts+ --target prod
Running with dbt=1.8.0
Found 14 models, 28 tests, 13 sources, 4 macros
Concurrency: 16 threads (target='prod')

1 of 14 START view model SILVER.stg_transfers ........... [RUN]
1 of 14 OK created view SILVER.stg_transfers ............. [OK in 0.8s]
2 of 14 START view model SILVER.stg_customers ........... [RUN]
2 of 14 OK created view SILVER.stg_customers ............. [OK in 0.7s]
3 of 14 START table model SILVER.int_transfer_profitability [RUN]
3 of 14 OK created table SILVER.int_transfer_profitability [OK in 18.4s]
4 of 14 START table model SILVER.int_customer_transfer_stats [RUN]
4 of 14 OK created table SILVER.int_customer_transfer_stats [OK in 22.1s]
...
12 of 14 START table model GOLD.mart_remittance .......... [RUN]
12 of 14 OK created table GOLD.mart_remittance ........... [OK in 31.7s]
13 of 14 START table model GOLD.mart_customer_360 ....... [RUN]
13 of 14 OK created table GOLD.mart_customer_360 ......... [OK in 44.2s]
14 of 14 START table model GOLD.mart_risk_compliance .... [RUN]
14 of 14 OK created table GOLD.mart_risk_compliance ...... [OK in 19.8s]

Finished running 14 models in 2 minutes 18.3 seconds.

✓  14 of 14 models OK
✓  28 of 28 tests passed
✗   0 errors   0 warnings

Done. PASS=14 WARN=0 ERROR=0 SKIP=0 TOTAL=14

4.6 Key dbt Tests

28 tests run automatically after every dbt run. The most important ones:

Test	Model	What it catches
unique(transfer_id)	stg_transfers	Duplicate records from source — a common ETL bug
not_null(send_amount_zar)	stg_transfers	Missing amounts that would corrupt revenue totals
relationships(sender_customer_id)	stg_transfers	Orphaned transfers with no customer record
accepted_values(transfer_status)	stg_transfers	Unknown status codes from new source system versions
expression_is_true(success_rate between 0 and 100)	mart_remittance	Broken division logic producing rates > 100%
unique(customer_id)	mart_customer_360	Fan-trap joins that inflate customer count
freshness(fact_remittance_transfer)	source	Pipeline failure — data not loaded in 48h

4.7 Reconciliation Controls

Two new intermediate models close a gap the KPI glossary had flagged but never built: int_payment_reconciliation and int_payout_reconciliation. Each reconciles one side of the money movement against FACT_REMITTANCE_TRANSFER — collection (FACT_PAYMENT) and settlement (FACT_PAYOUT) — counting only SUCCESS-status attempts so retried or declined attempts can't inflate the totals.

Test	Model	What it catches
accepted_values(payment_recon_status)	int_payment_reconciliation	Unhandled recon outcomes — forces every new status branch to be classified
expression_is_true(successful_payment_count <= 1)	int_payment_reconciliation	Duplicate successful charges on the same transfer
expression_is_true(successful_payout_count <= 1)	int_payout_reconciliation	Duplicate settlements disbursed for one transfer
expression_is_true(total_reconciliation_breaks >= 0)	mart_reconciliation	Sanity bound on the roll-up KPI itself
singular: assert_gold_remittance_volume_matches_bronze	mart_remittance	Silver→Gold control total — catches join fanout or dropped rows in the mart, to the cent

Why this matters: a revenue dashboard can look perfectly healthy while quietly under- or over-collecting — the top-line number nets errors out. The reconciliation mart reports collection and settlement completeness separately, so a corridor that's over-collecting (duplicate charges) doesn't cancel out a corridor that's under-collecting (missed charges) in the aggregate.

4.8 Incremental Materialisation

Every model in the first build of this project was materialised as a table — rebuilt from zero on every run. That is the right default while a project is small and the wrong one at 5 million transfers, where a full rebuild of the profitability model costs roughly 12 minutes of MEDIUM-warehouse time to reproduce history that cannot change.

The two incremental models use different strategies, and the reason why is the most important idea in this chapter.

Model	Strategy	Why this one
int_transfer_profitability	`merge` on transfer_id	A transfer is mutable after creation — a payout settling days later rewrites its cost and revenue. Append-only would duplicate the row; merge corrects it in place.
mart_remittance	`delete+insert` on month partitions	This is an aggregate. If a transfer flips from failed to completed, the month's `completed_count` must be recomputed from scratch, not incremented. Merging a partial aggregate onto an existing one would silently under-count.

The late-arriving data trap: the obvious incremental filter is where created_at > (select max(created_at) from this). On this platform that is quietly wrong. A transfer created on the 30th can settle on the 2nd, and the settlement is what populates partner_cost_zar. Filtering on strictly-greater-than loads that row once with incomplete cost data and never revisits it — so the margin is permanently overstated, by an amount nobody can see. Both models instead re-scan a incremental_lookback_days window (default 7) behind the watermark and let the merge overwrite whatever changed.

A second subtlety applies only to the mart: the list of months to rebuild is derived from the source, not from the target table. A transfer created in March but completed in April belongs to March's bucket, so April's arrival must rebuild March. Keying off the target's maximum month would miss exactly this backfill case.

Result: a typical daily run drops from roughly 14 minutes to under 90 seconds, while remaining arithmetically identical to a full refresh — a property the singular test assert_gold_remittance_volume_matches_bronze re-proves on every single build, to the cent. Incremental logic that is not continuously reconciled against a control total is a silent-drift generator, so the test is not optional.

4.9 Macros and Custom Generic Tests

The project declared a macro-paths directory from day one and left it empty. Four macros now carry logic that was previously copy-pasted across models, and three custom generic tests express assertions dbt ships no equivalent for.

Macro	Purpose
`generate_schema_name`	Overrides dbt's default. In prod, models land in a bare `GOLD` / `SILVER` so Power BI and the Snowflake roles point at a stable address regardless of who ran the build; in dev, schemas are namespaced per engineer so two people never collide.
`zar()`	Money is held at NUMBER(18,6) because FX conversion needs the precision, but every reported figure must settle to whole cents — otherwise reconciliation chases sub-cent drift that is not a real break.
`safe_pct()`	Returns NULL, not 0, when the denominator is zero or NULL. A fake 0% reads on a dashboard as "we earned no margin" when the truth is "margin is undefined here" — and averaging a column of those zeros drags every corridor average down.
`limit_data_in_dev()`	Clamps non-prod builds to a recent slice of history, so testing a one-line change does not cost a full-scan of 5M rows.

Custom generic test	What it expresses that built-ins cannot
`completeness_within_tolerance`	A two-sided band. Reconciliation completeness above 100% is as broken as below it, and dbt ships nothing for "must sit inside a range where both edges are failures".
`not_negative`	Allows zero, ignores NULL. Deliberately does not conflate the two — NULL-ness is `not_null`'s job, and merging them makes failures harder to read.
`no_orphan_transfers`	A referential check with a tolerance. A strict `relationships` test fails the whole build for one late-arriving payout, which in a streaming remittance pipeline is normal rather than broken. This allows a configurable orphan percentage, so genuine breakage still surfaces but ordinary lag does not page anyone at 3am.

A real bug this found: the existing test on collection_completeness_pct asserted between 0 and 100. But the validation run documented in §5.5 shows MKR at 102.0% — over-collecting through duplicate charges. The test would have failed on the project's own published figures. Over-collection is a break, not an impossibility, and the assertion has been replaced with the two-sided tolerance band above.

4.10 Model Contracts and Versioning

mart_remittance feeds Power BI, the Excel workbook, the interactive map and this document. That makes its schema a published interface rather than an implementation detail, so it now carries an enforced contract: every column has a declared type and the build fails at Snowflake if a column is dropped, renamed, or retyped.

What this prevents: without a contract, renaming a column does not break anything loudly. dbt rebuilds happily, and the failure surfaces days later as a Power BI visual quietly rendering blank — at which point the cause is several commits back. A contract turns a silent downstream failure into a loud build-time one.

Contracts alone would make necessary changes impossible, so the model is also versioned. v2 is current and adds the surrogate key; v1 is retained with a deprecation_date so BI datasets pinned to the old shape keep working through a migration window. Removing a version becomes an announced, dated change rather than a silent drop.

4.11 Unit Tests

Data tests and unit tests answer genuinely different questions, and a project needs both:

	Data tests	Unit tests
Question	Is the data currently in the warehouse valid?	Is the SQL logic correct?
Input	Whatever is in Snowflake right now	Fixed, hand-written rows
Needs warehouse data	Yes	No
Catches	Bad data arriving	Bad logic shipping

The distinction matters most at boundaries the real data happens not to contain. There is no transfer sitting at exactly R200.00 today, so no data test can prove the platinum-tier threshold is handled correctly — the >= could be a > and nothing would fail. A unit test pins it with a fabricated row, and keeps pinning it after someone edits that CASE expression two years from now.

Unit test	Asserts
test_profitability_tier_boundaries	Every tier edge, including exact-threshold values: R200.00 is platinum, R199.99 is gold, R0.00 is bronze rather than loss.
test_incomplete_transfers_are_excluded	Only completed transfers reach the model. Losing this filter would recognise revenue on failed transfers — the most damaging silent error the model could produce, because the resulting number still looks entirely plausible.
test_safe_pct_returns_null_not_zero	A zero-revenue transfer yields NULL margin, never 0%.

Why these run first in CI: they need no Snowflake data and no credentials, so they finish in seconds and cost nothing. Logic errors get caught before a warehouse is ever resumed.

4.12 The Semantic Layer

"Net revenue" is currently computed in four places: a Power BI DAX measure, an Excel formula, this document's narrative, and the map's JSON export. They agree today only because a dedicated QA pass forced them to agree. Nothing stops them drifting apart again, because the definition lives in four codebases and no one of them is authoritative.

A MetricFlow semantic model defines the measures once against mart_remittance, and ten metrics on top of them. Every consumer becomes a caller rather than a re-implementer.

Metric	Type	Note
net_revenue_zar	simple	The headline P&L number. If two reports disagree, this is the arbiter.
success_rate	ratio	Defined as completed ÷ initiated, not as an average of per-row rates — averaging an average weights a 12-transfer corridor the same as a 400,000-transfer one, which is how this number most often gets reported wrongly.
net_revenue_mom_growth	derived + offset	Period comparison defined once here, rather than as bespoke DAX only the Power BI report understands.
digital_volume_share	filtered ratio	The clearest indicator of strategic direction, so it is a first-class metric rather than a slicer rebuilt in each report.

4.13 CI/CD and Environments

Three environments, distinguished by more than a schema name: dev (personal schema, small warehouse, history clamped to 3 months), ci (throwaway schema per pull request, dropped afterwards), and prod (key-pair auth, bare schema names, grants and audit logging active).

The CI pipeline is deliberately ordered cheapest-first, so the fastest feedback comes from the checks that cost nothing:

Stage	Needs warehouse?	What it does
1 — Lint & parse	No	SQLFluff over changed models only. Retrofitting a linter across an existing project in one commit produces an unreviewable diff; gating changed files reaches the same destination without the big-bang change.
2 — Unit tests	No	Mocked inputs. Seconds, free.
3 — Slim build	Yes	`state:modified+` with `--defer`: build only what changed plus its children, resolving everything else against production.

Why Slim CI matters here: a pull request touching one staging model should not rebuild 5M rows through every downstream mart. Deferral takes a typical PR from a ~14-minute full build to under two minutes. That is the difference between CI people wait for and CI people learn to route around — and CI that gets routed around is not a quality gate, it is a formality.

Failure mode handled explicitly: if the production manifest cannot be fetched, the pipeline falls back to a full build rather than proceeding with an empty selection. A CI run that passes because it silently tested nothing is considerably worse than a slow one.

Two further details close the loop. Because exposures are declared (§4.9's lineage now runs source-to-consumer), CI can annotate a pull request when a change reaches something a human signs against — the finance reconciliation pack, or a board dashboard. And the CI schema is dropped in an always() step, including on failure, behind a macro that refuses to drop anything not prefixed CI_ and refuses to run against prod at all: a run-operation issuing DROP SCHEMA CASCADE is exactly the thing that, given one wrong argument, deletes GOLD.

Chapter 05

Gold Layer — Analytical Marts

Business-ready tables optimised for Power BI, direct SQL queries, and Snowpark ML feature extraction

5.1 mart_remittance

The core remittance mart has one row per business × corridor × month × channel × payment method. It pre-computes every KPI a remittance executive needs:

Metric Group	Key Fields
Funnel	initiated_count, completed_count, failed_count, success_rate_pct
Revenue	total_net_revenue_zar, avg_revenue_per_transfer_zar, fx_margin / fee split
FX	avg_fx_spread_pct, corridor-level margin analysis
Speed	avg_completion_minutes, median_completion_minutes
Risk	fraud_rate_bps (basis points of total volume)

5.2 mart_customer_360

One row per current customer (SCD2 IS_CURRENT = TRUE) combining transfer behaviour, wallet activity, card spending, USD savings, and loan data into a single analytical record. Two derived scores are computed entirely in Snowflake SQL:

LTV Score — weighted composite of lifetime revenue, card spend, transfer count, wallet balance, and USD savings
Engagement Score (0–100) — recency × frequency × KYC completion × wallet activity

5.3 Snowflake Architecture Diagram

5.4 Power BI Connection

Power BI connects to the GOLD schema using the AFRIMONEY_VIEWER role and AFRIMONEY_ANALYTICS_WH (SMALL, auto-suspend 300s). The DirectQuery mode is used for the mart tables — Snowflake handles all aggregation pushdown, so Power BI visuals remain fast even against 5M-row fact tables in the underlying Gold.

DirectQuery tip: Enable Query folding in Power Query and use aggregation tables for the most common visual types (monthly trend, corridor bar chart). This reduces ANALYTICS_WH credit consumption by 60–80% compared to importing all mart rows.

5.5 mart_reconciliation

Grain: business × corridor × month. This is the finance/ops sign-off surface — did the platform collect what it committed to collect, and disburse what it committed to disburse? It joins int_payment_reconciliation (collection side) to int_payout_reconciliation (settlement side) and rolls both up into completeness percentages plus a single total_reconciliation_breaks count that should trend to zero.

Metric Group	Key Fields
Collection	total_expected_collection_zar, total_actual_collection_zar, collection_completeness_pct, missing_payment_count, duplicate_payment_count
Settlement	total_committed_amount_zar, total_disbursed_amount_zar, settlement_completeness_pct, missing_settlement_count, duplicate_settlement_count
Roll-up	total_reconciliation_breaks — sum of every break type across both sides, for a one-glance health check

Example output from a validation run (synthetic sample, deliberately seeded with a few broken transfers to prove the model catches them):

Business	Completed Transfers	Collection Completeness	Settlement Completeness
MKR	178	102.0% (over-collecting — duplicate charges)	99.3%
MMY	92	96.5% (under-collecting — missing payments)	96.1%

Why split by corridor group instead of one blended number: MKR and MMY have opposite problems here — one is over-collecting, one is under-collecting. Blended together the errors would partly cancel out and the dashboard would look clean. The mart intentionally keeps them apart.

5.6 mart_corridor_live — a Dynamic Table

Every other mart in this project is batch, and that is correct for finance reporting: a stable daily snapshot is a feature, because numbers that shift under a reviewer mid-signoff are worse than numbers a few hours old.

Operations has the opposite requirement. When a settlement partner fails on the ZA-ZW corridor, the wallboard needs to show it within minutes. Three options were considered:

Option	Verdict
Scheduled dbt run every 5 minutes	Wasteful. The warehouse resumes, scans and rebuilds whether or not anything changed, and job invocations are billed individually.
Snowflake Stream + Task	Works, but it is imperative plumbing living outside dbt. The DAG would no longer describe real lineage, and the task needs separate deployment and monitoring.
Dynamic Table	Chosen. Declarative — state a `target_lag` and Snowflake derives the refresh schedule and maintains it incrementally. It stays a dbt node, so it appears in the DAG, the docs and the lineage graph.

The model computes rolling 24-hour corridor health — success rate, failure rate, completion latency, and a corridor_health_status of HEALTHY / DEGRADED / SLOW / CRITICAL / STALLED / NO_TRAFFIC that the wallboard sorts on directly.

Two deliberate configuration choices. target_lag is 5 minutes, not 1: below roughly a minute Snowflake tends to abandon incremental maintenance on a join this wide and fall back to full refreshes, costing materially more for latency nobody on the ops floor can act on. And refresh_mode is pinned to INCREMENTAL rather than left at AUTO, so a future query change that silently breaks incrementalisation fails at deploy time instead of appearing as a surprise on the credit bill.

The window is bounded on purpose: the model filters to the last 24 hours. An unbounded dynamic table over 5M rows cannot refresh incrementally on a 5-minute lag — the bound is what makes the latency target achievable at all.

Chapter 06

Snowpark ML Pipeline

Training, registering, and deploying 3 ML models — entirely inside Snowflake, no data egress

6.1 Why Snowpark ML?

Traditional ML pipelines extract data from the warehouse, ship it to a Python server, train the model, and push predictions back. This creates data egress costs, PII exposure risk, and pipeline complexity.

Snowpark ML runs Python code inside Snowflake's compute. The data never leaves. The feature tables, training jobs, model registry, and prediction tables are all Snowflake objects — version controlled, governed, and accessible to the same RBAC roles as your SQL queries.

0 bytes

Data egress to external ML platform

Models in Snowflake Model Registry

42ms

Fraud score API latency p99

6.2 Feature Store

Before training any model, features are materialised into AFRIMONEY_ML_DB.FEATURE_STORE tables. This serves two purposes:

Reproducibility — the exact feature set used to train a model version is saved as a snapshot in Snowflake time travel
Reuse — multiple models share the same feature tables, avoiding duplicated computation

-- Snowpark Python: feature engineering pushed down to Snowflake features = df.select( F.col("TRANSFER_ID"), F.log(F.greatest(F.col("SEND_AMOUNT_ZAR"), F.lit(1))).alias("SEND_AMOUNT_LOG"), F.col("FX_SPREAD_PCT"), F.hour(F.col("CREATED_DATETIME")).alias("HOUR_OF_DAY"), F.hash(F.col("CORRIDOR_CODE")).alias("CORRIDOR_HASH"), # encode categoricals F.col("IS_SUSPECTED_FRAUD").cast(IntegerType()).alias("LABEL_FRAUD"), ) # This executes as a single SQL query in Snowflake — no Python loop features.write.mode("overwrite").save_as_table("FEATURE_STORE.FRAUD_FEATURES")

6.3 Model Results

Snowflake ML — Model Registry — AfriMoney Models

FRAUD_DETECTION

v1_20260628 | GradientBoostingClassifier | Snowpark ML

PRODUCTION

AUC: 0.9124 Avg Precision: 0.7831 Features: 15 Latency: 42ms p99

CUSTOMER_CHURN

v1_20260628 | RandomForestClassifier | Weekly batch

PRODUCTION

AUC: 0.8612 Churn Rate: 44.8% Features: 21 Drift: monitoring

CREDIT_RISK_PD

v1_20260628 | GradientBoostingClassifier | Mukuru only

PRODUCTION

AUC: 0.7841 Default Rate: 11.2% SHAP: enabled NCA compliant

6.4 The Three Models Explained

Model 1 — Fraud Detection (GradientBoostingClassifier)

Business question: Is this transfer likely to be fraudulent at the moment of initiation?

How it works: A GBM model trained on 5M transfer records learns that certain combinations of send amount, time of day, corridor, and payment method are statistically associated with fraud. The model outputs a probability score (0–1). Transfers above 0.40 trigger a compliance hold before processing.

Top features: send_amount_log, corridor_hash, payment_attempts, hour_of_day, fx_spread_pct

AUC-ROC: 0.91 Threshold: 0.40 Real-time inference Deployed as UDF

Model 2 — Customer Churn (RandomForestClassifier)

Business question: Which customers are at risk of not sending another transfer in the next 90 days?

How it works: An RF model trained on the mart_customer_360 feature set. Recency (days since last transfer) and engagement score are the strongest predictors. Churned customers are scored weekly and segmented into 5 risk bands for CRM campaigns.

AUC-ROC: 0.86 Churn Rate: ~45% Weekly batch 5 risk segments

Model 3 — Credit Risk PD (GradientBoostingClassifier) — Mukuru only

Business question: What is the probability this loan applicant will default?

How it works: Trained on 200K Mukuru loan applications with repayment history. The model calculates a PD (Probability of Default) used for origination decisions and IFRS 9 Expected Credit Loss (ECL) calculations. SHAP explainability is enabled — every declined application gets a human-readable reason for NCA compliance.

AUC-ROC: 0.78 Default Rate: ~11% SHAP explanations NCA compliant

6.5 Fraud Score UDF — Deployed in Snowflake

-- After Snowpark training, the model is deployed as a Snowflake UDF -- Any SQL query or Power BI report can call it: SELECT transfer_id, send_amount_zar, AFRIMONEY_DB.UTILS.GET_FRAUD_SCORE( send_amount_zar, fx_spread_pct, payment_attempts, HOUR(created_datetime), HASH(channel) ) AS fraud_score FROM AFRIMONEY_DB.BRONZE.FACT_REMITTANCE_TRANSFER WHERE created_datetime >= DATEADD('hour', -1, CURRENT_TIMESTAMP()) ORDER BY fraud_score DESC;

Chapter 07

KPI Framework

The complete metric library — from executive scorecards to risk basis points

7.1 Executive KPIs

KPI	Snowflake SQL Formula	Owner
Total Transfer Volume	SUM(send_amount_zar) WHERE is_completed	CEO / CFO
Net Revenue	SUM(net_revenue_zar) WHERE is_completed	CFO
Monthly Active Senders	COUNT(DISTINCT sender_customer_id) in month	CEO / CMO
Transfer Success Rate	SUM(is_completed) / COUNT() 100	COO
Revenue per Active Customer	SUM(net_revenue) / COUNT(DISTINCT customer_id)	CFO / CMO
Repeat Sender Rate	Customers with completed_transfers ≥ 2 / all active	CMO
Digital Adoption Rate	Digital channel transfers / all transfers * 100	Product

7.2 Risk KPIs — Computed in mart_risk_compliance

KPI	Formula	Target	Alert
Fraud Rate	fraud_flagged / total_transfers * 10,000	< 5 bps	> 10 bps
KYC Completion Rate	LEVEL_2+ customers / all registered	> 90%	< 80%
Transfer Success Rate	completed / initiated * 100	> 80%	< 70%
Cancellation Rate	cancelled / initiated * 100	< 8%	> 15%

Vanity metrics to avoid: "17 million customers served" means nothing without paired context. Always report: monthly active customers, transacting rate (% who sent in last 30 days), and retention rate. A large registered base with 5% monthly active rate is a crisis, not a success story.

7.3 Reconciliation KPIs — Computed in mart_reconciliation

KPI	Formula	Target	Alert
Collection Completeness	total_actual_collection_zar / total_expected_collection_zar * 100	99.5–100.5%	< 98% or > 102%
Settlement Completeness	total_disbursed_amount_zar / total_committed_amount_zar * 100	99.5–100.5%	< 98% or > 102%
Reconciliation Breaks	missing + duplicate + mismatch counts, collection + settlement	0	> 0 for 2 consecutive days

Owner: Finance / Treasury Ops. Unlike the KPIs above, these are checked outside the "higher is better" pattern — both under- and over-100% completeness are breaks, since over-collection means a customer or partner is owed money back.

Chapter 08

Data Governance, PII & Compliance

POPIA, tokenisation strategy, dbt data lineage, and Snowflake access controls

8.1 PII Classification in Snowflake

Field	Bronze	Silver / Gold	Power BI
Full name	Plaintext	SHA-256 hash	Never visible
ID / Passport	AES-256 encrypted	Tokenised reference	Never visible
Mobile number	Plaintext	Reversible vault token	Last 4 digits only
Bank account	AES-256 encrypted	Masked (****1234)	Never visible
Transaction amounts	Plaintext	Plaintext	Visible (required)
Transfer reference	Plaintext	Plaintext	Visible

POPIA obligation: South Africa's Protection of Personal Information Act requires that any cross-border transfer of personal data (e.g., to a cloud region outside SA) has a documented lawful basis or an adequacy assessment. Snowflake's South Africa region (hosted on AWS Cape Town) keeps data in-country by default — verify your Snowflake account region before go-live.

8.2 Snowflake Dynamic Data Masking

-- Masking policy: ANALYST role sees masked mobile numbers CREATE OR REPLACE MASKING POLICY mobile_mask AS (val VARCHAR) RETURNS VARCHAR -> CASE WHEN CURRENT_ROLE() IN ('AFRIMONEY_ENG', 'AFRIMONEY_ADMIN') THEN val ELSE REGEXP_REPLACE(val, '.(?=.4)', '*') END; ALTER TABLE BRONZE.DIM_CUSTOMER MODIFY COLUMN MOBILE_NUMBER_TOKEN SET MASKING POLICY mobile_mask;

Chapter 09

Implementation Roadmap

A three-phase plan from Snowflake setup to production ML in 12 months

9.1 Phase 1 — Foundation (Months 1–3)

Month	Task	Owner	Tool
1	Snowflake account setup: warehouses, RBAC, stage	Platform Engineer	Snowflake SQL
1	Bronze DDL: all 20 table definitions deployed	Data Engineer	Snowflake SQL
1	PII masking policies applied to Bronze	Security Eng	Snowflake SQL
2	Initial COPY INTO loads: all tables verified	Data Engineer	SnowSQL / ADF
2	dbt project: staging models passing 28 tests	Analytics Eng	dbt + Snowflake
3	Gold marts live: mart_remittance + mart_customer_360	Analytics Eng	dbt + Snowflake
3	Power BI connected: executive dashboard live	BI Developer	Power BI + Snowflake

9.2 Phase 2 — Analytics (Months 4–6)

Month	Task	Owner	Tool
4	mart_fx_profitability + mart_wallet_card deployed	Analytics Eng	dbt
5	mart_loans_mukuru + mart_insurance_mukuru live	Analytics Eng	dbt
5	mart_risk_compliance: fraud dashboard live	Risk / Analytics	dbt + Power BI
6	Snowflake Streamlit app: internal data explorer	Data Engineer	Streamlit in Snowflake

9.3 Phase 3 — ML & Real-Time (Months 7–12)

Month	Task	Owner	Tool
7	Snowpark Feature Store: fraud + churn features	Data Scientist	Snowpark Python
8	Fraud model v1: trained, registered, UDF deployed	Data Scientist	Snowpark ML
9	Fraud UDF integrated into transfer processing API	Backend Eng	Snowflake REST API
10	Churn model: weekly batch scores in Snowflake, CRM sync	Data Scientist	Snowpark ML + CRM
11	Credit PD model: live at loan application; SHAP deployed	Data Scientist / Risk	Snowpark ML
12	Daily ML refresh stored procedure scheduled via Snowflake Task	MLOps	Snowflake Tasks

Final state after 12 months: The AfriMoney Intelligence Platform will have 40M+ rows of live operational data in Snowflake Bronze, 14 dbt models transforming it daily, 3 Snowpark ML models scoring in real-time and batch, and a Power BI dashboard that gives the executive team a live view of both brands in a single pane of glass — with zero data leaving Snowflake.

Table of Contents

1.1 The Business Problem

1.2 Mukuru — Africa's Largest Money Transfer Operator

1.3 Mama Money — Wallet-First Fintech Challenger

1.4 Why One Platform?

2.1 Why Snowflake?

2.2 Virtual Warehouse Strategy

2.3 Database & Schema Layout

2.4 RBAC — Role-Based Access Control

3.1 Source Systems Inventory

3.2 The ETL Pipeline — Step by Step

Generate synthetic data (Python)

Upload CSVs to Snowflake internal stage

COPY INTO Bronze tables

Verify via COPY_HISTORY & INFORMATION_SCHEMA

Cluster keys applied automatically

3.3 Snowflake Worksheet — Bronze Query

3.4 COPY INTO Performance

4.1 Why dbt?

4.2 Project Structure

4.3 Materialisation Strategy

4.4 dbt DAG — Full Pipeline

4.5 dbt Run Output

4.6 Key dbt Tests

4.7 Reconciliation Controls

4.8 Incremental Materialisation

4.9 Macros and Custom Generic Tests

4.10 Model Contracts and Versioning

4.11 Unit Tests

4.12 The Semantic Layer

4.13 CI/CD and Environments

5.1 mart_remittance

5.2 mart_customer_360

5.3 Snowflake Architecture Diagram

5.4 Power BI Connection

5.5 mart_reconciliation

5.6 mart_corridor_live — a Dynamic Table

6.1 Why Snowpark ML?

6.2 Feature Store

6.3 Model Results

6.4 The Three Models Explained

Model 1 — Fraud Detection (GradientBoostingClassifier)

Model 2 — Customer Churn (RandomForestClassifier)

Model 3 — Credit Risk PD (GradientBoostingClassifier) — Mukuru only

6.5 Fraud Score UDF — Deployed in Snowflake

6.6 Similar Corridors — Content-Based Risk Triage

Look up a corridor

Similar corridors

7.1 Executive KPIs

7.2 Risk KPIs — Computed in mart_risk_compliance

7.3 Reconciliation KPIs — Computed in mart_reconciliation

8.1 PII Classification in Snowflake

8.2 Snowflake Dynamic Data Masking

9.1 Phase 1 — Foundation (Months 1–3)

9.2 Phase 2 — Analytics (Months 4–6)

9.3 Phase 3 — ML & Real-Time (Months 7–12)