The Register Never Sleeps

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

We run a 600-store retail chain feeding a Snowflake warehouse, but the POS terminals only bulk-upload each day's sales to S3 after close, so operations is blind to intraday numbers and learns that a store went dark mid-afternoon only when the nightly totals come in low. Build a pipeline that lands each sale in the warehouse within minutes and raises an alert when a store stops sending during business hours. Voids and same-day price corrections arrive as new events that reference the original transaction, and each has to update that original in place instead of adding a second row.

Every swipe lands in the warehouse. The table has to stay current without breaking.

Business requirements

• The operations dashboard tracks intraday sales across stores; today the end-of-day batch leaves ops blind during the day.
• Some transactions are voided or price-corrected the same day, and the corrections reference the original; the warehouse has to apply each correction without producing two records.
• If a store has stopped sending transactions during business hours, ops needs to know quickly; today it's discovered when end-of-day numbers come in low.

Worked solution and explanation

What this really is

This is a change-data problem wearing a retail costume: a void isn't a new sale, it's a restatement of an old one, and a silent register isn't missing data, it's the signal. Swapping the end-of-day batch for continuous ingestion is the easy half everyone reaches for. The trap is treating the void as just another row and trusting someone to notice a dark store. Get the first wrong and fact_sales carries both 'completed' and 'voided' for one transaction id, so totals diverge from the register tape. Get the second wrong and ops learns a store died at noon only when nightly numbers come in low.

Walk the requirements

Step 1: Land each sale within minutes

An S3 event notification triggers snowpipe_ingest to load each POS file into staging_table on arrival, and ops_dashboard reads the warehouse. No end-of-day wait. When a store reconnects after an outage and dumps a backlog, Snowpipe absorbs it one file at a time.

Step 2: Resolve voids against the original by id

staging_table dedups on transaction id first (Snowpipe is at-least-once). Then fact_sales_merge runs a MERGE: a new sale inserts; a void or price correction, which arrives as a new event referencing the original id, updates that row's status and net amount instead of appending. Appending the void is the version where duplicates pile up and totals drift.

Step 3: Alert on silence during business hours

heartbeat_monitor tracks per-store arrival and fires to ops_alerts when a store goes quiet past its expected tolerance, with store id and last-seen time. Catching the gap at end of day is the failure this replaces.

The shape that fits

Common follow-up questions

• A void arrives for a transaction that hasn't been ingested yet. What does the `MERGE` do, and how does `fact_sales` converge? (Tests whether out-of-order events are handled: insert a placeholder the original later reconciles, or buffer until it arrives. Either way the table settles to one row per id.)
• A slow store trips `heartbeat_monitor` with false positives. How do you avoid alerting fatigue without missing real outages? (Tests per-store baselines over a global threshold, so a naturally quiet afternoon has a wider silence window than a high-volume store.)