# The Register Never Sleeps > Every swipe lands in the warehouse. The table has to stay current without breaking. Canonical URL: 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. ## Worked solution and explanation ### Why this problem exists in real interviews Three properties pull at one POS pipeline: minutes-fresh visibility for ops, voids and corrections that must apply without duplicating, and store-outage detection that fires within hours instead of at end of day. The trap is treating the void as just another row and treating a silent store as something someone will eventually notice. The default reach swaps the end-of-day batch for continuous file ingestion that appends every event. Snowpipe fires on each S3 file arrival, ops sees intraday sales, and it looks done. Then two problems surface. Snowpipe delivers at-least-once, so a re-fired S3 notification lands the same file twice and the fact table gains duplicate transactions. A void arrives referencing the original transaction; the appender writes it as a new row, so the warehouse now holds both 'completed' and 'voided' for one transaction id. And a store stops sending at noon while nobody notices until the end-of-day totals come in low. > **Trick to Solving** > > Snowpipe lands S3 files continuously, a staging table deduplicates by transaction id (delivery is at-least-once), and a MERGE keyed on transaction id resolves voids and corrections in place. > > 1. S3 event notifications trigger Snowpipe, which loads each file into a staging table within minutes; no waiting for end of day. > 2. The staging table dedups by transaction id, then a MERGE into fact_sales inserts new sales and updates the original row for voids and corrections, so consumers see one row per transaction at its current state. > 3. Per-store heartbeat monitoring fires when a store goes silent during business hours, so ops sees the outage within hours. --- ### Walk the requirements #### Step 1: Land transactions in the warehouse within minutes POS terminals write transaction files to S3, and an S3 event notification triggers Snowpipe to load each file into a staging table within minutes of arrival. The ops dashboard reads the warehouse and sees intraday sales as they land. Without the event-triggered continuous path the named problem, end-of-day blindness, is unaddressed; without a warehouse fact table the dashboard has nowhere to read. When stores reconnect after an outage and bulk-upload accumulated transactions, Snowpipe absorbs the burst the same way, one load per file. #### Step 2: Voids resolve against the original by id, not as duplicates Snowpipe delivers at-least-once, so the staging table first deduplicates by transaction id before anything reaches the fact table. Then a MERGE keyed on transaction id applies the change: a new sale inserts, and a void or price correction (which arrives as a new event referencing the original transaction id with a correction_type) updates the original row's status and net amount rather than adding a row. Consumers always see one row per transaction id at its current state. Appending the void as a new row is the version where the warehouse carries duplicates and totals diverge from the register tape; the staging dedup plus MERGE is the contract. #### Step 3: Per-store heartbeat alerts during business hours Each store sends transactions during its business hours. A monitoring step tracks per-store transaction arrival; when a store goes silent during its operating hours past a tolerance, an alert fires to ops with the store id and the time of the last transaction. Ops sees the silence within hours. Discovering the gap at end of day is the version where the team learns from low numbers; the per-store heartbeat surfaces the silence while there is still time to act. --- ### The shape that fits > **What this design gives up** > > Continuous Snowpipe ingestion costs more than the end-of-day batch, and it runs on serverless compute billed per file, so many tiny files cost more than fewer right-sized ones. The MERGE by transaction id needs the fact table clustered on that key, and per-store heartbeat monitoring needs per-store baselines. That implementation cost is the price; the win is ops seeing the day as it happens, voids resolving in place instead of as duplicates, and store outages surfacing in hours. > **What reviewers check** > > A reviewer looks at the canvas for these properties: > - An event-triggered continuous ingestion path lands POS files in a staging table and then the warehouse within minutes. > - A staging layer deduplicates and a merge stage applies voids and corrections against the original transaction by id, so the fact table holds one row per transaction at its current status. > - Per-store heartbeat detection alerts ops when a store stops sending during business hours. > **The mistake that ships** > > What ships swaps end-of-day batch for continuous append and stops there. Ops sees intraday sales, but a re-fired Snowpipe notification lands a file twice and voids accumulate as new rows, so downstream queries see duplicate transaction ids and totals diverge from the register tape. A store goes silent at noon and nobody finds out until end-of-day numbers come in low. The rebuild adds staging dedup, MERGE by id, and heartbeat monitoring, all reachable in the original conversation if continuous ingestion had been treated as continuous plus correct plus observed rather than just faster. --- ## Common follow-up questions - A void arrives for a transaction that hasn't been ingested yet (out of order). What does this design do, and how does the warehouse converge? _(Tests whether the candidate sees the MERGE handling out-of-order events: the void can either insert a placeholder void row that the original's later arrival reconciles, or buffer until the original arrives. Either way, the fact table converges to one row per transaction id with the correct final state.)_ - A store's heartbeat fires false positives during slow hours. How does the design avoid alerting fatigue without missing real outages? _(Tests whether the candidate's heartbeat monitor uses per-store baselines (not a global threshold), so a store with naturally slow afternoon hours has a wider expected-silence window than a high-volume store. Alerting fatigue is what makes ops stop reading alerts; per-store calibration is what keeps each one actionable.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_register_never_sleeps) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.