Streamlit for Data Engineers: Internal Data Apps & Pipeline Dashboards

streamlit for data engineers answers a very specific, very common need: you have a warehouse full of pipeline metadata, freshness timestamps, and row counts, and someone — an analyst, an on-call engineer, your own future self at 3 a.m. — needs to see it and act on it without you standing up a React frontend and a Flask API. Streamlit turns a single Python script into a web app. You write import streamlit as st, add a few st. calls that print DataFrames and draw charts, run streamlit run app.py, and you have an internal tool on a URL. No HTML, no JavaScript, no callback wiring, no template engine.

That shape is a real departure from the two things data engineers reached for before it: a Jupyter notebook that only you can run and that no non-technical teammate will ever open, or a "proper" web app that costs a week of frontend work you do not want to own. Streamlit lives in between — it is code-first like the notebook and shareable like the web app. This guide walks the four ideas an interviewer or a code reviewer will actually probe when you put Streamlit in your stack — the top-to-bottom rerun model with widgets and session_state, the two caching decorators, displaying data and connecting to a warehouse, and assembling all of it into a pipeline-health / SLA dashboard — and pairs each with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the DataFrame shaping that feeds every widget on the data-analysis practice library →, sharpen the transforms behind your tables on the dataframe-basics practice set →, and rehearse the freshness-and-breach logic your dashboard renders on the sla-monitoring practice set →.

---

On this page

• Why Streamlit fits data engineering in 2026
• The rerun model, widgets & session_state
• Caching — st.cache_data vs st.cache_resource
• Displaying data & connecting to warehouses
• Building a pipeline-health / SLA dashboard
• Cheat sheet — Streamlit recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Streamlit fits data engineering in 2026

Streamlit is a Python script that renders as a web app — that one fact decides where it fits and how it behaves

The one-sentence invariant: a Streamlit app is an ordinary Python script that the framework re-executes top to bottom every time the user interacts with it, turning st. calls into UI. Everything surprising about Streamlit — why a variable resets, why caching matters so much, why you need session_state — falls out of that single sentence. There is no component tree you register, no event loop you write, no separate frontend build. You write the script the way you would write an analysis, and Streamlit renders it.

What Streamlit does — and deliberately does not — do.

• UI from Python. st.dataframe(df), st.line_chart(df), st.button("Run"), st.metric("Rows", 1200) — each call emits a widget. You never touch HTML or CSS for the common cases.
• State and rerun handled for you. The framework owns the render loop: interact with a widget and the whole script reruns. You do not manage a DOM or diff a virtual tree.
• Not a BI product, not a heavy web framework. Streamlit is not trying to be Tableau (no semantic layer, no governed metrics) and not trying to be Django (no ORM, no auth framework, no routing beyond simple multipage). It is the fastest path from "I have a DataFrame" to "my team can click on it."

Where Streamlit sits against the alternatives data engineers weigh.

• vs a Jupyter notebook. A notebook is for you exploring; a Streamlit app is for others using. The notebook has hidden execution order and stale cells; Streamlit reruns cleanly every time, so what the viewer sees always matches the current inputs.
• vs Flask / FastAPI + a JS frontend. A hand-rolled web app gives you total control and costs days of frontend work you must maintain. Streamlit trades that control for a Python-only script you finish in an afternoon — the right trade for an internal tool with ten users, the wrong one for a public product.
• vs Dash / Panel. Dash is callback-oriented (you wire inputs to outputs explicitly); Streamlit is rerun-oriented (no callbacks needed for the basics). Streamlit is faster to write; Dash gives finer-grained control when you truly need it.
• vs a BI dashboard (Looker, Metabase). BI tools own governed metrics and scheduled reports; Streamlit owns interactive tools that do things — trigger a backfill, edit a config, kick a re-run — which a read-only BI tile cannot.

What interviewers listen for.

• Do you say "the whole script reruns on every interaction" in the first sentence? — this is the senior signal; everything else is a corollary.
• Do you reach for st.cache_data / st.cache_resource unprompted when you mention loading data, because you know the rerun would otherwise re-query every time? — required framing.
• Do you know that a plain Python variable does not survive a rerun, and that st.session_state is the fix? — the number-one beginner gap.
• Do you place Streamlit as "internal tools and dashboards, not a public product" rather than as a Flask replacement for everything? — placement maturity.

Worked example — six lines that become a shareable app

Detailed explanation. The canonical Streamlit "hello world" reads a DataFrame and renders it as a title, an interactive table, and a chart. It looks trivial, and that is the point: the same six lines that render a toy DataFrame scale unchanged to a live warehouse query, because Streamlit only ever sees a DataFrame and a few st. calls. There is no server code, no route, no template — the script is the app.

