I built a dimensional model for a digital wallet platform, following money from payment initiation through fees, transfers, disputes, settlement, and daily balances. The project was small by design: the point was to reason carefully about fact-table grain, referential integrity, incremental loads, and the analytical errors that appear when those decisions are wrong.
The Modeling Decisions
I separated payments, fees, disputes, transfers, settlements, and wallet balances because they do not share the same grain. A fee can occur several times per payment, a settlement represents what actually moved to a merchant on a later schedule, and a wallet balance is a periodic snapshot. Combining them into one wide transaction table would either duplicate monetary values or leave most columns null.
I kept fact_settlement separate from payments because settlement is something the platform should reconcile against payment activity, not assume from it. I modeled fact_wallet_balance_daily at (wallet_id, date_id) so historical balance questions do not require replaying every transfer.
The payment reference stays on the fact as the business key used by the incremental load. It identifies a transaction but has no descriptive attributes that justify a separate dimension.
Model at a Glance
Methodology
&
Insights
Incremental ETL
I loaded payments and fees through staging tables rather than truncating and rebuilding the facts. Each staged row carries last_updated; the procedure reads the previous successful timestamp from etl_watermark, MERGEs only newer rows, and advances the watermark after the load.
That choice matters because downstream tables reference generated payment IDs. Recreating fact_payment from scratch could change those keys and orphan the fee and dispute records that point back to them. Payments therefore use a soft-delete state, while the business reference remains stable across loads. The project prompt describes this staging, watermark, and soft-delete pattern explicitly.
What Broke
The merchant-revenue query exposed the most useful failure in the project. I joined fact_payment directly to fact_fees and then summed payment amounts. Because one payment can have multiple fee rows, the join changed the grain before aggregation and counted the same payment more than once.
For QuickEats, the query reported $182.00 in approved revenue when the underlying approved payments totaled $91.00. TechMart happened to have one fee row per payment, so its result stayed correct. Both outputs looked plausible, which is exactly why this class of fan-out bug survives casual review.
The fix is to aggregate fees to one row per payment before joining them back to payments:
I also found two weaker points in the load process. The validation query uses CHECKSUM(), which can collide and therefore cannot guarantee row equality, and the fact MERGE and watermark update are not wrapped in the same transaction. With DATETIME2(0) and a strict > filter, rows sharing the recorded watermark second can also be skipped.
What the Model Can’t Do Yet
Customer KYC status and merchant risk tier are overwritten in place, so the model cannot answer what those attributes were when an older payment occurred. I would add Type 2 history with effective dates before treating the schema as suitable for compliance-oriented analysis.
Several integrity rules also remain outside the database. Status fields have no CHECK constraints, the schema cannot guarantee that the wallet on a payment belongs to the payment's customer, and currency IDs exist without an exchange-rate fact, so cross-currency totals are not meaningful. The current model also soft-deletes payments while deleted fees are physically removed, which creates inconsistent history semantics.
Most importantly, fact_settlement exists but is not yet reconciled against approved payments and fees. The next version should calculate the expected merchant net by date and currency and compare it with the settlement fact, because that disagreement is exactly what the table should make visible.
