Building a versioned pricing node master

A settlement re-run for a trading day two months old maps a meter to pnode AECO_LOAD, but that node was re-parented from the AECO zone to PSEG three weeks ago — query the master as it stands today and you settle the old day against the wrong zone, and the charge no longer reconciles. This guide builds the store that removes that whole class of bug: a slowly-changing-dimension type-2 (SCD-2) pricing node master where every version carries an effective_from/effective_to interval and an as-of lookup returns exactly the version in force on any date. It is the concrete build behind Reference Data Management, and the resolver it exposes is what Pricing Logic Implementation calls to bind a settlement point to the node that was authoritative on the trading day.

The flow below is the loop this page implements: a fresh pnode snapshot is diffed against the open versions, changed keys are closed and reopened, and the master answers as-of queries against a persistent table.

Prerequisites #

• Python packages: the standard library only — sqlite3 for durable storage, decimal for the per-node money field, datetime for effective dating, and hashlib for the load digest. pandas is optional and used only if your snapshot arrives as a DataFrame; the core store is dependency-free.
• Data dependencies: a periodic pnode snapshot from the ISO registry (PJM node list, CAISO Full Network Model, MISO CPNode export, or ERCOT settlement point list) carrying at minimum the node identifier, zone, voltage, status, and — where the tariff defines one — a per-node settlement adder in $/MWh. Every snapshot must be a complete current picture, not a delta.
• Permissions / access: read-only credentials for the market operator’s data portal to pull the snapshot, and write access to the master’s storage path. No market-submission scope is required — this store is consumed by pricing, it does not file anything back to the ISO.

Implementation #

The store is a single class. The natural key is (iso, pnode_id); each row is one version with a half-open validity interval \([effective_from, effective_to)\), and the open version uses a far-future sentinel so an as-of BETWEEN resolves it. The per-node settlement_adder is money, so it is carried as a Decimal end to end and stored as a fixed-precision string — never a float column, which would reintroduce binary drift into a value that lands in a charge. The load is idempotent: re-applying the same snapshot detects no change and writes nothing.

import sqlite3
import hashlib
from datetime import datetime, timezone, date
from decimal import Decimal
from typing import Optional, Sequence

# Far-future sentinel for an open version so BETWEEN/half-open lookups work.
OPEN_END = date(2999, 12, 31)
# Attributes whose change opens a new version. Money field included deliberately.
TRACKED = ("zone", "voltage_kv", "status", "settlement_adder")


