A Semantic Layer in DuckDB

Altertable released semantic_layer, an open-source DuckDB extension for DuckDB 2.0 that lets teams define tables, relationships, dimensions and metrics once via CREATE SEMANTIC VIEW so queries get joins and aggregations generated at the correct grain, and queries that would fan out are rejected rather than answered. The extension, installable from the DuckDB community repository, follows Snowflake semantic view syntax and can infer a semantic model from an existing dbt or Looker repository; Altertable cites a case where joining order_items to orders inflated average order value by 27% ($1,738 versus $1,371) because each order was counted once per item. The work targets both analyst-written SQL and agent-generated queries, which Altertable says can guess differently at what revenue means on repeated runs.

9 MIN READ SYLVAIN UTARD A Semantic Layer in DuckDB A semantic layer inside DuckDB: define metrics once in SQL, and every query, from an analyst or an AI agent, gets the joins and aggregations right. 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: SELECT customers.segment, SUM order items.amount AS revenue, AVG orders.total AS avg orderFROM order itemsJOIN orders ON order items.order id = orders.idJOIN customers ON orders.customer id = customers.idWHERE YEAR orders.ordered at = 2025GROUP 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 https://github.com/altertable-ai/duckdb-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. Describe the model once 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 https://altertable.ai/docs/lakehouse/modeling/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 https://docs.snowflake.com/en/user-guide/views-semantic/overview . 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: 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. Ask for dimensions and metrics The same question, asked of the view: SELECT FROM SEMANTIC VIEW sales DIMENSIONS customers.segment METRICS items.revenue, orders.avg order WHERE orders.order year = 2025 ORDER BY segment; | segment | revenue | avg order | |---|---|---| | Enterprise | 313,511 | 1,371 | | Mid-market | 429,586 | 1,384 | | Public sector | 401,503 | 1,470 | | SMB | 323,619 | 1,320 | | Startup | 331,833 | 1,406 | 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. Agents query the definition This is where it changes how agents work. In Altertable, our own agent and our customers' agents reach the lakehouse through our MCP tools https://altertable.ai/docs/api-references/mcp-tools . They list the semantic metrics https://altertable.ai/docs/agents/knowledge/semantic-layer 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. Wrong questions get an error 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: 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. Time is part of the metric "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 earlieritems.revenue ytd CUMULATIVE metric time.day TO YEAR AS SUM amount , -- running total since January 1revenue growth AS items.revenue / items.revenue ly - 1 -- change versus last year Asking for them month by month in 2024: 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; | month | revenue | revenue ytd | revenue growth | |---|---|---|---| | 2024-01-01 | 271,892 | 271,892 | 0.1165 | | 2024-02-01 | 262,021 | 533,913 | 0.0604 | | 2024-03-01 | 269,518 | 803,431 | -0.0044 | | 2024-04-01 | 262,296 | 1,065,727 | 0.0330 | 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. Materializations for dashboards 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. Metrics next to the data When we compared semantic layers last year https://altertable.ai/blog/2025-07-01-state-semantic-layers-2025 , 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 https://github.com/altertable-ai/duckdb-semantic layer . Bug reports are most useful with the CREATE SEMANTIC VIEW statement, the query, and the output of semantic view sql for it. Sylvain Utard Co-Founder & CEO Seasoned leader in B2B SaaS and B2C. Scaled 100+ teams at Algolia 1st hire & Sorare. Passionate about data, performance and productivity.