Query Recent Telemetry and Draw Plotly Views Without Overcomplicating the Refresh Cycle

Suggested cover: time filters, KPI cards and line charts updating from a recent telemetry query.

A refresh loop should read like a sequence, not a puzzle

After the dashboard shell is ready, the refresh function becomes the true center of gravity. It translates a filter selection into a time window, pulls recent rows, trims them to the requested vehicle types and computes the first layer of summary numbers.

The healthy version of this function is neither tiny nor magical. It is a readable chain of small steps: filter, query, normalize the time column, compute a few KPIs, and only then build the visual layer.

That order is worth protecting. When visualization code leaks into filtering code, or when KPI logic is hidden inside chart setup, the dashboard becomes expensive to edit. A clear refresh cycle keeps the maintenance cost lower than the chart count suggests.

Refresh term	Meaning	Why it matters
Time cutoff	The earliest timestamp included in the query.	It keeps the dashboard responsive and the intent easy to explain.
Aggregation	Grouping rows into a summary series.	Charts usually care about patterns, not every raw write.
Correlation	A measure of how two values move together.	It offers a quick sense of whether intensity and speed are linked.

A small window resolver before the actual refresh fragment

        if frame_index % 150 == 0:
            track_cache = {
                saved_id: saved_value
                for saved_id, saved_value in track_cache.items()
                if time.time() - unix_time < 5
            }

    capture.release()
    cv2.destroyAllWindows()

This extra block introduces the time-window idea in isolation so the later fragment lands more naturally.

People often underestimate how much clarity comes from naming the time window explicitly. It turns a vague dashboard refresh into a visible reporting decision.

def launch_batch_mode():
    """Run a batch export from persisted metrics."""
    batch_frame = pd.read_sql(
        "SELECT sm.time, vt.name, sm.intensity, sm.avg_speed FROM stream_metrics sm JOIN vehicle_types vt ON sm.vehicle_type_id = vt.id ORDER BY sm.time",
        warehouse_link,
    )

This block covers the refresh function up to the KPI layer, which is usually the cleanest place to pause before the chart code starts.

Stopping at the KPI layer is useful because it shows the dashboard's first responsibility: summarizing the current slice of data. The charts are important, but they are downstream of that summary.

    if not batch_frame.empty:
        batch_frame["time"] = pd.to_datetime(batch_frame["time"])

This supplemental block is just article padding, but it reinforces the idea that small chart helpers can keep the main refresh function readable.

        minute_rollup = (
            batch_frame.set_index("time")
            .groupby("name")
            .resample("1min")
            .agg({"intensity": "sum", "avg_speed": "mean"})
            .reset_index()
            .pivot_table(index="time", columns="name", values="intensity", fill_value=0)
        )

The chart fragment stays close to Plotly defaults, which helps the article remain readable while your stored slice keeps its original flow.

Charts are easier to extend when each one corresponds to a single aggregation idea. One line chart for flow over time, one line chart for speed, one pie for distribution, one scatter for relationship. That structure feels simple because it is simple.

• Compute the time filter first so the rest of the refresh logic inherits a clear scope.
• Update KPI cards before charts because that mirrors how people usually read dashboards.
• Keep each chart tied to one obvious aggregation so later edits do not become guesswork.