FINTECH ANALYTICS ENGINEERING CASE STUDY
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
01 / PROBLEM
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
Metrics calculated from the deterministic seed-42 dataset.
| 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% |
03 / ARCHITECTURE
Customers, wallets and transactions declared as dbt sources.
Explicit fields, types, categories and source-level checks.
Incremental transaction fact and reusable customer model.
Daily KPIs, monthly engagement and cohort retention.
04 / KEY DECISIONS
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: mergetransaction_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_atDAU 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 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.
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.
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
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
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.
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.