Reference Data Management

A final true-up recomputes a trading day from ninety days ago and prices three thousand intervals against a pricing node that was retired last month — the node no longer exists in the current master table, the lookup returns null, and the run either crashes or silently drops those charges. That failure has nothing to do with the pricing math; it is a reference-data failure, and it is the single most common reason a replayed settlement no longer reconciles to the number the ISO published at the time. Reference Data Management is the subsystem that prevents it: it treats every slowly-changing attribute the engine depends on — pricing nodes, settlement calendars, tariff and rate versions, counterparty and account maps — as effective-dated, versioned records so that an as-of lookup for any historical date returns exactly the value that was in force then, not the value in force today. Within the Core Architecture & Market Taxonomy for Energy Settlements framework, this component sits underneath every calculation stage: the pricing, loss, and imbalance engines are only reproducible if the master data they read is itself reproducible.

The distinction that makes this hard is between transactional data — a metered interval, a trade, a cleared price, which is naturally timestamped and never changes — and reference data, which describes the world (which node maps to which meter, which rate applies to which service class) and mutates through business events that carry no natural timestamp on the fact itself. A node gets re-mapped, a tariff sheet is refiled, an account changes owner; each event has a date it takes effect, and settlement must honour that date. The diagram below traces how a reference feed becomes a queryable, effective-dated master.

Everything downstream inherits its reproducibility from this layer. When Pricing Logic Implementation maps a settlement point to a pricing node, or Settlement Cycle Mapping resolves which cycle a trading day belongs to, the value it receives must be the one that was authoritative on that trading day. The sections below cover the standards that govern these master domains, a step-by-step implementation of an effective-dated store, the edge cases that break naive versioning, the alerting that catches a bad feed before it corrupts a run, and the reconciliation discipline that proves the master is intact.

Specification & Standards #

No single standard dictates how you store reference data, but several dictate what the data must say, and the master has to carry enough provenance to prove it matched the authoritative source on any historical date.

• Pricing node registries. Each ISO/RTO publishes and continuously revises its list of pricing nodes (pnodes), aggregates, hubs, and zones — PJM’s node list, CAISO’s Full Network Model, MISO’s CPNode registry, ERCOT’s settlement point list. Nodes are added when the network model changes, retired when equipment is decommissioned, and occasionally re-parented between zones. The field-level shape of each feed is catalogued under ISO/RTO Data Format Standards; the versioning obligation is that a retired node must remain resolvable for every trading day it was live.
• Tariff and rate revisions. FERC-jurisdictional tariffs are filed and revised through the eTariff system, each revision carrying an explicit effective date. Retail rate schedules move through state PUC dockets on their own effective dates. A settlement run for a past day must apply the tariff revision that was effective on that day, which is exactly why the calculation engine treats the tariff as versioned input rather than hard-coded logic.
• Settlement calendars. The mapping from a trading day to preliminary, final, and resettlement windows is itself reference data that shifts with holidays, DST boundaries, and tariff amendments to the settlement timeline. These calendars are consumed by Settlement Cycle Mapping and must be effective-dated so a re-run reconstructs the timeline the market actually used.
• Counterparty and account identity. Legal entity identifiers, ISO account numbers, and internal book mappings change through novations, mergers, and re-registrations. NAESB business practices define how counterparties are identified in scheduling and interchange; the master must preserve which entity owned an account on the trading day, independent of who owns it now. Read and write access to these identity records is governed by Security & Access Boundaries.

The pattern that satisfies all four is the slowly-changing dimension, type 2 (SCD-2). Rather than overwriting a record when an attribute changes, SCD-2 closes the current version by stamping its effective_to, and inserts a new version with a fresh effective_from. Every version is retained; the “current” row is simply the one whose validity interval is open. The table below fixes the natural key and versioning strategy per reference domain — this is the contract the rest of the engine keys off.

The loss-factor row bridges into Loss Factor Mapping Strategies, which reads the same as-of resolution to prove which multiplier was in force. The concrete build of the first row — a fully versioned pricing node master with as-of lookup — is detailed in Building a versioned pricing node master.

