A Semantic Layer in DuckDB

Two dashboards show the average order value for the same customer segment and the same year. One says $1,371. The other says $1,738. Both queries run without an error, and both numbers look reasonable.

If you have ever written SQL for a metric, you have probably been on one side of that argument. Here is how the wrong number happens.

A store has customers, their orders, and the order_items in each order. We want revenue and average order value per customer segment in 2025. Revenue is the sum of the items, so we join them in:

1
2
3
4
5
6
7
8
SELECT customers.segment,
       SUM(order_items.amount) AS revenue,
       AVG(orders.total) AS avg_order
FROM order_items
JOIN orders    ON order_items.order_id = orders.id
JOIN customers ON orders.customer_id = customers.id
WHERE YEAR(orders.ordered_at) = 2025
GROUP BY customers.segment;

Revenue comes out right. The average order comes out 27% too high. The join repeats each order once per item, so an order with seven items counts seven times in the average, and large orders pull it up.

Nobody made a typo. Nothing in the schema says that orders.total belongs to an order rather than an item, so whoever writes the query has to remember it, every time, for every metric. Add "compared with last year", "year to date", or a customer count next to revenue, and the list of things to remember grows faster than anyone can review it.

More and more, the query is not written by a person but by an agent. It may handle the join correctly and still return a different number than the dashboard, because it had to guess what revenue means: before or after discounts, by order date or ship date. Ask it twice and it may guess twice.

We built semantic_layer, an open-source DuckDB extension that records those definitions next to the data. You describe tables, relationships, and metrics once with CREATE SEMANTIC VIEW. Every query then gets its joins and aggregations generated at the right grain, and a query that would fan out is rejected instead of answered.

Most teams already have these definitions somewhere: MetricFlow metrics in a dbt project, measures and explores in LookML. We do not want to invent another modeling language next to them. When a team connects its dbt or Looker repository to Altertable, we infer the semantic model from what is already there, and the extension sets it in stone as SQL in the database, where every query can use it.

You can also write the definition yourself. The syntax follows Snowflake semantic views. The extension is in the DuckDB community repository, for DuckDB 2.0:

INSTALL semantic_layer FROM community;
LOAD semantic_layer;

Send LOAD on its own: it turns on a grammar extension, and the new syntax applies from the next query.

A semantic view declares the tables with their keys, the many-to-one relationships between them, and the dimensions and metrics on top:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE SEMANTIC VIEW sales
  TABLES (
    orders    PRIMARY KEY (id) TIME (ordered_at),
    customers PRIMARY KEY (id),
    items AS order_items PRIMARY KEY (id)
  )
  RELATIONSHIPS (
    orders (customer_id) REFERENCES customers,
    items (order_id) REFERENCES orders
  )
  DIMENSIONS (
    customers.segment AS segment,
    orders.order_year AS YEAR(ordered_at)
  )
  METRICS (
    items.revenue        AS SUM(amount),
    items.revenue_ly     OFFSET (metric_time.day BY INTERVAL 1 YEAR) AS items.revenue,
    items.revenue_ytd    CUMULATIVE (metric_time.day TO YEAR) AS SUM(amount),
    orders.avg_order     AS AVG(total),
    customers.customers  AS COUNT(*),
    revenue_per_customer AS items.revenue / customers.customers,
    revenue_growth       AS items.revenue / items.revenue_ly - 1
  );

Each metric has a home: items.revenue aggregates order items, orders.avg_order aggregates orders, customers.customers counts customers. That prefix is the piece of information the hand-written query was missing.

The definition is validated when it is created. Tables and columns must exist, keys and relationships must agree, the relationship graph must be acyclic, and every expression must type-check.

The same question, asked of the view:

1
2
3
4
5
6
7
SELECT * FROM SEMANTIC_VIEW(
  sales
  DIMENSIONS customers.segment
  METRICS items.revenue, orders.avg_order
  WHERE orders.order_year = 2025
)
ORDER BY segment;

Revenue matches the hand-written query. The average order matches the one you get by querying orders and customers without order_items. The extension produced both from one request.

This is where it changes how agents work. In Altertable, our own agent and our customers' agents reach the lakehouse through our MCP tools. They list the semantic metrics first, then query the lakehouse with SEMANTIC_VIEW(...), naming the metrics and dimensions they need.

The agent no longer guesses what revenue means or how to join the tables behind it. It asks for items.revenue by customers.segment, and the extension compiles the definition we wrote in SQL. Two agents asking the same question get the same number, and it is the number a dashboard shows, because they all read the same definition.

