Data Modeling for ETRM Systems
Every ETRM system, however sophisticated its analytics, rests on a data model. This course builds that foundation from first principles: the entities, keys, and relationships that describe trading, and the discipline of turning them into a physical schema that stays correct as the business changes.
The pipeline at the heart of this course
Why the model is the foundation
A trading system is, at bottom, a set of assertions about the world: this trade exists, it references this instrument, it belongs to this book, it obligates these cash flows. The data model is where those assertions are made precise. Get it right and everything downstream, valuation, risk, reporting, becomes a query. Get it wrong and every downstream calculation inherits the ambiguity, and teams spend their days reconciling numbers that should never have diverged.
This course treats data modeling as an engineering discipline rather than a diagramming exercise. You learn to identify entities and their true identity, to choose keys deliberately, to normalize until redundancy is gone and then to denormalize consciously for analytics, and to express the whole thing as a physical schema with constraints that make invalid states unrepresentable. The canonical ETRM core you build here, trades, instruments, counterparties, books, cash flows, is the same structure the rest of the twelve courses extend.
In practice this means treating the schema as a contract that other teams build against. When the valuation team, the risk team, and the reporting team all read from the same trade, instrument, and position definitions, their numbers agree by construction rather than by reconciliation, and a change to the model is a deliberate, reviewed event rather than a surprise. The course frames modeling as the act of writing that contract carefully enough that it rarely has to change.
Entities, keys, and identity
The first hard question in any model is identity: what makes two rows the same thing or different things. A trade has a natural business identity, but natural keys drift and get reused, so a durable model pairs a stable surrogate key with the business identifiers rather than trusting the latter alone. Instruments, counterparties, and books each need the same care, because a downstream join is only as trustworthy as the keys it joins on.
You learn to distinguish primary keys, which enforce row identity, from foreign keys, which enforce relationships, and to reason about cardinality: a trade has exactly one instrument but many cash flows; a counterparty belongs to exactly one legal-entity hierarchy but appears on many trades. These cardinalities are not decoration; they become the referential-integrity constraints that stop the database from ever holding an orphaned cash flow or a trade pointing at a nonexistent book.
Identity questions surface in unexpected places. Two trades booked as a package, a trade and its later amendment, a counterparty that merged with another, each forces a decision about what counts as the same thing, and the model has to answer consistently. The course works through these cases concretely, because the abstract rule about surrogate versus natural keys only becomes real when you apply it to a counterparty that changed its name and its identifier on the same day.
Normalization, and knowing when to stop
Normalization is the process of removing redundancy so that every fact is stored exactly once. Third normal form, where every non-key attribute depends on the key, the whole key, and nothing but the key, is the working target for the transactional core, because it makes updates safe: change a counterparty's rating in one place and every trade sees it. The course walks through the anomalies that under-normalized models suffer, update, insertion, and deletion anomalies, using concrete ETRM examples.
But normalization has a cost in query complexity and join count, and analytics workloads often want the opposite: wide, denormalized tables that answer a question in one scan. The skill is knowing which world you are in. The transactional core stays normalized for correctness; the analytical marts, built later in the position and P&L course, are deliberately dimensional. This course teaches both and, crucially, the reasoning that decides between them.
The discipline of stopping at third normal form for the core, rather than chasing higher normal forms or sliding back into convenient denormalization, is a judgment the course makes explicit. Over-normalizing produces a schema that is theoretically pure but painful to query and slow to evolve; under-normalizing produces the anomalies that corrupt data. The working target is the level where every fact lives in exactly one place and updates are safe, and the course gives you the reasoning to defend where that line sits.
Temporal and bitemporal modeling
Trading data is temporal in a way that trips up naive models. A trade has a trade date, a value date, and a settlement date, and reference data like a counterparty rating or a curve definition changes over time. Worse, there are two independent time axes: when something was true in the business world, and when the system learned about it. Confusing these is the source of countless reconciliation breaks.
You learn slowly changing dimension patterns for reference data, where history is preserved by versioning rows rather than overwriting them, and bitemporal modeling, where every fact carries both valid time and transaction time so the system can answer both what is true now and what we believed last Tuesday. This matters enormously for audit and for point-in-time revaluation, and it recurs in the curves, event-sourcing, and STP courses.
Temporal modeling is where many otherwise-competent schemas quietly fail. A model that stores only current values cannot answer what a position was yesterday, cannot revalue a book as of a past date, and cannot explain why a number changed without a new trade. The course treats the temporal and bitemporal patterns as core rather than advanced material, because in trading they are not optional refinements but the difference between a system that can be audited and one that cannot.
From logical model to physical DDL
The course closes by turning the logical model into a physical schema: choosing data types with the right precision for prices and quantities, where rounding error is a real financial risk, adding the constraints that enforce the model's rules, and designing for change so that a new product type does not require a painful migration. You leave with a runnable schema and the judgment to defend every choice in it.
The artifact below shows the shape of that core schema. It is deliberately small, but it is the seed of everything: notice how the constraints, not comments, are what actually keep the data honest.
The move from logical model to physical DDL is where design meets the database, and the course insists that the constraints do real work. A foreign key that is documented but not enforced is a comment, not a guarantee, and the difference shows up the first time bad data tries to enter. You finish able to write a schema where the rules of the trading domain are enforced by the database itself, so that whole classes of error simply cannot occur.
Keys, integrity, and the cost of getting identity wrong
It is worth dwelling on identity because it is where the most expensive modeling mistakes are made. Suppose a source system reuses a trade reference after a trade is cancelled, a surprisingly common occurrence. If your model treats that reference as the trade's identity, the new trade silently inherits the old one's history, and no amount of downstream cleverness can untangle it. The discipline of a stable, meaningless surrogate key, assigned once and never reused, is what prevents this entire class of failure.
Foreign keys deserve the same seriousness. A foreign key is not merely a hint that two tables are related; it is a promise the database enforces, that no cash flow can exist without its trade, that no trade can reference a counterparty that was never created. Turning these promises off for performance, as teams under pressure sometimes do, is how a database fills quietly with orphaned rows that produce wrong aggregates no one can explain. This course argues, with examples, that integrity constraints are not overhead but the cheapest insurance a trading system can buy.
You also learn to reason about cardinality precisely, because it drives both the schema and the queries. A one-to-many relationship between trade and cash flow becomes a foreign key on the many side; a many-to-many relationship, say between trades and the regulatory reports they appear in, needs an associative table. Getting these structures right at design time is the difference between queries that are natural and queries that fight the schema.
Dimensional modeling and the analytical layer
Once the transactional core is sound, the course turns to the analytical layer, where the design goals invert. Analysts and dashboards do not want to navigate a fully normalized schema with a dozen joins to answer one question; they want wide, intuitive tables organized around the questions they ask. Dimensional modeling delivers this: facts, the measurable events like a P&L movement or a trade, surrounded by dimensions, the descriptive context like book, counterparty, and date, by which those facts are sliced.
The subtlety that trips people up is grain: the precise definition of what one row of a fact table represents. A P&L fact at daily-book-instrument grain behaves very differently from one at trade grain, and mixing grains in a single table produces double-counting that can take days to diagnose. The course teaches you to declare grain explicitly and to reason about additivity, which measures can be summed across which dimensions, so that a dashboard total is always correct no matter how the user slices it.
You leave understanding both worlds and, crucially, the boundary between them: the normalized core is the source of truth, and the dimensional marts are derived from it through explicit, testable transformations. This separation is a theme the architecture and position-and-P&L courses build on directly.
Where this course sits, and what it unlocks
This is the first course in the bundle for a reason: nothing else stands without it. The reference-data course extends this model with governance and versioning; the trade-lifecycle course adds events over these entities; the position-and-P&L course aggregates and revalues them; the architecture course lands them on a lakehouse; and the STP capstone wires the whole thing together. Every one of those courses assumes the modeling discipline established here.
That is why the course invests so heavily in getting identity, keys, and constraints right. A weakness in the model is not local; it propagates into every later course and every downstream number. Conversely, a sound model makes the later courses dramatically easier, because so much correctness is already guaranteed by the schema. The time spent here is repaid many times over across the rest of the bundle.
Common modeling failures, and how to avoid them
The course is candid about how models go wrong in practice, because recognizing the failure patterns is the fastest way to avoid them. The most common is using a natural business key as the primary key, which breaks the moment that key is reused or corrected upstream. The fix is a stable surrogate key with the business identifiers as attributes, a pattern the course applies consistently.
A second frequent failure is under-constraining the schema, treating referential integrity and check constraints as optional, which lets the database accumulate invalid rows that produce wrong aggregates no one can trace. A third is conflating time axes, mixing business date and system date, or ignoring the need for history, which makes point-in-time questions unanswerable. The course names each pattern, shows the damage it causes with a concrete example, and gives the modeling discipline that prevents it.
By studying these failures deliberately, you leave able not just to build a sound model but to review an existing one and spot the latent problems before they become incidents.
A worked example: modeling a physical forward
Consider modeling a physical crude forward, a common ETRM trade, to see the principles in action. The trade references an instrument, which references a product, a grade, and a delivery location; a counterparty, which rolls up to a legal entity; and a book, which rolls up to a desk. The trade carries a quantity, a price, a trade date, and a delivery window, and it generates one or more cash flows on settlement.
Each of these is a modeling decision with consequences. The delivery location is reference data, not free text, so that trades at the same location aggregate correctly. The quantity and price carry deliberate precision, because a rounding error on a large notional is real money. The delivery window is temporal data that will drive scheduling and settlement events later. And every foreign key is a constraint the database enforces, so the trade can never reference a location, counterparty, or book that does not exist.
Walking through this one trade, the course shows how the abstract principles, keys, normalization, precision, temporal modeling, constraints, become concrete decisions, and how getting each right means the trade slots cleanly into every downstream calculation the later courses build.
Operational realities: migrations, versioning, and change
A model is not built once and frozen; it evolves as the business adds products, markets, and requirements. The course closes on the operational reality of change: how to extend a schema without a painful, risky migration, how to version the model so that data captured under an old structure remains interpretable, and how to evolve constraints without breaking existing data.
The key discipline is designing for extension from the start, favoring additive changes, new nullable columns, new tables, over destructive ones, and using patterns that absorb new product types without restructuring the core. The course shows how a well-designed model accommodates years of change with minimal migration, and how a poorly designed one requires a painful rebuild at the first unexpected requirement.
Design trade-offs: how much to normalize
The central judgment in ETRM modeling is how far to push normalization before the query cost outweighs the integrity benefit. A fully normalized core makes every fact single-sourced and every update safe, but a report that needs trade, instrument, counterparty, and book together then pays for four joins on every read. The discipline this course teaches is to keep the transactional core in third normal form, where writes happen and correctness is non-negotiable, and to push denormalization into a separate analytical layer that is rebuilt from the core rather than edited directly.
That separation is not a compromise; it is the design. The core owns truth and accepts the join cost that correctness implies, while the marts own speed and accept the redundancy that interactivity implies, and because the marts are derived, their redundancy can never drift from the core. Getting this boundary in the right place is what lets a schema serve both a booking system that must never double-count and a dashboard that must answer in milliseconds, without either compromising the other.
Carrying the model into the capstone
Everything downstream in the program is an extension of the core you build here. Reference data attaches to the instrument and counterparty entities; the lifecycle course turns the trade into a sequence of events; curves feed the valuation that produces P&L over these positions; the lakehouse lands this same model in its silver layer; and the STP capstone flows trades through it end to end. A weakness in the core, an ambiguous key, a missing constraint, a temporal axis left implicit, does not stay local; it propagates into every later module and surfaces as a reconciliation break no one can explain.
That is why this course comes first and why its rigor matters out of proportion to its apparent simplicity. When you reach the capstone and wire twelve modules into one flow, the reason the flow holds together is that every module speaks the same canonical model, agreed here, with the same keys, the same grain, and the same temporal discipline.
Performance and scale considerations
A model that is correct but slow is only half-built, and this course keeps performance in view as a design input rather than an afterthought. The normalized core pays a join cost on reads, and at scale that cost is managed through deliberate indexing on the keys that joins actually use, through partitioning large tables by the dimensions queries filter on, and through pushing analytical load onto the derived dimensional marts rather than the transactional core. The point is that the schema's shape and its physical tuning are decided together.
Precision and data-type choices also have a scale dimension. Numeric types with the right precision cost more storage and comparison time than looser ones, but in a trading system the alternative, rounding error, is unacceptable, so the course teaches choosing precision to the requirement and then optimizing around it. You learn to reason about where a schema will be queried hardest and to shape the physical model so those paths stay fast as data grows.
Testing and validating the model
A schema is a set of claims about the data, and this course treats those claims as testable. Constraints are the first line of testing, because a foreign key or check constraint fails loudly the moment bad data tries to enter, but the course goes further, showing how to write data-quality assertions that verify the model holds in practice: no orphaned cash flows, no trades without a valid book, no temporal ranges that invert. These assertions run continuously, not just at build time.
The course also teaches validating the model against real usage before it is trusted. Loading representative data, running the queries the platform will actually issue, and confirming that aggregations sum correctly and joins return what they should is how you catch a modeling error while it is cheap to fix. You finish able to demonstrate, not merely assert, that your schema enforces the rules of the trading domain it represents.
How it works, concretely
-- Reference entities first (parents before children)
CREATE TABLE counterparty (
counterparty_id bigint PRIMARY KEY,
legal_entity_id bigint NOT NULL REFERENCES legal_entity(legal_entity_id),
name text NOT NULL,
credit_rating text,
valid_from date NOT NULL,
valid_to date NOT NULL DEFAULT date '9999-12-31',
CHECK (valid_to > valid_from) -- no zero-length or inverted history
);
CREATE TABLE instrument (
instrument_id bigint PRIMARY KEY,
product_id bigint NOT NULL REFERENCES product(product_id),
currency char(3) NOT NULL,
delivery_location text
);
CREATE TABLE trade (
trade_id bigint PRIMARY KEY, -- surrogate key
external_ref text NOT NULL, -- business identifier
instrument_id bigint NOT NULL REFERENCES instrument(instrument_id),
counterparty_id bigint NOT NULL REFERENCES counterparty(counterparty_id),
book_id bigint NOT NULL REFERENCES book(book_id),
trade_date date NOT NULL,
quantity numeric(20,6) NOT NULL, -- precision matters
price numeric(20,8) NOT NULL,
buy_sell char(1) NOT NULL CHECK (buy_sell IN ('B','S')),
UNIQUE (external_ref) -- one row per business trade
);
CREATE TABLE cashflow (
cashflow_id bigint PRIMARY KEY,
trade_id bigint NOT NULL REFERENCES trade(trade_id) ON DELETE CASCADE,
pay_date date NOT NULL,
amount numeric(20,2) NOT NULL,
currency char(3) NOT NULL
);
-- A cash flow can never outlive its trade, and a trade can never
-- reference a book, instrument, or counterparty that does not exist.Chapters 1 to 20
Twenty sequential chapters. Within the full bundle these are chapters 1 through 20 of 240.
- 001Why data modeling is the foundation of every ETRM systemWhy the shape of the data decides whether every downstream number is trustworthy or ambiguous. You learn to reason about a schema as a set of enforced assertions rather than a diagram.
- 002Entities, attributes, and relationships in trading dataIdentifying the real-world things a trading system tracks and the relationships that bind them. Getting the entities and relationships right here is what makes every later join meaningful.
- 003Primary keys, surrogate keys, and natural keys for tradesChoosing stable surrogate keys over drifting natural keys, and why identity is the first design choice. Surrogate keys give the model a stable spine that survives business-key reuse and change.
- 004Normalization to third normal form, and when to stopUsing surrogate, natural, and composite keys deliberately so joins are always sound. The chapter shows concretely how a wrong key choice quietly corrupts downstream aggregation.
- 005The canonical trade entity and its core attributesRemoving redundancy to third normal form so every fact is stored exactly once. You see the update, insertion, and deletion anomalies that under-normalized trading data suffers.
- 006Instruments, products, and contract templatesModeling the central trade entity and the attributes every trade must carry. The canonical trade becomes the entity every other course in the program extends.
- 007Counterparties, legal entities, and hierarchiesRepresenting instruments, products, and reusable contract templates cleanly. Contract templates let many trades share structure without duplicating it row by row.
- 008Books, portfolios, strategies, and desksModeling counterparties and the legal-entity hierarchies they roll up into. The hierarchy is what lets exposure to related entities be aggregated and controlled together.
- 009Locations, delivery points, and geographyStructuring books, portfolios, strategies, and desks for aggregation and control. These aggregation dimensions are the ones the desk will later slice positions and P&L by.
- 010Currencies, units of measure, and conversionsModeling delivery points, hubs, and geography that physical trades depend on. Unit and currency handling is a frequent source of silent error, so the model makes it explicit.
- 011Temporal modeling: business date vs system dateHandling currencies, units of measure, and safe conversion between them. Confusing these two dates is the root of a whole class of reconciliation breaks.
- 012Slowly changing dimensions for reference dataSeparating business date from system date so temporal logic stays correct. Versioning history rather than overwriting it is what makes point-in-time queries possible.
- 013Bitemporal modeling: valid time and transaction timePreserving reference-data history with slowly changing dimension patterns. Bitemporality lets the platform answer both what is true and what we believed at any past moment.
- 014The dimensional model: facts and dimensions for ETRMModeling valid time and transaction time together for full bitemporal history. The dimensional layer is derived from the normalized core, never edited independently.
- 015Star schema vs snowflake for trading martsDesigning facts and dimensions that make analytics fast and intuitive. You learn when the extra joins of a snowflake are worth it and when a star is cleaner.
- 016Grain, additivity, and semi-additive P&L factsChoosing star or snowflake shapes for trading marts, and the trade-offs of each. Getting additivity wrong is how a P&L total silently fails to equal the sum of its parts.
- 017Data types, precision, and the cost of roundingGetting grain and additivity right so P&L sums correctly across dimensions. Precision choices here are a real financial control, not a cosmetic detail.
- 018Constraints, referential integrity, and data contractsChoosing data types and precision so rounding never becomes a financial error. Constraints turn the model's rules into guarantees the database itself enforces.
- 019Modeling for change: extensibility without migrationsEnforcing rules with constraints and data contracts rather than hope. Designing for extension avoids the painful migrations that rigid schemas force later.
- 020From logical model to physical schema and DDLDesigning for extension so a new product does not force a painful migration. You finish able to turn a logical model into runnable, constraint-enforced physical DDL.
What you'll be able to do
- Translate trading requirements into a normalized relational model with sound keys and constraints
- Decide deliberately between normalized and dimensional designs for a given workload
- Model temporal and bitemporal history so point-in-time questions are answerable
- Turn a logical model into a physical schema whose constraints make invalid states impossible
- Design for extension so new products do not force painful migrations
Audience & prerequisites
Data engineers, analytics engineers, and ETRM implementers who want the modeling foundation every later course builds on. Basic SQL helps; no trading background is assumed.
Prove the module by building it
Design a normalized ETRM core schema (trades, instruments, counterparties, books) with keys, constraints, and a dimensional mart on top, delivered as runnable DDL and an ER diagram.
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 Data Modeling for ETRM Systems course cover?
The relational and dimensional foundations: entities, keys, normalization, and the canonical trade model every ETRM system rests on. It runs to 20 sequential chapters (chapters 1 to 20 of the 240-chapter bundle).
Where does this sit in the learning path?
It is course 1 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. Design a normalized ETRM core schema (trades, instruments, counterparties, books) with keys, constraints, and a dimensional mart on top, delivered as runnable DDL and an ER diagram.
What technologies are involved?
The course works with PostgreSQL, SQL, ER modeling, dbt, Star schema, 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.