Skip to lesson
Exit
Revenue Recognition & R2R1 / 2

1 min lesson

Three-way reconciliation with drift detection

Explain what the example in "Three-way reconciliation with drift detection" is doing and why it matters.

Step 1 of 2

Three-way reconciliation with drift detectionusage → invoiced → recognized

The query finance trusts is the one that finds the breaks itself. Join the three views by contract and period, then return only the rows where the amounts don't agree within tolerance.

Returns only contracts where rated usage, invoiced and recognized drift apart
select
  u.contract_id,
  u.period_month,
  u.rated_usage,
  i.invoiced,
  r.recognized,
  i.invoiced  - u.rated_usage  as bill_drift,
  r.recognized - i.invoiced    as recog_drift
from usage_rated   u
left join invoiced_amt i
  on i.contract_id = u.contract_id and i.period_month = u.period_month
left join recognized_amt r
  on r.contract_id = u.contract_id and r.period_month = u.period_month
where abs(coalesce(i.invoiced, 0)   - u.rated_usage)  > 0.01
   or abs(coalesce(r.recognized, 0) - coalesce(i.invoiced, 0)) > 0.01
order by abs(i.invoiced - u.rated_usage) desc;
A good reconciliation returns nothing

When the books tie out, this query is empty. That property makes it a control you can run on a schedule and alert on: any row is an exception that needs a human. Frame your queries this way in the interview - clean state is zero rows - and you signal that you think in controls, not just reports.