Building a Real-Time Analytics Dashboard with Streamlit and DuckDB

Building fast, interactive data science dashboards traditionally required spinning up heavy backend databases, caching layers, and complex API endpoints. For local analytics and rapid prototyping, this introduces unnecessary infrastructure overhead.

By combining Streamlit—Python's premier framework for turning data scripts into shareable web apps—with DuckDB's blazing-fast columnar analytical engine, you can query gigabytes of local Parquet or CSV files with sub-second latency and render stunning interactive dashboards instantly.

Architecture of a Streamlit + DuckDB App

The application architecture relies on an efficient in-memory data pipeline connecting user interface widgets directly to zero-copy file queries:

Frontend UI (Streamlit): Renders interactive sidebar filters, date range pickers, metrics, and Plotly charts that trigger reactive script reruns upon user input.
Analytical Engine (DuckDB): Acts as the lightning-fast SQL backend, executing vectorised filters directly over local Parquet storage without memory bottlenecks.
Caching Layer (`st.cache_data`): Caches connection instances and base queries to ensure high-frequency dashboard interactions remain instantaneous.

Pandas vs. DuckDB for Streamlit Dashboards

Evaluation Metric	Pandas DataFrame Filtering	DuckDB SQL Engine
Memory Consumption	Loads entire datasets into RAM (prone to MemoryErrors on large files)	Zero-copy streaming reads and memory-efficient out-of-core execution
Query Complexity	Requires chained boolean indexing and complex method syntax	Expressive standard SQL with window functions and complex joins
Rendering Latency	Slower on multi-gigabyte datasets during interactive filtering	Blazing fast sub-second aggregations via vectorised execution

Python Implementation Blueprint

Using streamlit and duckdb, you can build a complete analytical dashboard app in a single Python script (`app.py`):

              
            
Python
Streamlit dashboard app powered by DuckDB SQL queries
import streamlit as st
import duckdb

st.set_page_config(page_title="Analytics Dashboard", layout="wide")
st.title("🚀 Real-Time Analytics Dashboard")

# 1. Initialize cached DuckDB connection
@st.cache_resource
def get_db_connection():
    return duckdb.connect(database=":memory:", read_only=True)

con = get_db_connection()

# 2. Sidebar Filters
st.sidebar.header("Filter Controls")
min_passengers = st.sidebar.slider("Minimum Passengers", 1, 6, 1)

# 3. Query Parquet data using DuckDB with filter parameters
query = f"""
    SELECT 
        vendor_id,
        COUNT(*) as trip_count,
        AVG(fare_amount) as avg_fare
    FROM 'data/taxi_trips.parquet'
    WHERE passenger_count >= {min_passengers}
    GROUP BY vendor_id
"""

df_results = con.execute(query).fetchdf()

# 4. Render metrics and charts in Streamlit
st.subheader("Summary Metrics")
col1, col2 = st.columns(2)
col1.metric("Total Filtered Trips", f"{df_results['trip_count'].sum():,}")
col2.metric("Average Overall Fare", f"${df_results['avg_fare'].mean():.2f}")

st.dataframe(df_results, use_container_width=True)
st.bar_chart(df_results.set_index('vendor_id')['trip_count'])

Production Best Practices

Deploying Streamlit dashboards powered by DuckDB to production cloud environments requires adherence to caching and storage hygiene:

Use `@st.cache_resource` for Database Connections: Always cache your DuckDB connection object to prevent re-instantiating connections on every user interaction.
Store Data in Parquet Format: Avoid CSV files for large datasets; storing your source tables as partitioned Parquet files unlocks DuckDB's columnar pruning optimizations.
Set Read-Only Mode: When connecting DuckDB to multi-user web applications, initialize connections with `read_only=True` to prevent concurrent write contention.