Thirty Million Unique Jobs a Year

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

Press sheets keep going out half-full because new orders reach the ganging step too late, and the operations team hears about missed delivery promises from customers. We make 30 million unique print products a year across 10 acquired brands, ganging many customer orders onto each press sheet. Design the pipeline.

One press run, many orders. Group them right.

Business requirements

• The ganging algorithm needs a real-time view of pending orders.
• Our analytics are fragmented across 10 acquired companies running different ERP systems.
• Our operations team needs to know before a job starts whether it will meet its delivery promise.

Worked solution and explanation

What this really is

This is one canonical order stream fanned out three ways, dressed up as print-shop logistics. Ganging, promise checks and cross-brand analytics all read the same orders, so the real skill is making one shared order shape a contract that sits upstream of every reader. Anyone can draw a warehouse. The trap is letting the ganging algorithm poll ten ERP databases and leaving the mapping to a nightly job. Do that and an order confirmed after the last poll misses its sheet, the ERPs take query load they were never sized for, and every reader carries its own drifting copy of ten mappings.

Walk the requirements

Step 1: Feed the pool, not the algorithm's queries

Change data capture turns a confirmed order into an event within seconds. The Flink canonicalizer maps it, and order_bus lands it in ganging_pool, a Redis store keyed by order. The algorithm reads the pool on every re-plan, so a late order still makes the next sheet and the ERPs never see its reads.

Step 2: Map ten schemas before anyone reads

The canonicalizer emits one shape: order, brand, product, size, quantity, deadline, status. If you harmonize at query time instead, every reader carries its own ten mappings, and they drift apart within a quarter. An edit or a cancellation arrives as a change event and replaces that order's state in the pool.

Step 3: Score jobs while they are still scheduled

ganging_optimizer writes each planned sheet to scheduled_sheets. delivery_promise_scorer joins those jobs with live press_telemetry and with the order deadlines on the bus. It pushes any job likely to miss its promise to operations_team before the job reaches a press.

Step 4: Land the same shape for analysts

order_bus also loads cross_brand_warehouse. Because the shape was fixed upstream, cross-brand reporting reads one table instead of ten, and a daily load is plenty for analysts who read yesterday's numbers.

Algorithm queries the brands

Ten schemas are joined inside the algorithm, and its read load lands on production ERPs. An order confirmed after the last poll misses the cycle.

Algorithm reads the pool

ganging_pool holds canonical orders seconds after confirmation. The ERPs see only the change-log reader.

Common follow-up questions

• An eleventh brand arrives with its own schema. What changes? (Only a new change-log reader and one new mapping in the canonicalizer. Every reader downstream stays untouched.)
• Finance wants each sheet's utilization next to the order analytics. What changes? (Planned sheets also flow into the warehouse beside the orders they carried, and the real-time ganging path stays as it is.)