Question. Turn a two-row pipeline-runs DataFrame into a titled app with an interactive table and a bar chart, runnable with one command.

Input.

pipeline	rows_loaded
orders_el	4200
events_el	9100

Code.

import streamlit as st
import pandas as pd

st.title("Pipeline runs")

df = pd.DataFrame(
    {"pipeline": ["orders_el", "events_el"], "rows_loaded": [4200, 9100]}
)

st.dataframe(df)                       # interactive, sortable table
st.bar_chart(df, x="pipeline", y="rows_loaded")

Step-by-step explanation. import streamlit as st pulls in the whole UI toolkit. st.title(...) emits an <h1> at the top of the page. Building df is ordinary pandas — Streamlit has no opinion about how you get the DataFrame. st.dataframe(df) renders a sortable, scrollable, searchable grid; st.bar_chart(...) draws a chart from the same frame with no plotting library imported. You launch it with streamlit run app.py, which starts a local server and opens the browser; every save auto-reloads.

Output.

the app shows	rendered from
page title "Pipeline runs"	st.title(...)
an interactive 2-row table	st.dataframe(df)
a bar chart, pipeline vs rows_loaded	st.bar_chart(...)
a live URL on localhost:8501	streamlit run app.py

Rule of thumb. If your logic ends in a DataFrame, a number, or a chart, Streamlit can display it — the toy example and the production dashboard differ only in where the DataFrame comes from, never in the st. calls that render it.

---

2. The rerun model, widgets & session_state

The whole script reruns top-to-bottom on every interaction — widgets return values, session_state is what survives

If you internalise one thing about Streamlit, make it this: there is no event loop and no callback graph; every widget interaction re-executes your entire script from the first line to the last. A button click, a slider drag, a dropdown change — each schedules a fresh top-to-bottom run. Widgets are not "handlers you attach"; they are function calls that return their current value on each run. This is why a normal variable resets and why state needs a special home.

The rerun loop, precisely.

• Interaction → rerun. The user moves a slider; Streamlit re-runs app.py from the top. Your code executes again, the slider call returns the new value, and the page is redrawn from scratch.
• Widgets return values, not events. value = st.slider("Days", 1, 30, 7) returns 7 on the first run and whatever the user picked on later runs. You use the return value inline — no onChange handler.
• Plain variables do not persist. Any x = 0 at the top is re-initialised to 0 on every rerun. If you increment it on a click, the increment is wiped by the next rerun. This is the single most common beginner bug.

st.session_state — the store that survives reruns.

• A dict scoped to the browser session. st.session_state is a dictionary-like object that persists across reruns for one user session. Write st.session_state["count"] = 0 once, read and mutate it on later runs.
• Widgets can bind to it via key. Give a widget key="days" and its value is mirrored at st.session_state["days"]; reading the key elsewhere gives the live value without threading it through function arguments.
• Initialise defensively. Because the script reruns, guard initialisation with if "count" not in st.session_state: so you seed the value once and never clobber it.

Callbacks and the button gotcha.

• Callbacks run before the rerun. st.button("Add", on_click=fn) runs fn first, then reruns the script. Callbacks (on_click, on_change) are where you mutate session_state cleanly, and they receive args / kwargs.
• st.button is transient. st.button(...) returns True only on the single rerun immediately following the click, then False again. So if st.button("Go"): x = compute() recomputes once — do not expect the True to "stick." For durable toggles use st.session_state or st.checkbox.

Worked example — a counter that actually counts

Detailed explanation. The classic demonstration of the rerun model is a click counter, because the naive version is broken in a way that teaches the whole model. A plain variable is reset by the rerun; session_state plus a callback is the fix. Getting this right is the difference between an app that "forgets" and one that holds state.

Question. Build a button that increments a counter and shows the running total, surviving every rerun.

Input. Three clicks of the "Add one" button.

Code.

import streamlit as st

if "count" not in st.session_state:     # seed once, never on later reruns
    st.session_state.count = 0

def increment():                        # callback runs BEFORE the rerun
    st.session_state.count += 1

st.button("Add one", on_click=increment)
st.write("Count:", st.session_state.count)

Step-by-step explanation. On the first run, count is absent, so it is seeded to 0. Clicking the button fires increment before the rerun, so count becomes 1; then the script reruns, the if guard is skipped (key exists), and st.write prints 1. Each subsequent click repeats: callback mutates state, script reruns, the guard leaves the existing value alone. Had we written count = 0 as a plain variable at the top, every rerun would reset it to 0 and the display would never exceed 1.

Output.

click #	value before callback	value shown
1	0	1
2	1	2
3	2	3

Rule of thumb. Anything that must outlive a rerun lives in st.session_state; mutate it in a callback, and guard its initialisation with an if key not in st.session_state check.

Streamlit interview question on the rerun model

