Capstone · Finance reconciling billing and the data warehouse

Reconcile subscription revenue across systems

The warehouse revenue total is higher than the billing system's. Order lines were re-sent with new versions, account updates arrived from several systems, some payments reference unknown accounts, and amounts in different currencies were added together.

Rebuild the revenue table at the right grain, attach each payment to one current account record, report orphans, and give totals per currency that reconcile to the input.

Checkpoints

1. Latest version per order line; superseded versions listed.
Deduplicate order lines at the right grain
2. One current record per account by a deterministic rule.
Pick one surviving record per account
3. Payments matched to accounts; missing and unknown accounts reported.
Referential integrity with explicit exceptions
4. Totals and counts per currency, never converted or mixed.
Sum amounts by currency, without converting

Final quality report

Write it in your private notes below (saved to your account) or in your own document. It isn't auto-graded; use it to practise the handoff interviewers ask about.

The difference between the old and new totals, explained line by line.
Row counts at each step (input = clean + exceptions).
Orphan payments by reason and who owns the fix.
Why totals stay per currency, and what would be needed to convert them.