CSV vs EDI 834 vs REST API: Choosing a Payroll Ingestion Approach

Every payroll integration starts with a vector decision that nobody schedules a meeting for: does the source system hand over a nightly flat file, a benefits-carrier EDI 834 transmission, or a REST endpoint to poll or subscribe to? That choice, part of the Multi-Format Payroll Data Ingestion & Normalization framework, is usually made by whichever engineer took the vendor’s onboarding call, and it quietly sets the latency floor, error surface, and operational cost for the life of the integration. CSV Ingestion Pipelines, EDI 834 Parsing, and REST API Payroll Sync each solve ingestion competently in isolation. This guide is the decision layer above them: a rubric for picking the right vector for a given source, and the standing reminder that whichever one you pick, it has to converge on the same canonical schema before a single dollar reaches gross-to-net.

None of the three vectors is categorically better. A flat file is the cheapest thing a legacy HRIS can produce and is fine for a static nightly load; an EDI 834 is the only format most benefits carriers speak and is fine for enrollment data, not payroll disbursement; a REST API is the only vector that can approach real time, and it is also the easiest one to misuse for a bulk historical load. Choosing wrong doesn’t fail loudly — it shows up months later as a rate-limit outage during open enrollment, a silently dropped column in a “simple” CSV, or a benefits feed being trusted for wage data it never carried.

Comparing the Three Ingestion Vectors

Latency: batch vs. near-real-time. CSV and EDI 834 are batch by construction — a vendor generates a file on a schedule (nightly, per pay cycle, per enrollment cutoff) and latency is measured in hours. REST is the only vector that can approach seconds-to-minutes latency, but only if the source actually pushes webhooks; a REST endpoint that you poll on a fixed interval is still batch, just with a shorter window. Confusing “the transport is HTTP” with “the data is real time” is the single most common latency mistake on this list.

Schema rigor and self-description. A CSV has no schema unless you build one beside it — the file is a grid of strings, and a vendor can reorder, rename, or drop a column without any signal beyond a parser exception three fields later. EDI 834’s X12 envelope (ISA/GS/ST) is a real, versioned grammar with loop and segment structure, so structural drift is far more likely to fail loudly at the transaction-set level — but the vocabulary inside it is benefits enrollment, not payroll, which is its own hazard (see Failure Modes below). REST sits in between: an OpenAPI or JSON Schema contract gives it real rigor, but plenty of vendor APIs ship “documentation” that drifts from behavior with no version bump to catch.

Error surface. CSV failures are the quietest: a dropped or reordered column can produce a file that still parses, just with wrong values in the wrong fields — the classic silent-drift failure this guide keeps coming back to. EDI 834 and REST both surface errors more explicitly — control-total mismatches and functional-acknowledgment rejections for 834, HTTP status codes and JSON Schema validation errors for REST — but “explicit” does not mean “cheap to handle”: a 429 mid-page-fetch and a segment-count mismatch mid-file both require dedicated retry and quarantine logic, not a bare except.

Idempotency and replay. Every vector needs a deterministic idempotency key, but the raw material differs. CSV gives you nothing native — the key has to be a content hash or a composite of business fields, built exactly the way async batch processing already does for retried files. EDI 834 gives you the ISA13 interchange control number, which anchors a natural replay boundary as long as you fold it into the key rather than trusting it alone. REST APIs often expose a resource id or an Idempotency-Key header, but webhook delivery is the trap: carriers redeliver on timeout, so a webhook delivery id is not the same thing as a stable idempotency key, and treating it as one produces duplicate postings.

Throughput and volume. CSV wins for bulk, static loads — a chunked reader can stream a hundred thousand rows in a single pass with no network round trips. EDI 834 throughput is bounded by segment-parse overhead, which is real but predictable. REST throughput is bounded by the vendor’s rate limit, which is why it is the wrong tool for a bulk historical backfill. A rough ceiling on sustained REST throughput:

R_{\text{max}} = \frac{\text{calls\_per\_window}}{\text{window\_s}} \times \text{records\_per\_call}

At 100 calls per 60-second window and 50 records per call, that ceiling is roughly 83 records per second — fine for continuous sync, an obvious bottleneck for a 2-million-row conversion load that a CSV export would move in minutes.

Versioning. CSV versioning is implicit and fragile: nothing forces a vendor to bump anything when a column is renamed. EDI 834 is formally versioned (e.g., the 005010X220 implementation guide), with a companion guide that documents every segment. REST is usually the most disciplined, with a path or header version (/v2/…) and a versioned OpenAPI spec — though “usually” is doing real work in that sentence, and a vendor deprecating /v1/ on a fixed date is an operational event you have to track.

