Online Marketplace - Seller Payouts

A hard 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
hard
Seniority
L6

Problem

We run an online marketplace where third-party sellers list products and buyers purchase them. The platform collects the full payment from the buyer, deducts a commission, and pays the seller. Refunds are common and complicate the accounting. Finance needs a model that reconciles every dollar from collection to payout. Design the data model.

The buyer paid one number. The seller got a different one.

Worked solution and explanation

What this problem really is

This is a double-entry ledger hiding inside a payouts prompt. The buyer paid one number and the seller got a different one because commission, tax, and refunds all move money between those two points. The real skill being probed: can you let two grains coexist without conflating them, an order-line fact for what was sold and an append-only ledger for what cash moved? A single wide fact_orders with a refund_amount column looks fine until the first refund, when you either overwrite history or the numbers stop reconciling. Get the grain wrong and you can never reproduce last quarter's payout on demand.

Build it up

Step 1: Declare the grain twice

fact_orders is one row per product per seller per order line, keyed by order_line_id. fact_ledger_entries is one row per cash movement (charge, commission, tax, refund) keyed by ledger_entry_id and linked back with order_line_id. Two facts, two grains, one join key.

Step 2: Model refunds as new negative rows

A refund is not a mutation of the original charge. It is a fresh ledger row with a negative amount and entry_type = 'REFUND_BUYER'. The fact_orders row it refers to stays untouched, so any historical GMV snapshot stays reproducible.

Step 3: Keep tax out of revenue

Tax collected is a pass-through liability, not platform revenue. Give it its own entry_type = 'TAX' (or a dedicated column) so subtotal and commission math stay clean and remittance audits have a single place to look.

Step 4: Bridge batch payouts to their orders

One payout covers many order lines. fact_payouts lives at the batch grain; payout_order_lines is the junction whose FKs payout_id and order_line_id answer "which orders funded this payout?" without scanning the whole ledger.

Here is one defensible model. The dual-fact split is the anchor: order economics and cash movements sit in separate tables with different grains, joined through the immutable ledger, and every fact hangs off the same conformed dimensions.

`fact_ledger_entries` is append-only; a refund is a new negative row, never an UPDATE to `fact_orders`. `payout_order_lines` carries both FKs up to their parent PKs.
  • dim_buyers: buyer_id BIGINT PK, email TEXT, country TEXT, created_at TIMESTAMP
  • dim_sellers: seller_id BIGINT PK, legal_name TEXT, commission_tier TEXT, onboarded_at TIMESTAMP
  • dim_products: product_id BIGINT PK, seller_id BIGINT FK, sku TEXT, category TEXT
  • dim_date: date_id INT PK, calendar_date DATE, fiscal_week INT
  • fact_orders: order_line_id BIGINT PK, order_id BIGINT, buyer_id BIGINT FK, seller_id BIGINT FK, product_id BIGINT FK, date_id INT FK, subtotal DECIMAL
  • fact_ledger_entries: ledger_entry_id BIGINT PK, order_line_id BIGINT FK, entry_type TEXT, party_id BIGINT, amount DECIMAL, posted_at TIMESTAMP
  • fact_payouts: payout_id BIGINT PK, seller_id BIGINT FK, payout_date DATE, gross_amount DECIMAL, net_amount DECIMAL
  • payout_order_lines: payout_id BIGINT FK, order_line_id BIGINT FK, proceeds_amount DECIMAL
  • dim_products.seller_id references dim_sellers.seller_id
  • fact_orders.buyer_id references dim_buyers.buyer_id
  • fact_orders.seller_id references dim_sellers.seller_id
  • fact_orders.product_id references dim_products.product_id
  • fact_orders.date_id references dim_date.date_id
  • fact_ledger_entries.order_line_id references fact_orders.order_line_id
  • fact_payouts.seller_id references dim_sellers.seller_id
  • payout_order_lines.payout_id references fact_payouts.payout_id
  • payout_order_lines.order_line_id references fact_orders.order_line_id

Reconciling a payout batch

Seller reconciliation by payout batch
SELECT
    p.payout_id,
    s.legal_name,
    p.net_amount,
    SUM(l.amount) FILTER (WHERE l.entry_type = 'CHARGE') AS gross_charges,
    SUM(l.amount) FILTER (WHERE l.entry_type = 'COMMISSION') AS commission,
    SUM(l.amount) FILTER (WHERE l.entry_type IN ('REFUND_BUYER','REFUND_SELLER_DEBIT')) AS refunds
FROM fact_payouts p
JOIN dim_sellers s ON s.seller_id = p.seller_id
JOIN payout_order_lines pol ON pol.payout_id = p.payout_id
JOIN fact_ledger_entries l ON l.order_line_id = pol.order_line_id
WHERE p.payout_date = '2025-03-15'
GROUP BY p.payout_id, s.legal_name, p.net_amount

Fan out from fact_payouts through payout_order_lines into the ledger, then pivot cash movements with FILTER.

Dual fact (orders + ledger)
Clean grains, append-only cash history, reproducible finance reports. Cost: writes land in two tables and cross-fact reconciliation joins through payout_order_lines.
Single wide `fact_orders` with refund columns
Faster reads for a plain GMV query, fewer tables. Cost: refunds and batch payouts force destructive UPDATEs, historical snapshots drift, and one payout covering many order lines cannot be expressed at all.

Common follow-up questions