Cache a Small Analytics Window Without Letting Fresh State Go Missing

Suggested cover: a compact analytics screen where a cache boundary, refresh state and a recent KPI window sit side by side.

The loader becomes easier to reuse when its steps stay separated

The data-loading section already contains a clean sequence: open the connection, resolve the cutoff, define the SQL, read the frame, normalize the timestamp and return the result. The logic is direct, but it is easier to scan when those stages are split into smaller source-faithful blocks.

This version keeps the same function and the same query, but spreads the code across a few more slices so the database boundary and the cleanup step are easier to lift back into the script.

Term	Meaning	Why it matters
TTL	The cache window declared on the loader.	It prevents noisy reloads while preserving near-real-time behavior.
Cutoff	The timestamp boundary derived from the slider value.	It defines exactly how much history the query should see.
Normalization	The time conversion that runs after the SQL read.	It prepares the frame for later grouping and charting.

The loader opens with the decorator, connection and cutoff

@st.cache_data(ttl=10)
def load_data(hours):
    conn = get_connection()
    cutoff = datetime.now() - timedelta(hours=hours)

The first slice keeps the cache rule and the database handoff close together, which makes the freshness boundary easy to trace.

The SQL stays in its own block

    query = """
        SELECT 
            sm.time,
            vt.name as vehicle_type,
            sm.intensity,
            sm.avg_speed
        FROM stream_metrics sm
        JOIN vehicle_types vt ON sm.vehicle_type_id = vt.id
        WHERE sm.time >= %s
        ORDER BY sm.time DESC
        LIMIT 500
    """

Keeping the SQL together as a standalone block makes the selected columns and ordering rules easier to verify.

The read and cleanup steps remain literal too

    df = pd.read_sql(query, conn, params=(cutoff,))
    conn.close()

The dataframe read and connection close are short enough to sit together without hiding what the loader is doing.

    if not df.empty:
        df['time'] = pd.to_datetime(df['time'])

    return df

The timestamp normalization and return stay in their own slice now, so the cleanup boundary is easier to spot.

• The loader still uses the original function body, but it now reads in smaller pieces.
• The query is no longer buried inside one large code block with the rest of the function.
• The database close and the timestamp conversion remain direct source fragments.

When the loader is split into small literal stages, it is easier to put the whole function back together correctly.