Multi-Table Semantic Views

In this tutorial, you will define a semantic view over three related tables, declare PK/FK relationships between them, and see how the extension joins only the tables needed for each query. By the end, you will understand how to model a star schema with semantic views and query across table boundaries.

Time: 10 minutes

Prerequisites:

  • Completed the Getting Started tutorial

  • Familiarity with star schema concepts (fact table, dimension tables, foreign keys)

Create the Schema

Create a simple e-commerce schema with three tables: orders (fact table), customers (dimension table), and products (dimension table).

CREATE TABLE customers (id INTEGER, name VARCHAR, city VARCHAR);
INSERT INTO customers VALUES
    (1, 'Alice', 'Portland'),
    (2, 'Bob',   'Seattle'),
    (3, 'Carol', 'Portland');

CREATE TABLE products (id INTEGER, name VARCHAR, category VARCHAR);
INSERT INTO products VALUES
    (10, 'Widget',  'Hardware'),
    (20, 'Gadget',  'Hardware'),
    (30, 'Service', 'Software');

CREATE TABLE orders (
    id INTEGER, customer_id INTEGER, product_id INTEGER,
    amount DECIMAL(10,2), ordered_at DATE
);
INSERT INTO orders VALUES
    (1, 1, 10, 25.00,  '2024-01-15'),
    (2, 1, 20, 50.00,  '2024-01-20'),
    (3, 2, 10, 25.00,  '2024-02-10'),
    (4, 2, 30, 100.00, '2024-02-14'),
    (5, 3, 20, 50.00,  '2024-03-01');

Define the Semantic View

Declare all three tables in the TABLES clause, each with an alias and primary key. Then declare how they connect in the RELATIONSHIPS clause.

CREATE SEMANTIC VIEW shop AS
TABLES (
    o AS orders    PRIMARY KEY (id),
    c AS customers PRIMARY KEY (id),
    p AS products  PRIMARY KEY (id)
)
RELATIONSHIPS (
    order_customer AS o(customer_id) REFERENCES c,
    order_product  AS o(product_id)  REFERENCES p
)
DIMENSIONS (
    c.customer AS c.name,
    c.city     AS c.city,
    p.product  AS p.name,
    p.category AS p.category,
    o.month    AS date_trunc('month', o.ordered_at)
)
METRICS (
    o.revenue     AS SUM(o.amount),
    o.order_count AS COUNT(*)
);

The RELATIONSHIPS clause tells the extension how tables connect:

  • order_customer AS o(customer_id) REFERENCES c means “the customer_id column on orders (alias o) is a foreign key to the primary key of customers (alias c).”

  • Each relationship gets a name (order_customer, order_product) that identifies the join path.

Dimensions and metrics reference columns through their table alias: c.customer AS c.name creates a dimension called customer from the name column on the c (customers) table.

Tip

The PRIMARY KEY declaration is used by the extension to synthesize JOIN ON clauses. It does not create a constraint in DuckDB. It is metadata for the semantic view only.

Query One Dimension Table

Request revenue by customer. The extension joins only the customers table; products is not needed and is not included.

SELECT * FROM semantic_view('shop',
    dimensions := ['customer'],
    metrics := ['revenue', 'order_count']
) ORDER BY revenue DESC;

┌──────────┬─────────┬─────────────┐
│ customer │ revenue │ order_count │
├──────────┼─────────┼─────────────┤
│ Bob      │  125.00 │           2 │
│ Alice    │   75.00 │           2 │
│ Carol    │   50.00 │           1 │
└──────────┴─────────┴─────────────┘

Verify which tables were joined by inspecting the generated SQL:

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

The expanded SQL includes a LEFT JOIN to customers but no join to products.

Query Across Both Dimension Tables

Request dimensions from both customers and products. The extension joins both tables through the orders fact table.

SELECT * FROM semantic_view('shop',
    dimensions := ['customer', 'product'],
    metrics := ['revenue']
) ORDER BY customer, product;

┌──────────┬─────────┬─────────┐
│ customer │ product │ revenue │
├──────────┼─────────┼─────────┤
│ Alice    │ Gadget  │   50.00 │
│ Alice    │ Widget  │   25.00 │
│ Bob      │ Service │  100.00 │
│ Bob      │ Widget  │   25.00 │
│ Carol    │ Gadget  │   50.00 │
└──────────┴─────────┴─────────┘

Use a Computed Dimension

The month dimension uses date_trunc('month', o.ordered_at) as its expression. Dimensions can be any SQL expression, not just column references.

SELECT * FROM semantic_view('shop',
    dimensions := ['month'],
    metrics := ['revenue', 'order_count']
) ORDER BY month;

┌────────────┬─────────┬─────────────┐
│   month    │ revenue │ order_count │
├────────────┼─────────┼─────────────┤
│ 2024-01-01 │   75.00 │           2 │
│ 2024-02-01 │  125.00 │           2 │
│ 2024-03-01 │   50.00 │           1 │
└────────────┴─────────┴─────────────┘

Describe the View

Use DESCRIBE SEMANTIC VIEW to see the full definition:

DESCRIBE SEMANTIC VIEW shop;

The output uses a property-per-row format. Each row describes one property of one object (table, relationship, dimension, metric) in the view.

Update the View

To change a semantic view, use CREATE OR REPLACE:

CREATE OR REPLACE SEMANTIC VIEW shop AS
TABLES (
    o AS orders    PRIMARY KEY (id),
    c AS customers PRIMARY KEY (id),
    p AS products  PRIMARY KEY (id)
)
RELATIONSHIPS (
    order_customer AS o(customer_id) REFERENCES c,
    order_product  AS o(product_id)  REFERENCES p
)
DIMENSIONS (
    c.customer AS c.name,
    c.city     AS c.city,
    p.product  AS p.name,
    p.category AS p.category,
    o.month    AS date_trunc('month', o.ordered_at),
    o.year     AS date_trunc('year', o.ordered_at)
)
METRICS (
    o.revenue     AS SUM(o.amount),
    o.order_count AS COUNT(*),
    o.avg_order   AS AVG(o.amount)
);

The new year dimension and avg_order metric are immediately available for queries.

Tip

CREATE OR REPLACE replaces the full view definition. To rename a view without changing its definition, use ALTER SEMANTIC VIEW … RENAME TO instead:

ALTER SEMANTIC VIEW shop RENAME TO shop_v2;

Clean Up

DROP SEMANTIC VIEW shop;

What You Learned

You now know how to:

  • Declare multiple tables with aliases and primary keys in the TABLES clause

  • Define PK/FK relationships in the RELATIONSHIPS clause

  • Query dimensions from different tables, with the extension joining only what is needed

  • Use SQL expressions (like date_trunc()) as dimension definitions

  • Update a semantic view with CREATE OR REPLACE SEMANTIC VIEW

Next, explore advanced features in the how-to guides: