One Utility, Four Data Shapes, Three Latency Requirements
Northwind Utilities operates 40,000 smart electricity meters. Each meter posts a JSON message every 15 seconds containing a meter ID, a timestamp, a voltage reading, and a nested array of variable-length interval readings. Peak ingestion is roughly 2,700 messages per second. Billing must produce exact, auditable monthly totals per account. A customer portal shows current-hour usage and refreshes every 5 seconds. A data science team wants to train forecasting models against three years of history. Separately, field technicians upload JPEG photographs of meter installations and scanned PDF copies of customer contracts. The company has one data platform budget and an explicit goal of not storing the same data in three different technologies.
1. Classify each of the four data assets (meter JSON messages, billing totals, technician photos, scanned contracts) as structured, semi-structured, or unstructured, and state which one clearly belongs in a relational store. Justify each classification rather than just labelling it.
Read the worked answer
The question to ask is not 'what file format is it' but 'is there a schema that the system can rely on and enforce?'
- Meter JSON - semi-structured. It has a predictable envelope (meter ID, timestamp, voltage) but a nested, variable-length array whose shape differs by meter model and firmware. It is self-describing, so a consumer can parse it without a fixed contract, but nothing enforces the shape at write time.
- Billing totals - structured. Fixed set of typed columns (account, period, kWh, amount), a known grain, and business rules that must hold. This is the asset that belongs in a relational store, because billing needs ACID transactions, exact decimal arithmetic, uniqueness and foreign-key constraints, and a recovery story you can explain to an auditor.
- Technician photos - unstructured. No schema; content is read by humans or a vision model, not by a query engine.
- Scanned contracts - unstructured. The PDF is pixels. OCR output would be semi-structured, but the retained artefact itself is not.
Judgement point: the JSON is not unstructured just because it is not in rows. Semi-structured means 'has named fields, no enforced schema' - that is exactly what the meter payload is.
2. For each of the three workloads - monthly billing, the live customer portal, and model training - decide whether batch or stream processing is appropriate, and explain what drives the decision.
Read the worked answer
Two properties drive the decision: how fast the value decays, and how complete the answer must be before it is useful.
| Workload | Mode | Reasoning |
|---|---|---|
| Monthly billing | Batch (or scheduled micro-batch) | The answer is only valid once the whole period has closed. You need the ability to re-run a period after a correction or a late meter reading. Sub-second latency adds cost and complexity and buys nothing. |
| Live portal | Stream | A 5-second refresh target means the value of the number decays in seconds. Batch would show yesterday's usage; the feature stops existing. |
| Model training | Batch | Full scans over three years, throughput and cost per TB matter, latency is irrelevant. Training on a stream adds noise without benefit. |
The important insight is that all three read the same source, so the platform should not be chosen once for the whole dataset. One ingestion path, one durable raw store, and then different compute per consumer. This is the cheapest way to honour the 'don't store it three times' constraint while still meeting a 5-second freshness requirement for one consumer and a 30-day completeness requirement for another.
3. The CTO proposes loading everything - meter events, photos, and contracts - into a single relational database. Give two concrete failure modes, and state the smallest change that fixes each.
Read the worked answer
Failure mode 1 - the fixed schema fights the data. At 2,700 messages/second with a nested variable-length array, every new meter model triggers a schema change and a migration. Row-by-row inserts plus index maintenance on a relational table becomes a write bottleneck, and the nested array has to be shredded into child tables that are rewritten constantly. Fix: land the events in a schema-flexible store (a lake with JSON, or a document/key-value store) and keep only the derived billing facts in the relational database.
Failure mode 2 - blobs inflate the whole system's recovery story. JPEGs and scanned PDFs are hundreds of KB to several MB each. Putting them in the database grows every full backup, every restore, and every geo-replica, and it ties the recovery point objective of the billing system to archive-sized data. Fix: store the binaries in object storage and keep only a URL or path plus metadata in a relational table.
The general principle: a relational database is optimised for constrained, typed, transactional rows. Putting unconstrained payloads and large binaries in the same store does not simplify the platform - it transfers all three workloads' risk onto the one workload that must never fail.