Ghosts in the Ledger

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
L4

Problem

We run a B2B SaaS platform that bills customers monthly. Each invoice pairs a header carrying the billing period and running status with a variable set of charges for subscription tiers, usage overages, and one-time fees, where every charge locks in the price that was in effect when the bill was issued. Finance needs to see what each customer still owes as invoices age past their due date.

Invoices go out, partial payments trickle in, and some customers are three months overdue.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate understands header versus line grain and the independence of price snapshots from the product catalog. Invoicing is the canonical one-to-many header-detail pattern, and getting the unit price semantics wrong produces audit failures that interviewers see every quarter.

Break down the requirements

Step 1: Declare the two grains

invoices has one row per invoice number. invoice_line_items has one row per charge on that invoice. The relationship is strictly 1:M and every line belongs to exactly one header.

Step 2: Snapshot the price on the line

Unit price must live on invoice_line_items at write time. Looking it up from products later breaks the moment pricing changes, and every historical invoice would restate itself, which is a direct audit failure.

Step 3: Dimension customers

Customer name, billing address, and payment terms live once on customers. Embedding them on each invoice is a Type 1 SCD in disguise and erases the ability to rerun an old invoice.

Step 4: Reconcile at the header

AR aging rolls up at the invoice level (issued, due, status, outstanding). Line items feed gross margin analysis. Both rollups share one set of conformed dimensions.

The solution

Below is one conceptually sound approach. The grain anchors the design: one row per invoice on the header, one row per charge on the detail.

`unit_price` is snapshotted on `invoice_line_items` at issue time; `products.list_price` is advisory only.
  • customers: customer_id INT PK, legal_name TEXT, billing_email TEXT, payment_terms_days INT, created_at TIMESTAMP
  • products: product_id INT PK, sku TEXT, product_name TEXT, list_price DECIMAL, is_subscription BOOLEAN
  • invoices: invoice_id BIGINT PK, customer_id INT FK, issue_date DATE, due_date DATE, status TEXT, total_amount DECIMAL
  • invoice_line_items: line_id BIGINT PK, invoice_id BIGINT FK, product_id INT FK, quantity DECIMAL, unit_price DECIMAL, line_total DECIMAL
  • customers references invoices
  • invoices references invoice_line_items
  • products references invoice_line_items

The analysis pattern

AR aging by customer
SELECT
    c.legal_name,
    SUM(CASE WHEN i.due_date >= CURRENT_DATE THEN i.total_amount ELSE 0 END) AS current_ar,
    SUM(CASE WHEN i.due_date < CURRENT_DATE AND CURRENT_DATE - i.due_date <= 30 THEN i.total_amount ELSE 0 END) AS past_due_30,
    SUM(CASE WHEN CURRENT_DATE - i.due_date > 30 THEN i.total_amount ELSE 0 END) AS past_due_60_plus
FROM invoices i
JOIN customers c ON c.customer_id = i.customer_id
WHERE i.status = 'outstanding'
GROUP BY c.legal_name
ORDER BY past_due_60_plus DESC

Trade-offs and alternatives

Header-detail snapshot
Two tables, immutable lines, fast reporting. Cost: reprice changes cannot be retroactively applied, which is the point for AR but awkward for promotions.
Event-sourced invoices
Append every state transition (created, revised, paid) as events; project invoices as a read model. Cost: more infrastructure, but every change is auditable and replayable.

Common follow-up questions