Badging Items That Already Sold Out

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

We promise same-day delivery on millions of SKUs, and the engine that decides which items get the same-day badge needs fresh data every few minutes. Stale features are making us over-promise on items that are out of stock or whose nearest fulfillment center is at capacity. Design a data pipeline that keeps the feature store current.

Same-day delivery. The features have to be faster.

Business requirements

Worked solution and explanation

What this really is

This is cache invalidation dressed up as a delivery badge. The badge engine needs a per-SKU answer in milliseconds, and the truth lives in two OLTP databases that change every second. Everyone draws a feature store. What separates candidates is where the badge engine's reads land and what happens to a row when the SKU dies. Query the OLTP on every page view and inventory falls over at peak. Refresh nightly and you badge items that sold out at 9am. Never expire rows and discontinued SKUs keep promising same-day delivery, each false promise paid out as a voucher.

Walk the requirements

Step 1: Capture changes off the log, not the tables

CDC tails the inventory and order change logs, so the live databases see zero query traffic from the badge path. Polling every few minutes is a full scan at millions of SKUs, and the operations team will veto it.

Step 2: Materialize into an online store keyed on SKU

A streaming job folds stock level and fulfillment-center capacity into one row per SKU and writes it to a key-value store within seconds. The badge engine does a single point lookup, so the page never waits on a join.

Step 3: Give every online entry an expiry

The online feature store carries a TTL on each entry, and SKU lifecycle events ride the same stream. A 'discontinued' event deletes the row, and the TTL is the backstop that ages out anything the stream missed, so a dead SKU stops being badged.

Step 4: Fork the same stream into the offline store

The streaming job also appends to a lakehouse table for training, and the nightly training job reads it. One feature definition feeds both tiers, so the model never trains on numbers the engine never served.

The shape that fits

CDC keeps the OLTP off the hot path; Flink writes per-SKU features into an expiring Redis store for millisecond reads and forks history to Delta Lake for training; lifecycle events delete dead SKUs.
  • inventory db (source, CDC)
  • orders db (source, CDC)
  • sku lifecycle (source, Kafka)
  • streaming features (transform, Flink); freshness: real-time
  • online feature store with TTL expiry (storage, Redis); freshness: < 1min
  • offline store (storage, Delta Lake)
  • badge engine (consumer, API)
  • training job (consumer, Jupyter); freshness: < 24h
  • inventory db -> streaming features
  • orders db -> streaming features
  • sku lifecycle -> streaming features
  • streaming features -> online feature store with TTL expiry
  • streaming features -> offline store
  • online feature store with TTL expiry -> badge engine
  • offline store -> training job
  • Sources + features: inventory db, orders db, sku lifecycle, streaming features
  • Stores: online feature store with TTL expiry, offline store
  • Consumers: badge engine, training job
What usually gets drawn
The badge engine queries inventory on each view, with a nightly batch table as fallback. OLTP load scales with page traffic and the fallback is hours stale.
What holds at peak
CDC into a stream, writes into an online store whose entries expire. OLTP load is flat regardless of traffic, and freshness is bounded by stream lag, not a cron schedule.

Common follow-up questions