Batch or live data: warehouses and streams

Tomorrow is fine, or now

A delivery app has two questions about its orders.

The finance team asks: "What did we sell last month, city by city?" An answer tomorrow morning is fine.

The fraud team asks: "Is this card payment stolen?" The answer is needed in under a second, before the food is cooked.

The same orders travel two different ways.

By the end of this lesson

Note

I can say when data goes to a warehouse and when it streams.

Key words

Word	What it means	Example
Data warehouse	a store of history, built for reports
Batch	many rows moved together on a schedule
Event	one thing that happened, recorded at once	a card payment
Stream	events handled one by one as they arrive
Latency	how long until data can be used

The cells students fill in are left blank in this preview.

A warehouse holds history for reports

A data warehouse keeps months or years of rows from many sources in one place. Analysts ask it questions in SQL.

Data usually arrives in a batch: a pipeline loads yesterday's rows overnight. So the warehouse is a few hours behind, and that is fine for a monthly report.

Ask the warehouse a question

Code: sql

-- Last week's orders, loaded overnight. Total sales for each day.
SELECT day, COUNT(*) AS orders, SUM(amount) AS sales
FROM orders
GROUP BY day
ORDER BY day;

-- Change day to city in both places, then press Run.

A stream acts on each event

Some decisions cannot wait for the night. A stolen card has to be stopped now.

In a stream, each event is handled as it arrives, within a second or less. The program keeps a little state, such as a running total, and acts on every event in turn.

Handle payments as a stream

Code: python

# Card payments arrive one at a time. Each one is an event.
payments = [
    {"card": "A", "amount": 12.00},
    {"card": "B", "amount": 450.00},
    {"card": "A", "amount": 8.50},
    {"card": "C", "amount": 1200.00},
]

running_total = 0
for event in payments:
    running_total = running_total + event["amount"]
    # Act on this event now, before the next one arrives.
    if event["amount"] > 1000:
        print("Hold payment on card", event["card"], "for a check")
    else:
        print("Approve card", event["card"])
print("Total so far:", running_total)

# Change the limit from 1000 to 400, then press Run.

Batch or stream: side by side

What	Batch to a warehouse	Stream
Data moves	many rows together	one event at a time
Latency	hours	a second or less
Good for	reports and trends	alerts and live screens
Delivery app example	sales by city last month	blocking a stolen card
Cost to build and run	lower	higher