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:

```sql
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`](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](/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:

```sql
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:

```sql
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:

```sql
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](/docs/api-references/mcp-tools). They list the [semantic metrics](/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:

```sql
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:

```sql
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.

<Emphasis>An error is a better answer than a plausible wrong number.</Emphasis>

## 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:

```sql
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:

```sql
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:

```sql
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 <Link to="/blog/2025-07-01-state-semantic-layers-2025">compared semantic layers last year</Link>, 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.