A Streamlit analytics app that reads from a DuckDB file has one recurring problem: DuckDB’s single-writer model means that if dbt (or any other process) writes to the file while Streamlit holds a write connection, one of them fails.
The solution is consistent: open DuckDB in read-only mode from Streamlit. Here’s how to do that correctly.
The error without read-only mode
If Streamlit opens DuckDB in default write mode and dbt also runs, you’ll see one of:
IO Error: Could not set lock on file "tuva.duckdb": Resource temporarily unavailableWhichever process connects second loses. If Streamlit is running and dbt tries to start, dbt fails with the IO error. If dbt is running and Streamlit restarts, Streamlit fails to connect. Either way, one of them needs to be in read-only mode.
Opening DuckDB read-only in Streamlit
import duckdb
import streamlit as st
@st.cache_resource
def get_connection():
return duckdb.connect("tuva.duckdb", read_only=True)
con = get_connection()st.cache_resource is the right cache decorator here — it caches the connection object across all sessions and re-renders. Using st.cache_data would serialize the connection object, which doesn’t work and would try to create a new connection on every call.
read_only=True tells DuckDB to open the file with a read lock only. Multiple read-only connections can coexist with each other and with a single write connection (dbt), so the conflict disappears.
Querying with the connection
Read-only mode doesn’t change query syntax:
@st.cache_data(ttl=300)
def load_patient_summary(min_gaps: int = 0):
con = get_connection()
return con.execute("""
SELECT
patient_id,
patient_age,
patient_sex,
total_paid_amount,
suspecting_gaps
FROM hcc_suspecting__summary
WHERE suspecting_gaps >= ?
ORDER BY suspecting_gaps DESC
""", [min_gaps]).df()DuckDB’s parameterized query syntax uses ? as placeholders. The .df() method returns a pandas DataFrame.
st.cache_data(ttl=300) caches the result for 5 minutes. For a development dataset that doesn’t change, you can omit TTL entirely — the cache persists until the Streamlit server restarts or the cache is explicitly cleared.
The path problem on deployment
On Streamlit Community Cloud, the .duckdb file needs to be at a known path accessible to the app. Three options:
Commit the file to the repo — works for files under ~100MB. Git LFS is needed above that. The file gets checked out alongside the code on every deploy.
Build at startup — have the app run dbt build or a Python script to construct the database from source data at container start. This works but adds startup time (3-5 minutes for the full Tuva pipeline) and requires the source data to be accessible at deploy time.
Export to Parquet — after building the DuckDB database, export the final mart tables to Parquet files and have Streamlit read those instead. Parquet files have no lock semantics, so there’s no concurrency issue.
For the analytics app in this project, the DuckDB file is committed to the repo (synthetic data, under 50MB).
Thread safety
DuckDB read-only connections are thread-safe for reads. Streamlit uses a thread-per-session model, so if multiple users open the app simultaneously, they share the same st.cache_resource connection. This is safe because all operations are reads.
If you need isolation between users (e.g., user-specific state or transactions), create a connection per session by not using st.cache_resource — but for a read-only analytics app, the shared connection is correct and more efficient.
What can’t be done in read-only mode
In read-only mode, any attempt to write fails:
con.execute("CREATE TABLE test AS SELECT 1")
# RuntimeError: Unable to create database: read_only = trueThis means no dbt run, no CREATE TABLE, no INSERT, no DROP. The Streamlit app can only read. That’s the correct constraint — the app is a consumer of the data, not a producer.