---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: semantic_layer
excerpt: |
  DuckDB Community Extensions
  Define metrics, dimensions and relationships once in SQL, then query them at the right grain

extension:
  name: semantic_layer
  description: Define metrics, dimensions and relationships once in SQL, then query them at the right grain
  version: "0.1.0"
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - redox

repo:
  github: altertable-ai/duckdb-semantic_layer
  ref: d03fcadc22627eae229e5a416d31289e3bc70873
  variegata: 1cea189f6d4f9e6af30c09ecb085883784cd04e0

docs:
  hello_world: |
    INSTALL semantic_layer FROM community;
    LOAD semantic_layer;

    -- The example uses the TPC-H tables generated by DuckDB's tpch extension.
    INSTALL tpch;
    LOAD tpch;
    CALL dbgen(sf = 0.01);

    CREATE SEMANTIC VIEW sales
      TABLES (
        orders    AS orders    PRIMARY KEY (o_orderkey) TIME (o_orderdate),
        customers AS customer  PRIMARY KEY (c_custkey),
        lines     AS lineitem  PRIMARY KEY (l_orderkey, l_linenumber)
      )
      RELATIONSHIPS (
        orders_to_customers AS orders (o_custkey) REFERENCES customers,
        lines_to_orders     AS lines (l_orderkey) REFERENCES orders
      )
      FACTS (
        lines.net_price   AS l_extendedprice * (1 - l_discount),
        orders.line_count AS COUNT(lines.l_linenumber)
      )
      DIMENSIONS (
        customers.segment AS c_mktsegment,
        orders.order_year AS YEAR(o_orderdate)
      )
      METRICS (
        lines.revenue        AS SUM(lines.net_price),
        lines.revenue_ly     OFFSET (metric_time.day BY INTERVAL 1 YEAR) AS lines.revenue,
        lines.revenue_ytd    CUMULATIVE (metric_time.day TO YEAR) AS SUM(lines.net_price),
        orders.avg_order     AS AVG(o_totalprice),
        orders.avg_lines     AS AVG(orders.line_count),
        customers.customers  AS COUNT(*),
        revenue_per_customer AS lines.revenue / customers.customers,
        revenue_growth       AS lines.revenue / lines.revenue_ly - 1
      )
      COMMENT = 'TPC-H sales';

    -- Joins lineitem → orders → customer, aggregates each metric at its own grain.
    SELECT * FROM SEMANTIC_VIEW(
      sales
      DIMENSIONS customers.segment, orders.order_year
      METRICS lines.revenue, orders.avg_order, orders.avg_lines
      WHERE orders.order_year >= 1998
    )
    ORDER BY segment, order_year;

    -- The same view, queried by name. Metrics are wrapped in AGG(...).
    SELECT segment, AGG(revenue) AS revenue
    FROM sales
    WHERE order_year = 1998
    GROUP BY segment
    ORDER BY revenue DESC;

    -- TIME (o_orderdate) adds a calendar every metric reaching orders can group by.
    SELECT * FROM SEMANTIC_VIEW(
      sales
      DIMENSIONS metric_time.month
      METRICS lines.revenue, lines.revenue_ytd, revenue_growth
      WHERE metric_time.year = DATE '1997-01-01'
    )
    ORDER BY month;
  extended_description: |
    **semantic_layer** is a semantic layer inside DuckDB. Describe logical tables, the
    relationships between them, and the facts, dimensions and metrics that matter, with
    `CREATE SEMANTIC VIEW`. Then query any combination with `SEMANTIC_VIEW(...)` or a
    plain `SELECT ... GROUP BY` using `AGG(...)`. There is no YAML file and no separate
    server: semantic queries are expanded into ordinary DuckDB SQL at bind time.

    Joins only follow many-to-one relationships, and metrics of different grains are
    aggregated separately. A query that would fan out is rejected instead of returning
    inflated numbers.

    - One `TIME (<column>)` per table gives every metric a shared calendar
      (`metric_time.day` / `week` / `month` / `quarter` / `year`), with period-over-period
      offsets, running totals, to-date and trailing windows.
    - Many-to-many relationships, semi-additive metrics, role-playing relationships,
      time-dependent and range joins, metric filters and window metrics.
    - Materializations pre-aggregate metrics at a chosen grain. Matching queries read
      them, and DuckLake sources can be refreshed incrementally.
    - Semantic views show up in `information_schema` like regular views, with one typed
      column per dimension, fact and metric.
    - The syntax follows [Snowflake semantic views](https://docs.snowflake.com/en/user-guide/views-semantic/overview).

    Documentation: https://github.com/altertable-ai/duckdb-semantic_layer

extension_star_count: 17
extension_star_count_pretty: 17
extension_download_count: 544
extension_download_count_pretty: 544
image: '/images/community_extensions/social_preview/preview_community_extension_semantic_layer.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

<div class="extension_compatibility_table"></div>

| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| Linux ARM64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| macOS AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| macOS ARM64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| Windows AMD64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ❌ | ❌ | ✅ |

### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

|               function_name                | function_type | description | comment | examples |
|--------------------------------------------|---------------|-------------|---------|----------|
| __semantic_view_add_materialization        | table         | NULL        | NULL    |          |
| __semantic_view_create                     | table         | NULL        | NULL    |          |
| __semantic_view_describe                   | table         | NULL        | NULL    |          |
| __semantic_view_drop                       | table         | NULL        | NULL    |          |
| __semantic_view_drop_materialization       | table         | NULL        | NULL    |          |
| __semantic_view_placeholder                | table         | NULL        | NULL    |          |
| __semantic_view_query                      | table         | NULL        | NULL    |          |
| __semantic_view_refresh_materialization    | table         | NULL        | NULL    |          |
| __semantic_view_rename                     | table         | NULL        | NULL    |          |
| __semantic_view_set_comment                | table         | NULL        | NULL    |          |
| __semantic_view_set_max_staleness          | table         | NULL        | NULL    |          |
| __semantic_view_show_dimensions_for_metric | table         | NULL        | NULL    |          |
| __semantic_view_show_materializations      | table         | NULL        | NULL    |          |
| __semantic_view_show_members               | table         | NULL        | NULL    |          |
| __semantic_view_show_views                 | table         | NULL        | NULL    |          |
| semantic_layer_peg_parse                   | table         | NULL        | NULL    |          |
| semantic_view_sql                          | table         | NULL        | NULL    |          |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|              name              |                              description                               | input_type | scope  | aliases |       typed_value       |
|--------------------------------|------------------------------------------------------------------------|------------|--------|---------|-------------------------|
| active_grammar_extensions      | The grammar extensions used by the parser.                             | VARCHAR[]  | LOCAL  | []      | [shell, semantic_layer] |
| semantic_view_materializations | Answer semantic view queries from fresh materializations when they can | BOOLEAN    | GLOBAL | []      | true                    |
| temp_directory                 | Set the directory to which temporary files are written.                | VARCHAR    | GLOBAL | []      | post.db.tmp             |