A DuckDB extension implementing Julian Hyde's "Measures in SQL" paper (arXiv:2406.00251).
Yardstick adds measure-aware SQL to DuckDB. Measures are aggregations that know how to re-aggregate themselves when the query context changes. This enables:
- Percent of total calculations without CTEs or window functions
- Year-over-year comparisons with simple syntax
- Drill-down analytics that automatically adjust aggregation context
Sidemantic is a fully featured semantic layer from Sidequery that supports the yardstick syntax across many different databases. Even mix and match yardstick syntax with your existing semantic model definitions.
-- Load the extension
INSTALL yardstick FROM community;
LOAD yardstick;
-- Create the sales table
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
year INTEGER,
region VARCHAR(50),
amount DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO sales (id, year, region, amount) VALUES
(1, 2023, 'North', 15000.00),
(2, 2023, 'North', 22000.00),
(3, 2023, 'South', 18000.00),
(4, 2023, 'South', 12000.00),
(5, 2023, 'East', 25000.00),
(6, 2023, 'West', 19000.00),
(7, 2024, 'North', 28000.00),
(8, 2024, 'North', 31000.00),
(9, 2024, 'South', 21000.00),
(10, 2024, 'South', 16000.00),
(11, 2024, 'East', 33000.00),
(12, 2024, 'East', 29000.00),
(13, 2024, 'West', 24000.00),
(14, 2024, 'West', 27000.00);
-- Create a view with measures
CREATE VIEW sales_v AS
SELECT
year,
region,
SUM(amount) AS MEASURE revenue,
COUNT(*) AS MEASURE order_count
FROM sales;
-- Query with AGGREGATE() and AT modifiers
SELECT
year,
region,
AGGREGATE(revenue) AS revenue,
AGGREGATE(revenue) AT (ALL region) AS year_total,
AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL region) AS pct_of_year
FROM sales_v;
-- Variance from the global average
SELECT
region,
AGGREGATE(revenue) AS revenue,
AGGREGATE(revenue) AT (ALL) / 4.0 AS expected_if_equal, -- 4 regions
AGGREGATE(revenue) - (AGGREGATE(revenue) AT (ALL) / 4.0) AS variance
FROM sales_v;
-- Nested percentages (% of year, and that year's % of total)
SELECT
year,
region,
AGGREGATE(revenue) AS revenue,
100.0 * AGGREGATE(revenue) / AGGREGATE(revenue) AT (ALL region) AS pct_of_year,
100.0 * AGGREGATE(revenue) AT (ALL region) / AGGREGATE(revenue) AT (ALL) AS year_pct_of_total
FROM sales_v;
-- Compare 2024 performance to 2023 baseline for each region
SELECT
region,
AGGREGATE(revenue) AT (SET year = 2024) AS rev_2024,
AGGREGATE(revenue) AT (SET year = 2023) AS rev_2023,
AGGREGATE(revenue) AT (SET year = 2024) - AGGREGATE(revenue) AT (SET year = 2023) AS growth
FROM sales_v;
-- Filter to specific segments
SELECT
year,
AGGREGATE(revenue) AS total_revenue,
AGGREGATE(revenue) AT (SET region = 'North') AS north_revenue,
AGGREGATE(revenue) AT (SET region IN ('North', 'South')) AS north_south_combined
FROM sales_v;Notebooks embed their dependencies via juv metadata.
uv tool install juv
# or run without install
uvx juv run docs/yardstick_adtech_kitchen_sink.ipynb
uvx juv run docs/yardstick_looker_demo.ipynbThese notebooks load the local extension if it exists at:
build/release/extension/yardstick/yardstick.duckdb_extension
and fall back to INSTALL yardstick FROM community.
CREATE VIEW view_name AS
SELECT
dimension1,
dimension2,
AGG(expr) AS MEASURE measure_name
FROM table;Yardstick automatically handles the grouping. All DuckDB aggregate functions are supported; non-decomposable aggregates (COUNT(DISTINCT), MEDIAN, PERCENTILE_, QUANTILE_, MODE) are recomputed from base rows at query time and support AT modifiers, but can be more expensive.
SELECT
dimensions,
AGGREGATE(measure_name) [AT modifier]
FROM view_name;On DuckDB 1.5+, queries containing AGGREGATE() are automatically intercepted by the extension's parser override, so no special prefix is needed. On older versions, the SEMANTIC prefix is still supported as a fallback.
| Modifier | Description | Example |
|---|---|---|
AT (ALL) |
Grand total across all dimensions | AGGREGATE(revenue) AT (ALL) |
AT (ALL dim) |
Total excluding specific dimension | AGGREGATE(revenue) AT (ALL region) |
AT (ALL expr) |
Total excluding ad hoc dimension | AGGREGATE(revenue) AT (ALL MONTH(date)) |
AT (SET dim = val) |
Fix dimension to specific value | AGGREGATE(revenue) AT (SET year = 2022) |
AT (SET dim = expr) |
Fix dimension to expression | AGGREGATE(revenue) AT (SET year = year - 1) |
AT (SET expr = val) |
Fix ad hoc dimension to value | AGGREGATE(revenue) AT (SET MONTH(date) = 6) |
AT (WHERE cond) |
Pre-aggregation filter | AGGREGATE(revenue) AT (WHERE region = 'US') |
AT (VISIBLE) |
Use query's WHERE clause | AGGREGATE(revenue) AT (VISIBLE) |
On DuckDB builds with grammar-extension support, Yardstick recognizes AS MEASURE, AT (...), and CURRENT dimension / CURRENT(dimension) through native PEG grammar rules. LOAD yardstick enables this adapter without setting active_grammar_extensions. Native references retain their expression spans and local relation scope, including quoted identifiers and CTE shadowing. CURRENT belongs to an AT SET or WHERE expression; ordinary SQL aliases and nested queries keep their own scope. Measure registration and context semantics remain shared with DuckDB 1.5.5.
Native query traversal lowers CTE bodies, subqueries, and set-operation operands independently. Aggregate calls are discovered from expression nodes, including parenthesized AT operands and queries inside INSERT, UPDATE, DELETE, CREATE VIEW, CREATE TABLE AS, EXPLAIN, and COPY statements. Visible filters retain outer query correlations. Subquery projections group their outer column dependencies, while implicit measure projections preserve their column names. DuckDB 1.5.5 retains the compatibility parser; native forms whose source spans cannot be represented also use that path.
The native frontend supports DISTINCT, FILTER, argument ORDER BY, OVER, and EXPORT_STATE on one-argument AGGREGATE() calls. DuckDB 1.5.5 retains its compatibility frontend; these call decorations require the native frontend. DuckDB's multiargument aggregate(list, function_name) remains ordinary DuckDB syntax.
SELECT AGGREGATE(DISTINCT revenue),
AGGREGATE(revenue) FILTER (WHERE region = 'US')
FROM sales;
SELECT year, AGGREGATE(revenue) OVER (
ORDER BY year ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS rolling_revenue
FROM sales_by_year;Call decorations operate on the aggregate functions in the measure definition. For a derived measure such as SUM(amount) / COUNT(amount), DISTINCT applies separately to both aggregate inputs, and FILTER restricts the base rows for both sides. Scalar arithmetic, casts, and wrappers remain intact. A call filter is combined with an existing definition filter using AND. Argument ordering, as in AGGREGATE(labels ORDER BY priority DESC NULLS LAST), takes precedence over definition ordering; the definition's ordering remains as tie breakers. Filter and ordering expressions can reference exposed dimension aliases, including computed dimensions.
Window partitions and ROWS, RANGE, or GROUPS frames select visible rows, then recompute the measure from their original base rows. This preserves averages and derived ratios when visible rows represent groups of unequal size. A window call's FILTER selects frame input rows, so it can reference joined relations; filters in the measure definition still apply to aggregate leaves. Frames retain DuckDB's peer, exclusion, and empty-frame behavior; repeated references introduced by joins do not duplicate the same base row. Window calls also support AT context modifiers, which transform the context selected by the filtered frame.
Window argument ordering can mix source and joined input fields. When a join repeats a base row, its first occurrence under the caller's ordering supplies the joined keys; definition ordering still breaks ties. If AT expands the context to a base row absent from the frame, its joined keys are NULL, while its source keys are recomputed.
AGGREGATE(measure) EXPORT_STATE exports sufficient aggregate state for later finalization. A single aggregate produces DuckDB's native state; a derived measure produces a composite state carrying its aggregate leaves and scalar formula. yardstick_finalize(state) accepts either form. yardstick_combine(left, right) combines two states with matching formulas and aggregate types, then yardstick_finalize evaluates the result. DuckDB's own aggregate-specific export restrictions still apply.
SELECT yardstick_finalize(state)
FROM (SELECT AGGREGATE(revenue) EXPORT_STATE AS state FROM sales);Combining exported states follows DuckDB's native state semantics: it does not retain a cross-shard set of distinct input values. Combining states exported with DISTINCT therefore does not remove duplicates shared by different shards. Recompute from the combined base rows when global distinctness is required.
The native frontend also supports full and partial CREATE VIEW column lists for measure views. Header names apply to both dimensions and measures, while derived measures retain their declaration dependencies. Star projections are expanded against the originating session before header positions are mapped. Temporary definitions stay session-local; a single statement cannot combine temporary and permanent measure views with the same name.
If other grammar extensions are active, include yardstick alongside them in active_grammar_extensions to use that combined grammar. Rewritten parses and the internal execution connection preserve it. Otherwise Yardstick keeps its legacy frontend for that connection.
This is a staged frontend migration: forms the native grammar cannot parse retain the legacy path. Brace shorthand remains a separate Rust helper, not a fully supported SQL frontend.
For the combined-grammar regression suite, configure with -DYARDSTICK_BUILD_GRAMMAR_TEST_EXTENSION=ON, build yardstick_test_grammar_loadable_extension, and run the SQL tests with YARDSTICK_NATIVE_PEG=1 and YARDSTICK_GRAMMAR_TEST_EXTENSION set to the fixture extension's absolute path. The fixture is excluded from normal builds.
Supported build targets are DuckDB v1.5.5 and the upcoming v2.0-cyanoptera release branch. The latter is a moving branch, not a released version. CI requires both targets to build and pass tests; DuckDB main runs separately as a scheduled, advisory canary.
Prerequisites:
- CMake 3.12+
- C++17 compiler
- Cargo
git submodule update --init --recursive
make release # builds Rust library and DuckDB extension
make test # builds release and runs testsUse make release explicitly: plain make only builds the Rust library. To build another supported target, check out that ref in the duckdb submodule and use a fresh build directory.
The extension will be at build/release/extension/yardstick/yardstick.duckdb_extension
See LIMITATIONS.md for known issues and workarounds.
Key limitations:
- Window-defined measures with
AT (...)must evaluate to a single value per context, or they error
"I used this to integrate into a copilotkit chat interface serving graphs, works really well for the llm." - JFox, DuckDB Discord
- Julian Hyde, "Measures in SQL" (2024). arXiv:2406.00251
- DuckDB Extension Template
MIT