CSV vs Parquet for settlement ledger storage

A month-end reconciliation job reads back last quarter’s settled charges, sums a DELTA_AMOUNT column, and the total is off by three cents from the figure that was posted in April — not because the arithmetic changed, but because the ledger was written to CSV and the reader inferred those cent-quantized strings as 64-bit floats. Choosing the storage format for an append-only settlement ledger is not a cosmetic decision: it determines whether a stored Decimal survives a round-trip, how fast a nine-month audit query scans, and whether re-reading a file reproduces the exact bytes an auditor signed off on. This guide sits under Settlement Cycle Mapping and compares CSV against Apache Parquet for exactly that persistence layer.

The diagram traces the same ledger row through both formats, showing where the CSV path risks a typing loss that the Parquet path avoids by carrying an explicit schema.

The two formats are not interchangeable for money #

CSV is a row-oriented text encoding: every value, including a -50.00 charge delta, is serialized as characters with no attached type. Whatever type discipline the writer applied is discarded at the file boundary, and the reader reconstructs types by inference or by an out-of-band schema the file itself does not carry. Parquet is a binary, columnar, self-describing format: each column stores a physical type and, for money, a decimal logical type with fixed precision and scale, so a value written as Decimal("-50.00") is read back as the identical Decimal. That single structural difference — schema carried inside the file versus reconstructed outside it — drives almost every entry in the decision below, and it is the same schema-enforcement discipline the Reference Data Management component applies to versioned master data.

Decision matrix #

The table is the core of this guide: score your ledger against the dimension that actually binds your workload, not the format you already have tooling for.

The pattern most settlement teams converge on falls straight out of the last two rows: accept CSV at the ingest boundary because that is what ISO/RTO portals emit, then persist the validated, typed ledger as Parquet part files. The comparison that follows for trade feeds — Parsing CSV vs XML trade feeds with pandas — reaches the same split between wire format and storage format.

Prerequisites #

• Python packages: pandas>=2.0, pyarrow>=14 (the Parquet engine and the Decimal-aware Arrow type system), plus the standard-library decimal and hashlib modules. pyarrow ships the decimal128 type that maps cleanly to Python Decimal.
• Data dependency: a settlement ledger frame with at least OPERATING_DAY, NODE_ID, SETTLEMENT_CYCLE, REVISION_SEQ, and a signed DELTA_AMOUNT column already quantized to the cent — the row shape produced by the mapping in How to map PJM settlement cycles to internal ledgers.
• Permissions: write access to the append-only ledger store (a partitioned directory, object bucket, or warehouse stage) and read access for the reconciliation job.

Implementation #

Both snippets round-trip the same frame. The point is to keep every amount as Decimal on the way out and get an identical Decimal on the way back — the failure the whole comparison turns on.

Writing and reading CSV without a float leak #

import pandas as pd
from decimal import Decimal
import hashlib

CENTS = Decimal("0.01")

# A minimal settled-ledger frame; DELTA_AMOUNT is already cent-quantized Decimal.
ledger_df = pd.DataFrame({
    "OPERATING_DAY": ["2026-04-01", "2026-04-01", "2026-04-02"],
    "NODE_ID": ["PJM_WEST_HUB", "PJM_WEST_HUB", "PJM_WEST_HUB"],
    "SETTLEMENT_CYCLE": ["RT", "RT", "RT"],
    "REVISION_SEQ": [0, 1, 0],
    "DELTA_AMOUNT": [Decimal("1200.00"), Decimal("-50.00"), Decimal("875.25")],
})


def write_ledger_csv(df: pd.DataFrame, path: str) -> None:
    """Serialize the ledger to CSV. Decimals render as exact text; no rounding here."""
    # Pin quoting and line terminator so the bytes are reproducible across platforms.
    df.to_csv(path, index=False, lineterminator="\n")


def read_ledger_csv(path: str) -> pd.DataFrame:
    """Read the ledger back. The critical control: DELTA_AMOUNT must NOT be inferred.

    Passing dtype=str keeps the amount as text so pandas never routes it through a
    64-bit float, then we cast explicitly to Decimal. Reading without this guard is
    exactly how cent values acquire binary drift.
    """
    df = pd.read_csv(path, dtype={"DELTA_AMOUNT": str, "NODE_ID": str,
                                  "SETTLEMENT_CYCLE": str, "OPERATING_DAY": str})
    df["DELTA_AMOUNT"] = df["DELTA_AMOUNT"].map(lambda v: Decimal(v).quantize(CENTS))
    df["REVISION_SEQ"] = df["REVISION_SEQ"].astype(int)
    return df

The comment carries the whole risk: pd.read_csv with no dtype map will happily parse -50.00 as numpy.float64, and from that point the cent guarantee is gone. CSV can be correct for money, but only because you disabled its default type inference — the format gives you no help.

Writing and reading Parquet with a native decimal type #

import pyarrow as pa
import pyarrow.parquet as pq

# Declare the schema once. decimal128(18, 2) preserves 16 integer digits + 2 cents.
LEDGER_SCHEMA = pa.schema([
    ("OPERATING_DAY", pa.string()),
    ("NODE_ID", pa.string()),
    ("SETTLEMENT_CYCLE", pa.string()),
    ("REVISION_SEQ", pa.int32()),
    ("DELTA_AMOUNT", pa.decimal128(18, 2)),
])


