Enterprise Trading Platform · architecture diagrams

Analytics service

Python 3.12+, pandas, DuckDB. No port and no inbound API. A batch pipeline that copies trading history into a star schema, and a dashboard that reads it.

This service exists because two questions have incompatible access patterns. "What is my balance right now" wants one row from a normalised table in a few milliseconds. "What did the desk trade last quarter, by instrument, by week" wants a full scan and an aggregate, and it is allowed to take seconds. Serving both from PostgreSQL means the second one can starve the first, and the first one is order placement.

DuckDB is the analytical store. One file, no server, no account, no credentials. The star schema in contracts/analytics-schema.sql is plain ANSI SQL and runs on it unchanged, which is what makes the lesson portable: a graduate who can explain why fact_trades is loaded in batch rather than read live can explain it on any warehouse.

Internal structure of the analytics service Command line entry points, a read-only PostgreSQL connection and an optional trade-events consumer on the left. Seven packages in the centre: cli, etl, db, dashboard, fauxnance, kafka_sink and config. The DuckDB file, the dashboard output and the Fauxnance candles endpoint on the right. INBOUND OUTBOUND run on the host, on a schedule etl dashboard kafka-sink PostgreSQL read only, as analytics_reader orders, accounts, instruments trade-events group analytics-loader optional, and reconciled The ETL never writes to PostgreSQL. It holds a read-only role for exactly that reason, so a mistake in the pipeline cannot damage the system of record. Analytics service no port Python 3.12+ · pandas · DuckDB · matplotlib or plotly · pytest analytics/src/analytics cli Three commands. No daemon, no scheduler. etl run · etl full-refresh · etl backfill --from --to dashboard · kafka-sink entry points declared in pyproject.toml under [project.scripts] etl The source of truth for fact_trades. extract.py · transform.py · validate.py · load.py · watermark.py · pipeline.py incremental by default, from the watermark. backfill reloads a named date range. rows that fail validation go to etl_quarantine rather than into the fact table the load is idempotent: rerunning it does not count a trade twice db Two connections, two directions. operational.py opens PostgreSQL read only · warehouse.py opens the DuckDB file db/schema.sql creates the star schema and the pipeline's own bookkeeping tables dashboard Reads the warehouse, never the source. report.py · insights.py · palette.py reads DuckDB from Sprint 7 onwards, and PostgreSQL directly only before the store exists fauxnance Run on demand, not on a schedule. client.py · GET /candles/{symbol} for end-of-day history seeds stg_market_candles. Nothing here sits on the order path. kafka_sink Optional. Adds to the batch load, never replaces it. consumer.py · group analytics-loader · kafka_processed_events keyed on eventId a streamed row must reconcile against the batch load, which stays authoritative config One place reads the environment. config.py, built per command · seed_universe.py · timeutil.py DuckDB file one file, no server, no account path from DUCKDB_PATH Dashboard output charts and tables built from the star schema Fauxnance API GET /candles/{symbol} end-of-day history only Four star schema tables and four bookkeeping tables live in that file. The table below says what each one holds and why it is there. ENVIRONMENT PG_HOST · PG_PORT · PG_DATABASE · PG_USER · PG_PASSWORD · DUCKDB_PATH FAUXNANCE_BASE_URL · FAUXNANCE_API_KEY · FAUXNANCE_TIMEOUT_SECONDS · FAUXNANCE_MAX_RETRIES · KAFKA_BOOTSTRAP_SERVERS · KAFKA_TRADE_EVENTS_TOPIC · KAFKA_CONSUMER_GROUP
Module names are the packages under analytics/src/analytics. Nothing in this service listens on a port, and nothing in the platform calls it.

What lives in the DuckDB file

TableKindHolds
fact_tradesfactOne row per executed trade, with foreign keys to the three dimensions and the measures the dashboard aggregates.
dim_accountdimensionAccount attributes, with effective dates so a historical trade keeps the attributes it had at the time.
dim_instrumentdimensionSymbol, name, currency and sector.
dim_datedimensionA calendar, so a query can group by week or quarter without date arithmetic in SQL.
etl_watermarkbookkeepingHow far the incremental load has read. This is what makes etl run resumable.
etl_quarantinebookkeepingRows that failed validation, with the reason. A pipeline that silently drops bad rows hides a defect upstream.
kafka_processed_eventsbookkeepingeventId values already handled by the optional streaming load, so a redelivery does nothing.
stg_market_candlesstagingEnd-of-day candles pulled from the Fauxnance API, used for price context rather than for trade facts.

Batch and stream, and which one wins

The batch extract from PostgreSQL is authoritative for fact_trades. Consuming trade-events is optional, and where a team builds it, the streamed rows reconcile against the batch load rather than replacing it. Two writers to one fact table with no agreement about which is correct produces a number nobody can defend in a review, which is worse than a number that is an hour old.

Sprint 4 points the dashboard at PostgreSQL because the analytical store does not exist yet. Sprint 7 moves it. After that, pointing the dashboard back at PostgreSQL is a defect, not a shortcut.