Metrics
All pages
Docs · Read and analyzeMarkdown

DuckDB

The syvain_metrics DuckDB extension attaches an organization's Metrics as a read-only database: folders, experiments, annotations, catalog and series are tables you query with SQL. Filters on experiment_id, experiment_slug, folder_id and metric_name decide which API requests run, DuckDB evaluates everything else, and an optional on-disk cache makes repeat queries answer without the network. Use it for any analysis that spans more than one run.

Install

In the DuckDB CLI, start with -unsigned, install once and load in every session:

duckdb -unsigned
INSTALL syvain_metrics FROM 'https://metrics.041.io/public/artifacts/duckdb';
LOAD syvain_metrics;

FORCE INSTALL picks up a newer build; a specific release is under https://metrics.041.io/public/artifacts/duckdb/releases/<version>.

In Python, install the wheel, which pins the matching duckdb release and bundles the extension binary:

uv add syvain-metrics-duckdb

Builds exist for linux_amd64, linux_arm64 and osx_arm64, each for one DuckDB release (1.5.5 today); an extension loads only in the release it was built for. The extension is not signed by DuckDB, so DuckDB must allow unsigned extensions before it loads.

Attach

CREATE SECRET syvain (TYPE syvain_metrics, API_KEY 'ak_...');
ATTACH 'metrics.041.io' AS m (TYPE syvain_metrics, CACHE_DIR '~/.syvain-metrics/duckdb-cache');
SELECT slug, status FROM m.experiments LIMIT 5;

The ATTACH path is the API host without a scheme; DuckDB hands https:// paths to httpfs before the extension sees them. Leave it empty, ATTACH '' AS m (TYPE syvain_metrics), to take the host from the secret or the CLI login, falling back to https://metrics.syvain.com. Both hosts are the same service; the cache is kept per host, so pick one and keep it.

option default meaning
API_KEY none organization API key; wins over every other source
SECRET best match name of the syvain_metrics secret to use
CACHE_DIR none directory of the persistent cache; without it nothing is kept on disk
CONCURRENCY 16 parallel API requests, 1 to 256

The API key comes from, in order:

  1. the API_KEY ATTACH option;
  2. a syvain_metrics secret: the one SECRET 'name' names, or else the one whose scope best matches the host;
  3. SYVAIN_METRICS_API_KEY;
  4. the login saved by syvain-metrics auth login, in $XDG_CONFIG_HOME/syvain-metrics/auth.json or ~/.config/syvain-metrics/auth.json. A browser sign-in saves a personal API key there for exactly this; see command line.

A secret takes API_KEY and HOST. A secret with a HOST applies to that host only, so keys for two hosts can coexist; one without applies to any host.

Series requests run on their own pool of CONCURRENCY fetchers, so a low DuckDB threads setting does not serialize them. Catalog and annotation scans are also capped by threads.

Tables

folders lists every folder.

column type
folder_id VARCHAR
name VARCHAR
path VARCHAR absolute, /models/mamba
parent_id VARCHAR NULL at the root

experiments lists every experiment, or only the selected ones.

column type
id VARCHAR
slug VARCHAR
folder_id VARCHAR NULL for experiments at the root
status VARCHAR created, running, done or error
description VARCHAR
meta JSON the experiment's configuration
error_message VARCHAR
error_meta JSON
created_at, updated_at, started_at, done_at, last_event_at TIMESTAMP UTC; NULL when unset

annotations holds one row per annotation.

column type
experiment_id, experiment_slug, folder_id VARCHAR
id VARCHAR
annotation VARCHAR the text
meta JSON includes step when the annotation was logged with one
created_at TIMESTAMP

catalog holds one row per series name of an experiment.

column type
experiment_id, experiment_slug, folder_id VARCHAR
metric_name VARCHAR
metadata JSON each metadata key with every value seen, {"split": ["train", "valid"]}

series holds one row per point.

column type
experiment_id, experiment_slug, folder_id VARCHAR
metric_name VARCHAR
metadata JSON the point's partition, {"split": "valid"}
step BIGINT NULL for points stored without a step
timestamp TIMESTAMP UTC
value DOUBLE

