The Float

A medium 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
medium
Seniority
mid
Asked in
Capital One

Problem

A card issuer needs a warehouse that reconciles what a cardholder can spend right now against what has actually posted to their account. Every swipe creates a temporary hold that later settles, often for a different amount and sometimes never, and a cardholder can dispute a charge after it posts. Model the schema that supports available-credit checks, statement generation, and dispute tracking across accounts that each belong to a customer.

Worked solution and explanation

Why this problem exists in real interviews

This is authorization-versus-settlement reconciliation wearing a warehouse-design costume. The real skill being probed: can you separate the hold event from the posting event when they share an amount only by coincidence? Anyone can draw a transactions table. The trap is treating one swipe as one row. Get that wrong and available credit is silently wrong, an expired hold inflates spend forever, and a restaurant tip that settles above the hold has nowhere to live.

Break down the requirements

Step 1: Anchor balances on the account, not the customer

A customer can hold several cards. Credit limit and available credit are properties of an account, so dim_accounts carries the limit and every fact hangs off account_key. Customer is a dimension one level up.

Step 2: Split the hold from the post

fact_authorizations is the hold: amount, authorized_at, expires_at, and a status. fact_settlements is what posts: it references the originating auth and carries its own posted_amount and posted_at. One auth can yield zero settlements (expired), one, or several (partial captures), so the settlement-to-auth edge is one-to-many.

Step 3: Make balances a snapshot, not a replay

Available credit on the hot path cannot rescan billions of events. fact_statement_snapshots stores one row per account per cycle with posted, pending, and available balances. Those are semi-additive: you can sum across accounts, never across cycles.

Step 4: Keep disputes reversible

A dispute targets one settled charge. fact_disputes references the settlement and carries a reason code, state, and resolution_amount. The original posted amount is never mutated, so the ledger stays auditable.

The reference model

Two event facts (authorizations, settlements) linked one-to-many, a periodic snapshot for statement balances, a dispute fact hanging off settlements, and conformed customer, account, and merchant dimensions. The snapshot is what keeps the authorization hot path fast; the event facts are what keep it correct.

`fact_authorizations` and `fact_settlements` are one-to-many; balances live as a periodic snapshot per account per cycle.
  • dim_customers: customer_key BIGINT PK, full_name TEXT, ssn_hash TEXT, joined_at TIMESTAMP
  • dim_accounts: account_key BIGINT PK, customer_key BIGINT FK, card_last4 TEXT, credit_limit DECIMAL, status TEXT, opened_at TIMESTAMP
  • dim_merchants: merchant_key BIGINT PK, merchant_name TEXT, category_code TEXT, country TEXT
  • fact_authorizations: auth_key BIGINT PK, account_key BIGINT FK, merchant_key BIGINT FK, auth_amount DECIMAL, authorized_at TIMESTAMP, expires_at TIMESTAMP, auth_status TEXT
  • fact_settlements: settlement_key BIGINT PK, auth_key BIGINT FK, account_key BIGINT FK, merchant_key BIGINT FK, posted_amount DECIMAL, posted_at TIMESTAMP, cycle_date_key INT FK
  • fact_disputes: dispute_key BIGINT PK, settlement_key BIGINT FK, account_key BIGINT FK, reason_code TEXT, status TEXT, resolution_amount DECIMAL, disputed_at TIMESTAMP
  • fact_statement_snapshots: snapshot_key BIGINT PK, account_key BIGINT FK, cycle_date_key INT FK, posted_balance DECIMAL, pending_balance DECIMAL, available_credit DECIMAL, minimum_due DECIMAL
  • dim_customers references dim_accounts
  • dim_accounts references fact_authorizations
  • dim_merchants references fact_authorizations
  • fact_authorizations references fact_settlements
  • dim_accounts references fact_settlements
  • fact_settlements references fact_disputes
  • dim_accounts references fact_statement_snapshots
Single transaction table
One row per charge with amount and status. * Simple to draw * Cannot represent hold != post amount * Partial captures and tips have no home * Expired holds silently inflate spend
Split auth and settlement
One fact for the hold, one for the post, linked one-to-many. * Hold and settled amounts each have a home * Zero, one, or many settlements per auth * Expired holds drop out of pending on their own * Available credit is limit minus posted minus open holds

The available-credit read

Available credit for an account right now
SELECT
    a.account_key,
    a.credit_limit,
    COALESCE(posted.posted_balance, 0) AS posted_balance,
    COALESCE(holds.pending_balance, 0) AS pending_balance,
    a.credit_limit
      - COALESCE(posted.posted_balance, 0)
      - COALESCE(holds.pending_balance, 0) AS available_credit
FROM dim_accounts a
LEFT JOIN (
    SELECT account_key, SUM(posted_amount) AS posted_balance
    FROM fact_settlements
    GROUP BY account_key
) posted ON posted.account_key = a.account_key
LEFT JOIN (
    SELECT account_key, SUM(auth_amount) AS pending_balance
    FROM fact_authorizations
    WHERE auth_status = 'open'
      AND expires_at > NOW()
    GROUP BY account_key
) holds ON holds.account_key = a.account_key
WHERE a.account_key = 550142

Posted balance from settlements, open holds from unexpired authorizations, both netted against the limit.

Common follow-up questions