Batch vs streaming ingestion for sponsor portal sync

Problem statement #

You are syncing award and subaward state from a sponsor portal into your institutional store, and you have to decide between two ingestion shapes: a scheduled batch pull that sweeps the portal on a fixed cadence, or an event-streaming consumer that reacts to change notifications as they arrive. This guide compares the two on latency, idempotency and replay, audit reconciliation, operational complexity, cost, and back-pressure, gives a runnable Python reference for each, and recommends when to pick which.

It sits under API Polling & Portal Integration, part of Automated Ingestion & Data Sync Workflows. The parent guide establishes the acquisition contract both shapes inherit: every payload is fingerprinted, validated against a canonical schema, and upserted by a stable idempotency key so that repeated delivery can never create a duplicate award row. This page assumes that gate exists and focuses only on the ingestion topology feeding it.

Prerequisites #

• Python 3.10+ with type hints and datetime.now(timezone.utc).
• Libraries: requests>=2.31 for the batch pull, a streaming client for the consumer (any queue or log consumer works; the reference uses a generic consumer interface), and SQLAlchemy>=2.0 with psycopg[binary] for the idempotent upsert. Install with pip install "requests>=2.31" "SQLAlchemy>=2.0" "psycopg[binary]".
• Environment variables (never hard-code credentials, per Security Boundary Configuration):
  • PORTAL_BASE_URL, PORTAL_TOKEN — sponsor endpoint and OAuth2 token.
  • SYNC_DB_URL — the SQLAlchemy connection string.
• A canonical validation model and a unique idempotency key. Both topologies write through the same Schema Validation Pipelines gate and the same conflict-keyed upsert; the task-level pull mechanics are covered in Automating daily grant portal polling with Python requests.

The two topologies side by side #

Both shapes converge on one validation gate and one keyed upsert; they differ in when records arrive and how the run knows it has seen everything. Batch pulls a bounded window on a schedule and reconciles the whole window at once; streaming consumes an unbounded event flow and reconciles against a moving watermark.

Figure: batch and streaming feed the same validate-and-upsert gate; they differ in arrival timing and in how completeness is reconciled.

Reference A — scheduled batch pull #

The batch worker sweeps a bounded window (records modified since the last successful high-water mark), fingerprints each payload, and upserts by idempotency key. The window bound is what makes reconciliation tractable: at the end of the run you know exactly which records the window should have contained.

import hashlib
import logging
from datetime import datetime, timedelta, timezone
from typing import Any

import requests

logger = logging.getLogger(__name__)


def pull_batch_window(base_url: str, token: str, since: datetime,
                      upsert, quarantine) -> dict[str, Any]:
    """Sweep records modified since `since`, upsert each by idempotency key.
    Returns a reconciliation summary bounded to the window."""
    headers = {"Authorization": f"Bearer {token}"}
    window_end = datetime.now(timezone.utc)
    params = {"modified_since": since.isoformat(), "modified_until": window_end.isoformat()}

    resp = requests.get(f"{base_url}/awards", headers=headers, params=params, timeout=30)
    resp.raise_for_status()
    records = resp.json()["items"]

    stats = {"read": 0, "upserted": 0, "quarantined": 0, "window_end": window_end}
    for raw in records:
        stats["read"] += 1
        content_hash = hashlib.sha256(
            repr(sorted(raw.items())).encode("utf-8")
        ).hexdigest()
        key = f"{raw['sponsor']}:{raw['award_id']}:{raw.get('subaward_id') or '-'}"
        try:
            upsert(key=key, content_hash=content_hash, payload=raw)  # ON CONFLICT DO UPDATE
            stats["upserted"] += 1
        except ValueError as exc:  # validation rejection
            quarantine(raw, reason=str(exc))
            stats["quarantined"] += 1

    logger.info("Batch window %s..%s: %s", since, window_end, stats)
    # Advance the high-water mark ONLY on a clean window so a failure re-pulls it.
    return stats

Reference B — event-streaming consumer #

