Financial Trading Warehouse

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're building the data warehouse for a trading platform that handles stocks, ETFs, and crypto with fractional-share quantities. Every trade executes in the instrument's native currency and is converted to the account's home currency, and regulators can ask us to reproduce any past trade confirmation and any account's holdings exactly as they stood on a historical date, even after account and instrument details have since been updated. Design the dimensional model with a per-trade fact, a daily position snapshot, and dimensions that keep their full history.

Every trade, every tick, every fraction of a share. The regulators want receipts.

Worked solution and explanation

Why this problem exists in real interviews

This probes whether a candidate can combine multi-currency fact design with SCD Type 2 on regulated dimensions and an immutable, append-only trade log. Trading warehouses are the domain where every one of these choices is a regulatory requirement, and interviewers look for candidates who call them out explicitly.

Break down the requirements

Step 1: Precision is a production concern

In production every monetary column would use a fixed-scale decimal so rounding never compounds across reconciliation. That is a physical-type concern; the dimensional model itself just has to carry the right measures on the right grain. What the model must get right is that those measures are stored at write time, not recomputed later.

Step 2: Carry native and home currency

A euro trade at 1.08 USD/EUR stores native_amount, home_amount, and fx_rate. Storing only one of the three forces a lossy recomputation at query time when FX rates shift, which silently rewrites past P&L.

Step 3: Type 2 dimensions everywhere

Account attributes, instrument metadata, and FX rates all evolve over time. Point-in-time audits require that a trade from 2023 joins back to the 2023 state of each dimension, not the current state. Type 2 with effective dates is the only model that holds up.

Step 4: Append-only trade fact

A correction is never an in-place update. It is a new trade row that offsets the original. The fact table is immutable, and the net position is computed from the sum of all fact rows per account per instrument.

Step 5: Snapshot positions periodically

fact_positions is a daily periodic snapshot per account per instrument. It makes as-of position queries cheap and gives the SEC a stable artifact rather than a sum-over-history at audit time.

The solution

Below is one defensible design: SCD Type 2 on the account and instrument dimensions, append-only trade fact with dual-currency measures, and a daily position snapshot for audit stability.

The account and instrument dimensions are Type 2; `fact_trades` is append-only and carries both native and home currency amounts.
  • dim_accounts: account_key BIGINT PK, account_id TEXT, account_type TEXT, home_currency TEXT, effective_from DATE, effective_to DATE
  • dim_instruments: instrument_key BIGINT PK, symbol TEXT, asset_class TEXT, tick_size DECIMAL, effective_from DATE, effective_to DATE
  • dim_currencies: currency_key INT PK, iso_code TEXT, decimal_places INT, fx_rate_to_usd DECIMAL, effective_from DATE, effective_to DATE
  • dim_dates: date_key INT PK, full_date DATE, is_trading_day BOOLEAN, market_session TEXT
  • fact_trades: trade_id BIGINT PK, account_key BIGINT FK, instrument_key BIGINT FK, currency_key INT FK, trade_date_key INT FK, quantity DECIMAL, native_amount DECIMAL, home_amount DECIMAL, fx_rate DECIMAL, side TEXT
  • fact_positions: snapshot_date_key INT PK, account_key BIGINT FK, instrument_key BIGINT FK, quantity DECIMAL, cost_basis_home DECIMAL, market_value_home DECIMAL
  • dim_accounts references fact_trades
  • dim_instruments references fact_trades
  • dim_currencies references fact_trades
  • dim_dates references fact_trades
  • dim_accounts references fact_positions
  • dim_instruments references fact_positions
  • dim_dates references fact_positions

The analysis pattern

Account P&L in home currency for a historical period
SELECT
    a.account_id,
    i.symbol,
    SUM(CASE WHEN t.side = 'buy' THEN -t.home_amount ELSE t.home_amount END) AS realized_pnl_home
FROM fact_trades t
JOIN dim_accounts a
    ON a.account_key = t.account_key
    AND a.effective_from <= '2024-06-30'
    AND (a.effective_to IS NULL OR a.effective_to > '2024-06-30')
JOIN dim_instruments i
    ON i.instrument_key = t.instrument_key
    AND i.effective_from <= '2024-06-30'
    AND (i.effective_to IS NULL OR i.effective_to > '2024-06-30')
JOIN dim_dates d ON d.date_key = t.trade_date_key
WHERE d.full_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY a.account_id, i.symbol

Trade-offs and alternatives

Type 2 dimensions plus append-only fact
Familiar Kimball pattern, fits most BI tools, audit friendly. Cost: joins carry an effective-date predicate and corrections are offset rows.
Bitemporal fact with valid-time columns
Bitemporal fact tracks both effective and knowledge time on each row. Cost: more complex query semantics and most SQL engines need explicit temporal joins, but it supports 'what did we think was true as of this date' questions directly.

Common follow-up questions