Selectors

Scans of annotations, catalog and series need an experiment selector in the WHERE clause: =, IN (...) or OR chains on experiment_id, experiment_slug or folder_id. Without one the query fails and says so. experiments without a selector reads every experiment. A metric_name selector limits which series are fetched; without it every series of the selected experiments is fetched and DuckDB filters.

folder_id selects experiments directly in that folder, not below it. An uncorrelated IN (SELECT ...) is a selector too, because DuckDB runs the subquery first and hands the scan its values, so a subtree is one query:

SELECT experiment_slug, count(*) AS points
FROM m.series
WHERE folder_id IN (SELECT folder_id FROM m.folders WHERE path = '/sweeps/lr' OR path LIKE '/sweeps/lr/%')
  AND metric_name = 'loss'
GROUP BY ALL;

DuckDB passes a subquery's values as a list only up to the dynamic_or_filter_threshold setting, which ATTACH raises to 10,000 unless it is already higher. For a larger subquery, SET dynamic_or_filter_threshold above its row count. LIKE, list_contains(...) and other expressions on the selector columns are not selectors.

Parenthesize ->> comparisons next to other conditions: (metadata->>'split') = 'valid'. Without the parentheses DuckDB parses a AND metadata->>'split' = 'valid' as ((a AND metadata) ->> 'split') = 'valid'.

Example queries

Best validation loss of every run in a folder, and the step it was reached:

SELECT experiment_slug,
       min(value)            AS best_val_loss,
       arg_min(step, value)  AS best_step
FROM m.series
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
GROUP BY experiment_slug
ORDER BY best_val_loss;

The last logged value of several metrics per run:

SELECT experiment_slug, metric_name, metadata,
       max(step)              AS last_step,
       arg_max(value, step)   AS final_value
FROM m.series
WHERE experiment_slug IN ('mamba-run-001', 'mamba-run-002')
  AND metric_name IN ('loss', 'grad_norm')
GROUP BY ALL
ORDER BY experiment_slug, metric_name;

Final validation loss against hyperparameters from meta:

SELECT e.slug,
       (e.meta->>'$.config.lr')::DOUBLE          AS lr,
       (e.meta->>'$.config.batch_size')::INTEGER AS batch_size,
       arg_max(s.value, s.step)                  AS final_val_loss
FROM m.series s
JOIN m.experiments e ON e.id = s.experiment_id
WHERE s.folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND e.folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND s.metric_name = 'loss' AND (s.metadata->>'split') = 'valid'
  AND e.status = 'done'
GROUP BY ALL
ORDER BY lr, batch_size;

Two runs side by side, one row per step, NULL where a run has no point:

PIVOT (
  SELECT experiment_slug, step, value
  FROM m.series
  WHERE experiment_slug IN ('baseline', 'candidate')
    AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
) ON experiment_slug USING any_value(value)
ORDER BY step;

Runs that evaluate at different steps, aligned to the latest candidate point at or before each baseline point:

WITH v AS (
  SELECT experiment_slug, step, value
  FROM m.series
  WHERE experiment_slug IN ('baseline', 'candidate')
    AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
)
SELECT b.step, b.value AS baseline, c.value AS candidate, c.value - b.value AS delta
FROM (SELECT * FROM v WHERE experiment_slug = 'baseline') b
ASOF JOIN (SELECT * FROM v WHERE experiment_slug = 'candidate') c ON b.step >= c.step
ORDER BY b.step;

Training loss smoothed into 1,000-step buckets across a folder, per rank:

SELECT experiment_slug, metadata->>'rank' AS rank,
       step // 1000 * 1000 AS bucket, avg(value) AS loss
FROM m.series
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
  AND metric_name = 'loss' AND (metadata->>'split') = 'train'
GROUP BY ALL
ORDER BY experiment_slug, rank, bucket;

Which runs failed, and why:

SELECT slug, error_message, done_at FROM m.experiments
WHERE status = 'error' ORDER BY done_at DESC;

