marimo
marimo notebooks are Python files whose SQL cells run on DuckDB. Create the DuckDB connection and the records view in one Python cell, and every SQL cell after it can query your bucket or table directly. marimo re-runs the cells that depend on a value when it changes, so a slider or dropdown in one cell filters the queries below it.
| You have | Use |
|---|---|
| Bucket objects, or an Iceberg or DuckLake table | A DuckDB connection in a setup cell, then SQL cells |
| A DataFrame from another cell | A SQL cell on the default engine, which queries it by variable name |
Install
pip install "marimo[sql]" duckdb
The sql extra installs DuckDB, Polars and the SQL parser that SQL cells use. Use DuckDB 2.0 or later if you read DuckLake tables.
Credentials
marimo loads a .env file into the kernel's environment: the one next to your pyproject.toml, or next to the notebook when there is no project. Put a read-only storage key there and add the file to .gitignore:
S2B_KEY_ID=<access_key_id>
S2B_SECRET=<secret_access_key>
To load a different file, set dotenv = [".env.prod"] under [tool.marimo.runtime] in pyproject.toml. Variables exported in the shell that starts marimo edit also reach the notebook.
Connect
Create the connection and the view in a Python cell:
import os
import duckdb
import marimo as mo
con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
con.execute(
"CREATE SECRET s2b_bucket (TYPE s3, KEY_ID ?, SECRET ?, REGION 'us-east-1')",
[os.environ["S2B_KEY_ID"], os.environ["S2B_SECRET"]],
)
con.sql("""
CREATE VIEW records AS
SELECT * FROM read_json(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited', hive_partitioning = true,
columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
resource: 'JSON', scope: 'JSON', data: 'JSON'})
""")
For Google Cloud Storage, S3-compatible storage, or a table destination, use the secret and view from DuckDB instead. The Iceberg and DuckLake sections end with the same records view.
marimo finds con in the notebook and lists it in the connection dropdown of each SQL cell, and its tables and views in the Data Sources panel.
SQL cells
Add a SQL cell, pick con in its connection dropdown, and write SQL. Python values go in {}. In the notebook file, a SQL cell is a call to mo.sql:
days = mo.ui.slider(1, 30, value=7, label="Days")
days
events = mo.sql(
f"""
SELECT data->>'$.event' AS event, count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records
WHERE signal = 'event' AND date >= current_date - {days.value}
GROUP BY ALL ORDER BY events DESC
""",
engine=con,
)
Moving the slider re-runs the query. events is a Polars DataFrame (pandas if Polars is not installed), so the next cell can chart it, or query it again from a SQL cell on the default engine, which finds DataFrames by variable name:
top = mo.sql(f"SELECT event, events FROM events WHERE events > 30")
The example queries run unchanged in SQL cells on con.
Run as an app or a report
A marimo notebook is a Python file, so it runs without the editor:
marimo run report.py # serve it as a read-only app
marimo export html report.py -o report.html # run every cell, save static HTML
python report.py # run it as a script
marimo export html in a scheduled job gives you a report built from the latest deliveries. Set the credential variables in the job's environment, or keep the .env file next to the notebook.
Notes
- Queries run when a cell runs. A notebook left open does not pick up new deliveries until you re-run the setup cell or the SQL cells;
mo.ui.refreshre-runs cells on a timer. - A SQL cell's result is held in memory. Filter on
date(bucket objects) oringested_at(tables) before returning many rows. - marimo requires each global variable to be defined in one cell only. Keep
conand the view in a single setup cell. - For views shared with a team, create them in MotherDuck and query them from marimo through a MotherDuck connection.