Six Hours to Refresh Every Number

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 transformation pipeline behind both the live ratings feed, which clients hold to a 15-second delivery contract, and the corrected price and rating history they query. Our data lead's dbt models in the warehouse cannot serve both: a full refresh takes 6 hours, and incremental runs leave history stale after a split or merger.

Ratings change. The incremental model has to keep pace.

Business requirements

• Investment banks pay for a real-time ratings feed with a contractual 15-second deliver; today's rebuild blocks delivery and the SLA is at risk.
• Historical queries asking for a rating as of a specific past date have to return the rating that was in effect then, not today's.
• When a stock splits, decades of history for that security need to be retroactively adjusted; rebuilding the full history each time is not viable.

Worked solution and explanation

This is two products sharing one pipeline, dressed up as a dbt performance problem. The 6-hour refresh is the symptom, not the disease: one batch layer is being asked to honor a 15-second client contract and to repair decades of history. Anyone can say 'add streaming'. What separates candidates is scoping the correction: a split touches one security, so you rebuild that security's date ranges and nothing else. Get it wrong and you either miss the SLA behind a rebuild, or ship a price series that is silently wrong after the first split.

Walk the requirements

Step 1: Give the client feed its own streaming path

A rating change goes from the source through a stream processor into a feed store, and clients read from that store. Nothing on this path waits for a dbt run, so the 15-second SLA holds even while the warehouse is mid-rebuild. The warehouse is a sibling consumer of the same source, not a stage in the feed.

Step 2: Model ratings as validity ranges and join as-of

Store each rating with security_id, valid_from and valid_to, and join on query_date BETWEEN valid_from AND valid_to. A 'latest rating' column rewrites history every time a rating changes. The ranged history is what answers 'what was the rating on that date' during an audit.

Step 3: Overwrite only the affected security's partitions

A corporate action arrives with a security and an effective date. The rebuild reads just that security's price partitions from that date and overwrites them. The 6-hour full refresh becomes a slice, and rerunning it gives the same result because it replaces partitions instead of appending to them.

Full refresh on every split

Correct, but it takes 6 hours and competes with every other job for the warehouse. Splits are routine, so the cost recurs. Skipping it instead leaves the history wrong.

Scoped partition overwrite

Reads the corporate action, finds the security and the date range, and overwrites those partitions. Cost scales with one security's history, and a rerun converges to the same state.

Common follow-up questions

• A rating change is on the real-time feed but the warehouse has not loaded it, and a client asks for the rating 'as of now'. What goes wrong? (Tests whether you assign ownership: current-state reads come from the feed store, and history reads are only guaranteed up to the last warehouse load.)
• Two corporate actions land for the same security on the same day. How do you keep the overwrite deterministic? (Tests applying actions in effective-date order to the affected partitions, not in arrival order, so reruns give the same end state.)