Data engineering in the AI era · intermediate · About 10 minutes
The daily sales total that grows after midnight
Monday's sales on the dashboard say $412,600. Finance runs the same query on Thursday and gets $426,600. Nobody changed the data, and both numbers are right for the moment they were taken.
You own the nightly job that builds the daily sales table for 140 shops.
What happened
Every night at 00:30 a job builds daily_sales for the day that just ended. It reads the sales
events with that day's sold_at date, sums them per shop and overwrites that day's row. The
dashboard and the weekly report read daily_sales.
On Thursday, finance ran the same sum over the raw events for Monday and got $14,000 more than the dashboard shows. They ask which number is wrong, and why the dashboard never changed.
The incident is made up. How things work in it is real, with sources at the end.
The evidence
Everything you need is here. Read it before you decide.
| Summed at | Total for Monday | Share of the final total |
|---|---|---|
| Tue 00:30 (the nightly job) | $412,600 | 96.6% |
| Wed 00:30 | $423,700 | 99.2% |
| Thu 10:00 (finance) | $426,600 | 99.9% |
| Fri 00:30 | $427,100 | 100.0% |
The till app keeps each sale when the shop's connection drops and sends it when the connection
returns. Every event has sold_at, the time of the sale, and received_at, the time it reached
the warehouse.
| Shop | sold_at | received_at | Amount |
|---|---|---|---|
| S-114 | Mon 18:42 | Tue 07:15 | $1,320 |
| S-087 | Mon 11:05 | Wed 14:30 | $2,940 |
| S-031 | Mon 20:10 | Thu 09:12 | $880 |
-- runs at 00:30 for the day that just ended
insert overwrite daily_sales partition (sale_date = '{{ ds }}')
select shop_id, sum(amount) as sales
from sales_events
where date(sold_at) = '{{ ds }}'
group by shop_id;Your decisions
Decision 1 of 3
Decision 1
Why do the dashboard and finance disagree?
Decision 2
How do you change the nightly job?
Decision 3
What do people reading the dashboard need?
What went wrong
Every sale has 2 times: when it happened (sold_at) and when the warehouse heard about it
(received_at). The job grouped by the first but ran on the second's schedule. At 00:30 on Tuesday
it saw every Monday sale that had arrived, wrote Monday's row, and moved on to Tuesday. Sales from
shops that were offline arrived later with a Monday sold_at, into a day the job would never build
again.
The fix keeps both promises. Each run rebuilds a window of recent days, here the last 4, because the
evidence shows nothing arrives later than that, and each day is overwritten as a whole, so running it
again for the same day gives the same answer. This is what Airflow's best practices ask of a task:
read and write a specific partition by the run's own date, never "the latest data". dbt's microbatch
models do the same with a lookback setting that reprocesses the batches before the latest one. A
streaming job draws the same line with a WatermarkWatermarkIn stream processing, a marker that says how far event time has got: data older than the latest event seen, less a set delay, is treated as too late, so windows can close and their state be freed., the time it waits for late events before it
closes a window.
Pick the window from the data, not a guess. Measure how late events arrive, choose a window that covers nearly all of them, and say on the dashboard which days can still change.
Keep these
- An event has the time it happened and the time it arrived. Group by the first, schedule by the second.
- Rebuild a window of recent days, each overwriting its own day, and the late data has somewhere to go.
- Measure how late data arrives before you choose the window, and show readers which days can still move.
The ideas behind it
Sources