FINTECH ANALYTICS ENGINEERING CASE STUDY
WalletFlow Analytics
A reporting layer for a fictional digital-wallet business, built to turn customer, wallet and transaction events into consistent product and performance metrics.
Tools used
- SQL
- dbt
- BigQuery
- Python
- GitHub Actions
01 / PROBLEM
Operational transaction data is not automatically reporting-ready.
The project starts with synthetic customer, wallet and transaction records. The modelling challenge is to create a stable analytics layer where transaction performance, customer activity and retention use consistent definitions.
Instead of calculating metrics independently in dashboards, the reporting logic is moved into dbt models so the same definitions can be reused across analysis and BI tools.
02 / REPORTING OUTPUT
Transaction and engagement snapshot
Metrics calculated from the deterministic seed-42 dataset.
Monthly engagement
| Month | MAU | Avg DAU | DAU/MAU | Retention |
|---|---|---|---|---|
| Jan | 242 | 25.68 | 10.61% | N/A |
| Feb | 240 | 26.82 | 11.18% | 95.87% |
| Mar | 239 | 26.42 | 11.05% | 96.25% |
| Apr | 244 | 25.57 | 10.48% | 97.91% |
| May | 243 | 27.32 | 11.24% | 97.13% |
| Jun | 238 | 25.80 | 10.84% | 95.06% |
Metric definitions
- DAU
- daily distinct customers
- MAU
- monthly distinct customers
- DAU/MAU
- avg DAU / MAU
- Monthly retention
- returning / prior MAU
- Cohort retention
- retained / cohort size
- Success rate
- successful / attempts
03 / ARCHITECTURE
Layered models keep source cleanup, reusable logic and reporting separate.
Raw events
Customers, wallets and transactions declared as dbt sources.
Standardise
Explicit fields, types, categories and source-level checks.
Facts and dimensions
Incremental transaction fact and reusable customer model.
Reporting
Daily KPIs, monthly engagement and cohort retention.
04 / KEY DECISIONS
The project is built around choices that affect correctness, maintainability and cost.
Reprocess recent history
The transaction fact uses BigQuery MERGE processing and re-reads a configurable three-day window. This gives delayed transactions and recent corrections another chance to enter the fact table without rebuilding all history.
vars:
incremental_lookback_days: 3
unique_key: transaction_id
strategy: mergeOne row per transaction
transaction_id defines the fact-table grain and also acts as the MERGE key. Matching records can be updated instead of appended as duplicates.
transaction_id
wallet_id
customer_id
amount_ngn
status
created_atTransaction attempts count as activity
DAU and MAU are based on customers who attempt a transaction. A failed payment is still evidence that the customer used the product. Successful activity remains useful for other measures, including the first-success cohort definition.
Monthly and cohort retention answer different questions
Monthly retention compares current activity with the immediately previous month. Cohort retention groups customers by first successful transaction and tracks how those groups behave in later months.
Partition and cluster for common access patterns
The transaction fact is partitioned by transaction date and clustered by customer ID, transaction type and status. These fields match common reporting and filtering patterns.
Explicit columns instead of SELECT *
Transformation and test SQL names the fields it depends on. This makes upstream schema changes easier to review and prevents unnecessary fields from flowing through the project.
05 / DATA QUALITY
Tests protect both structure and business meaning.
Structural checks
- Primary keys are unique and non-null.
- Transactions reference valid customers and wallets.
- Statuses and transaction types use accepted values.
- Successful transactions require completion timestamps.
- Transaction amounts must be positive.
Business-rule checks
- Status flags must be mutually exclusive.
- Daily status counts must reconcile to transaction count.
- Average DAU cannot exceed MAU.
- Retained customers cannot exceed the comparison population.
- Month-zero cohort retention must equal 100%.
- Rate metrics must remain between 0 and 1.
Freshness and late-arrival logic would use true ingestion metadata.
The synthetic source does not contain a warehouse ingestion timestamp, so created_at is used as a freshness proxy. In a production implementation I would prefer a field such as loaded_at, _ingested_at or a reliable update timestamp. I would also choose the lookback window from observed source-arrival behaviour rather than treating three days as a universal rule.
06 / DELIVERY
Automated checks keep the repository reviewable.
GitHub Actions
Pull requests run repository checks, including protection against SELECT * in model and test SQL. When BigQuery credentials are configured, the workflow can also run dbt seed, dbt build and dbt source freshness.
Reporting layer
The final marts are designed for BI consumption so product metrics are defined upstream rather than recreated separately in Looker, Looker Studio or Power BI.