Operational cost. CSV has the lowest infrastructure cost and the highest hidden cost: firefighting drift, building reconciliation tooling after the fact, and re-running failed batches by hand. EDI 834 has the highest upfront cost — an X12 parser, trading-partner setup, AS2 or VAN transport — but the lowest ongoing surprise cost, because the format’s rigor front-loads the pain. REST has moderate infrastructure cost (webhook receivers, signature verification, retry queues, secret rotation) with an ongoing cost tied to the vendor’s release cadence and rate-limit policy changes.

Audit implications. EDI 834 is the only vector with a native audit artifact: the 999 functional acknowledgment records exactly what was accepted or rejected, transaction by transaction. CSV and REST both require you to build the audit trail yourself — a file manifest plus content hash for CSV, a request/response log plus webhook-delivery-attempt trail for REST — following the same append-only, checksum-chained pattern documented in the parent framework’s audit and reporting pipeline.

Decision rubric

• Choose CSV when the source is a legacy HRIS or timekeeping system with no API, the load is bulk and periodic (nightly, per pay cycle), and you can enforce a schema contract externally (a Pydantic model or JSON Schema checked at ingestion, not vendor goodwill).
• Choose EDI 834 when the source is a benefits carrier or a trading partner that only speaks X12, and the data is enrollment and eligibility — not gross pay. Never let an 834 feed stand in for a payroll wage file; it was never built to carry one.
• Choose REST when the source offers webhooks or a low-latency polling endpoint, the volume per interval is modest enough to fit the rate limit, and you need near-real-time reconciliation (e.g., mid-cycle employee changes). Do not choose REST for a one-time bulk conversion load — that is a CSV or bulk-export job even when the vendor’s primary interface is an API.
• When a source offers more than one vector, prefer the one with the strongest native audit and idempotency story for the data type — EDI 834 for enrollment, REST for live employee-master sync, CSV only when nothing more structured exists.

Canonical Convergence: One Schema, Three Adapters

Whichever vector wins the decision above, the output has to be indistinguishable once it reaches the calculation engines. That is the entire point of the Data Boundary Definitions standard: a PayrollRecord with Decimal-safe money, a resolved tax_jurisdiction, and a deterministic idempotency_key is the only vocabulary the rest of the pipeline understands, regardless of whether it arrived as a comma-separated row, an X12 segment, or a JSON payload.

Each vector still has its own quarantine conditions at the boundary, and conflating them is how silent failures slip through:

• CSV quarantines on a missing or renamed required column, a gross_pay field that fails to parse as Decimal, or a row count that disagrees with a trailer/control record if the vendor supplies one.
• EDI 834 quarantines on a segment or loop count that disagrees with the transaction-set trailer (SE01), an unresolvable member or subscriber id, or a plan effective date that falls outside any loaded rule window.
• REST quarantines on a JSON Schema validation failure, a gross_pay value that arrives as a native float instead of a string or Decimal-parseable numeral, or a webhook signature that fails verification.

All three quarantine paths converge on the same Fallback Routing Strategies hierarchy, and the reason code recorded at quarantine names which vector-specific gate fired — that is what keeps reconciliation deterministic when three formats feed the same ledger.

Production Implementation Pattern

The pattern below is a common IngestionAdapter protocol with three concrete adapters — CSV, EDI 834, REST — each responsible only for its own transport and raw-to-canonical mapping. Everything downstream of to_canonical is vector-agnostic: a factory selects the adapter from source configuration, and the calculation engines never see the source format again. All money is Decimal, all logging is structured key=value, and the code is PEP 8 compliant and runnable as-is against real inputs.

"""Adapter pattern: three ingestion vectors, one canonical contract."""
import csv
import logging
from decimal import Decimal
from datetime import date
from typing import Any, Iterator, Protocol

from pydantic import BaseModel, ConfigDict, Field, field_validator

logger = logging.getLogger("payroll.ingestion_adapter")


class PayrollRecord(BaseModel):
    """Canonical record. Every adapter must produce exactly this shape."""

    model_config = ConfigDict(arbitrary_types_allowed=True)

    employee_id: str = Field(..., pattern=r"^[A-Z0-9]{6,12}$")
    pay_period_start: date
    pay_period_end: date
    gross_pay: Decimal = Field(..., ge=Decimal("0"))
    tax_jurisdiction: str
    source_vector: str
    idempotency_key: str

    @field_validator("gross_pay", mode="before")
    @classmethod
    def reject_float_money(cls, value: Any) -> Decimal:
        # Never let binary float rounding enter monetary state, regardless
        # of which vector produced the raw value.
        if isinstance(value, float):
            raise TypeError("gross_pay must not be a float; pass str or Decimal")
        return Decimal(str(value))


