Equities, ETFs, and the SEC

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L5

Problem

Fractional share quantities and multi-currency settlement run through every trade a retail brokerage executes across equities, ETFs and crypto. Design the warehouse pipeline, data model and ingestion included, that serves business analytics, risk reporting and SEC/FINRA regulatory submissions.

Fractional shares, multi-currency, point-in-time. All of it.

Business requirements

• A fifth of equity trades are fractional shares; quantities can't lose precision in the warehouse.
• SEC rules require trade records be retained unchanged for six years; we can't fail that audit.
• Account margin status, account type, and KYC status change over time; a trade from last year has to be reported using the account state in effect then, not today's.

Worked solution and explanation

This is three correctness promises dressed up as a brokerage warehouse: exact fractions, history that remembers, and records that cannot move. Anyone can draw a star schema with a trade fact and an account dimension. What separates candidates is seeing that each promise breaks quietly under the obvious design. An INT quantity truncates fractional shares. An overwritten dimension rewrites last year's report with today's account state. And an archive cut from the warehouse is only as unchanged as the last restatement, because it is a derived copy, not the trade record. You find out at the first audit.

Walk the requirements

Step 1: Land quantity in an exact decimal

A fifth of equity trades are fractional, so decimal_quantity_normalizer casts quantity to a fixed-scale NUMERIC before it reaches fact_trades. INT truncates the fraction and FLOAT drifts on aggregation, and either way a disputed balance will not reconcile.

Step 2: Keep account history as an SCD2

Margin status, account type and KYC status change. account_scd_loader writes one row per change into dim_account_scd2 with valid_from and valid_to. The load into fact_trades joins on trade_time BETWEEN valid_from AND valid_to, so a trade from last March reports under last March's account state.

Step 3: Archive the raw trades, not the warehouse

raw_trade_snapshot_writer runs daily and copies the trades exactly as they arrived on trade_execution_feed into six_year_write_once_archive, object storage under a six-year write-once retention policy. It reads the feed, not fact_trades, so no cast, join or restatement can touch what was filed. Regulators read only this store.

Current-state account dimension

One row per account_id, updated in place. Cheap equi-join, but the moment an account changes, every historical report is silently restated.

SCD2 account dimension

One row per change with valid_from and valid_to. The as-of join costs more and the table grows, but any past trade reports under the account state in effect then.

Common follow-up questions

• Crypto trades 24/7, but a daily snapshot assumes a market-close cutover. What does the snapshot capture, and when? (Tests whether you treat the cutover as a stated policy, such as a UTC midnight cut recorded with each snapshot, so the regulator knows the window filed.)
• An account's KYC status is corrected retroactively. How does that flow through `dim_account_scd2`, and what happens to snapshots already filed? (Tests that the dimension gains a corrected row while filed snapshots stay unchanged, and the correction appears in later filings as an amendment.)