Same-Day Sales, Every Store

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

Point-of-sale terminals across our retail stores generate transactions all day, and today that sales data reaches the business intelligence team only as a nightly batch. Design a pipeline to bring POS data into Snowflake in near-real-time.

The cash register data needs to be queryable by morning.

Business requirements

• The BI team wants same-day visibility of POS sales instead of waiting for the nightly batch.
• Years of historical sales already live in our Snowflake warehouse and need to stay consistent with whatever we build.

Worked solution and explanation

The trap is the upsert

This is an append-only ledger problem dressed up as a latency problem. Anyone can swap the nightly batch for a stream. The trap is the reflex to make that stream idempotent by upserting on the transaction id. Replay safety is real, but an upsert keyed on the transaction rewrites the original when a void or correction arrives. Finance loses the trail, and any historical row that shares a key can be silently changed. BI gets fresh data and nobody trusts the number.

The reference design

Step 1: Stream events instead of waiting for the nightly drop

Flink on continuous_ingestion picks up each POS event within a minute, and staging_merge lands micro-batches in fact_sales well inside fifteen minutes. That is what same-day visibility means here. Anything that reads the table the next morning gets the prior day complete, so no second batch path is needed.

Step 2: Insert only events the table has not seen

staging_merge anti-joins the incoming batch against fact_sales on event_id and inserts the rest. A replayed batch finds every id already present and changes nothing. That is the whole idempotency story, and it needs no UPDATE.

Step 3: Append voids and corrections as rows

A void arrives as a new row with event_type 'void' and an original_txn_id pointing at the sale. The original stays 'sale' and is never touched. Finance sees both, and BI nets them out at read time.

Step 4: Write to the historical schema, leave history alone

New rows use the existing fact_sales columns. Anything extra, like a terminal id, is a nullable addition, so old reports behave the same. Because the merge only inserts, it can never modify a row from prior years.

Upsert on transaction id

Replays are safe, but a void overwrites the sale. The correction trail is gone and a reused key can rewrite history.

Insert-only on event id

Replays are safe because the event_id anti-join drops repeats. Voids land as their own rows and existing rows are never modified.

Common follow-up questions

• A void lands an hour after the sale. What does BI show in between, and after? (Tests read-time netting: the sale shows until the void row arrives, then the net view changes without any row being edited.)
• A terminal emits a new field the history never had. How do you absorb it? (Tests additive, nullable schema evolution on `fact_sales` instead of a migration that breaks old reports.)
• A bad deploy corrupted two hours of events. How do you recover without touching good rows? (Tests replaying from the source through the same `event_id` anti-join, which inserts only what is missing.)