Store, Site, and Distributor

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

Sales records arrive on three clocks: store registers and the website emit each sale as it happens, while distribution partners send a weekly file of their sales and sometimes resend a corrected week to replace the original. Design the pipeline that lands all three in one view of performance across channels.

Sales data is piling up. Someone has to make sense of it.

Business requirements

• The VP of Sales wants one view of performance across stores, the website, and distributors; today the three are reported separately.
• Prices change monthly; revenue from last March has to be reported using the price the customer actually paid then, not today's.
• Regional managers see only their region's sales; finance sees everything; the rule has to be enforced no matter how a query is written.

Worked solution and explanation

What this really is

This is a convergence problem dressed up as a sales dashboard. Three channels arrive on three clocks. The skill being probed is whether you can let each channel keep its own cadence while every row still lands in one place, valued once and guarded once. Anyone can draw a stream and a batch job. What separates candidates is the three rules that live downstream of ingest. A resent partner week must replace rows, not sit beside them. A sale must pick up the price of its own date. Region visibility must belong to the reader, not to the query. Miss any one of them and the unified view reports numbers nobody actually charged.

The default shape is one nightly job. It pulls all three channels, joins to the current price book, and writes one big table that everyone queries. Store and website sales stay invisible until the next morning. A corrected partner week gets appended, so that partner's revenue doubles. Last March's revenue is recomputed at today's prices. A regional manager who skips the dashboard filter sees every region.

Walk the requirements

Step 1: Keep each channel on its own clock

Register and website events share one stream processor, live_sales_stream, because both emit each sale as it happens. Partner files go through partner_week_load on their weekly schedule. Forcing one cadence on all three either holds the fast channels back to the slowest file or runs a streaming job over data that changes once a week.

Step 2: Make a resent week replace, not add

partner_week_load deletes that partner's rows for the week and writes the new file in their place (delete_write). The replacement is scoped to one partner and one week, so a correction rewrites exactly the slice it corrects. If you append instead, a corrected file doubles that partner's revenue and nothing errors.

Step 3: Value every sale as of its own date

value_at_sale_date_price joins merged sales to dated_price_book on sale_date BETWEEN valid_from AND valid_to. A sale from last March picks up last March's price no matter when the row is processed. A join to the current price silently rewrites history every month, and finance finds out the first time a quarter has to be restated.

Step 4: Guard the rows, not the dashboard

region_row_security sits between cross_channel_sales_warehouse and every reader. It filters rows by who is asking, so a regional manager's query returns only their region however it is written, and finance's login sees everything. A filter saved in the dashboard is one hand-written query away from a leak.

The reference design

Common follow-up questions

• A partner resends a week from two months ago. What does `partner_week_load` replace, and how far downstream does the correction travel? (Tests whether the replacement is scoped to one partner and one week, and whether the valuation still uses that week's prices rather than today's.)
• A store is reassigned to a different region. Whose numbers do its past sales count toward? (Tests whether the candidate sees that region assignment is itself dated, just like price, and that the guard has to read the assignment in effect on the sale date.)
• A regional manager moves to finance. What changes, and where? (Tests whether visibility follows the reader's role: only the mapping changes, and no query, dashboard or export is touched.)