Question. A candidate writes clicks = 0 at the top of the script and does if st.button("Click"): clicks += 1; st.write(clicks). The display never goes past 1. Explain exactly why, and fix it so the count accumulates.

Solution Using session_state seeded once with a callback

Code.

import streamlit as st

# BROKEN: `clicks` is a plain variable, reset to 0 on every rerun
# clicks = 0
# if st.button("Click"):
#     clicks += 1
# st.write(clicks)          # always 0 or 1

# FIXED: state lives in session_state, mutated in a callback
if "clicks" not in st.session_state:
    st.session_state.clicks = 0

def bump():
    st.session_state.clicks += 1

st.button("Click", on_click=bump)
st.write(st.session_state.clicks)

Step-by-step trace.

event	rerun #	clicks at top	after handler	displayed
initial load	1	seeded 0	0	0
click	2	0 (state)	bump → 1	1
click	3	1 (state)	bump → 2	2
click	4	2 (state)	bump → 3	3

1. In the broken version, clicks = 0 executes on every rerun, so the pre-increment value is always 0; the click makes it 1, and the next rerun resets it. The counter can never exceed 1.
2. st.button(...) returns True only on the rerun right after the click, so the += 1 fires at most once per click — but against a variable that was just reset.
3. The fix seeds clicks in session_state once, guarded by the not in check, so it is not clobbered on reruns.
4. The on_click=bump callback mutates the persisted value before the redraw, so st.write reads the accumulated total.

Output:

after 3 clicks	broken version	fixed version
displayed count	1	3

Why this works — concept by concept:

• Top-to-bottom rerun — because the entire script re-executes on each interaction, any assignment at module scope is re-run and therefore re-initialised; state cannot live in a local variable.
• session_state persistence — a dict scoped to the session survives reruns, so a value written once is readable and mutable on every later run.
• Guarded initialisation — the if key not in st.session_state seed runs on the first rerun only, protecting the accumulated value from being reset.
• Callback ordering — on_click runs before the rerun, so the mutation is visible to the redraw in the same cycle, avoiding an off-by-one lag.
• Cost — the rerun is O(script length) each interaction; state access is O(1), which is why heavy work must be cached rather than repeated.

Analysis
Topic — data-analysis
Interactive data-analysis app problems

Practice →

DataFrames Topic — dataframe-basics DataFrame state-and-filter problems

Practice →

---

3. Caching — st.cache_data vs st.cache_resource

The rerun re-runs everything, so caching is not optional — pick st.cache_data for values and st.cache_resource for connections

Because the whole script reruns on every click, an uncached pd.read_sql(...) re-queries the warehouse every time a user drags a slider — unusable. Streamlit's answer is two decorators, and the interview question is always "which one and why." Say it in one breath: st.cache_data memoizes the return value and hands each caller a fresh copy; st.cache_resource caches a single global object and hands every caller the same instance.

st.cache_data — for data you compute or fetch.

• Memoizes serializable results. Wrap a function that returns a DataFrame, a dict, a list, an API response — anything picklable. On a cache hit Streamlit skips the body and returns the stored value.
• Keyed on the function's inputs. Streamlit hashes the arguments (and the function's code); same args → cache hit, different args → recompute. So load(start, end) caches per date range automatically.
• Returns a copy every time. Each caller gets its own copy of the result, so one user mutating the DataFrame cannot corrupt another user's cached copy. That safety is the whole reason it is separate from cache_resource.

st.cache_resource — for global singletons.

• Caches the object itself, not a copy. A database connection, a SQLAlchemy engine, an ML model, a client handle — things that are expensive to create and meant to be shared. Every caller receives the same live object.
• Not serialized, not copied. Because it is shared, it must be safe for concurrent use; a connection pool or a thread-safe client is the right kind of thing to put here.
• One per app, across sessions. Unlike session_state (per session), a cache_resource object is shared across all users and reruns until the app restarts or you clear it.

The knobs and the failure modes.

• ttl. Expire entries after N seconds (ttl=600) so a dashboard shows data at most ten minutes stale without a manual refresh.
• max_entries. Cap the number of cached results (e.g. max_entries=50) to bound memory when the argument space is large.
• show_spinner and .clear(). Toggle the "Running..." spinner, and call load.clear() to evict programmatically (e.g. after a backfill writes new rows).
• The classic mistake: putting a database connection in st.cache_data (it tries to pickle the connection and fails or misbehaves), or putting a DataFrame you mutate in st.cache_resource (mutations leak across users because there is no copy).

Worked example — cache the query result, cache the engine once

Detailed explanation. The everyday pattern pairs the two decorators: cache_resource opens the SQLAlchemy engine one time and shares it; cache_data runs the query and memoizes the resulting DataFrame per set of parameters. Together they turn a per-rerun round trip into a per-argument-set round trip.

