Building a Real-Time Dashboard with GCP

"Real-time" is the most expensive word in a dashboard request. It usually arrives without a number attached, and the number matters: a dashboard that is a minute behind and one that is a second behind are different systems. A real-time dashboard on GCP is a chain of stages. Each adds a little delay, and the total is what the viewer experiences.

This guide builds the chain, shows where the delay hides, and replays an hour of real events to show what "live" numbers do after they first appear.

First, how fresh?

Ask what decision the dashboard supports and how quickly anyone could act on it. An operations screen watched by a dispatcher needs seconds. A sales chart glanced at twice a day does not, whatever the request said. For the batch version, see building a data pipeline on GCP.

The chain

Stages 4 and 5 are where most "the dashboard is not live" complaints come from. Data Studio keeps query results and serves them again until a freshness threshold passes. For a BigQuery source that threshold is 12 hours by default, and the shortest setting is one minute (Google Cloud: manage data freshness). A streaming pipeline with a two-second delay behind a report on default settings is a twelve-hour dashboard.

Stage by stage

Publish. Applications publish each event to a Pub/Sub topic. See Pub/Sub explained.

Land. If the events are already in the shape of the table, a BigQuery subscription is the shortest path. It writes messages into an existing table as they arrive, with no subscriber to run (Google Cloud: BigQuery subscriptions). If events need parsing, enriching, deduplicating or windowing first, put Dataflow in between. See Pub/Sub to Dataflow to BigQuery. Either way, rows written through the Storage Write API's default stream can be queried immediately (Google Cloud: Storage Write API).

Summarise. Do not point the dashboard at the raw event table. Three ways to keep a small table of totals:

• A materialized view. BigQuery maintains it in the background and combines the precomputed part with recent changes, so results are fresh (Google Cloud: materialized views). See materialized views in BigQuery.
• A per-minute table, updated every minute by a scheduled statement.
• A continuous query, a SQL statement that runs without stopping and writes its output rows to a table or a topic (Google Cloud: continuous queries).

Serve. Connect the BI tool to the small table and set its freshness to match the need. BI Engine, BigQuery's in-memory layer, can speed up the repeated small queries that dashboards send (Google Cloud: BI Engine). See Looker and Looker Studio.

An hour of events, replayed

The script replays one real hour: the 2,440 New York taxi trips picked up between 08:00 and 09:00 on 15 January 2024 (NYC TLC: trip record data). Each event arrives a few seconds to a minute after it happened, and every 50th arrives seven minutes late. Those delays are invented.

Every minute, the script recomputes the last ten minutes of a per-minute table from the events that have arrived. DuckDB stands in for BigQuery, and nothing runs on Google Cloud.

from datetime import datetime, timedelta

import duckdb

db = duckdb.connect()
db.execute("SET TimeZone = 'UTC'")

# One hour of real trips, replayed as a stream. Each event arrives a little after it happened,
# and every 50th event arrives seven minutes late.
db.execute("""
    CREATE TABLE events AS
    SELECT pickup_at, fare_amount,
           pickup_at + to_seconds(CASE WHEN n % 50 = 0 THEN 420 ELSE 5 + n % 60 END) AS arrived_at
    FROM (SELECT tpep_pickup_datetime AS pickup_at, fare_amount,
                 row_number() OVER (ORDER BY tpep_pickup_datetime, tpep_dropoff_datetime) AS n
          FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')
          WHERE tpep_pickup_datetime >= TIMESTAMP '2024-01-15 08:00:00'
            AND tpep_pickup_datetime <  TIMESTAMP '2024-01-15 09:00:00')""")
total, late = db.execute("SELECT count(*), count(*) FILTER (arrived_at - pickup_at > INTERVAL 5 MINUTE) "
                         "FROM events").fetchone()
print(f"events in the hour: {total:,}   arriving more than five minutes late: {late}")


def replay(window, show=()):
    """Every minute, recompute the last `window` minutes from the events that have arrived."""
    db.execute("CREATE OR REPLACE TABLE per_minute (minute TIMESTAMP, trips BIGINT, fares DOUBLE)")
    clock = datetime(2024, 1, 15, 8, 0)
    while clock < datetime(2024, 1, 15, 9, 10):
        clock += timedelta(minutes=1)
        since = clock - timedelta(minutes=window)
        db.execute("DELETE FROM per_minute WHERE minute >= ?", [since])
        db.execute("""INSERT INTO per_minute
                      SELECT date_trunc('minute', pickup_at), count(*), sum(fare_amount)
                      FROM events WHERE arrived_at <= ? AND pickup_at >= ? GROUP BY 1""", [clock, since])
        if f"{clock:%H:%M}" in show:
            raw = db.execute("SELECT count(*) FROM events WHERE arrived_at <= ?", [clock]).fetchone()[0]
            rows, shown = db.execute("SELECT count(*), max(trips) FILTER (minute = TIMESTAMP '2024-01-15 08:21:00') "
                                     "FROM per_minute").fetchone()
            print(f"{clock:%H:%M}          {raw:>15,}   {rows:>20}   {shown:>21}")
    return db.execute("SELECT sum(trips) FROM per_minute").fetchone()[0]


