Event Sourcing & Append-Only Tables
You'll learn to
- -Explain event sourcing as keeping the diary of changes instead of only the latest value
- -Look up a value as it was at any past moment, and see what an overwritten row would get wrong
- -Plan for the one cost of append-only tables: they only grow
Event sourcing stores every change to a piece of data as an immutable, time-stamped event, instead of overwriting one mutable row. The current state is worked out by replaying the events, not stored as the only truth. This extends the append-only audit trail from the previous chapter into a complete way of modeling data that genuinely needs its full history.
An Analogy: The Bank Statement
A bank statement lists every deposit and withdrawal. A balance written in pencil on a card shows only the latest total. If you wonder what your balance was on the 5th, or why it changed, the statement can answer and the pencil card cannot. Event sourcing keeps the statement.
Multi-Currency Trading: Rates That Change Constantly
An exchange rate is not one fact. It is a continuous series of facts. A trade executed at 2:47 pm must be valued with the rate in effect at 2:47 pm, not whatever the rate happens to be when someone looks later. A table with one row per currency pair that is overwritten on every update destroys exactly the information needed to value a historical trade correctly.
CREATE TABLE exchange_rate_events (
id INTEGER PRIMARY KEY,
currency_pair TEXT NOT NULL, -- 'USD/EUR'
rate DECIMAL(12, 6) NOT NULL,
effective_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
-- never UPDATEd - a rate change is always a new row, never a modification
);
-- "what was the USD/EUR rate at the moment this trade executed?"
SELECT rate FROM exchange_rate_events
WHERE currency_pair = 'USD/EUR' AND effective_at <= :trade_timestamp
ORDER BY effective_at DESC LIMIT 1;The demo shows the cost of getting this wrong. The USD to EUR rate was 0.9200 at 9:00, 0.9150 at 14:30 and 0.9180 at 15:10. A trade of 100,000 USD at 14:47 should use 0.9150. A table that only holds today's latest rate would use 0.9180 and be wrong.
Using the overwritten rate mis-values this single trade by 300 EUR. Across many trades, and in a dispute or an audit, that is a serious problem.
Risk Snapshots: Point-in-Time State
A margin and risk system must answer "what was this account's risk exposure at 9:00 am, before the market moved?" for compliance and disputes, not only "what is it now?" The same pattern applies. Each recalculation writes a new risk_snapshots row and never updates an old one. The current value is simply the most recent snapshot, a special case of the lookup above rather than a separately maintained number.
CREATE TABLE risk_snapshots (
id INTEGER PRIMARY KEY,
account_id INTEGER NOT NULL REFERENCES accounts(id),
exposure_amount DECIMAL(15, 2) NOT NULL,
margin_ratio DECIMAL(6, 4) NOT NULL,
calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- "current" risk is just the most recent snapshot for that account:
SELECT * FROM risk_snapshots WHERE account_id = :account_id
ORDER BY calculated_at DESC LIMIT 1;The IPO Pipeline as a Workflow of Events
An IPO (initial public offering, when a company first sells shares to the public) moves through stages such as filing, roadshow, pricing and allotment. This combines two techniques. Each stage change is an appended event, as in this chapter, while the set of valid stage changes is still governed by a transitions table, as in the previous chapter. The two compose, they do not compete.
Append-only tables grow without bound by design. A real system needs a plan: archive old events to cold storage, or keep a separate "current state" table that is refreshed from the event stream. Do not assume an ever-growing table stays fast.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Multi-Currency Trading, the Risk & Margin System, and the IPO Pipeline in the LLD Lab's Wolf of Wall Street act.
An append-only event table is exactly the shape a running-total window function is built for - SQL Lab's "The Million Mark" (SUM OVER) computes a cumulative total across rows without collapsing them the way a plain GROUP BY would, which is what querying this chapter's event log for "balance as of any point in time" actually needs.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!