Question. Write a helper that opens a Postgres engine once and a query function that caches its DataFrame for five minutes, keyed on the day range.

Input. Two reruns with the same days=7, then one with days=30.

Code.

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine, text

@st.cache_resource                      # one engine, shared across all reruns/users
def get_engine():
    return create_engine(st.secrets["db_url"])

@st.cache_data(ttl=300)                 # memoize the DataFrame per `days`, 5-min TTL
def load_runs(days: int) -> pd.DataFrame:
    sql = text("SELECT pipeline, status, ended_at FROM runs "
               "WHERE ended_at > now() - make_interval(days => :d)")
    with get_engine().connect() as conn:
        return pd.read_sql(sql, conn, params={"d": days})

days = st.slider("Look-back (days)", 1, 30, 7)
st.dataframe(load_runs(days))

Step-by-step explanation. The first rerun calls get_engine(), which builds the engine and caches the object; load_runs(7) runs the SQL and caches the DataFrame under key days=7. Dragging the slider and releasing at 7 again reruns the script, but get_engine() returns the cached engine and load_runs(7) returns the cached DataFrame — zero database work. Moving the slider to 30 is a new argument, so load_runs(30) misses the cache and queries once, then caches that too. After five minutes the days=7 entry expires and the next access re-queries.

Output.

rerun	engine built?	query run?	why
1 (days=7)	yes	yes	cold cache
2 (days=7)	no	no	both cache hits
3 (days=30)	no	yes	new arg → data miss
4 (days=7, >5 min later)	no	yes	ttl expired

Rule of thumb. If the function returns data, decorate with st.cache_data; if it returns a thing you open once and reuse (a connection, an engine, a client, a model), decorate with st.cache_resource.

Streamlit interview question on choosing a cache decorator

Question. An engineer decorates their get_connection() function with @st.cache_data and their load_orders() DataFrame function with @st.cache_resource. The app is slow and occasionally shows another user's filtered data. Which decorators are swapped, and what breaks in each case?

Solution Using cache_resource for the connection and cache_data for the DataFrame

Code.

import streamlit as st
import pandas as pd

# WRONG:
# @st.cache_data           -> tries to pickle a live connection (fails / re-opens)
# def get_connection(): ...
# @st.cache_resource       -> shares ONE DataFrame object; mutations leak across users
# def load_orders(): ...

# RIGHT:
@st.cache_resource                      # the connection is a shared singleton
def get_connection():
    return open_warehouse_connection()

@st.cache_data(ttl=600)                 # each caller gets a fresh copy of the data
def load_orders(region: str) -> pd.DataFrame:
    conn = get_connection()
    return pd.read_sql(f"SELECT * FROM orders WHERE region = '{region}'", conn)

Step-by-step trace.

symptom	wrong decorator	what actually happens
app is slow	get_connection under cache_data	connection is not a picklable value; caching misbehaves, so it re-opens each rerun
user B sees user A's data	load_orders under cache_resource	the DataFrame is a shared singleton — no copy — so B reads A's filtered frame
fixed: fast	get_connection under cache_resource	one connection, shared, reused every rerun
fixed: isolated	load_orders under cache_data	each region's result is a separate copy, keyed on region

1. st.cache_data is designed for serializable return values and hands back a copy; a live DB connection is neither serializable nor safe to copy, so it belongs in cache_resource.
2. st.cache_resource shares one object across all sessions; a DataFrame stored there is mutated in place by whoever touches it, leaking one user's view into another's.
3. Swapping them restores both properties: the connection is opened once and shared, and each query result is an isolated, argument-keyed copy.
4. Adding ttl=600 to load_orders bounds staleness without changing correctness.

Output:

function	correct decorator	guarantee
get_connection	st.cache_resource	one shared, reused connection
load_orders	st.cache_data	per-arg copy, no cross-user leak

Why this works — concept by concept:

• cache_data semantics — memoizes a function's return value keyed on its arguments and returns a fresh copy each call, which is exactly right for query results and computed frames.
• cache_resource semantics — caches a single global object with no copy, which is exactly right for connections and models that are expensive to build and meant to be shared.
• Copy vs share — the copy in cache_data is what isolates users; the shared instance in cache_resource is what makes a connection reusable — mixing them swaps safety for a leak and speed for a stall.
• TTL invalidation — a time-to-live bounds staleness so a dashboard refreshes on its own, and .clear() lets a write path evict immediately.
• Cost — a cache hit is O(1) plus a hash of the arguments, turning an O(query) round trip on every rerun into one round trip per distinct argument set.

DataFrames
Topic — dataframe-basics
DataFrame caching and memoization problems

Practice →

Analysis Topic — data-analysis Query-and-aggregate analysis problems

Practice →

---

