Evidence
Evidence builds reports from Markdown and SQL. Source queries run when you build, and their results are stored as Parquet files next to the site; pages then query those files in the browser. With the DuckDB source, the source queries read your Send2Bucket bucket directly, so there is no warehouse to run.
| You have | Use |
|---|---|
| A bucket destination | A DuckDB source that reads the objects |
| Views in MotherDuck | A MotherDuck source |
| Another warehouse | That warehouse's Evidence source, with the SQL from its querying page |
We ran the DuckDB source against an S3-compatible bucket: npm run sources and npm run build. The MotherDuck source follows Evidence's documentation and was not run by us.
DuckDB source
Start from Evidence's project template, or add @evidence-dev/evidence, @evidence-dev/core-components and @evidence-dev/duckdb to a Node project. In evidence.config.yaml, list the DuckDB plugin:
plugins:
components:
"@evidence-dev/core-components": {}
datasources:
"@evidence-dev/duckdb": {}
Create a source folder, sources/s2b/, with three kinds of file.
connection.yaml uses an in-memory DuckDB database:
name: s2b
type: duckdb
options:
filename: ":memory:"
initialize.sql runs before every source query. It creates the secret and the records view from DuckDB:
INSTALL httpfs; LOAD httpfs;
CREATE SECRET s2b_bucket (TYPE s3, PROVIDER credential_chain, CHAIN 'env', REGION '<region>');
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'});
CHAIN 'env' reads AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY from the environment, so no key is written in the project. For S3-compatible storage, add ENDPOINT '<endpoint>' and URL_STYLE 'path' to the secret.
Each other .sql file is one source query, and its file name is the table name. events.sql:
SELECT date_trunc('hour', received_at) AS hour,
data->>'$.event' AS event,
count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records
WHERE signal = 'event' AND date >= current_date - 30
GROUP BY ALL
slow_spans.sql:
SELECT data->>'$.name' AS span,
data->>'$.traceId' AS trace_id,
((data->>'$.endTimeUnixNano')::HUGEINT - (data->>'$.startTimeUnixNano')::HUGEINT) / 1e6 AS duration_ms
FROM records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 100
Aggregate in the source query. Everything a source query returns is copied into the site, so a query that returns raw records makes a large site and a slow build. The date filter keeps DuckDB from listing and reading older folders.
Run the source queries with credentials in the environment:
AWS_ACCESS_KEY_ID=<access_key_id> AWS_SECRET_ACCESS_KEY=<secret_access_key> npm run sources
Pages
A page queries the source tables as s2b.<file name>, in DuckDB SQL, and passes the result to components. pages/index.md:
---
title: Product events
---
```sql events_by_name
select event, sum(events) as events, sum(revenue_usd) as revenue_usd
from s2b.events
group by event
order by events desc
```
<BarChart data={events_by_name} x=event y=events />
```sql slowest
select * from s2b.slow_spans order by duration_ms desc limit 5
```
<DataTable data={slowest} />
On the sample data, events_by_name returns page.view 177, order.paid 53 with revenue 4161, email.sent 47 and signup 23.
MotherDuck source
If your views live in MotherDuck, add @evidence-dev/motherduck and a source with:
name: s2b
type: motherduck
options:
database: s2b
Supply the token as an environment variable, EVIDENCE_SOURCE__s2b__token, or through the source settings page of npm run dev, which stores it in connection.options.yaml. Use a read-scoped token. Source queries then select from the views, with the SQL from DuckDB: Example queries.
Notes
- Data is as fresh as the last
npm run sources. Run it andnpm run buildon a schedule, for example in CI, and publish thebuildfolder. - Evidence ignores errors in
initialize.sql. If a source query fails with "Table with name records does not exist", run the statements ininitialize.sqlin the DuckDB CLI to see the real error, which is usually a credential or path problem. - The DuckDB plugin bundles its own DuckDB version (1.4 in plugin 2.0). It reads bucket objects and Iceberg tables, but not DuckLake catalogs that need DuckDB 2.0. For a DuckLake destination, query it through MotherDuck or another engine.
- If
npm installfails on a TypeScript peer dependency conflict, addtypescript@5to the project's dependencies.