Katkat
Back

Design Chargeback Ingestion and CSV Delivery

NubankSoftware Engineer · Onsite
MediumSoftware EngineerOnsiteSystem Design

Last updated

Tests the ability to design an end-to-end financial transaction pipeline that ingests, deduplicates, and validates chargeback events, then reliably batches and delivers immutable CSV files to external card schemes via SFTP.

Scenario

A global digital bank processes card chargebacks and disputes initiated by customers. The system must ingest dispute requests from upstream microservices, validate and deduplicate them, track their lifecycle, and periodically batch eligible transactions into standardized CSV files. These files must be securely and reliably transferred to payment card networks (such as Mastercard or Visa) via SFTP within strict cutoff windows.

Requirements

Functional Requirements

  • Ingestion & Validation: Accept chargeback event payloads, validate schemas and business rules (e.g., valid dispute reason codes, eligible transaction age), and deduplicate duplicate incoming events.
  • Lifecycle State Machine: Maintain and track the state of each chargeback record (e.g., RECEIVED, VALIDATED, BATCHED, TRANSMITTED, ACKNOWLEDGED, REJECTED).
  • Batch Aggregation & CSV Export: Gather all validated, un-batched chargebacks at predefined cutoff windows (e.g., every 2 hours) and generate immutable CSV files matching the card network's schema requirements.
  • Secure & Resilient Delivery: Transmit CSV files to the partner's SFTP server with automatic retry logic, checksum verification, and support for resume/retransmission upon failure.

Non-Functional Requirements

  • Zero Data Loss: Chargebacks represent monetary claims; every accepted dispute must be accounted for and auditable.
  • Strict Idempotency: Network retries from upstream services must not create duplicate chargeback filings with the card network.
  • Auditability: Generated CSV exports must be stored immutably to verify historical transmissions during dispute audits.
  • Fault Tolerance: Upstream spikes or downstream SFTP outages must not block dispute ingestion.

Scale & Constraints

  • Daily chargeback volume: ~500,000 events per day (~50 events/sec average, 500 events/sec peak).
  • Export frequency: Batch generation runs every 2 hours (12 files/day), averaging ~40,000 rows per file.
  • Network constraint: External SFTP endpoint has occasional connectivity drops and strict connection rate limits.

What to Cover

  1. End-to-End Architecture: Flow from API/message queue ingestion down to the SFTP server.
  2. Deduplication Strategy: Handling duplicate events at the API boundary and data store level.
  3. Data Model & Batching Logic: State transitions, schema design, and how to safely query and mark batched records without race conditions.
  4. SFTP Transmission & Recovery: Mechanics of reliable file transfer, checksum verification, state tracking, and handling transient network failures.

Hints

Hint 1

Use a unique composite key (such as card transaction ID combined with dispute phase code) backed by an idempotency store or unique database index to filter out duplicate submissions.

Hint 2

Decouple file generation from file transmission: write generated CSVs with cryptographic checksums to immutable object storage first, then trigger worker tasks to handle the SFTP transfer.

Hint 3

To avoid double-batching records during concurrent export runs, use an atomic state transition query with row-level locks (e.g., `SELECT ... FOR UPDATE SKIP LOCKED`) or conditional updates to move items from `VALIDATED` to `BATCHED`.

Follow-up questions

  • If the card network returns an asynchronous response CSV containing line-item validation failures 24 hours later, how would you design the reconciliation pipeline to update individual dispute records?
  • How would you ensure that a file transfer does not leave corrupted or partially written files on the partner's SFTP server if the connection drops midway?
  • How do you monitor and alert when an upcoming card network cutoff window is at risk due to persistent SFTP connection timeouts?