Observable
Observable Framework and Observable Notebooks build static pages. Queries against your bucket or table run on your machine or in CI when the page is built; the browser only receives the results. Readers never hold storage credentials, and the page shows the data as of the last build.
| You have | Use |
|---|---|
| A Framework project (dashboards, reports) | A Python data loader that runs DuckDB and writes Parquet |
| An Observable notebook (Notebooks 2.0, Observable Desktop or Notebook Kit) | A DuckDB database connector and SQL cells |
We built both examples below from our own S3-compatible bucket and opened the output in Chrome. We ran the notebook with the Notebook Kit command line, not in Observable Desktop. The connector settings come from Notebook Kit 2.11's source and database documentation.
Framework data loader
A data loader is a script named after the file it produces. Framework runs src/data/events.parquet.py and serves its standard output as data/events.parquet. Use DuckDB in the loader to read the bucket and write only the columns the page needs:
# src/data/events.parquet.py
import os
import sys
import tempfile
import duckdb
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"]],
)
with tempfile.TemporaryDirectory() as tmp:
out = os.path.join(tmp, "events.parquet")
con.execute(f"""
COPY (
SELECT received_at, date, data->>'$.event' AS event,
(data->>'$.cents')::INTEGER AS cents
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'}})
WHERE signal = 'event' AND date >= current_date - 30
) TO '{out}' (FORMAT parquet)
""")
with open(out, "rb") as f:
sys.stdout.buffer.write(f.read())
For S3-compatible storage, Google Cloud Storage or a table destination, use the secret and the source from DuckDB. Other questions from the example queries go in the loader's SELECT the same way.
Framework runs .py loaders with python3 from your PATH, so start npm run dev or npm run build in an environment that has duckdb installed, or name the interpreter in observablehq.config.js:
export default {
interpreters: {".py": ["uv", "run", "-q", "--with", "duckdb", "python3"]}
};
Loaders inherit the environment of the build or dev command, so export S2B_KEY_ID and S2B_SECRET there, or set them as secrets in the CI job that builds the site. Use a read-only key.
On the page, register the Parquet file as a table in the front matter and query it with a sql code block. This SQL runs in the reader's browser on DuckDB-Wasm:
---
title: Events
sql:
events: ./data/events.parquet
---
```sql id=by_event
SELECT event, count(*) AS events, sum(cents) / 100 AS revenue_usd
FROM events
GROUP BY ALL
ORDER BY events DESC
```
```js
Plot.barY(by_event, {x: "event", y: "events", sort: {x: "-y"}}).plot()
```
Refreshing the data
Framework caches loader output in src/.observablehq/cache and runs a loader again only when the cached file is missing or older than the loader script. npm run build reuses the cache too. To publish newer deliveries, delete the cached file (here src/.observablehq/cache/data/events.parquet) before building, or build on a schedule in CI from a clean checkout.
Everything the loader writes is published with the site. Select only the columns and rows the page shows.
Notebook database connector
In Observable Notebooks 2.0, a SQL cell names a database, and the query runs on your machine when the notebook is built. Configure a DuckDB database in .observable/databases.json next to the notebook. DuckDB reads the bucket directly, so the database itself can be in memory:
{
"s2b": {
"type": "duckdb",
"options": {"s3_region": "us-east-1"}
}
}
options are DuckDB settings. For S3-compatible storage add s3_endpoint and s3_url_style, as in DuckDB. DuckDB takes the key from the AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables of the process that runs the query. You can put s3_access_key_id and s3_secret_access_key in options instead; then keep databases.json out of version control.
Each query starts a new DuckDB instance, so there is no records view to reuse. Put the source in each SQL cell:
<script id="1" type="application/sql" database="s2b" output="by_event">
SELECT data->>'$.event' AS event, count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
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'})
WHERE signal = 'event'
GROUP BY ALL
ORDER BY events DESC
</script>
In Observable Desktop this is a SQL cell with s2b picked as its database and by_event as its output; JavaScript cells below it can chart by_event.
With the Notebook Kit command line, install the DuckDB driver next to it. Notebook Kit 2.11 loads @duckdb/node-api 1.3; the 1.5 and 2.0 releases fail to load:
npx notebooks build --root . -- events.html
Refreshing the data
Query results are saved in .observable/cache/ next to the notebook and reused by later builds. Delete the cached file to run the query again and pick up new deliveries. The cache holds query results, so keep it out of version control if the data is sensitive.
We ran the connector against bucket objects only. For an Iceberg or DuckLake table (DuckLake needs DuckDB 2.0, newer than the connector's 1.3), or for queries you would rather not repeat in every cell, use a Framework loader, or write Parquet with DuckDB and attach the file to the notebook.
Notes
- Both paths publish a snapshot. The page changes only when you rebuild it after clearing the cache.
- Results end up in the published site, readable by anyone who can open it. Aggregate in the loader or query rather than shipping raw records.
- Filter on
datein the source so a build reads only the folders it needs.