The Points Arrive Two Days Late

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

Design the pipeline that takes transaction feeds from the third-party bank aggregators our loyalty app links users' accounts through, and turns purchases at partner brands into reward points in each user's balance.

The bank data shows up late. The rewards were already sent.

Business requirements

• Bank aggregators retry on errors and the same purchase has been getting credited twice; users have been getting double rewards.
• Bank data arrives a day or two late, and a transaction landing today might come from a moment when a different offer was running; eligibility has to be checked against the offer that was active then.
• Once we know a transaction qualifies, the points have to land in the user's balance within minutes.

Worked solution and explanation

This is a keyed upsert plus a point-in-time lookup dressed up as a loyalty app. Anyone can wire bank events to a points table. The trap is doing it with the two shortcuts that look free: crediting on every delivery, and checking eligibility against whatever offers are live today. Aggregators retry, so the first shortcut double-credits users. Bank data lands a day or two late, so the second one tells a user their purchase didn't qualify for an offer that was running when they bought.

Walking the requirements

Step 1: Put a queue between the aggregators and the points path

Aggregators deliver at least once, so duplicates are a given. A durable event bus gives the processor one ordered stream to key and dedupe on, and it absorbs the burst when a late batch of a day's purchases arrives at once. Without it, every retry hits your crediting logic directly.

Step 2: Make the credit write idempotent on the full key

dedup_credit_ledger upserts on (user_id, transaction_id, offer_id). A second delivery rewrites the same row, so it changes nothing, and the balance is read off that ledger. offer_id belongs in the key because one purchase can match two offers, and each match should credit once. Leave it out and the second offer is silently swallowed.

Step 3: Keep offer history in object storage and join by transaction time

Persist every change to the offer catalog as a dated snapshot (offer_id, valid_from, valid_to, conditions). The stream reads the snapshot that was active at the transaction's timestamp. A purchase from two days ago is judged by the offers running then, and a changed catalog today can't retroactively disqualify anyone.

Step 4: Stream the qualified credits into the balance

Points must land within minutes of qualifying, so the eligibility stage is a streaming job, not a nightly batch. A batch would stack another day on top of data that is already a day or two late.

Looks free, breaks quietly

Subscribe to events, read the live catalog, and INSERT a credit on every delivery. A retry doubles the points. A two-day-old purchase is checked against an offer that expired yesterday and gets no match.

What the design does

Dedupe through an upsert on (user_id, transaction_id, offer_id) and read the offer snapshot as of the transaction time. Retries are no-ops, and late data earns the offer it actually qualified for.

Common follow-up questions

• An aggregator reverses a purchase days after it was credited. How does the design take the points back? (Tests whether the candidate models a reversal as another idempotent event keyed on the same transaction, writing a debit that the balance nets out, rather than a manual fix.)
• An offer rule was misconfigured and some past purchases should have qualified. How do you recredit them? (Tests treating snapshots as immutable history: publish a corrected snapshot, replay the affected events, and let the upsert key skip anything already credited.)