DuckDB Dev Dashboard

This repository contains the code to operate the duckdb-dev-dashboard: https://duckdblabs.github.io/duckdb-dev-dashboard

The tech stack:

• back-end: ducklake, with postgres catalog, and storage on cloudflare R2
• front-end: evidence (https://docs.evidence.dev)
• hosted: Github Pages
• updates: periodically via Github actions cron

Note: the ci-stats and extension-downloads pages are inactive for the time being. They read a ducklake whose storage lived on a Cloudflare R2 account that has been deleted, so there is no data left to serve. Nothing was removed - the pages, sources and feeds are all still here, only switched off:

• evidence/pages/ci-stats.md.disabled and evidence/pages/extension-downloads.md.disabled - renamed, so evidence does not build them as routes
• evidence/sources/{ci_metrics,extension_downloads}/connection.yaml.disabled - renamed, so evidence skips those source directories
• make generate_sources passes benchmarks, so the dead lake is never attached
• both entries in FEEDS (feeds/run_feeds.py), both links in evidence/pages/index.md and make maintain in maintain_dashboard.yml are commented out

The benchmarks dashboards are unaffected: they read a separate lake on S3 (see below).

Setup

Create a postgres-r2-ducklake with DuckDB CLI

• create a bucket at Cloudflare R2, to serve as data store for the ducklake. Get the following vars:
  • DUCKLAKE_STORAGE_S3_KEY_ID
  • DUCKLAKE_STORAGE_S3_SECRET
  • DUCKLAKE_STORAGE_S3_ENDPOINT (the path of the bucket, e.g. r2://my-bucket/)
  • DUCKLAKE_STORAGE_R2_ACCOUNT_ID
• create a Postgres server, e.g. at https://neon.com/ to serve as catalog for the ducklake. Get the following vars, (mentioned in the connection string):
  • DUCKLAKE_CATALOG_PG_HOST
  • DUCKLAKE_CATALOG_PG_USER
  • DUCKLAKE_CATALOG_PG_PASSWORD
• for convenience and local testing, add the vars mentioned above to a .env file (gitignored) and run make secrets to create persistent secrets to connect to the ducklake. Note that secrets are stored in ~/.duckdb/stored_secrets.
• note that the front end is hosted on GitHub pages: https://docs.evidence.dev/deployment/self-host/github-pages

Second lake: benchmark results (read-only)

The benchmarks dashboard reads a second, independent DuckLake, owned by the benchmark harness (scripts/engineering/benchmark in duckdb-internal):

• catalog: s3://duckdb-benchmark-lake-847122343688/results.ducklake — a DuckDB database file, not postgres
• data: s3://duckdb-benchmark-lake-847122343688/data/, region eu-central-1

Because the catalog is a remote database file it can only be attached READ_ONLY; this repo never writes to it. Set the following vars (read-only S3 credentials are sufficient, and are all this repo should hold):

• BENCHMARK_LAKE_S3_KEY_ID
• BENCHMARK_LAKE_S3_SECRET

Both are required: make secrets fails if either is missing, so a missing credential surfaces there rather than leaving the benchmarks dashboard silently stale. make secrets creates benchmark_s3_secret and benchmark_ducklake_secret from them. The paths and region have defaults and only need setting to point at a different bucket: BENCHMARK_LAKE_BUCKET, BENCHMARK_LAKE_CATALOG, BENCHMARK_LAKE_DATA_PATH, BENCHMARK_LAKE_REGION.

To connect to it manually:

ATTACH 'ducklake:benchmark_ducklake_secret' AS bench (READ_ONLY);
USE bench;
SELECT benchmark, benchmark_name, scale_factor, count(*) FROM runs WHERE NOT is_test GROUP BY ALL;

The benchmark pages keep the v2.0 and v2.1 development histories selectable independently. Charts use the newest patch from each of the two newest minor release lines. Per-query timing tables keep fixed references to v1.4.5, v1.5.5 and v2.0.0. A reference appears only when matching results exist for that storage backend and platform, so v2.0.0 will also appear for DuckLake once it has been benchmarked there. The query history's “Slower than” filter uses the newest available release reference.

Overview charts and query-history dropdowns discover suites per storage backend from the results. H2O.ai's fixed 1-billion-row dataset appears as H2O.ai (1 billion rows) once results arrive. Shared names and display order live in evidence/sources/benchmarks/benchmark_series_list.sql; unrecognised suites use their recorded name. Query-history links accept any discovered suite. Suites with only release measurements still show baseline charts on the selected platform, including per-query baselines in query history. Incomplete release measurements show their successful and attempted query counts. Once development runs appear, baselines must match their query set.

The Python DuckDB dependency is pinned to 1.5.6 because Evidence's bundled reader cannot read the database format produced by DuckDB 2.0.0.

To check the benchmark queries without lake credentials, install requirements.txt and run python3 -m unittest discover -s tests. Front-end unit tests run with npm --prefix evidence run test:unit.

Testing set-up: Connecting to the ducklake

• to connect to the ducklake with the credentials created above:

ATTACH 'ducklake:ducklake_secret' AS my_ducklake (READ_ONLY);
USE my_ducklake;

The ducklake can be used as regular database, e.g PRAGMA show_tables; (no result if the ducklake is still empty).

• to connect to the catalog database (e.g. to see the metadata):

ATTACH '' AS postgres_db (TYPE postgres, SECRET pg_secret, READ_ONLY);
USE postgres_db;
SELECT * FROM ducklake_metadata;

• to directly query the data store (with the credentials created above) e.g.:

SELECT * FROM glob('r2://my-r2-bucket/**/*');

Adding Dashboards

Creating a dashboard requires the following steps:

• create a data feed (python script); this will run periodically and add should create and update the data in the ducklake required for the dashboard
• define a 'source' in evidence; a subdirectory under ./evidence/sources
• define a 'page' in evidence; a markdown file under ./evidence/pages

defining data feeds

Data feeds are scripts that periodically store data in the ducklake

• all data feeds are python packages under ./feeds/ and will be run by run_feeds.py (via make run_feeds)
• to add a data feed, add python script (single file package) in a directory under ./feeds/ and update run_feeds.py
• data feeds should create the data table on first run
• the general lay-out of a data feed can be as follows:

data = my_func_to_fetch_data_from_somewhere()

from utils.ducklake import DuckLakeConnection
with DuckLakeConnection(dl_secret) as con:
    con.execute(<<< sql statments to create tables, add records, etc... >>>)

• to locally test data feeds, the following the following make targets are available:
  • make sync_local - makes a local copy from production (both catalog and data)
  • make run_feeds_local - stores the fetched data in the local copy of the ducklake.

defining sources

The evidence front-end (see ./evidence/README.md) can not directly serve from the ducklake, therefore .duckdb files will be created as in-between step. This is not ideal, since evidence itself also copies the data to convert the data into parquet. Therfore (for now) there are 2 build steps:

• make generate_sources: converts data in the ducklake into .duckdb persistent file
• make build: converts .duckdb into .parquet and builds the front-end

Steps to define a new source:

• initial step to create a new source, see: https://docs.evidence.dev/core-concepts/data-sources/duckdb/
  • run make dev to spawn the front-end (make sure evidence is installed locally, see ./evidence/README.md)
  • following the steps in the link above will create a subdirectory under ./evidence/sources
• add the source to evidence/sources/sources.json, to specify which tables from the ducklake are needed.
  • a source may also read from a different lake, and materialize an aggregate instead of copying a table whole:
    • lake_secret - name of the ducklake secret to attach (default: ducklake_secret)
    • read_only - attach the lake READ_ONLY instead of with AUTOMATIC_MIGRATION (default: false)
    • derived_tables - [{name, sql_file}]; the SQL file is run against the lake and its result is stored as table name. Use this when the raw table is far too large to ship to the browser: the benchmarks source turns one row per query per warm run per metric into one row per benchmark run. These SQL files live in ./benchmark_derived_tables/, deliberately outside evidence/sources/ - evidence treats every .sql under a source directory as a source query.
  • to refresh only some sources (e.g. when you do not have credentials for every lake): python3 -m evidence.sources.generate_sources ci_metrics extension_downloads
• run make generate_sources, this should create the .duckdb file (which is .gitignored, but needed for local testing).
• add one or more .sql files to select the data relevant for the dashboard

defining dashboard pages

Define dashboard pages in ./evidence/pages.

• To use the .sql files created in previous step, see https://docs.evidence.dev/core-concepts/queries/
• Bars / Charts and other components, see: https://docs.evidence.dev/core-concepts/components/
• locally test with make dev

example: ci stats dashboard to monitor CI

• data feed: feeds/collect_ci_metrics.py #todo update
• source: evidence/sources/ci_metrics/
• page: evidence/pages/ci-stats.md

The data feed that fetches and stores completed CI runs from:

• https://api.github.com/repos/duckdb/duckdb/actions/workflows
• https://api.github.com/repos/duckdb/duckdb/actions/runs
• https://api.github.com/repos/duckdb/duckdb/actions/runs/{RUN_ID}/jobs

Note that only consecutive 'completed' runs are stored. After an initial run the script will add new completed runs ('append only').