Which Promotion Is Actually Working

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

ERP shipment events stream out continuously, about two million a day, while each of roughly 300 retailers drops a daily sell-through file and third-party market data lands once a week. Design the ingestion and analytics warehouse that turns all three into ROI for about forty concurrent trade promotions, each source loaded on its own cadence.

Was the promotion worth it? The data knows.

Business requirements

• Commercial analytics wants to know which promotions are working and which are wasting money, with data fresh enough to act on.
• A sale during a promotion has to be credited to the promotion that was running at the time, not whatever promotion is active now.
• Finance can't sign off on sales numbers that don't reconcile to the ERP shipment totals.
• Retailer files contain duplicate rows and bad product codes; the commercial team wants to review them, not have them silently dropped.

Worked solution and explanation

The real problem

This is a point-in-time join across three sources on three different clocks, dressed as promotion ROI. Anyone can land the data. What separates candidates is crediting each sale to the promotion that was running on its date, and reconciling to the ERP before anyone reads the number. The trap is one nightly load plus a join to 'today's promotion'. Both shortcuts produce numbers that look right and aren't: a sale gets this week's promotion instead of last week's, and finance's total quietly excludes whatever the validator dropped.

Walk the requirements

Step 1: Give each source its own cadence into analytics_warehouse

erp_stream_ingest runs Flink continuously, retailer_file_loader runs once a day when the files land, and weekly_market_data_loader runs once a week. A single shared schedule either starves the streaming source or burns compute re-reading data that changes weekly. Sizing each loader to its source is the whole cadence argument.

Step 2: Attribute each sale to the promotion running then

promotion_calendar keeps every promotion with its valid_from and valid_to. sale_time_promotion_attribution joins sales on sale_date BETWEEN valid_from AND valid_to, so a late sale from last week still gets last week's promotion. Joining on 'the current promotion' mis-credits every prior sale the moment a new one starts.

Step 3: Reconcile to the ERP as a check, not a comment

erp_reconciliation_check reads the attributed sales and the ERP shipment totals from the warehouse for the same window. If the gap exceeds tolerance, promotion_roi_mart is not refreshed, and analysts keep the last reconciled numbers. Finance stops trusting a warehouse the moment one published total disagrees with the ERP.

Step 4: Quarantine bad partner rows; keep the good ones moving

retailer_row_validation sends duplicates and unknown product codes to bad_row_quarantine, and the rest of the file continues into the warehouse. The commercial team triages the quarantine on their own time. Silently filtering loses count of the problem and leaves finance reconciling against a total that quietly excluded part of the file.

The shape that fits

Common follow-up questions

• A market data file lands twice in a week with overlapping rows. What protects you, and what doesn't? (Tests whether each delivery is keyed by its week so a reload replaces rather than appends, and whether same-key rows with different values get a source-of-truth rule.)
• Commercial wants intra-day ROI for one high-value promotion while everything else stays as-is. What changes? (Tests scaling freshness locally: a streaming materialization for that promotion's sales, joined against the same `promotion_calendar`.)