4. Displaying data & connecting to warehouses

From DataFrame to dashboard is one call — st.dataframe, the built-in charts, and st.connection for the warehouse

Once you have a DataFrame, Streamlit gives you display primitives that need no plotting or web code, and a first-class way to get that DataFrame from a warehouse. The framing an interviewer wants: you render data with st.dataframe and the st.*_chart family, and you source it with st.connection, whose conn.query(...) caches results for you. No ORM, no cursor management, no manual connection pooling.

Display primitives, by intent.

• st.dataframe(df) — interactive. A sortable, scrollable, searchable grid. Style it with column_config (format a number as currency, render a URL as a link, show a progress bar in a cell) and enable row selection when you need the user to pick rows.
• st.data_editor(df) — editable. The same grid, but the user can edit cells; it returns the edited DataFrame, which is how you build a lightweight config or mapping editor.
• st.table(df) — static. Renders the whole frame at once, no interactivity — good for small, fixed summaries.
• st.metric(label, value, delta) — a KPI tile. A big number with an optional up/down delta; the building block of the header row on any dashboard.

Built-in charts, zero imports.

• st.line_chart / st.area_chart / st.bar_chart / st.scatter_chart. Pass a DataFrame plus x= and y= (and optional color= to split series) and Streamlit draws an interactive chart with no Matplotlib or Plotly import. Ideal for latency-over-time and volume-by-pipeline views.
• Escape hatch when you need it. For full control, st.plotly_chart(fig), st.altair_chart(chart), and st.pyplot(fig) accept figures from those libraries — you drop down only when the built-ins are not enough.

Connecting to a warehouse with st.connection.

• One call, configured by secrets. conn = st.connection("warehouse", type="sql") reads its URL/credentials from .streamlit/secrets.toml under [connections.warehouse], so no credentials live in code.
• conn.query(sql, ttl=...) caches results. The SQLConnection.query method runs the SQL and memoizes the DataFrame with a built-in TTL — it is st.cache_data under the hood, so you get caching without decorating anything.
• Typed connections. type="sql" covers any SQLAlchemy URL (Postgres, MySQL, DuckDB, SQLite); type="snowflake" uses the Snowflake connector; BigQuery is reachable via the SQLAlchemy dialect or a cache_resource client. Parameterise with params= — never f-string user input into SQL.

Worked example — query the warehouse and render three ways

Detailed explanation. The bread-and-butter dashboard cell queries the warehouse once through st.connection, then feeds the single result DataFrame into a metric, a chart, and a table. One query, three renders — and the query is cached, so reruns are free until the TTL lapses.

Question. Using st.connection, pull daily loaded-row counts for the last 14 days and show a total-rows metric, a line chart of the trend, and the raw table.

Input. A daily_loads table with day and rows_loaded columns.

Code.

import streamlit as st

conn = st.connection("warehouse", type="sql")

df = conn.query(
    """
    SELECT day, rows_loaded
    FROM daily_loads
    WHERE day > current_date - 14
    ORDER BY day
    """,
    ttl=600,                            # cache the result for 10 minutes
)

st.metric("Rows loaded (14d)", f"{df['rows_loaded'].sum():,}")
st.line_chart(df, x="day", y="rows_loaded")
st.dataframe(df, use_container_width=True)

Step-by-step explanation. st.connection("warehouse", type="sql") builds (or reuses) a connection from the [connections.warehouse] block in secrets.toml. conn.query(..., ttl=600) executes the SQL and caches the DataFrame for ten minutes, so any rerun within that window skips the round trip. The one df then drives three widgets: st.metric shows the summed total with a thousands separator, st.line_chart plots rows_loaded against day, and st.dataframe renders the sortable grid. Nothing here imports a database driver or a charting library directly.

Output.

widget	shows
st.metric	"Rows loaded (14d) — 128,400"
st.line_chart	14-point trend line of daily volume
st.dataframe	sortable 14-row table

Rule of thumb. Query once with conn.query(..., ttl=...) and reuse the one DataFrame for every widget; do not run a separate query per chart, and never build SQL by concatenating user input — pass params=.

Streamlit interview question on warehouse connections

Question. A dashboard calls pd.read_sql(build_sql(region), psycopg2.connect(DSN)) at the top of the script. It re-opens a connection and re-queries on every slider move, and the DSN is hard-coded. Rewrite it the Streamlit way so the connection is reused, the query is cached per region, and credentials are out of the code.

Solution Using st.connection with a parameterised, cached query

Code.

import streamlit as st

# BEFORE: new connection + query on every rerun, credentials in code, SQL injection risk
# conn = psycopg2.connect("host=... user=... password=...")
# df = pd.read_sql(f"SELECT * FROM runs WHERE region = '{region}'", conn)

conn = st.connection("warehouse", type="sql")   # credentials from secrets.toml

