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

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.

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 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.

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

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.)