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>' [, ...] ] ]
)

Parameters

Parameter	Type	Description
<view_name>	VARCHAR (positional)	The name of the semantic view to explain.
dimensions	LIST (named)	Optional list of dimension names. Supports alias.* wildcard patterns.
metrics	LIST (named)	Optional list of metric names. Supports alias.* wildcard patterns.

At least one of dimensions or metrics must be specified.

Note

explain_semantic_view() does not support the facts parameter. To inspect fact definitions, use DESCRIBE SEMANTIC VIEW.

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 "orders" AS "o"
LEFT JOIN "customers" AS "c" ON "o"."customer_id" = "c"."id"
GROUP BY
    1

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

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, or materialization routing decisions.