Step-by-Step Implementation #

An effective-dated store is built in four disciplined steps. Each is a pure transformation over the incoming feed and the existing master, so a re-ingestion of the same feed is a no-op — the load is idempotent, and running it twice never creates a spurious version.

Step 1 — Normalize the feed to a canonical schema and natural key. Before any comparison, every incoming record is coerced to the canonical column set and its natural key extracted. A feed that arrives with a renamed column or a changed identifier scheme is rejected to a dead-letter path rather than silently mis-keyed.

import pandas as pd
from decimal import Decimal

CANONICAL_COLS = ["iso", "pnode_id", "pnode_name", "zone", "voltage_kv", "status"]

def normalize_pnode_feed(raw: pd.DataFrame) -> pd.DataFrame:
    """Coerce a raw pnode export to the canonical schema and natural key."""
    missing = set(CANONICAL_COLS) - set(raw.columns)
    if missing:
        raise ValueError(f"pnode feed missing required columns: {sorted(missing)}")
    feed = raw[CANONICAL_COLS].copy()
    feed["pnode_id"] = feed["pnode_id"].astype(str).str.strip()
    feed["iso"] = feed["iso"].astype(str).str.upper().str.strip()
    # Natural key is (iso, pnode_id); it must be unique within a single feed.
    if feed.duplicated(subset=["iso", "pnode_id"]).any():
        raise ValueError("duplicate natural key in a single pnode feed snapshot")
    return feed

Step 2 — Detect real attribute changes against the current version. The load compares each incoming record to the open (current) version for its natural key. Only a genuine change to a tracked attribute opens a new version; a byte-identical record is ignored. This is where naive pipelines go wrong — they version on every feed arrival, exploding the table and destroying the meaning of an effective date.

TRACKED = ["pnode_name", "zone", "voltage_kv", "status"]

def diff_against_current(feed: pd.DataFrame, current: pd.DataFrame) -> pd.DataFrame:
    """Return only the feed rows whose tracked attributes actually changed."""
    key = ["iso", "pnode_id"]
    merged = feed.merge(current, on=key, how="left", suffixes=("", "_cur"),
                        indicator=True)
    is_new = merged["_merge"] == "left_only"
    changed = pd.Series(False, index=merged.index)
    for col in TRACKED:
        # NaN-safe inequality: a change on any tracked attribute flips the row.
        changed |= merged[col].astype(str) != merged[f"{col}_cur"].astype(str)
    to_version = merged[is_new | (changed & ~is_new)]
    return to_version[key + TRACKED]

Step 3 — Close the prior version and open the new one. For each changed key, the current version’s effective_to is stamped one instant before the new version’s effective_from, so the validity intervals abut without overlapping. Retirements are handled by closing the version without opening a successor — the node stops being current but remains resolvable for its historical window.

from datetime import datetime, timezone

# Open versions carry a sentinel far-future effective_to so an as-of BETWEEN works.
OPEN_END = pd.Timestamp("2999-12-31", tz="UTC")

def apply_scd2(master: pd.DataFrame, changes: pd.DataFrame,
               effective_from: pd.Timestamp) -> pd.DataFrame:
    """Close superseded versions and append new ones; append-only, no in-place edit."""
    key = ["iso", "pnode_id"]
    closing = effective_from - pd.Timedelta(seconds=1)
    # Close the open version for every changed key.
    open_mask = master["effective_to"] == OPEN_END
    to_close = master[key].merge(changes[key], on=key).drop_duplicates()
    close_idx = master.index[open_mask & master.set_index(key).index.isin(
        to_close.set_index(key).index)]
    master.loc[close_idx, "effective_to"] = closing
    # Append the new versions with an open validity interval.
    new_rows = changes.copy()
    new_rows["effective_from"] = effective_from
    new_rows["effective_to"] = OPEN_END
    new_rows["loaded_at"] = datetime.now(timezone.utc)
    return pd.concat([master, new_rows], ignore_index=True)

