Context
The venue’s order and trade activity lived in an operational archive built to run the market, not to report on it. On top of it they needed three things:
- a warehouse that stays current within minutes,
- an engine that bills every fill correctly against the venue’s fee schedule,
- surveillance that flags suspicious trading for compliance.
All of it had to meet the record-keeping rules a regulated marketplace works under.
I owned it end to end: the solution design, the scope and the estimate, then the architecture, the code, the dashboards, the runbooks and the handover guides. I also run it in production.
What I built
- Streaming ingestion. Seven extractors poll the source archive and publish to Redpanda, and ClickHouse consumes the stream into the warehouse. A separate consumer applies busts and corrections. Both run as systemd services rather than Airflow tasks: their target is under five minutes, and a batch scheduler is the wrong tool for that.
- Transforms and data quality. Eight Airflow DAGs drive dbt models in four layers (staging, enriched, business and audit), plus reference-data snapshots, intra-day quality gates and a daily reconciliation report. Airflow only orchestrates. The logic lives in tested modules, never in a DAG file.
- The fee engine. It prices every fill against the fee schedule as it stood on the trade date. Rules are matched as a ranked ladder, and each condition on a rule has three states: it matches, it doesn’t, or it doesn’t apply.
- A fill that can’t be priced is held with a named reason. It never gets a default fee.
- A month can’t close for invoicing until its figures reconcile.
- A companion dashboard walks any trade down the ladder to explain its fee, and lets the business try a rule change before it ships.
- Market surveillance. Alerts for patterns such as wash trading and price ramping, with fourteen validation gates, a fixture harness that proves each detector catches what it should, and liveness checks that alert when the surveillance run itself didn’t happen.
- FIX certification. A Go service that drives FIX 4.2 order-entry certification against the venue: sequence-number persistence, gap fill, reconnect backoff, an invariant rubric for results, and an operator console.
- Operations. Nine Grafana dashboards, thirteen runbooks, guides for onboarding and for deploying from scratch, and twenty architecture decision records.
What was hard
State over time. Four separate bugs turned out to be one mistake: “wait N minutes, then assume it arrived.” Each was first “fixed” by widening the threshold, which only turned a false alarm into a missed one. I replaced all four with invariants. Does downstream match upstream? Has the thing we’re waiting for actually landed? I wrote about it in Check the state, not the clock.
Numbers that were quietly wrong. Reconciliation showed the trade table holding duplicate rows, a side effect of how ClickHouse merges replaced rows when a query doesn’t force it to. The duplicates inflated fill counts and marked some orders as filled that never were. I fixed the model, not the report. Separately, I caught dbt silently dropping the partition keys and the retention policy from five tables during a migration. I rebuilt the keys from how the tables are queried today, not from the old design.
Never guess a mapping. Where a field’s true source was unclear, it stayed empty and logged until the venue confirmed it. In a billing system, a plausible guess is a bug that hasn’t happened yet.
Outcome
The platform is live in production and runs as one system, from the first message off the archive to an invoice that reconciles. It went from an empty repository to production in about three months, and the documentation is written so someone else can run it without me.