Reference & Master Data Management
Reference data is the quiet foundation every calculation trusts. This course builds a governed golden source of instruments, counterparties, calendars, and hierarchies, with survivorship, versioning, and validation gates that keep bad static data out of trading.
The pipeline at the heart of this course
Reference, master, and transactional data
Not all data is the same kind of thing. Transactional data records events, a trade, a cash flow, a fixing. Reference data describes the slowly changing context those events happen in, currencies, calendars, product taxonomies. Master data is the subset of reference data that is so central and so shared, counterparties, instruments, legal entities, that it deserves a single authoritative source. This course is about getting that master and reference layer right, because it is the substrate every other course depends on.
The distinction is practical, not academic. Transactional data is high-volume and append-heavy; master data is lower-volume but demands governance, because a single wrong counterparty mapping silently corrupts every trade that references it. Modeling and operating these differently is the first lesson.
The operational reality is that reference data is where most trading incidents begin, and the course keeps returning to that fact. A mistyped calendar, a counterparty mapped to the wrong legal entity, an instrument with a stale currency, none of these announce themselves; they surface downstream as a mispriced book or a limit that failed to bite, hours or days later, when the trail back to the cause has gone cold. Building the reference layer well is the cheapest insurance the platform can buy.
The golden record and survivorship
When the same real-world entity arrives from several sources, a counterparty known to three systems under three slightly different names, the system must decide which version is authoritative. That decision is the golden record, and the rules that build it are survivorship rules: which source wins for which attribute, how conflicts are resolved, how a match is even detected in the first place.
You learn match-merge logic, deterministic and probabilistic matching, and how survivorship is expressed as data and rules rather than as someone's memory. The golden record is versioned, so that history is never lost, and every merge is auditable, so that when a downstream number looks wrong the reference data can be reconstructed exactly as it was when the calculation ran.
Survivorship is subtle because the right answer depends on the attribute, not just the record. The most trustworthy source for a counterparty's legal name may differ from the most trustworthy source for its credit rating, so survivorship is expressed attribute by attribute rather than record by record. The course works through how to encode these rules as data so that the golden record is assembled by a repeatable, auditable process rather than by someone's judgment on the day.
Governance, stewardship, and approval
Reference data fails when no one owns it. This course treats stewardship as a first-class design concern: every critical data element has an owner, changes flow through a controlled process, and material changes require four-eyes approval before they take effect. The point is not bureaucracy but blast-radius control, a bad edit to a holiday calendar can misprice an entire book, so the edit path is deliberately governed.
Effective dating and versioning make this safe. Instead of overwriting a value, a change creates a new version with an effective date, so the old value remains available for point-in-time queries. The course shows how this is modeled and how it interacts with the bitemporal patterns from the modeling course.
Governance without tooling collapses under its own weight, so the course pairs stewardship with the mechanics that make it sustainable. Ownership is recorded, changes flow through an approval path proportional to their blast radius, and the whole thing is instrumented so that a steward can see the state of their domain at a glance. The aim is governance that is light enough to be followed and strict enough to matter, which is a design problem as much as a policy one.
Validation gates before trading
The most valuable thing a reference-data service does is refuse bad data. A validation gate sits between ingestion and publication: incoming static data is checked against quality rules, ranges, referential consistency, required fields, plausibility, and anything that fails is quarantined rather than published. This is the single biggest lever on ETRM data quality, because most trading incidents trace back to static data that should never have been accepted.
You build a rules engine that scores incoming data, surfaces breaches on a scorecard, and blocks publication until issues are resolved or explicitly overridden with an audit record. The artifact below sketches the golden-record model and a validation rule.
The validation gate is where the course's philosophy becomes concrete. Incoming data is not trusted because it arrived; it is checked against rules, ranges, referential consistency, plausibility, and anything that fails is quarantined for repair rather than published. The course shows how to make the gate strict without making it brittle, so that genuinely bad data is stopped while merely unusual data is flagged for a human rather than silently dropped.
Distributing reference data
Finally, good reference data has to reach the systems that need it, fast and consistently. The course weighs distribution patterns, a request-time API versus replication into each consumer, and the caching and staleness trade-offs each implies. A curve engine that caches counterparty data for performance must know how stale that cache may be, and the service must be able to invalidate it when a golden record changes.
You leave able to design a reference-data service end to end: ingest, match, govern, validate, version, and serve, with the guarantees the rest of the platform relies on.
Distribution is the last mile, and it is where good reference data is often undone. A perfectly governed golden record is worthless if consumers cache a stale copy and never learn it changed, so the course treats invalidation as seriously as it treats the golden record itself. You finish able to reason about which data can be replicated freely and which must be able to be invalidated the instant it changes, and to build a service that serves both correctly.
Match, merge, and the mechanics of a golden record
The golden record sounds simple until you try to build one. The first hard problem is matching: deciding that a counterparty arriving from a trading system and one arriving from a settlement system are, in fact, the same legal entity. Deterministic matching on a shared identifier is ideal but often unavailable; in its absence you fall back on probabilistic matching across name, address, and other attributes, with scoring thresholds that trade false matches against missed matches. The course makes these trade-offs concrete with worked examples.
Once records are matched, they must be merged, and merging raises the second hard problem: survivorship. When two matched records disagree on an attribute, which wins? The answer is rarely uniform; you might trust one source for legal name and another for credit rating. Survivorship rules encode these decisions as data, per attribute, with source priorities and recency considerations, so that the golden record is reproducible and defensible rather than the product of ad hoc judgment.
Throughout, the merged golden record retains links back to its contributing sources, so that when a downstream number looks wrong, an analyst can trace exactly which source contributed which attribute and when. This lineage is not a nicety; it is what makes the reference layer auditable and what lets you fix the root cause rather than patching the symptom.
The validation gate as the platform's immune system
If there is one idea from this course worth carrying everywhere, it is the validation gate. The premise is that the cheapest place to stop bad data is at the boundary, before it is published to anything that trades. The gate evaluates every incoming record against a battery of rules, format checks, referential checks, range and plausibility checks, cross-field consistency, and quarantines anything that fails rather than letting it through.
The course treats these rules as data, not code, so that a steward can add or adjust a rule without a software release, and so that every rejection is explainable in terms of the specific rule it violated. This matters operationally: when a feed is quarantined at three in the morning, the on-call engineer needs to know exactly why, and a rule expressed as data gives them that immediately.
You also learn the governance around overrides, because sometimes a record that fails a rule is genuinely correct and must be let through. An override is itself recorded as an audited event, with who approved it and why, so that the gate stays both strict and humane. This blend of automation and controlled human judgment is what a mature reference-data function looks like.
Where this course sits, and what it unlocks
Reference data is the second course because it governs the static data every later calculation trusts. The curves course relies on governed curve definitions; the position and credit courses rely on trustworthy counterparty and instrument masters; the STP capstone relies on the validation gate to keep bad static data out of the automated flow. A weakness here surfaces everywhere downstream, usually as an incident.
The course therefore treats reference data as infrastructure, not as a side table. The golden record, the survivorship rules, the versioning, and the validation gate you build here are consumed by the rest of the bundle, and the discipline of governing static data carefully is one you carry into every later course.
Common reference-data failures, and how to avoid them
The classic reference-data failure is the silent bad value: a wrong counterparty mapping, a mis-dated calendar, a corrupted instrument identifier that no one notices until a downstream number is wrong. Because reference data is shared, one bad value corrupts every calculation that touches it, and the damage is proportional to how widely the data is used. The validation gate exists precisely to catch these at the boundary.
A second failure is destructive overwriting, updating a reference value in place so that history is lost and point-in-time reconstruction becomes impossible. The fix is effective-dated versioning, which the course applies throughout. A third is ungoverned change, where anyone can edit critical static data with no approval or audit, turning a single mistake into a firm-wide incident. Stewardship, approval, and audited overrides address this.
Recognizing these patterns lets you build a reference layer that is not just correct today but stays correct and reconstructable as it changes, which is what the rest of the platform depends on.
A worked example: onboarding a new counterparty
Trace a new counterparty through the reference-data service to see the machinery work. The counterparty arrives from a source system, possibly matching an existing entity known under a different name. The matching logic scores the candidate against existing golden records; if it matches, the survivorship rules decide which attributes the incoming record updates and which it defers to; if it does not, a new golden record is created, versioned from its first effective date.
Before any of this is published, the validation gate checks the record: is the legal-entity identifier well formed, is the credit rating a known value, are required fields present. A failure quarantines the record for a steward to repair; a pass publishes it, versioned, with lineage back to its source. Downstream systems then receive it through the service's API or replication, with the caching and staleness guarantees the course established.
This single onboarding walks through match, survivorship, versioning, validation, and distribution, the whole service in miniature, and shows why each step exists: skip any one and a bad or ambiguous counterparty could reach trading and corrupt every trade that references it.
Operational realities: stewardship at scale
Reference data is only as good as the operation that maintains it, and the course closes on that operation. At scale, thousands of reference records change over time, and each change is a potential incident. The stewardship model, clear ownership, controlled change, four-eyes approval on material edits, is what keeps that change safe, and the audit trail is what lets a break be traced to the exact edit that caused it.
The course is realistic about the human side: stewards need tooling that makes the right action easy and the risky action deliberate, quality scorecards that surface problems before they spread, and clear escalation when a validation rule blocks something that turns out to be correct. A reference-data function that is technically sound but operationally unusable will be worked around, which defeats its purpose.
Design trade-offs: API versus replication
A reference-data service has to choose how consumers get their data, and the choice shapes the whole platform. Serving every lookup through an API keeps a single authoritative copy and makes invalidation trivial, but it puts the service on the critical path of every valuation and every limit check, where its latency and availability become everyone's problem. Replicating reference data into each consumer removes that dependency and makes reads local and fast, but it multiplies the copies that can go stale and turns invalidation into a distributed problem.
The course teaches a hybrid that most mature platforms converge on: replicate the slow-changing, high-read bulk into consumers for speed, but route the sensitive, must-be-current elements, a suspended counterparty, a corrected calendar, through a path that can force immediate invalidation. The skill is classifying each data element by how much staleness it can tolerate, because treating all reference data the same is how a stale credit rating slips into a limit check hours after it should have blocked a trade.
Carrying reference data into the capstone
In the STP capstone, reference data is the enrichment stage that sits immediately after capture, and its validation gate is the first place a trade can peel off into the repair queue. A trade that references an unknown counterparty, an unmapped product, or a missing calendar cannot flow straight through, and the quality of the golden source you build here directly determines the platform's straight-through rate. Poor reference data does not announce itself; it shows up as a stubbornly high manual-touch count that no amount of pipeline tuning can fix.
So this course is not a preliminary; it is one of the largest levers on the capstone's headline metric. The governance, survivorship, and validation you build here are what let the automated flow trust its own inputs, and that trust is the precondition for automation at all.
Performance and scale considerations
Reference data is lower-volume than transactional data but sits on the critical path of far more operations, so its performance profile is distinctive. A counterparty or calendar lookup may happen thousands of times per valuation run, which is why the course emphasizes caching and the careful invalidation that makes caching safe. The scale challenge is not raw volume but fan-out: a single reference change can require invalidating caches across many consumers at once.
The golden-record and versioning model also has a scale dimension, because keeping full history means the store grows even as the current view stays small. The course shows how to keep point-in-time queries fast despite accumulated history, through indexing on effective dates and through archiving strategies that keep the hot current data lean. You learn to serve current lookups at low latency while still preserving the complete history that audit depends on.
Testing and validating reference data
The validation gate is itself the primary testing mechanism for reference data, but the course shows how to test the gate as rigorously as the data. Every quality rule is exercised against known-good and known-bad examples, so you can prove that a malformed identifier is rejected and a valid one is accepted, and the survivorship rules are tested against conflicting source records to confirm the golden record is assembled as intended.
Beyond the gate, the course teaches reconciliation as an ongoing test: does the golden record still agree with its trusted sources, have any downstream caches drifted, does the effective-dated history reconstruct correctly. These checks turn reference-data quality from a one-time cleanup into a continuously verified property, which is the only form of data quality that survives contact with production.
How it works, concretely
-- Every master record keeps full version history (effective dated)
CREATE TABLE counterparty_master (
counterparty_id bigint NOT NULL,
version int NOT NULL,
source_system text NOT NULL, -- provenance for survivorship
name text NOT NULL,
lei char(20), -- legal entity identifier
credit_rating text,
effective_from timestamptz NOT NULL,
effective_to timestamptz NOT NULL DEFAULT 'infinity',
is_golden boolean NOT NULL, -- the surviving record
PRIMARY KEY (counterparty_id, version),
CHECK (effective_to > effective_from)
);
-- Only one golden version may be current at any point in time
CREATE UNIQUE INDEX one_current_golden
ON counterparty_master (counterparty_id)
WHERE is_golden AND effective_to = 'infinity';
-- A validation rule, expressed as data, evaluated at the gate
INSERT INTO dq_rule (rule_id, entity, expression, severity) VALUES
('CP-LEI-FORMAT', 'counterparty',
"lei ~ '^[A-Z0-9]{18}[0-9]{2}$'", 'REJECT'),
('CP-RATING-KNOWN', 'counterparty',
"credit_rating IN (SELECT code FROM rating_scale)", 'REJECT');
-- Records failing a REJECT rule are quarantined, never published.Chapters 21 to 40
Twenty sequential chapters. Within the full bundle these are chapters 21 through 40 of 240.
- 021Reference, master, and transactional data distinguishedSeparating transactional events from the slowly changing context they occur in. The distinction drives how each kind of data is stored, governed, and served.
- 022Why bad static data is the root of most ETRM incidentsWhy most trading incidents trace back to static data that was wrong. The chapter grounds why static-data quality is the highest-leverage investment in the platform.
- 023The golden record and single source of truthBuilding one authoritative golden record from many conflicting sources. A golden record is what lets every consumer trust a single authoritative version of an entity.
- 024Instrument master: identifiers and cross-referencesModeling instrument identifiers and the cross-references that link them. Cross-references are what reconcile the many identifiers the same instrument carries across systems.
- 025Counterparty master and the legal-entity hierarchyModeling counterparties and resolving them to a legal-entity hierarchy. The legal-entity hierarchy is what lets credit and exposure roll up correctly.
- 026Product and commodity taxonomiesStructuring product and commodity taxonomies that stay consistent. A clean taxonomy is what makes cross-product aggregation and reporting possible.
- 027Holiday calendars and business-day conventionsEncoding holiday calendars and business-day conventions used everywhere. Calendar errors misprice whole books, so business-day conventions are modeled carefully.
- 028Location, hub, and delivery-point reference dataModeling locations, hubs, and delivery points as governed reference data. Delivery-point data is what physical trades depend on to mean anything at settlement.
- 029Curve definitions and market-data referenceDefining curves and the market-data reference that valuation depends on. Curve definitions connect reference data to the market-data and valuation courses.
- 030Survivorship rules and match-merge logicWriting survivorship and match-merge rules that decide which value wins. Survivorship rules turn conflicting source records into one defensible golden version.
- 031Data stewardship, ownership, and governanceAssigning ownership and stewardship so every element has an accountable owner. Clear ownership is what stops reference data from decaying into an untrusted mess.
- 032Versioning static data and effective datingVersioning static data with effective dating instead of overwriting it. Effective dating preserves the past so a change never destroys the history that depended on it.
- 033Change management and four-eyes approvalControlling change with four-eyes approval to bound the blast radius. Four-eyes approval bounds the blast radius of a single mistaken edit.
- 034Data-quality rules, scorecards, and thresholdsScoring data quality with rules, scorecards, and thresholds. Scorecards make data quality visible and measurable rather than assumed.
- 035Validation gates before data reaches tradingPlacing a validation gate that quarantines bad data before it reaches trading. The validation gate is the single biggest lever on keeping bad data out of trading.
- 036Distributing reference data: APIs vs replicationChoosing between request-time APIs and replication to distribute data. The choice between API and replication shapes latency, coupling, and staleness across the platform.
- 037Caching, consistency, and staleness trade-offsBalancing caching, consistency, and staleness across consumers. You learn to classify each element by how much staleness it can safely tolerate.
- 038Auditability and lineage for static dataMaking static data fully auditable with lineage back to source. Lineage for static data is what lets a downstream number be explained back to its source.
- 039Integrating external vendors and feedsIntegrating external vendor feeds without losing provenance. External feeds are integrated behind the same validation and governance as internal data.
- 040Designing a reference-data service end to endAssembling ingest, match, govern, validate, and serve into one service. You finish able to build a reference service other teams can genuinely rely on.
What you'll be able to do
- Model a golden record with survivorship rules across conflicting sources
- Version static data with effective dating instead of destructive overwrites
- Design a validation gate that quarantines bad data before it reaches trading
- Govern change with stewardship, approval, and audited overrides
- Serve reference data through APIs and caches with controlled staleness
Audience & prerequisites
Data engineers and platform teams responsible for the static data that trading trusts. Useful for anyone who has debugged an incident that turned out to be bad reference data.
Prove the module by building it
Build a reference-data service with a golden-record model, survivorship rules, versioned history, and a validation gate that rejects bad static data before it reaches trading.
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 Reference & Master Data Management course cover?
The golden source of instruments, counterparties, calendars, and hierarchies that every downstream calculation trusts. It runs to 20 sequential chapters (chapters 21 to 40 of the 240-chapter bundle).
Where does this sit in the learning path?
It is course 2 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. Build a reference-data service with a golden-record model, survivorship rules, versioned history, and a validation gate that rejects bad static data before it reaches trading.
What technologies are involved?
The course works with MDM, Data quality, Airflow, SQL, REST APIs, 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.