region = st.selectbox("Region", ["us", "eu", "apac"])

df = conn.query(
    "SELECT pipeline, status, rows_loaded FROM runs WHERE region = :region",
    params={"region": region},          # bound param, not string interpolation
    ttl=600,                             # cache per region for 10 minutes
)

st.dataframe(df, use_container_width=True)

Step-by-step trace.

interaction	region	connection	query executed?
load	us	opened once, cached	yes (cold)
pick eu	eu	reused	yes (new param)
pick us again	us	reused	no (cache hit)
pick us, 11 min later	us	reused	yes (ttl expired)

1. st.connection(...) builds the connection once and caches it (via cache_resource internally), so no rerun re-opens it.
2. Credentials move to .streamlit/secrets.toml under [connections.warehouse], so the DSN is out of source control.
3. conn.query(sql, params={"region": region}, ttl=600) binds region as a parameter — closing the SQL-injection hole — and memoizes the result per region for ten minutes.
4. Re-selecting a region within the TTL is a cache hit, so the warehouse is queried once per distinct region per ten-minute window instead of once per interaction.

Output:

property	before	after
connections opened	one per rerun	one, reused
queries per repeat interaction	one each time	one per region per TTL
credentials	hard-coded	in secrets.toml
injection risk	yes (f-string)	no (bound param)

Why this works — concept by concept:

• st.connection — wraps connection construction in a cached factory, so the expensive open happens once and every rerun reuses the same handle.
• Cached query — conn.query(..., ttl=...) is cache_data under the hood, keying the DataFrame on the SQL and params, so identical interactions are free.
• Bound parameters — passing params= sends values separately from the SQL text, which both enables correct cache keying and eliminates injection.
• Secrets separation — reading credentials from secrets.toml keeps them out of the repo and lets the same code run against dev and prod by swapping the file.
• Cost — the query cost collapses from O(interactions) round trips to O(distinct params per TTL), the single biggest performance win in a Streamlit data app.

Analysis
Topic — data-analysis
Query, aggregate and visualize problems

Practice →

SLA Topic — sla-monitoring Freshness and SLA-metric problems

Practice →

---

5. Building a pipeline-health / SLA dashboard

Metrics, a freshness table, a backfill button — assembling the primitives into an SLA dashboard with forms and fragments

Everything so far converges here: a real internal tool a data team uses. A pipeline-health dashboard shows KPI metrics up top, a freshness table with SLA breaches flagged, and controls that do something — a form that triggers a backfill. Two more primitives finish it: st.form to batch inputs so the script does not rerun on every keystroke, and st.fragment to refresh part of the page without re-running the whole thing.

The layout primitives.

• st.columns for the KPI row. c1, c2, c3 = st.columns(3) then c1.metric(...) places metrics side by side. st.metric("SLA met", "96%", delta="-2%") shows the value and a coloured delta.
• st.dataframe with column_config for the freshness table. Format last_run as a timestamp, render minutes_late with a progress bar, and use row styling (via a Styler or a status column) to make breaches obvious.
• st.sidebar and st.tabs for structure. Filters go in st.sidebar; separate an "Overview" tab from a "Backfill" tab with st.tabs([...]).

Forms and buttons — controls that act.

• st.form batches input. Widgets inside with st.form("backfill"): do not trigger a rerun individually; the script reruns only when the user clicks st.form_submit_button(...). This is essential for a backfill panel with several inputs — you do not want a rerun (or a job) fired on every field change.
• The submit button gates the action. if st.form_submit_button("Run backfill"): is True on the submitting rerun, so you kick the job inside that block. Guard destructive actions with a confirmation checkbox or a typed-in pipeline name.
• Long jobs get status feedback. Wrap the work in with st.status("Backfilling...", expanded=True) as s: and update it, or use st.spinner / st.progress, so the user sees progress instead of a frozen page.

Auto-refresh without re-running everything.

• @st.fragment for partial reruns. Decorate a function with @st.fragment and only that block reruns when its own widgets change — the rest of the page stays put. This keeps a heavy dashboard responsive when one control should not re-query everything.
• run_every for a live tile. @st.fragment(run_every="30s") re-runs that fragment on a timer, so a freshness table or a "last updated" clock refreshes itself without a full-page reload.
• st.rerun() to force a cycle. After a backfill writes new rows, call load.clear() then st.rerun() to evict the cache and redraw with fresh data immediately, rather than waiting for the TTL.

Deployment, briefly.

• streamlit run app.py locally; behind a reverse proxy or on Streamlit Community Cloud (point it at a GitHub repo, set secrets in the dashboard) for zero-ops sharing.
• Docker for self-hosting: base image, pip install -r requirements.txt, EXPOSE 8501, CMD ["streamlit", "run", "app.py"]. Put credentials in secrets.toml or environment variables, never in the image.

