Semantic Views vs. Regular SQL Views¶

If you are coming from standard SQL, you already know CREATE VIEW. A semantic view is a different kind of abstraction with different capabilities and trade-offs. This page explains what each does, where they overlap, and why semantic views exist.

What a Regular View Does¶

A regular SQL view stores a fixed query. When you SELECT FROM the view, DuckDB substitutes the view’s query and executes it.

CREATE VIEW revenue_by_region AS
SELECT region, SUM(amount) AS revenue
FROM orders
GROUP BY region;

-- Querying the view always runs the same query
SELECT * FROM revenue_by_region;

The view is a single, predetermined query. Every column, join, and GROUP BY is baked in. To get revenue by category instead of by region, you create a second view.

What a Semantic View Does¶

A semantic view stores a model, a set of dimensions, metrics, relationships, and facts. It does not store a query. Instead, it generates a query on demand based on what you request.

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(*)
);

-- Revenue by region
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue']
);

-- Revenue by category (same view, different request)
SELECT * FROM semantic_view('order_metrics',
    dimensions := ['category'],
    metrics := ['revenue']
);

One semantic view definition serves many different queries. The extension generates the appropriate SQL (SELECT, GROUP BY, and JOIN) based on which dimensions and metrics are requested.

Key Differences¶

	Regular View	Semantic View
Stores	A fixed SQL query	A model (dimensions, metrics, relationships)
Query flexibility	One fixed output shape	Any combination of dimensions and metrics
JOIN logic	Baked into the query	Generated per request; unused tables excluded
GROUP BY logic	Baked into the query	Generated based on requested dimensions
Metric consistency	Each view defines its own aggregation	Defined once, reused across all queries
Query interface	`SELECT * FROM view`	`semantic_view('name', dimensions := [...], metrics := [...])`

Why Semantic Views Exist¶

The core problem semantic views solve is metric inconsistency. In a traditional analytics setup, every dashboard, report, and ad-hoc query defines its own GROUP BY and JOIN logic. Revenue might be calculated differently across three dashboards: one includes discounts, another does not, a third uses a different join path.

A semantic view defines each metric once:

METRICS (
    o.revenue AS SUM(o.amount)
)

Every query that requests revenue gets the same calculation. There is no possibility of drift between different consumers of the same metric.

The second problem is join management. In a star schema with five dimension tables, a regular view must join all five tables even if a query only needs one. A semantic view joins only the tables that the requested dimensions require. This reduces query complexity and can improve performance.

Trade-Offs¶

Semantic views are not a replacement for regular views in all cases.

When regular views are better:

Fixed reports where the query shape never changes
Complex queries that cannot be expressed as dimension/metric combinations (window functions, CTEs, correlated subqueries)
Queries that need fine-grained control over join types, ordering, or LIMIT within the view

When semantic views are better:

Multiple consumers (dashboards, reports, APIs) querying the same data model with different dimension/metric combinations
Star or snowflake schemas where join management across many tables is error-prone
Analytics layers where metric consistency matters (everyone should compute revenue the same way)
Exploration workflows where analysts want to slice data by different dimensions without writing new queries

Semantic Views Are Not Materialized¶

Semantic views do not store data. The extension is a preprocessor: it generates SQL and hands it to DuckDB for execution. Each query runs fresh against the underlying tables. There is no caching, no pre-aggregation, and no materialized result set.

This means semantic view query performance is the same as running the equivalent SQL directly. The value is in query generation and metric consistency, not in performance optimization.