The streaming worker reacts to each change event as it lands. There is no window; instead the consumer commits its offset only after the record is durably upserted, and it leans entirely on the idempotency key because at-least-once delivery means the same event can arrive more than once.

def consume_stream(consumer, upsert, quarantine) -> None:
    """Process change events continuously; commit offset only after a durable write.
    At-least-once delivery makes the idempotency key mandatory, not optional."""
    for event in consumer:  # blocks; yields one change event at a time
        raw = event.value
        content_hash = hashlib.sha256(
            repr(sorted(raw.items())).encode("utf-8")
        ).hexdigest()
        key = f"{raw['sponsor']}:{raw['award_id']}:{raw.get('subaward_id') or '-'}"
        try:
            # Same keyed upsert as batch: a redelivered event updates in place.
            upsert(key=key, content_hash=content_hash, payload=raw)
        except ValueError as exc:
            quarantine(raw, reason=str(exc))
        # Commit AFTER the write so a crash re-delivers rather than skips the event.
        consumer.commit(event.offset)

Because both references call the same keyed upsert, a record processed by batch and later re-seen by streaming — or a redelivered stream event — collapses onto one row. The topology changes the delivery pattern, not the write semantics.

Decision matrix #

Recommendation #

Choose scheduled batch as the default for sponsor-portal sync. Sponsor portals refresh award state on predictable, coarse cycles (nightly, or on submission deadlines), most awards change rarely, and federal reporting reconciles naturally against a bounded window with a countable expected set. Batch gives you the simplest audit story — a window either reconciled or it did not — with the least standing infrastructure, and its replay model is just “re-run the window.”

Choose streaming when latency is a real requirement and change volume is genuinely continuous: a portal that emits a change feed, an internal ERP that publishes award events you must reflect within seconds, or a high-throughput procurement stream where a nightly sweep would fall behind. Accept the cost — a broker, consumer-group management, offset-based reconciliation, and engineered back-pressure — because you are buying latency that batch cannot provide. A common mature pattern is a hybrid: stream for low-latency updates and run a periodic batch sweep as a reconciliation backstop that closes any gaps the stream missed. Whichever you pick, decouple heavy writes through Async Processing & Queue Management, and when you only need to detect what changed between runs, pair either topology with Incremental Change Data Capture.

Verification #

Confirm either topology is correct before trusting its output:

1. Batch count parity. For a completed window, upserted + quarantined must equal read, and the distinct idempotency keys upserted must match the window’s expected set. A gap means a record was silently dropped.
2. Streaming offset continuity. Confirm committed offsets advance without holes and never move past an un-upserted event. A committed offset ahead of a durable write means an event was skipped — a correctness defect.
3. Duplicate collapse. Deliver the same record twice (re-pull the window, or replay the offset). Exactly one row must exist for its idempotency key, and its content columns must be unchanged after the second delivery.
4. Reconciliation backstop. If running hybrid, confirm a batch sweep over a window the stream already covered produces zero new inserts — proving the stream lost nothing.

Troubleshooting #

Three gotchas specific to choosing between batch and streaming:

• Duplicate delivery in streaming. At-least-once delivery guarantees a redelivered event on any consumer restart or rebalance. Never treat a stream event as unique — always route it through the same idempotency-keyed upsert as batch, and commit the offset only after the durable write. Committing before the write turns a crash into silent data loss.
• Reconciliation gaps between batch windows. If a window advances its high-water mark on a partial failure, records modified in the failed slice are never re-pulled. Advance the mark only after a fully clean window, and bound each window with an explicit modified_until so a record modified during the run is not straddled and missed.
• Ordering assumptions. Streaming events for the same award can arrive out of order across partitions, and a late event can overwrite a newer one. Key upserts on the record identity and guard the update with a monotonic source version or modified_at so a stale event cannot clobber fresher state. Batch sidesteps this by taking the latest state within the window, but must still handle two modifications to one record inside a single window.

Frequently asked questions #

Related #

• Up to the parent topic: API Polling & Portal Integration
• Automating Daily Grant Portal Polling with Python requests
• Incremental Change Data Capture
• Async Processing & Queue Management