2 min lesson
Drill C - the schema
Rebuild the parts of "Drill C - the schema", then say why each one matters.
Step 1 of 3
Drill C - the schemaContracts, entitlements, usage, invoices, cash
Core tables and what each owns
- contracts
- contract_id, account_id, start/end, terms; the commercial agreement
- performance_obligations
- po_id, contract_id, type, ssp, allocated_amount; the ASC 606The US revenue-recognition standard; cited as the canonical judgment-heavy accounting work to keep human-led rather than hand to an agent, because facts and circumstances vary deal to deal. Press Enter for the full definition. units
- entitlements
- entitlement_id, contract_id, metric, included_qty, overage_rate; what's allowed
- usage_events
- event_id (idempotency key), customer_id, metric, qty, event_time, ingested_at
- usage_events_rated
- event_id, contract_id, billing_period, rated_amount; priced usage
- invoices / invoice_lines
- invoice_id, contract_id, status; lines carry charge_type, line_amount, billing_period
- rev_subledger_events
- contract_id, po_id, type (billing|recognition), amount, event_date, billing_period
- cash_applications
- payment_id, invoice_id, amount_applied, applied_at; closes AR
Note the immutable, append-only usage_events with a unique event_id - that is the idempotency anchor.
Learn more
Full explanation
Drill D - idempotency and late events on paper
Drill D - idempotency and late events on paperMetering pipeline hygiene
- 1Idempotent ingestion. Give every usage event a producer-generated stable
event_id; INSERT withON CONFLICT (event_id) DO NOTHINGso a retried delivery cannot double-count. - 2Event-time vs. ingest-time. Store both
event_timeandingested_at. Rating and recognition key off event_time so you assign usage to the period it actually happened, not the period it arrived. - 3Late-arriving events. A late event reopens the affected billing period: re-rate that period, post a delta to the subledger and emit a true-up line on the next invoice rather than mutating a closed, audited invoice.
- 4Watermark and cutoff. Define a close cutoff (for example T+2 days) after which a period is locked; events past the watermark route to a true-up, with the variance tracked as a control metric.
Learn more
Full explanation
Drill E - three data-quality controls and their queries
Drill E - three data-quality controls and their queriesEach control needs an enforcing query
- Control
- No duplicate usage
- What it catches
- Double-counted revenue from retries
- Enforcing check
- COUNT(*) vs COUNT(DISTINCT event_id) must be equal
- Control
- No orphan usage
- What it catches
- Metered usage with no contract/entitlement
- Enforcing check
- LEFT JOIN usage to contracts WHERE contract_id IS NULL returns zero rows
- Control
- Three-way tie-out
- What it catches
- Usage ≠ invoiced ≠ recognized
- Enforcing check
- Drill B returns zero rows above tolerance
| Control | What it catches | Enforcing check |
|---|---|---|
| No duplicate usage | Double-counted revenue from retries | COUNT(*) vs COUNT(DISTINCT event_id) must be equal |
| No orphan usage | Metered usage with no contract/entitlement | LEFT JOIN usage to contracts WHERE contract_id IS NULL returns zero rows |
| Three-way tie-out | Usage ≠ invoiced ≠ recognized | Drill B returns zero rows above tolerance |
A control you cannot express as a query that fails the build is just a hope.
Interview move
When you reach for AI mid-drill, narrate it: “I'll have Cursor scaffold the window-function syntax, then I'm checking the frame clause myself because off-by-one on ROWS BETWEEN is exactly the bug that misstates a roll-forward.” Accepting a scaffold and then auditing the risky line is the AI-authenticity behavior the screen rewards over both refusing AI and pasting it blind.