Worked example — a freshness table that flags SLA breaches

Detailed explanation. The heart of a pipeline-health dashboard is a freshness table: for each pipeline, how long since its last successful run, and is that within its SLA. The logic is a per-row comparison of lateness against a threshold, rendered so breaches jump out. This is the view an on-call engineer opens first.

Question. Given a DataFrame of pipelines with minutes_since_run and an sla_minutes threshold, compute a status of OK or BREACH and show a metric for the breach count plus the flagged table.

Input.

pipeline	minutes_since_run	sla_minutes
orders_el	25	60
events_el	130	90
dim_refresh	40	45

Code.

import streamlit as st
import pandas as pd

df = load_freshness()                   # cached conn.query(...) from earlier

df["status"] = df.apply(
    lambda r: "BREACH" if r.minutes_since_run > r.sla_minutes else "OK",
    axis=1,
)
breaches = int((df["status"] == "BREACH").sum())

st.metric("SLA breaches", breaches, delta=None)
st.dataframe(
    df.style.apply(
        lambda r: ["background-color: #fee2e2" if r.status == "BREACH" else "" for _ in r],
        axis=1,
    ),
    use_container_width=True,
)

Step-by-step explanation. load_freshness() returns the cached freshness DataFrame. The apply compares minutes_since_run to each pipeline's own sla_minutes and writes BREACH or OK into a new status column. breaches counts the flagged rows for the KPI tile. st.metric surfaces that count up top, and st.dataframe renders the table with a row-level Styler that tints breach rows red, so the one late pipeline is impossible to miss.

Output.

pipeline	minutes_since_run	sla_minutes	status
orders_el	25	60	OK
events_el	130	90	BREACH
dim_refresh	40	45	OK

The metric reads SLA breaches — 1, and the events_el row is highlighted.

Rule of thumb. Compare lateness against a per-pipeline SLA, not one global threshold; render the breach as both a headline metric and a highlighted row so the summary and the detail agree.

Streamlit interview question on triggering a backfill safely

Question. Product wants a button on the dashboard that backfills a chosen pipeline over a chosen date range. A naive if st.button("Backfill"): run_backfill(...) outside a form fires on stray reruns and offers no confirmation. Build a safe backfill control that only runs on explicit submit and refreshes the data afterward.

Solution Using st.form, a submit button, and a post-run rerun

Code.

import streamlit as st

with st.form("backfill"):
    pipeline = st.selectbox("Pipeline", ["orders_el", "events_el", "dim_refresh"])
    start, end = st.date_input("Date range", [])
    confirm = st.checkbox("I understand this reprocesses data")
    submitted = st.form_submit_button("Run backfill")

if submitted and confirm:
    with st.status(f"Backfilling {pipeline}...", expanded=True) as status:
        run_backfill(pipeline, start, end)      # your job trigger
        status.update(label="Backfill complete", state="complete")
    load_freshness.clear()                       # evict stale cached data
    st.rerun()                                   # redraw with fresh numbers
elif submitted and not confirm:
    st.warning("Tick the confirmation box before running a backfill.")

Step-by-step trace.

user action	reruns fired	backfill runs?
change pipeline dropdown (in form)	0	no — form defers rerun
pick a date range (in form)	0	no — still deferred
click "Run backfill", confirm unticked	1 (on submit)	no — warning shown
tick confirm, click "Run backfill"	1 (on submit)	yes, then cache cleared + rerun

1. Widgets inside with st.form(...) do not trigger a rerun on change, so choosing a pipeline and a date range fires no premature runs and no stray job.
2. st.form_submit_button reruns the script once and returns True only on that submitting run, so the job can only start from an explicit click.
3. The and confirm gate requires the checkbox, turning an accidental click into a harmless warning instead of a reprocessing job.
4. After the job, load_freshness.clear() evicts the cached freshness data and st.rerun() forces an immediate redraw, so the dashboard reflects the backfill without waiting for the TTL.

Output:

guarantee	mechanism
no run on field edits	st.form defers reruns to submit
no run without confirmation	and confirm gate
fresh data after run	.clear() + st.rerun()
visible progress	st.status(...)

Why this works — concept by concept:

• st.form batching — grouping inputs in a form suppresses the per-widget rerun, so multi-field controls neither thrash the script nor trigger side effects until submit.
• Submit-gated action — st.form_submit_button is the only trigger, and its True is confined to the submitting rerun, so a job starts exactly once per intentional click.
• Confirmation gate — requiring a checkbox before a destructive action converts the transient button click into a two-step, mistake-resistant flow.
• Cache clear + rerun — evicting the memoized query and forcing a rerun makes the freshly written rows appear immediately instead of on the next TTL boundary.
• Cost — the control adds O(1) work and one extra rerun per backfill, a negligible price for making a data-mutating action deliberate and its result immediately visible.