Checkpoints recorded as annotations:

SELECT experiment_slug, (meta->>'step')::BIGINT AS step, meta->>'path' AS path
FROM m.annotations
WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318' AND annotation = 'checkpoint saved'
ORDER BY experiment_slug, step;

Python

import duckdb
import syvain_metrics_duckdb

con = duckdb.connect(config={"allow_unsigned_extensions": "true"})
syvain_metrics_duckdb.load(con)
con.execute(
    "ATTACH 'metrics.041.io' AS m (TYPE syvain_metrics, CACHE_DIR '~/.syvain-metrics/duckdb-cache')"
)
rows = con.sql(
    """
    SELECT experiment_slug, min(value) AS best
    FROM m.series
    WHERE folder_id = '019e3bbe-dfe2-7c15-9b2e-ba2cf0f6d318'
      AND metric_name = 'loss' AND (metadata->>'split') = 'valid'
    GROUP BY ALL ORDER BY best
    """
).fetchall()

load(con) loads the bundled binary; extension_path() returns its path. With no secret and no API_KEY the key comes from SYVAIN_METRICS_API_KEY or the CLI login. Results convert with .df(), .pl() or .arrow() as in any DuckDB query.

Cache

With CACHE_DIR, experiment records, catalogs, annotations and series are kept on disk as JSON and Parquet under v2/<host>__<key fingerprint>/. Without it, the folder tree and experiment records are remembered for the attachment and everything else comes from the API each query.

The API keeps two counters per experiment: an experiment revision, which changes with record updates and annotations, and a series revision, which changes with metric activity. A new attachment checks cached experiments with one request per 500 experiments and downloads only what changed. Within an attachment, queries reuse the last validated revision until you revalidate or refresh, so a long-lived connection does not see new points on its own.

SELECT * FROM syvain_metrics_revalidate('m');                         -- recheck every cached experiment
SELECT * FROM syvain_metrics_revalidate('m', experiment_id := '019e...');
SELECT * FROM syvain_refresh('m');                                    -- reread the tree, then revalidate
SELECT * FROM syvain_refresh('m', folder_id := '019e...');
SELECT * FROM syvain_cache_status('m');
SELECT * FROM syvain_purge('m', experiment_id := '019e...');
SELECT * FROM syvain_purge('m');
SELECT syvain_version();
function does returns
syvain_metrics_revalidate(db, experiment_id :=, folder_id :=) rechecks the revisions of cached experiments experiments checked
syvain_refresh(db, experiment_id :=, folder_id :=) rereads the folder tree to find new or moved experiments, then revalidates experiments checked
syvain_purge(db, experiment_id :=, folder_id :=) deletes cached files, forcing a download on the next query experiments purged
syvain_cache_status(db) lists cached experiments experiment_id, slug, folder_id, status, catalog, annotations, series_files, bytes, fetched_at

Pass at most one of experiment_id and folder_id; without either, the function covers the whole cache. Revalidate and refresh keep unchanged data and fetch changed series lazily, on the next query that needs them.

The series revision is updated on a best-effort basis after ingestion. If that update fails, revalidation cannot see the new points until a later update succeeds; syvain_purge for the experiment forces a fresh download. Ordinary queries may answer from complete cached records while the API is unreachable; the maintenance functions report the outage instead.

The cached Parquet files are readable without the extension or a key:

SELECT * FROM read_parquet('~/.syvain-metrics/duckdb-cache/v2/*/experiments/*/series/*/loss.parquet');

That glob includes older revisions kept for readers that still use them; the current one is named in each experiment's record.json.

Requests and retries

A series scan reads the folder tree once to resolve selectors, then sends one streaming request per experiment for every series, or for the selected names not already cached, up to 1,000 names per request. Each series is checked against the stream's counts before anything enters the cache. Transport errors, HTTP 429 and 5xx, and API error events marked retryable are retried up to four times with backoff before the query fails. SYVAIN_METRICS_DEBUG=1 logs every request with its thread, status and latency to standard error.

Related: Python API client, command line, what to log, series storage.