Feature: opt-in DuckDB statement profiling on StatementEvent, with per-statement events for execute_script

[cofin]

Summary

SQLSpec can render an EXPLAIN for a statement (SQL.explain()), and it can tell an observer that a statement ran (StatementEvent), but there is no way to get the executed plan and runtime metrics of the statements an application actually runs. DuckDB produces exactly that through its query profiler at no measurable cost; today a consumer has to wire it up by hand around SQLSpec, and cannot do it at all for execute_script.

This proposes an opt-in that captures DuckDB's per-statement profile and surfaces it on the statement event, plus two small related gaps found along the way.

Why explain() does not cover this

	Executes the statement	Plan	Metrics
explain()	no	estimated	none
explain(analyze=True)	yes — replaces the real execution (a CREATE TABLE AS creates its table)	executed, text	timing, cardinality
DuckDB profiler (PRAGMA enable_profiling='json')	n/a — the statement runs normally	executed, JSON operator tree	latency, CPU time, rows scanned, system_peak_buffer_memory, system_peak_temp_dir_size, per-operator timing and cardinality

Measured on a 2M-row self-join + aggregate CREATE TABLE AS, median of 5 runs: plain 52 ms, profiler enabled 52 ms, EXPLAIN ANALYZE 60 ms. The profiler is the only option that is both free and non-intrusive, and the only one that reports peak memory and spill — the numbers that matter when diagnosing an out-of-memory pipeline.

Current behaviour

import json, tempfile
from pathlib import Path

import duckdb
import sqlspec
from sqlspec import SQL
from sqlspec.adapters.duckdb import DuckDBConfig
from sqlspec.core import StatementConfig
from sqlspec.observability import ObservabilityConfig

print("sqlspec", sqlspec.__version__, "| duckdb", duckdb.__version__)
profile = Path(tempfile.mkdtemp()) / "profile.json"
events = []

def observer(event):
    seen = json.loads(profile.read_text())["query_name"].strip()[:40] if profile.exists() else None
    events.append({"is_script": event.is_script, "sql": event.sql.strip()[:40], "profile_on_disk_is_for": seen})

config = DuckDBConfig(
    connection_config={"database": ":memory:"},
    observability_config=ObservabilityConfig(statement_observers=[observer]),
)
with config.provide_session() as session:
    session.execute("PRAGMA enable_profiling='json'")
    session.execute(f"SET profiling_output='{profile}'")
    session.execute("CREATE TABLE a AS SELECT range AS i FROM range(1000)")
    session.execute_script("CREATE TABLE b AS SELECT i * 2 AS j FROM a; CREATE TABLE c AS SELECT j + 1 AS k FROM b;")
for e in events[2:]:
    print(e)

base = SQL("SELECT 1", statement_config=StatementConfig(dialect="duckdb"))
print(base.explain(analyze=True, format="json").sql)
print(duckdb.connect().execute("EXPLAIN (ANALYZE, FORMAT json) SELECT 1").fetchall()[0][0])

sqlspec 0.64.0 | duckdb 1.5.5
{'is_script': False, 'sql': 'CREATE TABLE a AS SELECT range AS i FROM', 'profile_on_disk_is_for': 'CREATE TABLE a AS SELECT range AS i FROM'}
{'is_script': True, 'sql': 'CREATE TABLE b AS SELECT i * 2 AS j FROM', 'profile_on_disk_is_for': 'CREATE TABLE c AS SELECT j + 1 AS k FROM'}
EXPLAIN ANALYZE SELECT 1
analyzed_plan

What this shows:

1. The hand-rolled approach works for single statements. The observer runs synchronously after the statement and before the next one, so the profile on disk belongs to the statement in the event. A consumer can build capture out of public pieces — but has to manage the pragma, a scratch file, parsing, and mismatch guarding themselves.
2. It cannot work for scripts. execute_script splits and runs each statement internally (adapters/duckdb / adapters/adbc/driver.py via split_script_statements) but emits a single event with is_script=True. DuckDB overwrites the profile per statement, so by the time the observer runs only the last statement's profile exists (b in the event, c on disk). The only workaround is for the consumer to stop using execute_script and re-implement the split-and-loop.
3. explain(analyze=True, format="json") silently drops the format. build_duckdb_explain (sqlspec/builder/_explain.py) returns early on analyze and documents three supported forms, but DuckDB accepts EXPLAIN (ANALYZE, FORMAT json) and returns a JSON analyzed_plan.

Proposal

A. Opt-in statement profiling for DuckDB (native duckdb adapter and ADBC with a DuckDB driver). Shape is a suggestion:

DuckDBConfig(
    connection_config={...},
    driver_features={"enable_statement_profiling": True},   # or an ObservabilityConfig option
)

When enabled the driver turns the profiler on for each connection it creates, pointing profiling_output at a per-connection scratch file it owns, and after each statement attaches the parsed profile to the event:

class StatementEvent:
    ...
    profile: dict[str, Any] | None   # adapter-native executed plan + metrics; None when unavailable

Details that matter:

• Off by default; zero cost when off.
• Per-connection scratch file, cleaned up with the connection, so pooled/concurrent sessions never read each other's profile.
• Verify the profile's query_name against the statement before attaching it; on mismatch or unreadable file attach None. Capture must never raise into the caller's statement.
• The driver's own setup statements (PRAGMA enable_profiling, SET profiling_output) should not emit events, or should be marked so observers can skip them.
• profile is deliberately adapter-native and optional, which leaves room for other engines later (e.g. PostgreSQL auto_explain, SQLite EXPLAIN QUERY PLAN) without committing to a cross-database plan schema now.

B. Per-statement events from execute_script. Emit one StatementEvent per split statement (each carrying its own profile when A is enabled), with something like script_ordinal / script_statement_count so an observer can reassemble the script. Keeping the existing whole-script event as well is fine; the missing piece is the per-statement granularity. This is useful independently of profiling — per-statement duration and row counts for scripts are unobservable today.

C. DuckDB analyze + format. Have build_duckdb_explain render EXPLAIN (ANALYZE, FORMAT JSON) <stmt> when both are requested, instead of dropping format.

A and B together are the feature; C is a small independent fix and can be split into its own issue if that is tidier.

Environment

• sqlspec 0.64.0 (same code on main at the time of writing)
• duckdb 1.5.5
• Python 3.12, Linux