Step 4 — Serve an as-of lookup. Downstream engines never touch the raw master; they call a single as-of resolver that, given a natural key and a trading date, returns the one version whose validity interval contains that date. Formally, the resolved version for entity \(e\) on date \(d\) is the row satisfying \(effective_from \le d < effective_to\), which the half-open interval guarantees is unique.

def as_of(master: pd.DataFrame, iso: str, pnode_id: str,
          trade_date: pd.Timestamp) -> pd.Series:
    """Resolve the version in force on trade_date for one natural key."""
    rows = master[(master["iso"] == iso) & (master["pnode_id"] == pnode_id)
                  & (master["effective_from"] <= trade_date)
                  & (master["effective_to"] > trade_date)]
    if len(rows) == 0:
        raise KeyError(f"no pnode version for {iso}:{pnode_id} as-of {trade_date.date()}")
    if len(rows) > 1:
        raise ValueError(f"overlapping versions for {iso}:{pnode_id} — integrity broken")
    return rows.iloc[0]

The two guards in as_of are not defensive noise: a zero-row result means a run is asking for a node that did not exist on its trading day (a real reconciliation break), and a multi-row result means the append logic let two validity intervals overlap (a corruption that must halt the run, never be papered over by picking the first).

Edge Cases & Failure Modes #

Effective-dating is deceptively simple until real business events arrive out of order. The table below catalogues the failure modes that a production master must handle explicitly, each with the symptom it produces if ignored.

Retroactive corrections are the sharpest of these. When an ISO refiles a node’s zone assignment with an effective date in the past, the master must open a version back-dated to that effective date, which changes the answer for trading days that already settled. The correct response is not to overwrite history but to insert the corrected version and emit the list of affected trading days so the true-up process re-runs exactly those days.

def flag_affected_days(master: pd.DataFrame, iso: str, pnode_id: str,
                       corrected_from: pd.Timestamp) -> list:
    """List trade days whose settled result may change under a back-dated correction."""
    later = master[(master["iso"] == iso) & (master["pnode_id"] == pnode_id)
                   & (master["effective_to"] > corrected_from)]
    if later.empty:
        return []
    span_start = corrected_from.normalize()
    span_end = pd.Timestamp.now(tz="UTC").normalize()
    return list(pd.date_range(span_start, span_end, freq="D"))

The late-arriving-feed case is subtler: if a feed for an earlier effective date lands after a later one has already been loaded, a naive append leaves two open or overlapping intervals. The load must sort all versions for a key by effective_from and re-derive each effective_to from the next version’s start, so the timeline stays a clean, gapless, non-overlapping sequence regardless of arrival order.

Thresholds & Alerting #

A reference-data feed is a silent dependency — nothing errors when it goes stale, the engine simply keeps resolving yesterday’s world. The alerting tier exists to make staleness and anomalous churn loud before a run consumes bad data.

• Staleness threshold. Each domain has an expected refresh cadence (pnode lists weekly, tariffs event-driven, calendars annually with amendments). If a feed’s newest loaded_at exceeds its cadence tolerance, raise a review-required alert; block the run only if the domain is on the critical path for the trading day being settled.
• Churn threshold. A feed that suddenly changes 40% of pnode zones is far more likely to be a corrupt export than a real network event. Compare the fraction of natural keys that changed against a configurable ceiling (a typical band is 0–5% for pnodes, higher for tariff-heavy filing days) and quarantine the feed for manual sign-off above the ceiling rather than versioning it automatically.
• Orphan-reference threshold. After each load, count settlement points that no longer resolve to any current pnode version. A non-zero count on the active book is a block-invoice alert — those charges cannot be priced.
• Escalation routing. Informational alerts (routine version churn within band) log only. Review-required alerts (staleness, mild churn) route to the reference-data steward. Block-invoice alerts (orphan references, overlapping versions) page the on-call settlement analyst and hold the affected run. This mirrors the tiered routing configured across Threshold Tuning & Alerts.

from decimal import Decimal

