Meter events with duplicates, late gateways, and 900 TB of raw JSON
PulseGrid operates 80,000 smart meters that publish readings to Pub/Sub at up to 50,000 messages per second during peak. Each message is JSON with meter_id, reading_kwh, event_ts, and a device-generated message_id. Pub/Sub delivers at least once, and devices retry on timeout, so duplicates are common. Some gateways buffer for up to two hours during connectivity loss, so events arrive late and out of order. A BigQuery subscription writes raw messages to a table partitioned by ingestion time. The downstream hourly aggregate double-counts readings, and the streaming table has grown to 900 TB because raw JSON is stored indefinitely. The team also sees occasional BigQuery subscription errors when a message exceeds the 10 MB limit. They have Dataflow, Datastream, and Cloud Composer available. Finance wants to reduce storage and streaming insert cost; operations wants near-real-time dashboards with 5-minute freshness.
1. Choose between a BigQuery subscription, Dataflow, and Datastream for this ingestion. Justify the choice.
Read the worked answer
Use Dataflow with a Pub/Sub source. The BigQuery subscription is simple but does not deduplicate or handle event-time lateness; at-least-once delivery will continue to double-count. Datastream is for change data capture from databases, not meter events. Dataflow can deduplicate on message_id, apply event-time windowing with two hours of allowed lateness, route oversized messages to a dead-letter destination, and write with the Storage Write API.
The judgement is that the simplest sink is not adequate when correctness depends on deduplication and late data. Dataflow adds operational cost and code, but the downstream aggregate already shows double counting, so the cheaper ingestion path is producing wrong numbers. Keep Pub/Sub as the buffer and Dataflow as the processing layer. Use Composer only to schedule reconciliation jobs, not the main streaming path.
2. How would you deduplicate events and handle data that arrives up to two hours late?
Read the worked answer
Deduplicate on message_id using a stateful transform with a timer that retains keys longer than the maximum retry window. Window by event_ts, not ingestion time, with allowed lateness of two hours. Send events later than that to a side output and a separate late table for controlled reprocessing. Write the deduplicated raw events to a BigQuery table partitioned by DATE(event_ts) and clustered by meter_id. Use an idempotent MERGE on (meter_id, event_ts, message_id) as a second guard.
For the hourly aggregate, do not simply append. Recompute affected hours when late events arrive, or use a MERGE that replaces the hour's aggregate from the deduplicated detail. The tradeoff is latency versus completeness: operations sees a 5-minute view that may later be corrected, while the certified hourly table waits for the two-hour lateness bound.
3. What storage and cost changes would you make to the 900 TB raw table while preserving 5-minute freshness?
Read the worked answer
Stop storing raw JSON indefinitely in BigQuery. Store parsed, columnar fields and set a partition expiration of 30 days on the hot table; archive raw messages to Cloud Storage with a lifecycle policy. Partition by event_ts and cluster by meter_id to reduce scans. Use the Storage Write API from Dataflow rather than legacy streaming inserts, which is generally cheaper and supports exactly-once semantics. Create materialized views or scheduled aggregates for the 5-minute dashboards so they do not scan detailed events repeatedly.
The judgement is to separate hot detail from long-term archive. Finance gets lower storage and insert cost, while operations still has a fast path. Keep a small dead-letter table for oversized messages and monitor its growth. Do not simply delete raw data without confirming the archive is queryable and meets retention requirements.