Getting Started

In this tutorial, you will install the DuckDB Semantic Views extension, define your first semantic view over a single table, and query it in three different ways. By the end, you will understand the basic workflow: define dimensions and metrics once, then query any combination without writing GROUP BY or JOIN logic.

Time: 5 minutes

Prerequisites:

  • DuckDB 2.0 installed (CLI or Python package)

  • Basic SQL knowledge (SELECT, GROUP BY, aggregate functions)

Install the Extension

Start the DuckDB CLI and load the extension:

INSTALL semantic_views FROM community;
LOAD semantic_views;

import duckdb

con = duckdb.connect()
con.execute("INSTALL semantic_views FROM community")
con.execute("LOAD semantic_views")

To run the SQL in this tutorial from Python, pass it to con.execute() or con.sql(). One call can hold several statements separated by semicolons (see Several Statements in One Call).

Important

Each release of the extension is built for one DuckDB release and loads only there. This release needs DuckDB 2.0. On DuckDB 1.5, use v0.14.2, the latest release built for 1.5. If LOAD stops with semantic_views was built for DuckDB ... and cannot run in DuckDB ..., the installed extension and your DuckDB do not match; see Built for a different DuckDB release for the fix.

Create Sample Data

Create an orders table with some sample rows:

CREATE TABLE orders (
    id INTEGER,
    region VARCHAR,
    category VARCHAR,
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
    (1, 'East',  'Hardware', 25.00),
    (2, 'East',  'Software', 50.00),
    (3, 'West',  'Hardware', 25.00),
    (4, 'West',  'Software', 100.00),
    (5, 'East',  'Hardware', 50.00);

Define a Semantic View

Create a semantic view over the orders table. The TABLES clause declares the table with an alias and primary key. The DIMENSIONS clause names the columns available for grouping. The METRICS clause names the aggregations available for measurement.

CREATE SEMANTIC VIEW order_metrics AS
TABLES (
    o AS orders PRIMARY KEY (id)
)
DIMENSIONS (
    o.region AS o.region,
    o.category AS o.category
)
METRICS (
    o.revenue AS SUM(o.amount),
    o.order_count AS COUNT(*)
);

Each dimension and metric follows the pattern alias.name AS expression:

  • o.region AS o.region creates a dimension called region from the region column of the table aliased as o.

  • o.revenue AS SUM(o.amount) creates a metric called revenue that computes SUM(o.amount).

Verify the view was created:

SHOW SEMANTIC VIEWS;

You should see the view listed with its metadata:

┌──────────────────────┬───────────────┬───────────────┬───────────────┬─────────────┬─────────┐
│      created_on      │     name      │     kind      │ database_name │ schema_name │ comment │
├──────────────────────┼───────────────┼───────────────┼───────────────┼─────────────┼─────────┤
│ 2026-04-01T12:00:00Z │ order_metrics │ SEMANTIC_VIEW │ memory        │ main        │         │
└──────────────────────┴───────────────┴───────────────┴───────────────┴─────────────┴─────────┘

created_on shows when you ran the CREATE statement. comment is empty because this view has no COMMENT.

Query the Semantic View

Query the semantic view with the SEMANTIC_VIEW(…) construct in FROM, the same syntax Snowflake uses. Name the view, then list the dimensions and metrics you want after the DIMENSIONS and METRICS keywords. Pick any combination of the ones you defined. The result is an ordinary table, so you can add ORDER BY, LIMIT or a join to the outer query, as the examples below do with ORDER BY to fix the row order.

Dimensions and metrics together (grouped aggregation):

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region, category
    METRICS revenue, order_count
)
ORDER BY region, category;

┌────────┬──────────┬─────────┬─────────────┐
│ region │ category │ revenue │ order_count │
├────────┼──────────┼─────────┼─────────────┤
│ East   │ Hardware │   75.00 │           2 │
│ East   │ Software │   50.00 │           1 │
│ West   │ Hardware │   25.00 │           1 │
│ West   │ Software │  100.00 │           1 │
└────────┴──────────┴─────────┴─────────────┘

Dimensions only (distinct values, no aggregation):

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
)
ORDER BY region;

┌────────┐
│ region │
├────────┤
│ East   │
│ West   │
└────────┘

Metrics only (grand total, no GROUP BY):

SELECT * FROM SEMANTIC_VIEW(order_metrics
    METRICS revenue, order_count
);

┌─────────┬─────────────┐
│ revenue │ order_count │
├─────────┼─────────────┤
│  250.00 │           5 │
└─────────┴─────────────┘

Filtering the result with WHERE on the outer query:

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue
) WHERE region = 'East';

┌────────┬─────────┐
│ region │ revenue │
├────────┼─────────┤
│ East   │  125.00 │
└────────┴─────────┘

Warning

This WHERE runs on the result, after the metrics have been aggregated, so it can only name columns the query returned. To change what a metric measures, filter inside the construct instead, as the next example does.

Filtering the rows a metric aggregates with WHERE inside SEMANTIC_VIEW(...):

SELECT * FROM SEMANTIC_VIEW(order_metrics
    DIMENSIONS region
    METRICS revenue, order_count
    WHERE category = 'Hardware'
)
ORDER BY region;

┌────────┬─────────┬─────────────┐
│ region │ revenue │ order_count │
├────────┼─────────┼─────────────┤
│ East   │   75.00 │           2 │
│ West   │   25.00 │           1 │
└────────┴─────────┴─────────────┘

Each region’s revenue and order_count are now computed over its hardware orders only, although category is not in the output. How to Filter Semantic View Queries covers both filters in depth.

Note

SEMANTIC_VIEW(...) is shorthand for the semantic_view() table function, which takes the same request as named lists: semantic_view('order_metrics', dimensions := ['region'], metrics := ['revenue']). Both are supported, and they return the same result. The function form is handy when application code builds the lists.

Query It with Standard SQL

You can also query the view by name with an ordinary SELECT, as you would a table, the way Snowflake allows. Put the dimensions in GROUP BY and wrap each metric in AGG():

SELECT region, AGG(revenue) AS revenue, AGG(order_count) AS order_count
FROM order_metrics
WHERE category = 'Hardware'
GROUP BY region
ORDER BY region;

┌────────┬─────────┬─────────────┐
│ region │ revenue │ order_count │
├────────┼─────────┼─────────────┤
│ East   │   75.00 │           2 │
│ West   │   25.00 │           1 │
└────────┴─────────┴─────────────┘

This is the last query of the previous section in another spelling, and it returns the same rows. GROUP BY lists the dimensions, AGG() names the metrics, and WHERE filters the rows the metrics aggregate, like the WHERE inside SEMANTIC_VIEW(...). A metric already holds its aggregation, so you pass it to AGG() rather than writing SUM() around it. How to Query a Semantic View with Standard SQL covers the rest of this form.

Inspect the Generated SQL

Use explain_semantic_view() to see the SQL that the extension generates. It takes the same request as semantic_view(), as named lists:

SELECT * FROM explain_semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue']
);

The output shows the expanded SQL and the DuckDB query plan. This is useful for verifying that the extension produces the query you expect.

Clean Up

Drop the semantic view when you are done:

DROP SEMANTIC VIEW order_metrics;

What You Learned

You now know how to:

Next, learn how to model multiple tables with relationships in the Multi-Table Semantic Views tutorial. To see how a semantic view differs from a regular CREATE VIEW, read Semantic Views vs. Regular SQL Views.