# 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 POS terminals in 600 stores write each day's sales to S3 as one file per store after close, and the Snowflake warehouse loads those files overnight. Design a pipeline that lands each sale in the warehouse within minutes instead. ## 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 restates an old one. A silent register isn't missing data: it is 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. > **One key does both jobs** > > The transaction id is the whole design. File-triggered loading delivers at-least-once, and terminals re-upload files they are unsure landed, so the `MERGE` into `fact_sales` is keyed on that id. An unseen id inserts, a replayed sale rewrites its own row, and a void or correction updates the original. One key gives you both idempotent ingestion and in-place restatement, so consumers always see one row per transaction at its current state. --- ### Walk the requirements #### Step 1: Land each sale within minutes Once terminals upload a small file every minute or two, an S3 event notification triggers `continuous_ingest` to load each file into `staging_table` on arrival, and `ops_dashboard` reads the warehouse. There is no end-of-day wait. When a store reconnects after an outage and dumps a backlog, the loader absorbs it one file at a time. #### Step 2: Resolve voids against the original by id `staging_table` holds what landed, replays included. `fact_sales_merge` runs a `MERGE` keyed on transaction id: a new sale inserts and a replayed one rewrites the same row. A void or price correction arrives as a new event referencing the original id, and it 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. When a store goes quiet past its expected tolerance during opening hours, it fires to `ops_alerts` with the store id and last-seen time. Catching the gap at end of day is the failure this replaces. --- ### The shape that fits **Pipeline stages** - pos_terminals (source, API) - s3_staging (storage, S3) - continuous_ingest (transform, SQL); freshness: < 1min - staging_table (storage, Snowflake) - fact_sales_merge (transform, dbt); idempotency: upsert - fact_sales (storage, Snowflake) - heartbeat_monitor (quality gate, custom); alerting: Store silent past expected business-hours tolerance - ops_dashboard (consumer, dashboard) - ops_alerts (consumer, alerting) **Data flow** - pos_terminals -> s3_staging - s3_staging -> continuous_ingest - continuous_ingest -> staging_table - staging_table -> fact_sales_merge - fact_sales_merge -> fact_sales - fact_sales -> heartbeat_monitor - fact_sales -> ops_dashboard - heartbeat_monitor -> ops_alerts *`continuous_ingest` loads each S3 file into `staging_table` on arrival. A `MERGE` keyed on transaction id writes `fact_sales`, absorbing replays and resolving voids and corrections in place. `heartbeat_monitor` alerts ops to silent stores during business hours.* > **Continuous is not the same as correct** > > The version that ships stops at continuous append: ops sees intraday sales, so it looks done. But a re-uploaded file lands twice and voids stack as new rows, so `fact_sales` shows duplicate transaction ids and totals drift from the register tape. And a store dies at noon with nobody watching. An id-keyed `MERGE` and per-store monitoring are the two pieces that turn 'faster' into 'trustworthy'. > **You pay in compute for freshness** > > Continuous file loading bills per file, so many tiny POS files cost more than fewer right-sized ones. Keep `fact_sales` clustered on transaction id so the `MERGE` stays a targeted update. That cost buys ops the day as it happens, voids resolving in place, and outages surfacing in minutes instead of at close. ## 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, either by inserting a placeholder the original later reconciles or by buffering until the original 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 store gets a wider silence window than a high-volume one.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/real_time_pos_pipeline_with_snowpipe_and_merge) - [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.