explain_semantic_view()

Table function that shows the SQL generated by a semantic view query without executing the data query. Returns the expanded SQL, materialization routing decision, and the DuckDB query plan as rows of text.

Syntax

SELECT * FROM explain_semantic_view(
    '<view_name>',
    [ dimensions := [ '<dim_name>' [, ...] ] , ]
    [ metrics := [ '<metric_name>' [, ...] ] , ]
    [ facts := [ '<fact_name>' [, ...] ] , ]
    [ where_clause := '<predicate>' ]
)

Parameters

explain_semantic_view() accepts the same parameter set as semantic_view() – the two functions share one registration, so a query you can run you can also explain.

Parameter	Type	Description
<view_name>	VARCHAR (positional)	The name of the semantic view to explain. Matched case-insensitively (folded to lowercase per DuckDB identifier semantics), quoted or not. May carry a <schema>. (or <database>.<schema>.) qualifier; an unqualified name resolves through the session’s search_path, exactly as in semantic_view().
dimensions	LIST (named)	Optional list of dimension names. Supports alias.* wildcard patterns.
metrics	LIST (named)	Optional list of metric names. Supports alias.* wildcard patterns.
facts	LIST (named)	Optional list of fact names. Supports alias.* wildcard patterns. The expanded SQL shows each fact expression inlined, which makes this the quickest way to check how a chained fact resolved.
where_clause	VARCHAR (named)	Optional predicate applied before metrics are aggregated – the equivalent of Snowflake’s SEMANTIC_VIEW( … WHERE <predicate> ). See Pre-aggregation — where_clause. An omitted, empty, or whitespace-only value is treated as absent.
search_path	LIST (named)	The session’s schema resolution order, used to resolve an unqualified <view_name>. Supplied automatically – the extension’s parser override injects the caller’s search path into every explain_semantic_view() call it rewrites. Not intended to be written by hand.

At least one of dimensions, metrics, or facts must be specified. where_clause alone is not a query.

Warning

facts and metrics cannot be combined in the same query. Use facts := [...] or metrics := [...], not both. The restriction is enforced during expansion, so explain_semantic_view() reports it exactly as semantic_view() would.

Output

Returns multiple rows, each containing a single VARCHAR column:

Column	Type	Description
explain_output	VARCHAR	One line of the explain output.

The output has three sections:

1. Header: the view name, requested dimensions/metrics, and materialization routing decision.

2. Expanded SQL: the SQL query the extension generates, formatted with indentation.

3. DuckDB Plan: the physical query plan from EXPLAIN.

The header includes a -- Materialization: line that reports the routing decision:

• -- Materialization: <name> when the query matches a declared materialization and routes to the pre-aggregated table.

• -- Materialization: none when no materialization matches, or when the view has no materializations declared.

Added in version 0.7.0: The -- Materialization: header line.

Examples

Standard expansion (no materialization match):

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

Sample output:

-- Semantic View: analytics
-- Dimensions: customer_name
-- Metrics: revenue
-- Materialization: none

-- Expanded SQL:
SELECT
    c.name AS "customer_name",
    sum(o.amount) AS "revenue"
FROM "memory"."main"."orders" AS "o"
LEFT JOIN "memory"."main"."customers" AS "c"
    ON "o"."customer_id" = "c"."id"
GROUP BY
    1

-- DuckDB Plan:
┌─────────────────────────────┐
│     HASH_GROUP_BY           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─     │
│           ...               │
└─────────────────────────────┘

Fact query (row-level, no aggregation):

SELECT * FROM explain_semantic_view('analytics',
    facts := ['net_price', 'tax_amount']
);

The expanded SQL shows each fact expression inlined in place of its name, with chained facts resolved recursively.

Pre-aggregation filtering:

SELECT * FROM explain_semantic_view('order_metrics',
    dimensions := ['region'],
    metrics := ['revenue'],
    where_clause := 'ordered_at >= DATE ''2024-01-01'''
);

The expanded SQL shows where the predicate lands relative to the GROUP BY, which is the fastest way to confirm that a filtered metric is recomputed rather than filtered after aggregation.

Materialization routing match:

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

Sample output when a materialization covers the exact requested dimensions and metrics:

-- Semantic View: order_metrics
-- Dimensions: region
-- Metrics: revenue, order_count
-- Materialization: region_agg

-- Expanded SQL:
SELECT
    "region",
    "revenue",
    "order_count"
FROM "daily_revenue_by_region"

-- DuckDB Plan:
┌─────────────────────────────┐
│         SEQ_SCAN            │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─     │
│  daily_revenue_by_region    │
└─────────────────────────────┘

Tip

Use explain_semantic_view() to verify that the extension generates the SQL you expect, especially when debugging join paths, fact inlining, role-playing dimension scoped aliases, semi-additive CTE expansion, window function CTE expansion, pre-aggregation predicate placement, or materialization routing decisions.

Note

explain_semantic_view() reports how a query would run. To inspect the stored definition itself – fact expressions, comments, synonyms, declared keys – use DESCRIBE SEMANTIC VIEW or GET_DDL.