class IngestionAdapter(Protocol):
    """Every vector implements this; nothing downstream branches on format again."""

    source_name: str

    def fetch(self) -> Iterator[dict[str, Any]]:
        """Yield raw records from the source-specific transport."""
        ...

    def to_canonical(self, raw: dict[str, Any]) -> PayrollRecord:
        """Map one raw record onto the canonical PayrollRecord."""
        ...


class CsvIngestionAdapter:
    """Flat-file vector: streams rows, builds a content-derived idempotency key."""

    source_name = "csv"

    def __init__(self, path: str, source_run_id: str) -> None:
        self.path = path
        self.source_run_id = source_run_id

    def fetch(self) -> Iterator[dict[str, Any]]:
        with open(self.path, newline="", encoding="utf-8") as handle:
            yield from csv.DictReader(handle)

    def to_canonical(self, raw: dict[str, Any]) -> PayrollRecord:
        key = f"{raw['employee_id']}:{raw['pay_period_end']}:{self.source_run_id}"
        return PayrollRecord(
            employee_id=raw["employee_id"],
            pay_period_start=raw["pay_period_start"],
            pay_period_end=raw["pay_period_end"],
            gross_pay=raw["gross_pay"],  # str from csv.DictReader, never float
            tax_jurisdiction=raw["tax_jurisdiction"],
            source_vector=self.source_name,
            idempotency_key=key,
        )


class Edi834IngestionAdapter:
    """Benefits-enrollment vector: the ISA13 interchange control number anchors replay."""

    source_name = "edi834"

    def __init__(self, segments: list[dict[str, Any]], isa13: str) -> None:
        self.segments = segments
        self.isa13 = isa13

    def fetch(self) -> Iterator[dict[str, Any]]:
        yield from self.segments

    def to_canonical(self, raw: dict[str, Any]) -> PayrollRecord:
        # 834 carries enrollment/deduction elections, never gross pay.
        # gross_pay stays zero here; the deduction line is mapped
        # separately downstream through deduction-mapping rules.
        key = f"{raw['member_id']}:{raw['plan_effective_date']}:{self.isa13}"
        return PayrollRecord(
            employee_id=raw["member_id"],
            pay_period_start=raw["plan_effective_date"],
            pay_period_end=raw["plan_effective_date"],
            gross_pay=Decimal("0"),
            tax_jurisdiction=raw["tax_jurisdiction"],
            source_vector=self.source_name,
            idempotency_key=key,
        )


class RestIngestionAdapter:
    """HCM API vector: keyed on the vendor's resource id, not a webhook delivery id."""

    source_name = "rest"

    def __init__(self, records: list[dict[str, Any]]) -> None:
        self.records = records  # Replace with paginated httpx client calls.

    def fetch(self) -> Iterator[dict[str, Any]]:
        yield from self.records

    def to_canonical(self, raw: dict[str, Any]) -> PayrollRecord:
        key = f"{raw['employee_id']}:{raw['resource_id']}"
        return PayrollRecord(
            employee_id=raw["employee_id"],
            pay_period_start=raw["pay_period_start"],
            pay_period_end=raw["pay_period_end"],
            # Upstream json.loads must use parse_float=Decimal; a bare
            # float here raises at the boundary via reject_float_money.
            gross_pay=raw["gross_pay"],
            tax_jurisdiction=raw["tax_jurisdiction"],
            source_vector=self.source_name,
            idempotency_key=key,
        )


def build_adapter(config: dict[str, Any]) -> IngestionAdapter:
    """Factory: source config selects the vector; callers never branch on format."""
    vector = config["vector"]
    if vector == "csv":
        return CsvIngestionAdapter(config["path"], config["source_run_id"])
    if vector == "edi834":
        return Edi834IngestionAdapter(config["segments"], config["isa13"])
    if vector == "rest":
        return RestIngestionAdapter(config["records"])
    raise ValueError(f"unknown ingestion vector: {vector}")


def ingest(config: dict[str, Any]) -> list[PayrollRecord]:
    """Vector-agnostic entry point: swap config, not code, to change source."""
    adapter = build_adapter(config)
    canonical: list[PayrollRecord] = []
    for raw in adapter.fetch():
        try:
            record = adapter.to_canonical(raw)
        except (TypeError, ValueError, KeyError) as exc:
            logger.warning(
                "event=quarantine vector=%s error=%s", adapter.source_name, exc
            )
            continue
        canonical.append(record)
        logger.info(
            "event=normalized vector=%s emp=%s key=%s",
            adapter.source_name,
            record.employee_id,
            record.idempotency_key,
        )
    return canonical