print()
print("dashboard at   raw rows so far   rows in minute table   trips shown for 08:21")
kept = replay(window=10, show=("08:22", "08:24", "08:30", "09:10"))
short = replay(window=5)
print()
print("trips in the minute table at 09:10")
print(f"  recomputing the last 10 minutes: {kept:,} of {total:,}")
print(f"  recomputing the last  5 minutes: {short:,} of {total:,}   ({total - short} late events never counted)")

events in the hour: 2,440   arriving more than five minutes late: 48

dashboard at   raw rows so far   rows in minute table   trips shown for 08:21
08:22                      716                     22                      23
08:24                      798                     24                      52
08:30                    1,029                     30                      54
09:10                    2,440                     60                      54

trips in the minute table at 09:10
  recomputing the last 10 minutes: 2,440 of 2,440
  recomputing the last  5 minutes: 2,392 of 2,440   (48 late events never counted)

• The number for 08:21 was shown three ways. One minute after it ended, the dashboard said 23 trips. Two minutes later it said 52, and by 08:30 it said 54. All three were correct for what had arrived.
• The first change is ordinary delay. Events take up to a minute to arrive, so a minute that has just ended is always incomplete.
• The second is late data. Two events from 08:21 arrived seven minutes late and were added to the minute they belong to, not the minute they turned up in.
• A window that is too short loses data for good. Recomputing only the last five minutes never counts the 48 late events. The table is 2% low, and nothing reports an error.
• The dashboard table stayed small. After an hour it has 60 rows, while the raw table has 2,440 and grows with every event.

In BigQuery, the recompute step is a MERGE run every minute. It is written here in GoogleSQL and was not run:

MERGE `analytics.trips_per_minute` AS t
USING (
  SELECT TIMESTAMP_TRUNC(pickup_at, MINUTE) AS minute,
         COUNT(*) AS trips,
         SUM(fare_amount) AS fares
  FROM `analytics.trip_events`
  WHERE pickup_at >= TIMESTAMP_SUB(@run_time, INTERVAL 10 MINUTE)
  GROUP BY minute
) AS s
ON t.minute = s.minute
WHEN MATCHED THEN
  UPDATE SET trips = s.trips, fares = s.fares
WHEN NOT MATCHED THEN
  INSERT (minute, trips, fares) VALUES (s.minute, s.trips, s.fares);

Because it replaces the recent minutes instead of adding to them, running it twice changes nothing. Partition the event table by time so that each run reads only recent data.

Three rules for live numbers

1. Group by when the event happened, not when it arrived. Otherwise late events land in the wrong minute.
2. Choose how late is too late, on purpose. The window is a trade: longer catches more late events and reads more data on every run.
3. Mark the newest minute as provisional, or leave it off the chart. A line that dips at its right-hand edge every time anyone looks is not a fall in business. It is data still arriving.

What it costs

A live dashboard spends money in three places: the streaming writes, the queries that maintain the summary, and the queries the dashboard sends. The last is the one people forget. A report with ten charts, refreshed every minute by a wall screen, sends about 14,000 queries a day. Against a 60-row table that is negligible. Against the raw event table it is the largest line on the bill. See BigQuery cost optimization.

Common mistakes

• Building streaming ingestion and leaving the report on its default freshness.
• Pointing charts at the raw event table.
• Grouping by arrival time.
• Treating the newest value as final.
• Promising seconds when the decision takes hours.
• No alert when events stop. A flat line looks the same as a quiet day. See logging and monitoring on GCP.

Streaming and batch pipeline design are covered in the Data Engineering course.

Sources

• BigQuery subscriptions (Google Cloud Documentation) — Google Cloud
• Introduction to the BigQuery Storage Write API (Google Cloud Documentation) — Google Cloud
• Introduction to materialized views (Google Cloud Documentation) — Google Cloud
• Introduction to continuous queries (Google Cloud Documentation) — Google Cloud
• Manage data freshness (Google Cloud Documentation) — Google Cloud
• Introduction to BI Engine (Google Cloud Documentation) — Google Cloud
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission