Position & P&L Data Models
Trades and curves become decisions only when turned into positions and P&L. This course builds the aggregation and revaluation models that produce exposure and profit and loss, with attribution that decomposes every daily move and reconciles to the blotter.
The pipeline at the heart of this course
From trades to positions
A position is an aggregation: many trades collapsed into a net exposure by instrument, book, or counterparty. This course builds the aggregation logic carefully, because the same trades can be netted in different ways for different purposes, and confusing gross with net, or netting across things that should not be netted, produces exposure numbers that mislead the desk.
You define the position data model and the rules that roll trades up into it, handling the subtleties: offsetting long and short, aggregating across instruments that share risk, and preserving the dimensions, book, desk, counterparty, that the desk needs to slice by. The position is the bridge between the trade store and every risk and P&L number.
Aggregation looks simple until you try to do it correctly, and the course dwells on the subtleties because they are where exposure numbers go wrong. The same set of trades yields different positions depending on how they are netted, and netting across things that do not actually offset produces a number that looks clean and is quietly misleading. You learn to define aggregation precisely, preserving the dimensions the desk needs while netting only what genuinely offsets.
Revaluation and the P&L model
Positions are revalued against the curves from the previous course to produce mark-to-market, and the change in mark-to-market over time is P&L. The course models P&L at the right grain, distinguishing realized from unrealized, and structures it so that daily P&L can be explained rather than merely reported. A P&L number no one can explain is a P&L number no one should trust.
You learn the difference between clean P&L, used for risk, and the various adjusted measures used elsewhere, and how the P&L data model supports each without contradiction. The grain and additivity of the P&L fact, which measures sum and which do not, are treated with the rigor the modeling course established.
The P&L model's grain and additivity are treated with the same rigor the modeling course established, because a P&L that does not sum correctly across books, desks, and time is a P&L no one can build on. The distinction between realized and unrealized, and between clean and adjusted measures, has to be baked into the grain rather than patched on later. The course shows how to get this right so that every roll-up and drill-down is internally consistent.
P&L attribution
The heart of the course is attribution: decomposing a day's P&L into the causes that produced it, how much came from price moves, how much from new trades, how much from the passage of time, how much from FX. This is what turns P&L from a scoreboard into a diagnostic, and it is where data engineering meets financial meaning most directly.
You build the attribution model that separates these effects cleanly, using the Greeks and sensitivities as the bridge between market moves and P&L. Delta explains the price effect, theta the time effect, and so on, and the course shows how these are stored, aggregated, and reconciled so the decomposition adds up exactly to the total.
Attribution is where the course connects data engineering to financial meaning most directly, and it is the capability that turns P&L from a scoreboard into a diagnostic. Decomposing a day's move into price, time, and new-trade effects, using the Greeks as the bridge, is what lets a desk understand why it made or lost money, not just how much. The course builds the attribution model so that the pieces add up exactly to the total, because an attribution that does not reconcile is worse than none.
Reconciliation and integrity
A P&L system that does not reconcile is worse than none, because it invites false confidence. This course treats reconciliation as a first-class output: the P&L must tie back to the trade blotter, and any break must be explainable. You build the break-analysis logic that finds where position and P&L diverge from the underlying trades and flags it before it reaches a report.
Integrity checks run continuously: does the sum of attributed effects equal the total P&L, does the position derived from trades match the position of record, does yesterday's close plus today's activity equal today's open. These reconciliations are the difference between a system the desk trusts and one it second-guesses.
Reconciliation is the discipline that makes the whole thing trustworthy, and the course treats it as a first-class output rather than an afterthought. P&L that ties to the blotter, positions that match the record, attributed effects that sum to the total, these checks run continuously, and a break is surfaced immediately rather than discovered in a report. The course shows how to build the break analysis that finds where and why the numbers diverge.
Serving P&L at scale
Finally the course builds the aggregation cubes and serving layer that let P&L be sliced interactively, by book, desk, counterparty, and time, without recomputation each time. This connects directly to the dashboards course, which puts these numbers on a screen. You leave with a reconciling P&L pipeline: aggregate, revalue, compute, attribute, and reconcile.
The artifact below shows position aggregation and a P&L attribution decomposition that reconciles to the total.
Serving P&L at scale is what lets these numbers be used interactively, and the course connects it directly to the dashboards course that consumes them. Aggregation cubes that pre-compute along the dimensions the desk slices by turn a heavy recomputation into an instant drill-down, and the course shows how to build them without letting them drift from the source. You finish with a P&L pipeline that computes, attributes, reconciles, and serves, end to end.
Attribution: turning P&L into a diagnosis
Reporting a day's P&L as a single number tells the desk whether it made or lost money but nothing about why, and why is what lets a desk learn and control its risk. Attribution decomposes the day's change into its causes: how much came from the market moving against existing positions, how much from new trades done during the day, how much simply from the passage of time, how much from currency. This is where the data engineering acquires direct financial meaning.
The course builds the attribution model carefully, because the effects must be defined so they sum exactly to the total, with any residual small and explicable. The Greeks are the bridge: delta multiplied by the price move explains the first-order market effect, theta explains the time decay, and so on. Storing, aggregating, and reconciling these sensitivities so the decomposition ties out is a real engineering task, and one this course treats in depth.
Done well, attribution transforms P&L from a scoreboard into a diagnostic instrument. A desk that can see its P&L decomposed can distinguish a good day that was mostly luck from a good day that was earned, and can spot a risk building before it becomes a loss. That is why attribution, not raw P&L, is the real deliverable of this course.
Reconciliation and the discipline of tying out
A P&L system earns trust by reconciling, and loses it the first time it does not. This course makes reconciliation a first-class output rather than an afterthought. Every position derived from trades must match the position of record; every day's P&L must tie back to the trade blotter; yesterday's close plus today's activity must equal today's open. When any of these fails, the break must be findable and explainable, not buried in an aggregate.
You build the break-analysis logic that pinpoints where and why position or P&L diverges from the underlying trades, so that a break is a specific, actionable finding rather than a vague discrepancy. This is unglamorous work, and it is exactly what separates a P&L system the desk relies on from one it quietly re-checks in a spreadsheet.
The reconciliation discipline here connects directly to the streaming and STP courses, where the same principle, that every handoff must tie out, governs the real-time and automated flows. Reconciliation is not a stage; it is a property the whole platform must have, and this course establishes the habit.
Where this course sits, and what it unlocks
Position and P&L is the sixth course, the point where the trade model and the curves come together into the numbers a desk actually watches. It consumes the canonical trades, the lifecycle events, and the bitemporal curves from the earlier courses, and it produces the positions, P&L, and attribution that the dashboards course puts on a screen and the credit course checks against limits.
The reconciliation discipline you establish here, that positions and P&L must tie back to the blotter, is the same discipline the streaming and STP courses demand, so this course is where you first build the habit that the whole platform depends on.
Common P&L failures, and how to avoid them
The most common P&L failure is the unexplained number: a daily P&L that no one can decompose, which the desk therefore cannot fully trust. Attribution, decomposing the change into price, time, and new-trade effects that sum to the total, is the cure, and the course treats it as the real deliverable rather than raw P&L.
A second failure is grain confusion, mixing levels of aggregation in one fact table so that measures double-count, producing totals that are subtly wrong. The modeling course's grain discipline prevents this. A third is a P&L system that does not reconcile, inviting false confidence until the first break destroys trust; building reconciliation in from the start avoids it.
By avoiding these patterns you produce P&L that is explainable, correct across every slice, and reconciled to the source, which is what turns P&L from a scoreboard into an instrument the desk can act on.
A worked example: explaining a day's P&L
A desk head asks the question every day: why did we make or lose what we did. Attribution answers it. Take a book that gained overnight. The attribution decomposes the gain: so much from the market moving in favor of existing positions, computed as delta times the price move; so much from time passing, the theta; so much from new trades done during the day; so much from currency. These sum, up to a small explicable residual, to the total change.
The worked example runs the numbers through the attribution query shown earlier, showing how the position, the Greeks, and the day's price moves combine into a decomposition that ties out. The desk head can now see that, say, most of the gain came from a favorable move on an existing position rather than from new trades, which is a completely different story about the day than the headline number alone tells.
This is the course's payoff made concrete: P&L that is not just reported but explained, turned from a number into an understanding, and reconciled so the explanation is trustworthy.
Operational realities: breaks, timing, and cubes
In production, positions and P&L must be produced on a schedule, reconciled, and served for interactive analysis, and each has operational demands. Reconciliation must run every cycle and surface breaks as specific, actionable findings, so that a discrepancy between the P&L and the blotter is caught and diagnosed rather than quietly carried forward. The course's break-analysis logic makes this practical.
Timing matters too: intraday P&L is an estimate that must be reconciled against the official end-of-day number, and the difference must be understood rather than ignored. Finally, serving P&L for interactive slicing, by book, desk, counterparty, time, demands aggregation cubes so that drilling is instant rather than a fresh computation. These operational choices are what make the P&L usable by the desk in practice.
Design trade-offs: recompute versus incremental
There are two ways to keep positions and P&L current, and they suit different needs. Recomputing from the trade store gives an unarguably correct number, derived fresh from source, but it is expensive and too slow to run continuously. Maintaining positions incrementally, updating them as each trade and market move arrives, is fast enough for a live desk but accumulates drift and can quietly diverge from source if any update is missed.
The course teaches using both deliberately: incremental maintenance for the live view the desk trades on, and periodic full recomputation as the reconciling truth that catches any drift. The reconciliation between them, does the incrementally maintained position equal the freshly computed one, is not a nuisance check but a core control, and it is the same pattern the streaming course applies when it reconciles real-time positions against batch. Designing for both, and reconciling them, is what makes fast numbers also trustworthy numbers.
Carrying position and P&L into the capstone
Position and P&L are the aggregation stage of the STP flow, turning the trades that have passed capture, enrichment, and valuation into the exposures that the control stage checks and the dashboards display. The reconciliation discipline you build here, P&L ties to the blotter, attributed effects sum to the total, is exactly the per-handoff reconciliation the capstone runs between stages.
The attribution model matters for the capstone too, because when a flow produces a P&L number, the ability to decompose it into price, time, and new-trade effects is what lets an operator confirm the number is right rather than merely present. Unexplained P&L in an automated flow is a red flag; attributable P&L is a sign the flow is behaving. This course builds the capacity to tell the difference.
Performance and scale considerations
Positions and P&L are recomputed and sliced constantly, so their performance shapes how usable the whole risk view is, and the course keeps scale in view throughout. Full recomputation from the trade store is correct but heavy, so the course teaches incremental maintenance for the live view and pre-aggregated cubes for interactive slicing, reserving full recomputation for the reconciling batch run rather than the interactive path.
Attribution adds computational weight because it decomposes every position's move, so the course shows how to compute it efficiently, aggregating sensitivities rather than repricing every trade repeatedly. The design goal is a P&L layer that answers a drill-down instantly while still being able to reconcile from source, which means being deliberate about what is precomputed and what is derived on demand.
Testing and validating P&L
P&L has a natural correctness test the course leans on heavily: it must reconcile. The attributed effects must sum to the total, the position derived from trades must match the position of record, and yesterday's close plus today's activity must equal today's open, and each of these is an assertion that can be checked automatically on every run. A P&L system that passes these reconciliations continuously is one the desk can trust.
The course also teaches break analysis as both a control and a test: when a reconciliation fails, the system must locate where and why the numbers diverged, and building that capability is how you validate the P&L pipeline under real conditions. You finish able to demonstrate that your P&L ties out and to diagnose it quickly when it does not, which is exactly what a trading operation requires.
How it works, concretely
-- Net position per instrument and book (signed by buy/sell)
CREATE VIEW position AS
SELECT book_id,
instrument_id,
SUM(CASE WHEN buy_sell = 'B' THEN quantity ELSE -quantity END) AS net_qty
FROM trade
WHERE trade_date <= :as_of
GROUP BY book_id, instrument_id;
-- Daily P&L attribution: total change decomposed into causes
WITH pnl AS (
SELECT p.book_id, p.instrument_id, p.net_qty,
c_today.value AS px_today, c_yday.value AS px_yday,
g.delta, g.theta
FROM position p
JOIN curve_today c_today USING (instrument_id)
JOIN curve_yday c_yday USING (instrument_id)
JOIN greeks g USING (book_id, instrument_id)
)
SELECT book_id,
SUM(delta * (px_today - px_yday)) AS price_effect,
SUM(theta) AS time_effect,
SUM(net_qty * (px_today - px_yday)) AS total_pnl
FROM pnl
GROUP BY book_id;
-- Reconciliation check: price_effect + time_effect + residual = total_pnlChapters 101 to 120
Twenty sequential chapters. Within the full bundle these are chapters 101 through 120 of 240.
- 101From trades to positions: aggregation logicAggregating many trades into a net position by the right dimensions. The same trades net differently for different purposes, so aggregation logic is deliberate.
- 102Position keeping and the position data modelDesigning the position data model and keeping it correct. The position data model is the bridge between the trade store and every risk number.
- 103Netting, offsetting, and gross vs netNetting and offsetting, and knowing gross from net. Confusing gross and net, or netting wrongly, produces exposure numbers that mislead the desk.
- 104Exposure by book, desk, and counterpartySlicing exposure by book, desk, and counterparty. Exposure dimensions are what let risk be seen by book, desk, and counterparty at once.
- 105Mark-to-market and revaluation mechanicsRevaluing positions to mark-to-market against curves. Mark-to-market is the revaluation of positions against the curves from the previous course.
- 106The P&L data model and its grainDesigning the P&L data model at the right grain. Getting the P&L grain right is what makes the numbers additive and explainable.
- 107Realized vs unrealized P&LSeparating realized from unrealized P&L. The realized and unrealized split is fundamental to how P&L is read and reported.
- 108Daily P&L and the change explanationProducing daily P&L that can be explained, not just reported. Daily P&L is only trustworthy when it can be explained, not merely stated.
- 109P&L attribution: price, volume, and timeAttributing P&L to price, volume, and time effects. Attribution decomposes the day's move into price, volume, and time so it can be understood.
- 110Greeks and sensitivity dataStoring Greeks and sensitivities for attribution. Sensitivities are the bridge between market moves and the P&L they produce.
- 111Delta, gamma, vega storage and aggregationAggregating delta, gamma, and vega across a book. Storing and aggregating Greeks correctly is what makes portfolio-level risk coherent.
- 112New-trade and amendment P&L effectsIsolating the P&L effect of new trades and amendments. New-trade and amendment effects must be isolated so they do not distort market P&L.
- 113Carry, roll, and theta decompositionDecomposing carry, roll, and theta. Carry, roll, and theta decomposition explains the P&L that comes purely from time.
- 114FX effects and base-currency conversionHandling FX effects and base-currency conversion. Base-currency conversion is where FX effects enter and must be attributed cleanly.
- 115Reconciliation to the trade blotterReconciling computed P&L back to the trade blotter. Reconciliation to the blotter is what turns P&L from a guess into a controlled number.
- 116Break analysis and P&L integrity checksAnalyzing breaks where position and P&L diverge. Break analysis finds where position and P&L diverge from the underlying trades.
- 117Intraday P&L and estimate vs actualProducing intraday P&L and comparing estimate to actual. Intraday estimates versus actuals show how live and official numbers relate.
- 118Hypothetical and clean P&L for riskBuilding hypothetical and clean P&L for risk. Clean and hypothetical P&L are what feed risk measures without contradiction.
- 119Aggregation cubes and OLAP for P&LServing P&L through aggregation cubes for fast slicing. Aggregation cubes are what make P&L sliceable interactively without recomputation.
- 120A reconciling P&L pipeline end to endAssembling a reconciling P&L pipeline end to end. You finish with a P&L pipeline that computes, attributes, and reconciles end to end.
What you'll be able to do
- Aggregate trades into positions and revalue them against curves
- Model P&L at the right grain with correct additivity
- Decompose daily P&L into price, time, and new-trade effects
- Reconcile positions and P&L back to the trade blotter
- Serve sliceable P&L through aggregation cubes
Audience & prerequisites
Engineers turning trades and curves into the position, P&L, and attribution numbers a desk lives by, and anyone who has had to explain a P&L break.
Prove the module by building it
Compute positions from the trade store, revalue against curves, and produce a P&L attribution mart that decomposes daily change and reconciles to the trade blotter.
This course project is one of twelve that culminate in the bundle's end-to-end capstone, a complete working ETRM data platform run as a straight-through pipeline.
Learn it your way
This course is included in the ETRM Data Engineering and Analytics bundle, available self-paced with PDF handbooks, slide decks, video explainers, and hands-on labs and a project, and deliverable as private corporate training.
Explore the full 12-course bundle and its end-to-end capstone project →
Frequently asked questions
What does the Position & P&L Data Models course cover?
Turning trades and curves into positions, exposures, and profit and loss, with attribution that reconciles to the cent. It runs to 20 sequential chapters (chapters 101 to 120 of the 240-chapter bundle).
Where does this sit in the learning path?
It is course 6 of 12 in the ETRM Data Engineering and Analytics sequence. It builds on the courses before it and prepares for those after it.
Is there a project?
Yes. Compute positions from the trade store, revalue against curves, and produce a P&L attribution mart that decomposes daily change and reconciles to the trade blotter.
What technologies are involved?
The course works with SQL, Spark, pandas, dbt, OLAP, chosen to reflect how real ETRM data platforms are built.
Is it available self-paced?
Yes. Every course in the bundle is available self-paced with PDF handbooks, slide decks, video explainers, and hands-on labs and a project, with lifetime access.
Can my team take it as corporate training?
Yes. The whole bundle, or individual courses, can be delivered as private corporate training mapped to your stack and data. Use the contact form to scope it.
Take this course, or the whole platform
Enroll self-paced, or bring the ETRM Data Engineering bundle to your team.