class PnodeMaster:
    """SCD-2 versioned pricing node master backed by SQLite."""

    def __init__(self, db_path: str = "pnode_master.db"):
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self._init_schema()

    def _init_schema(self) -> None:
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS pnode_version (
                iso              TEXT NOT NULL,
                pnode_id         TEXT NOT NULL,
                zone             TEXT,
                voltage_kv       REAL,
                status           TEXT,
                settlement_adder TEXT,          -- Decimal serialized as string ($/MWh)
                effective_from   TEXT NOT NULL, -- ISO date
                effective_to     TEXT NOT NULL, -- ISO date, OPEN_END while current
                loaded_at        TEXT NOT NULL
            )""")
        # Enforce at most one open version per natural key at the storage layer.
        self.conn.execute("""
            CREATE UNIQUE INDEX IF NOT EXISTS ux_open_version
            ON pnode_version(iso, pnode_id)
            WHERE effective_to = '2999-12-31'""")
        self.conn.commit()

    def _current(self, iso: str, pnode_id: str) -> Optional[sqlite3.Row]:
        cur = self.conn.execute(
            "SELECT * FROM pnode_version WHERE iso=? AND pnode_id=? "
            "AND effective_to=?", (iso, pnode_id, OPEN_END.isoformat()))
        return cur.fetchone()

    @staticmethod
    def _changed(current: sqlite3.Row, incoming: dict) -> bool:
        """True if any tracked attribute differs. Money compared as Decimal."""
        for attr in TRACKED:
            cur_val, new_val = current[attr], incoming[attr]
            if attr == "settlement_adder":
                # Compare as Decimal so '1.50' and '1.5' are equal, not string-unequal.
                if Decimal(str(cur_val)) != Decimal(str(new_val)):
                    return True
            elif str(cur_val) != str(new_val):
                return True
        return False

    def apply_snapshot(self, iso: str, rows: Sequence[dict],
                       effective_from: date) -> dict:
        """
        Idempotently version a full snapshot. Each row: pnode_id, zone,
        voltage_kv, status, settlement_adder (Decimal). Returns load stats.
        """
        loaded_at = datetime.now(timezone.utc).isoformat()
        closing = effective_from.replace(day=effective_from.day)  # same-day close guard
        opened = closed = 0

        for row in rows:
            row = dict(row)
            # Normalize the money field to a fixed-precision Decimal string.
            adder = Decimal(str(row["settlement_adder"])).quantize(Decimal("0.0001"))
            row["settlement_adder"] = str(adder)
            current = self._current(iso, row["pnode_id"])

            if current is None:
                self._insert(iso, row, effective_from, loaded_at)
                opened += 1
            elif self._changed(current, row):
                # Close the prior version one day before the new one opens.
                prior_end = _prev_day(effective_from)
                self.conn.execute(
                    "UPDATE pnode_version SET effective_to=? "
                    "WHERE iso=? AND pnode_id=? AND effective_to=?",
                    (prior_end.isoformat(), iso, row["pnode_id"], OPEN_END.isoformat()))
                self._insert(iso, row, effective_from, loaded_at)
                closed += 1
                opened += 1
            # else: byte-identical version — idempotent no-op.

        self.conn.commit()
        return {"opened": opened, "closed": closed, "digest": self._digest(iso)}

    def _insert(self, iso: str, row: dict, eff_from: date, loaded_at: str) -> None:
        self.conn.execute(
            "INSERT INTO pnode_version (iso, pnode_id, zone, voltage_kv, status, "
            "settlement_adder, effective_from, effective_to, loaded_at) "
            "VALUES (?,?,?,?,?,?,?,?,?)",
            (iso, row["pnode_id"], row["zone"], row["voltage_kv"], row["status"],
             row["settlement_adder"], eff_from.isoformat(), OPEN_END.isoformat(),
             loaded_at))

    def as_of(self, iso: str, pnode_id: str, trade_date: date) -> dict:
        """Resolve the single version in force on trade_date. Money as Decimal."""
        cur = self.conn.execute(
            "SELECT * FROM pnode_version WHERE iso=? AND pnode_id=? "
            "AND effective_from<=? AND effective_to>?",
            (iso, pnode_id, trade_date.isoformat(), trade_date.isoformat()))
        found = cur.fetchall()
        if not found:
            raise KeyError(f"no pnode version for {iso}:{pnode_id} "
                           f"as-of {trade_date.isoformat()}")
        if len(found) > 1:
            raise ValueError(f"overlapping versions for {iso}:{pnode_id} — integrity broken")
        rec = dict(found[0])
        rec["settlement_adder"] = Decimal(rec["settlement_adder"])
        return rec

    def _digest(self, iso: str) -> str:
        """SHA-256 over the current version set — a tamper-evident master fingerprint."""
        cur = self.conn.execute(
            "SELECT pnode_id, zone, voltage_kv, status, settlement_adder "
            "FROM pnode_version WHERE iso=? AND effective_to=? "
            "ORDER BY pnode_id", (iso, OPEN_END.isoformat()))
        canonical = ";".join("|".join(str(v) for v in r) for r in cur.fetchall())
        return hashlib.sha256(canonical.encode()).hexdigest()


def _prev_day(d: date) -> date:
    from datetime import timedelta
    return d - timedelta(days=1)

The settlement_adder is the reason Decimal threads through the whole class. That per-node charge lands directly in a settlement line item, so representing it as a float — even in a table that is 90% descriptive text — would let a 1.5 versus 1.50 mismatch either miss a real change or fabricate a phantom one, and would seed rounding drift when the adder is later multiplied by delivered MWh. Comparing and storing it as a quantized Decimal keeps both the change detection and the eventual money math exact.

Verification #

Confirm the store behaves before any run resolves against it:

1. Idempotent load. Apply the same snapshot twice with the same effective_from. The second call must return {"opened": 0, "closed": 0} and leave the row count unchanged — proof the diff suppresses no-op versions.
2. Change opens exactly one version. Change one node’s zone, re-apply, and assert one close and one open: the prior version’s effective_to is now the day before, and a new open version exists.
3. As-of correctness across a boundary. A node re-parented on 2026-06-01 must resolve to the old zone for 2026-05-31 and the new zone for 2026-06-01 — the half-open interval makes the boundary day unambiguous.
4. Digest stability. The digest returned for an unchanged current set must be identical across loads; a changed digest with zero opened versions signals silent corruption.

def verify_master(master: PnodeMaster) -> None:
    """Exercise the SCD-2 invariants end to end."""
    snap_v1 = [{"pnode_id": "AECO_LOAD", "zone": "AECO", "voltage_kv": 230.0,
                "status": "ACTIVE", "settlement_adder": Decimal("1.5000")}]
    r1 = master.apply_snapshot("PJM", snap_v1, date(2026, 5, 1))
    assert r1 == {"opened": 1, "closed": 0, "digest": r1["digest"]}

    # Idempotent re-load: no new versions.
    r2 = master.apply_snapshot("PJM", snap_v1, date(2026, 5, 1))
    assert (r2["opened"], r2["closed"]) == (0, 0)
    assert r2["digest"] == r1["digest"]        # digest stable when nothing changed

    # Re-parent on 2026-06-01: one close, one open.
    snap_v2 = [{**snap_v1[0], "zone": "PSEG"}]
    r3 = master.apply_snapshot("PJM", snap_v2, date(2026, 6, 1))
    assert (r3["opened"], r3["closed"]) == (1, 1)

    # As-of resolves each side of the boundary correctly.
    assert master.as_of("PJM", "AECO_LOAD", date(2026, 5, 31))["zone"] == "AECO"
    assert master.as_of("PJM", "AECO_LOAD", date(2026, 6, 1))["zone"] == "PSEG"
    # Money field returns as Decimal, not float.
    assert master.as_of("PJM", "AECO_LOAD", date(2026, 6, 1))["settlement_adder"] \
        == Decimal("1.5000")
    print("pnode master invariants hold")

Running verify_master(PnodeMaster(":memory:")) should print the confirmation with no assertion failure. The unique partial index on the open interval is a second line of defence: if a load bug ever tried to leave two open versions for one key, SQLite rejects the insert rather than letting the as-of resolver later raise on an overlap.

Compliance Note #

This store is what makes a settlement run reproducible under FERC traceability: every priced charge must trace to a metered interval, a published price, and the reference data in force on the trading day, and an SCD-2 master with an as-of resolver is how the last of those is proven rather than assumed. The settlement_adder and zone assignment resolved for any past day must match the version that was authoritative then, so the per-load SHA-256 digest should be persisted alongside each settlement run’s audit record — a later audit or dispute can then confirm which master state the run consumed, satisfying the same tamper-evident expectation the Settlement Calculation & Validation Engines hold their line items to. Because node re-parenting and retirement are frequently filed with retroactive effective dates, a back-dated version must trigger resettlement of the affected trading days through Settlement Cycle Mapping rather than a silent overwrite, and the pnode identifiers must stay reconciled to the ISO’s published registry so a settlement point never resolves to a node the market operator does not recognize.

Frequently Asked Questions #

Why store the settlement adder as a Decimal string instead of a REAL column? #

Because it is money and it feeds a charge. A REAL (float) column cannot represent most decimal $/MWh values exactly, so a stored 1.50 can read back as 1.4999999 — which both breaks the change-detection comparison and seeds rounding drift when the adder is multiplied by delivered MWh downstream. Storing the quantized Decimal as a string and rehydrating it to Decimal on read keeps the value exact through versioning and settlement.

How does the as-of lookup guarantee it returns exactly one version? #

The validity intervals are half-open, [effective_from, effective_to), and the load always closes a prior version the day before the successor opens, so for any date the intervals partition the timeline without overlap or gap. The as_of query selects rows where effective_from <= trade_date < effective_to, which can match only one version; a multi-row result means the append logic corrupted the timeline and the method raises rather than guessing.

What happens when a node is retired? #

Retirement closes the node’s open version by stamping its effective_to and inserts no successor, so it disappears from the current version set that new runs read, but every historical trade date within its old validity window still resolves through as_of. The node is never deleted — deleting it is exactly the bug that makes an old true-up crash or silently drop that node’s charges.

Related #

• Reference Data Management
• Settlement Cycle Mapping
• Pricing Logic Implementation