Metrics from different tables can be combined. revenue_per_customer divides revenue, aggregated over order items, by the number of customers, aggregated over customers, within each segment:

1
2
3
4
5
SELECT * FROM SEMANTIC_VIEW(
  sales
  DIMENSIONS customers.segment
  METRICS items.revenue, customers.customers, revenue_per_customer
);

Some combinations have no correct answer. Customers have no order year, so a customer count by order year would either count customers once per order or need a rule nobody wrote down. The extension refuses:

SELECT * FROM SEMANTIC_VIEW(sales DIMENSIONS orders.order_year METRICS customers.customers);
-- Binder Error: Invalid dimension specified: The dimension entity 'orders' must be related to
-- and have an equal or lower level of granularity compared to the base metric or dimension
-- entity 'customers'.

The rule is the same one the opening query broke. A dimension can group a metric only if it lies on the metric's table or on a table that table references. Joins only follow many-to-one relationships, so they can never multiply the rows a metric aggregates.

"Revenue compared with the same month last year" and "revenue year to date" are some of the most requested numbers, and among the easiest to get wrong by hand. Each needs a self-join or a window function, and a filter in the wrong place quietly breaks the comparison.

In the view, they are metrics like any other. TIME (ordered_at) tells the extension which date each order belongs to, and gives every metric a shared calendar, metric_time, that can be grouped by day, week, month, quarter, or year. Three lines of the definition then build on revenue:

items.revenue_ly  OFFSET (metric_time.day BY INTERVAL 1 YEAR) AS items.revenue,  -- the same period, one year earlier
items.revenue_ytd CUMULATIVE (metric_time.day TO YEAR) AS SUM(amount),          -- running total since January 1
revenue_growth    AS items.revenue / items.revenue_ly - 1                       -- change versus last year

Asking for them month by month in 2024:

1
2
3
4
5
6
7
SELECT * FROM SEMANTIC_VIEW(
  sales
  DIMENSIONS metric_time.month
  METRICS items.revenue, items.revenue_ytd, revenue_growth
  WHERE metric_time.year = DATE '2024-01-01'
)
ORDER BY month;

January 2024 was 11.65% above January 2023, even though the query only asked for 2024. In a hand-written query, that WHERE would have removed the 2023 rows and left growth empty. Here, the comparison is part of the metric's definition, so the filter picks which months to show, not which rows the metric may read.

Dashboards ask the same coarse questions again and again. A materialization stores the aggregates of some metrics at a chosen grain:

ALTER SEMANTIC VIEW sales ADD MATERIALIZATION revenue_by_segment_year
  AS DIMENSIONS customers.segment, orders.order_year
     METRICS items.revenue, orders.avg_order;

Queries do not change. When a query's dimensions and filters can be computed from the stored ones, it reads the stored groups and rolls them up. Each group stores aggregate state rather than final values: AVG keeps a sum and a count, so averaging by segment from (segment, year) groups is exact rather than an average of averages.

On the same 100M-row schema stored in DuckLake, a materialization of 250 (segment, category) groups answered revenue and average quantity by segment in 6 ms instead of 381 ms. A query filtering on individual sale quantities, which the materialization does not retain, kept reading the sources at the same speed.

Freshness is explicit. A materialization over DuckLake tables records the snapshot it was built at and is only used while its sources are unchanged, or within a MAX_STALENESS you set. DuckDB tables have no change tracking, so their materializations require MAX_STALENESS. REFRESH MATERIALIZATION on DuckLake sources applies only the inserted and deleted rows: a 1,000-row insert refreshed in 17 ms instead of 690 ms for a full rebuild. Changes to a dimension table, such as customers changing segment, move unchanged rows between groups; there, the default AUTO mode rebuilds instead, because finding those rows costs more than starting over.

When we compared semantic layers last year, each one lived outside the database, in a BI tool or a separate service, with its own definition language. semantic_layer takes the opposite route: the model is SQL, it lives in the database, and it compiles to the queries you would have written yourself if you had remembered every grain.

That matters more as more of the SQL is written by agents. An agent querying a semantic view uses the definition instead of reinventing it, and a question the model cannot answer correctly comes back as an error it can read rather than a number it will repeat.

The extension is open source under MIT. Bug reports are most useful with the CREATE SEMANTIC VIEW statement, the query, and the output of semantic_view_sql() for it.