def write_ledger_parquet(df: pd.DataFrame, path: str) -> None:
    """Write an immutable Parquet part file with an explicit decimal column.

    Arrow maps Python Decimal straight onto decimal128; ZSTD is deterministic given
    a fixed level, which matters for byte-stable audit reproduction.
    """
    table = pa.Table.from_pandas(df, schema=LEDGER_SCHEMA, preserve_index=False)
    pq.write_table(table, path, compression="zstd", compression_level=3)


def read_ledger_parquet(path: str) -> pd.DataFrame:
    """Read the ledger back as Decimal — no inference, no cast, no drift.

    types_mapper hands pandas Arrow-backed columns so DELTA_AMOUNT arrives as the
    identical Decimal that was written. Column projection means an audit that only
    needs DELTA_AMOUNT never pays to scan NODE_ID.
    """
    table = pq.read_table(path, columns=["OPERATING_DAY", "DELTA_AMOUNT"])
    return table.to_pandas(types_mapper=pd.ArrowDtype)


def ledger_fingerprint(df: pd.DataFrame) -> str:
    """Order-stable SHA-256 over the settled rows for reconciliation and audit."""
    ordered = df.sort_values(["OPERATING_DAY", "NODE_ID", "REVISION_SEQ"])
    payload = "|".join(
        f"{r.OPERATING_DAY}:{r.NODE_ID}:{r.REVISION_SEQ}:{r.DELTA_AMOUNT}"
        for r in ordered.itertuples()
    )
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()

For the append-only requirement, Parquet’s immutability is a feature, not a limitation: you never rewrite an existing file, you write a new dated part file (ledger/operating_day=2026-04-01/part-000.parquet) and let the reader glob the partition. That maps exactly onto a settlement ledger where each revision run adds rows and never mutates a posted one.

Verification #

Confirm the round-trip before either format becomes the system of record:

• Type survival. After read_ledger_csv and read_ledger_parquet, assert every DELTA_AMOUNT is a Decimal (or Arrow decimal), not a float: assert all(isinstance(v, Decimal) for v in out["DELTA_AMOUNT"]) for the CSV path. A single float here is a defect, not a style nit.
• Value equality across formats. Write the same frame both ways, read both back, and diff: the summed DELTA_AMOUNT must be byte-for-byte equal to the cent. Because both paths avoid float, the residual must be exactly Decimal("0.00").
• Fingerprint stability. ledger_fingerprint over the re-read Parquet frame must equal the fingerprint computed at write time. A changed hash on unchanged data signals a non-deterministic write — usually an unpinned compression codec, an unsorted frame, or a stray float cast.
• Column pruning works. Read the Parquet file with columns=["DELTA_AMOUNT"] and confirm the returned table has one column; this is the projection that makes a nine-month audit scan cheap and is impossible in CSV without reading every field.

Compliance note #

An audit trail is only defensible if a third party can re-read the stored ledger and reproduce the figures that were filed. Both formats can satisfy this, but the guarantees differ:

• Reproducibility (FERC and SOX 404). Audit reproducibility means the same input bytes yield the same reconstructed values and the same hash on demand. Parquet reaches this through its embedded decimal128 schema plus a pinned compression codec, so the file is self-describing years later. CSV reaches it only when the reader is pinned to the exact dtype map, quoting, and null tokens used at write time — that reader configuration becomes part of the control and must be version-controlled alongside the data, satisfying the change-management expectation of SOX 404.
• Immutability of the record. An append-only ledger backing FERC Uniform System of Accounts reporting must never silently mutate a posted charge. Parquet part files are written once and never edited, giving each posted revision a distinct, hashable artifact; a CSV that is line-appended in place can be corrupted by a partial write and offers no such per-run boundary.
• Chain of custody. The ledger_fingerprint SHA-256 gives dispute resolution a deterministic anchor regardless of format, but only if the underlying values are truly reproduced — which is precisely why the float-avoidance controls above are compliance controls, not performance tuning.

Frequently asked questions #

If CSV can preserve Decimal with the right reader, why prefer Parquet at all? #

Because CSV preserves it only when every reader remembers to disable type inference and cast explicitly, forever. Parquet carries the decimal(18,2) type inside the file, so correctness does not depend on the reader’s configuration. For a ledger that outlives the engineers who wrote it, moving the guarantee from convention into the file itself is the safer default, and you gain columnar compression and column pruning at the same time.

Can I append a new revision run to an existing Parquet file? #

Not to an existing file — Parquet files are immutable. The idiomatic append is to write a new part file into the ledger’s partition directory and let the reader read the whole partition as one logical table. That fits an append-only settlement ledger cleanly: each Initial, Revised, and Final run becomes its own dated part file, and nothing already posted is ever rewritten.

Should ISO settlement feeds be stored as CSV because that is how they arrive? #

Store the raw drop as-is for provenance, but do not make CSV the queryable system of record. Ingest the CSV, validate and type it, then persist the ledger as Parquet. That separation — universal text at the wire boundary, typed columnar store for the ledger — is why the decision matrix scores CSV high on interchange and Parquet high on everything the reconciliation and audit jobs actually touch.

Related #

• Settlement Cycle Mapping — the parent component that produces the settled, delta-posted rows this ledger stores.
• How to map PJM settlement cycles to internal ledgers — where the DELTA_AMOUNT rows persisted here are generated.
• Reference Data Management — schema-versioning discipline for the master data the ledger references.