def churn_alert(changes: pd.DataFrame, feed: pd.DataFrame,
                ceiling: Decimal = Decimal("0.05")) -> dict:
    """Quarantine a feed whose change fraction exceeds the churn ceiling."""
    total = len(feed)
    churn = Decimal(len(changes)) / Decimal(total) if total else Decimal("0")
    tier = "block" if churn > ceiling else "info"
    return {"churn_fraction": churn, "tier": tier,
            "action": "quarantine" if tier == "block" else "auto_version"}

Note that even the churn ratio is computed with Decimal — while it is not money, it feeds a threshold decision that must be reproducible to the same digit on a replay, and mixing binary-float ratios into an audit-relevant gate reintroduces exactly the drift the money math avoids.

Testing & Reconciliation #

The master proves its own integrity through invariants that hold regardless of load history, and through a shadow reconciliation against the authoritative source.

The temporal-integrity invariant is the foundation: for every natural key, the set of version intervals must be non-overlapping and gapless, with exactly one open interval. A single SQL-style assertion over the loaded frame catches any append bug before a run reads corrupt data.

def assert_temporal_integrity(master: pd.DataFrame) -> None:
    """For each natural key, versions must be gapless, non-overlapping, one open."""
    for (iso, pnode_id), grp in master.sort_values("effective_from").groupby(
            ["iso", "pnode_id"]):
        ordered = grp.sort_values("effective_from").reset_index(drop=True)
        for i in range(len(ordered) - 1):
            # Each version must end exactly where the next begins (gapless, no overlap).
            gap = ordered.loc[i + 1, "effective_from"] - ordered.loc[i, "effective_to"]
            assert gap == pd.Timedelta(seconds=1), (
                f"timeline break for {iso}:{pnode_id} at version {i}")
        open_count = (ordered["effective_to"] == OPEN_END).sum()
        assert open_count <= 1, f"{iso}:{pnode_id} has {open_count} open versions"

The shadow reconciliation confirms the current version set equals the ISO’s live registry. Pull the authoritative pnode list, filter the master to open versions, and diff on the natural key: any key present in one but not the other is a discrepancy to investigate before the next run. A clean diff is the signal that the master is a faithful mirror, and the digest of the current version set can be hashed and stored per load so a later audit proves which master state a run consumed — the same tamper-evident discipline the Settlement Calculation & Validation Engines apply to line items. Unit tests should cover the four canonical scenarios: a first load (all new versions), an idempotent re-load (zero versions), a single attribute change (one close, one open), and a retirement (one close, no open successor).

Frequently Asked Questions #

Why not just overwrite reference data when it changes? #

Because a settlement run for a past trading day must resolve the value that was in force then. Overwriting destroys the historical answer, so a final true-up or a dispute re-run silently applies today’s mapping to yesterday’s energy and no longer reconciles to the number the ISO published. SCD-2 keeps every version and stamps its validity interval, so an as-of lookup always returns the historically correct value.

What is the difference between the effective date and the load date? #

The effective_from is the business date a value becomes true in the world — when a tariff revision takes effect or a node re-parents. The loaded_at is the wall-clock instant your pipeline ingested it. They differ whenever a feed arrives late or a correction is back-dated, and settlement must key on the effective date, never the load date. Storing both lets you answer “what was true on the trading day” and “when did we learn it” independently.

How does a retired pricing node stay resolvable? #

A retirement closes the node’s current version by stamping its effective_to but never deletes the row and never opens a successor. The node stops appearing in the current (open) version set, so new runs will not reference it, but every historical trading day within its old validity window still resolves through the as-of lookup. Deleting a retired node is the classic bug that makes an old run crash or drop charges.

How do you handle a correction that changes an already-settled day? #

Insert a new version with a back-dated effective_from matching the correction’s effective date, leaving the superseded version intact for the record. The load then emits the list of trading days whose validity intervals the correction touches, and those specific days are routed to resettlement — recomputing only the affected days rather than the whole history, with the correction flagged in the ledger for audit.

Related #

• Core Architecture & Market Taxonomy for Energy Settlements
• Settlement Cycle Mapping
• Building a versioned pricing node master
• Pricing Logic Implementation