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 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")

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 │
├─────────────────────┼───────────────┼───────────────┼───────────────┼─────────────┤
│ 2026-04-01T12:00:00 │ order_metrics │ SEMANTIC_VIEW │ memory        │ main        │
└─────────────────────┴───────────────┴───────────────┴───────────────┴─────────────┘

Query the Semantic View

Query the semantic view using the semantic_view() table function. Pick any combination of the dimensions and metrics you defined.

Dimensions and metrics together (grouped aggregation):

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

┌────────┬──────────┬─────────┬─────────────┐
│ 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']
);

┌────────┐
│ 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 with WHERE on the outer query:

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

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

Inspect the Generated SQL

Use explain_semantic_view() to see the SQL that the extension generates:

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.