Skip to content
LLD Learn/Advanced Transactional Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Event Sourcing & Append-Only Tables

4 min read

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.

Exchange rates as an append-only event stream, not a mutable snapshot
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.

Press Run: the same trade valued with the right rate, and with an overwritten one

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.

Point-in-time snapshots, queryable at any past moment
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.

Ready to Build This?

Design Multi-Currency Trading, the Risk & Margin System, and the IPO Pipeline in the LLD Lab's Wolf of Wall Street act.

Ready to Build This?

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.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!