SLA
Topic — sla-monitoring
SLA-breach and freshness-dashboard problems

Practice →

Pipelines Topic — pipelines Backfill and pipeline-control problems

Practice →

---

Cheat sheet — Streamlit recipes

Minimal app.

import streamlit as st
st.title("Hello")
st.write("It renders.")     # run with: streamlit run app.py

Widget + session_state counter.

if "n" not in st.session_state:
    st.session_state.n = 0
st.button("＋", on_click=lambda: st.session_state.__setitem__("n", st.session_state.n + 1))
st.write(st.session_state.n)

cache_data query helper.

@st.cache_data(ttl=300)
def load(days: int):
    return conn.query("SELECT * FROM runs WHERE day > current_date - :d",
                      params={"d": days})

st.connection SQL query.

conn = st.connection("warehouse", type="sql")   # creds in .streamlit/secrets.toml
df = conn.query("SELECT * FROM daily_loads", ttl=600)
st.dataframe(df)

Form with submit (no rerun until submit).

with st.form("f"):
    region = st.selectbox("Region", ["us", "eu"])
    go = st.form_submit_button("Load")
if go:
    st.dataframe(load_region(region))

Auto-refreshing fragment.

@st.fragment(run_every="30s")
def freshness_tile():
    st.metric("Last updated", now_str())   # only this block reruns on the timer
freshness_tile()

Decorator / primitive picker.

Situation	Use
Cache a DataFrame / query result	st.cache_data(ttl=...)
Cache a connection / engine / model	st.cache_resource
Keep a value across reruns	st.session_state
Batch several inputs, act on submit	st.form + st.form_submit_button
Refresh part of the page on a timer	@st.fragment(run_every=...)

---

Frequently asked questions

What is Streamlit and why do data engineers use it?

Streamlit is an open-source Python framework that turns a plain script into an interactive web app — you write import streamlit as st and a few st. calls, run streamlit run app.py, and get a shareable URL. Data engineers use it because it removes the frontend entirely: a pipeline-health dashboard, an SLA monitor, or a backfill console that would take days in Flask plus React takes an afternoon in one Python file. It is built for internal tools and dashboards, not public consumer products.

How does Streamlit's rerun model work?

Streamlit re-executes your entire script from top to bottom every time the user interacts with any widget. Widgets are function calls that return their current value on each run, so you use the returned value inline rather than attaching event handlers. The consequence is that ordinary variables reset on every rerun, which is why persistent values must live in st.session_state and expensive work must be wrapped in a cache decorator.

What is the difference between st.cache_data and st.cache_resource?

st.cache_data memoizes a function's return value — DataFrames, query results, anything serializable — keyed on the function's arguments, and it hands each caller a fresh copy so users cannot corrupt each other's data. st.cache_resource caches a single global object such as a database connection, engine, or ML model and gives every caller the same shared instance, with no copy. Use cache_data for data you compute or fetch and cache_resource for things you open once and reuse.

How do I keep values between reruns in Streamlit?

Store them in st.session_state, a dictionary-like object scoped to the browser session that survives reruns. Seed a key once with a guard — if "count" not in st.session_state: st.session_state.count = 0 — so the rerun does not reset it, and mutate it inside a widget callback such as on_click. Binding a widget with key="..." also mirrors its value into st.session_state automatically.

How does Streamlit connect to a data warehouse?

Use st.connection("name", type="sql"), which reads credentials from .streamlit/secrets.toml under [connections.name] so nothing sensitive lives in code. Call conn.query(sql, params={...}, ttl=600) to run a parameterised query whose DataFrame result is cached for the TTL — it uses st.cache_data internally, so repeated interactions do not re-hit the warehouse. type="sql" covers any SQLAlchemy URL (Postgres, MySQL, DuckDB), and type="snowflake" targets Snowflake directly.

How do I build an auto-refreshing pipeline dashboard in Streamlit?

Lay out KPI tiles with st.columns and st.metric, render a freshness table with st.dataframe (highlighting SLA breaches), and put action controls like a backfill trigger inside st.form so they only fire on submit. For live updates, decorate a section with @st.fragment(run_every="30s") so just that block re-runs on a timer instead of the whole page, and after a data-mutating action call your cached loader's .clear() followed by st.rerun() to redraw with fresh numbers immediately.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every Streamlit idea above, from the DataFrame that drives `st.dataframe` and `st.line_chart` to the freshness-and-breach logic behind an SLA table, rests on the data-shaping skills you can drill as graded problems. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you compute per-pipeline SLA status from a freshness table?" holds up under a senior interviewer's depth probes.

Practice data-analysis problems now →
SLA-monitoring drills →