The contract is deliberately thin: fetch owns transport, to_canonical owns mapping, and both raise rather than coerce on anything that would violate the boundary. Swapping a source from CSV to REST — the scenario the migration guide below walks through in reverse — means writing a new adapter and changing one factory branch, never touching the calculation engines or the audit logger.

Verification Checklist

1. Canonical-equivalence test. Construct the same logical record — identical employee, period, and gross pay — through all three adapters and assert model_dump(mode="json") is byte-identical after removing the source_vector field. If it isn’t, one adapter is smuggling vector-specific representation past the boundary.
2. Idempotency and replay per vector. Re-ingest the same CSV file and assert no duplicate idempotency_key reaches the sink. Replay an EDI 834 interchange with the same ISA13 and confirm the composite key, not the ISA13 alone, deduplicates correctly. Redeliver the same REST webhook twice and assert the resource-id-based key — not the delivery id — prevents a double post.
3. Decimal boundary test. Assert PayrollRecord(gross_pay=12.50, ...) raises TypeError regardless of which adapter constructs it, and that a CSV string, an EDI numeric element, and a REST JSON string all parse to the identical Decimal value for the same logical amount.
4. Schema-drift handling per vector. Rename a required CSV column and assert the row quarantines rather than silently shifting values. Corrupt an 834 segment count against its SE01 trailer and assert the transaction set rejects. Send a REST payload that violates the vendor’s JSON Schema and assert it quarantines with the validation error attached, not a bare parse exception.
5. Latency and SLA check. Assert a CSV batch completes within its nightly window, an EDI 834 file lands before its enrollment cutoff, and a REST sync’s end-to-end latency (webhook receipt to canonical upsert) stays under its near-real-time SLA — then alert on breach for every vector, not just the batch ones.

Failure Modes & Gotchas

• Picking REST for a nightly bulk load. Routing a multi-hundred-thousand-row historical conversion through a paginated API tuned for incremental sync trips the vendor’s rate limit repeatedly, and every 429 retry compounds the backlog instead of draining it. Root cause: treating “the vendor has an API” as equivalent to “the API is built for bulk transfer.” Fix: request a bulk export or CSV dump for one-time or large periodic loads and reserve REST for incremental, low-volume sync.
• CSV with no schema contract. A vendor renames gross_pay to grossPay or reorders columns, the parser keeps running because CSV has no structural enforcement, and wrong values land in the wrong fields with no exception raised. Root cause: trusting a header row as a schema instead of validating it. Fix: validate the header against a versioned Pydantic model or JSON Schema on every file and quarantine the entire batch on mismatch — never on a best-effort column match.
• EDI 834 treated as a payroll feed. An 834 carries benefits enrollment and deduction elections, not gross wages, but a rushed integration maps its dollar fields directly into gross_pay because “it’s a dollar amount in the file.” Root cause: conflating “structured EDI data” with “payroll data” — they solve different problems even from the same carrier. Fix: map 834 dollar elements to deduction records reconciled against payroll separately, never into gross pay; see reconciling EDI 834 enrollments to payroll deductions.
• Float money from JSON REST payloads. json.loads on a webhook body without parse_float=Decimal silently reintroduces binary rounding into gross_pay, and the reconciliation drifts by fractions of a cent across thousands of records until it breaches tolerance. Root cause: the JSON spec has no native decimal type, so the default parser hands back a Python float. Fix: parse every REST payload with parse_float=Decimal and let reject_float_money catch anything that still slips through as a native float.
• Assuming real-time when the source is actually batch. A team builds reconciliation logic assuming a REST-fronted vendor delivers changes within seconds, but the vendor’s own backend only refreshes its API cache once per hour — so “real-time” webhooks fire on a batch cadence underneath. Root cause: judging latency by transport (HTTP) instead of measuring the source system’s actual refresh cycle. Fix: instrument end-to-end latency from source event to webhook delivery before setting an SLA, and treat any vendor-side batch cadence as the real latency floor regardless of transport.

Frequently Asked Questions

Related

• CSV Ingestion Pipelines — the flat-file vector’s schema enforcement and quarantine rules.
• EDI 834 Parsing — the benefits-carrier vector’s segment state machine and control totals.
• REST API Payroll Sync — the HCM API vector’s pagination, signatures, and rate-limit handling.
• Choosing Between Batch and Real-Time Payroll Sync — measuring actual source latency before setting an SLA.
• Migrating a Payroll Feed from CSV to EDI 834 — a parallel-run cutover using this same adapter contract.
• Multi-Format Payroll Data Ingestion & Normalization — the parent framework this decision layer sits above.