Ship Before Fraud Finishes Checking

A hard 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

hard

Seniority

L6

Problem

Replacement phones are going out on claims the fraud team only reviews the next morning, from a nightly batch over yesterday's data. We insure over 300 million devices and take tens of thousands of claims a day: design a pipeline that scores each claim for fraud at submission time.

The claim looks clean. The fraud model disagrees.

Business requirements

• We ship a replacement device within hours of a claim; fraud has to decide before the package leaves, not after.
• A device passes through multiple owners and plans, and fraud needs to see the whole chain to spot the same device claimed under different plans.
• Hundreds of millions of device records can't be looked up live during the request; that would crush the operational system.
• If fraud scoring is unavailable, customers can't wait forever; the business chose to route those claims to manual review rather than block them.

Worked solution and explanation

What this really is

This is a feature-serving problem dressed up as insurance fraud. The skill is splitting device history into two tiers: an online store the scorer reads in milliseconds, fed by CDC, and a warehouse that keeps every owner, plan and repair with effective dates. You also decide in advance what happens when scoring fails. Anyone can draw a stream. The trap is a scorer that reaches back into the operational database for history. Tens of thousands of claims a day become tens of thousands of lookups against a 300-million-device system, and operations throttles it. Then the day the scorer times out, someone adds auto-approve and ineligible replacements start shipping.

Walk the requirements

Step 1: Put scoring on a sub-minute path

The claim lands on Kafka, and scoring_stream (Flink, 'real-time') joins it to features and emits a decision in seconds. Shipping waits on that decision. Any design where fraud reads last night's warehouse fails the one requirement the business actually named.

Step 2: Keep the chain with effective dates

Model device history as a slowly changing dimension keyed on (device_id, valid_from, valid_to), with one row per ownership transfer, plan switch or repair. 'Same device claimed under two plans' is then an as-of join. A table that stores only the latest state per device erases the exact evidence fraud is looking for.

Step 3: Feed features off CDC, not live reads

cdc_from_ops_db streams changes into feature_builder, which upserts device-keyed rows into feature_store. The upsert is idempotent, so a replay after a restart cannot double-count repairs. The operational DB sees zero traffic from scoring.

Step 4: Make manual review a designed path

When the scorer errors or feature lag crosses its budget, scoring_stream routes the claim to manual_review_queue and alerts. It does not approve the claim and it does not hold it. The business already chose this trade, so the canvas should draw it.

The reference design

Live lookup at scoring time

scoring_stream queries the ops DB per claim. Its load scales with claim volume, latency depends on someone else's system, and an ops outage blocks every claim.

CDC-fed online store

scoring_stream reads feature_store by device_id. Ops load comes from change volume only, lookups take milliseconds, and staleness is a number you can measure and route on.

Common follow-up questions

• An investigator asks who owned a device when claim X was filed. Which store answers, and what makes it correct? (The as-of join on `valid_from` / `valid_to` in `history_warehouse`; `feature_store` holds current state only.)
• CDC falls an hour behind. What changes for scoring? (A freshness budget on `feature_store` that routes claims to manual review instead of scoring on stale features.)