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> [ AS <alias> ]' [, ...] ] , ]
    [ metrics := [ '<metric_name> [ AS <alias> ]' [, ...] ] , ]
    [ facts := [ '<fact_name> [ AS <alias> ]' [, ...] ] , ]
    [ where_clause := '<predicate>' , ]
    [ params := row( <value> [, ...] ) , ]
    [ variables := { '<var_name>': <value> [, ...] } , ]
    [ column_order := [ '<list_name>' [, ...] ] ]
)

Parameters

explain_semantic_view() accepts the same parameters as semantic_view(), so any query you can run you can also explain. To explain a SEMANTIC_VIEW(…) query, write each clause as the parameter of the same name, with WHERE as where_clause and VARIABLES as variables. The construct turns into exactly that call, so

SELECT * FROM SEMANTIC_VIEW(shop DIMENSIONS region METRICS revenue WHERE ordered_at >= DATE '2024-01-01');

is explained by

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

A value the construct takes as a prepared-statement parameter goes in params, and writing METRICS before DIMENSIONS is column_order := ['metrics', 'dimensions'].

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	VARCHAR[] (named)	Optional list of dimension names or expressions. Supports alias.* wildcard patterns. An item may name its column ('region AS r'), as in semantic_view().
metrics	VARCHAR[] (named)	Optional list of metric names or expressions. Supports alias.* wildcard patterns and AS aliases.
facts	VARCHAR[] (named)	Optional list of fact names or expressions. Supports alias.* wildcard patterns and AS aliases. 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 construct’s WHERE. See Pre-Aggregation – WHERE in the Construct, or where_clause. An omitted, empty, or whitespace-only value is treated as absent.
params	row (named)	Optional values for where_clause’s $1, $2, …, as in semantic_view(). The plan is of the query with the values in place.
variables	STRUCT (named)	Optional values for the view’s variables, by name, as in semantic_view(). In the expanded SQL, each value the query sets appears as a parameter, numbered after where_clause’s own (CAST(($2) AS DOUBLE)), and each variable left unset has its default written in (CAST((1000) AS INTEGER)). Each is cast to the variable’s type when it has one. Added in version 0.18.0.
column_order	VARCHAR[] (named)	Optional order of the lists’ columns, for example ['metrics', 'dimensions']. It is checked as in semantic_view().
search_path	VARCHAR[] (named)	Reserved. Do not pass it. The function reads the session’s search_path itself, so an unqualified <view_name> resolves the way it does for semantic_view() (see The search_path Parameter).

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

Added in version 0.16.0: Aliases in items and the column_order parameter.

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, the requested dimensions and metrics (plus a -- Facts: line when facts are requested), and the materialization routing decision.

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

3. DuckDB Plan: the physical query plan, as EXPLAIN shows it. It is planned in your session and transaction, as semantic_view() would be, so a temporary table of your session or a table changed in your open transaction is planned as the query would read it.

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 (plan abridged):

-- 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:
physical_plan
┌───────────────────────────┐
│         PROJECTION        │
│           ...             │
└───────────────────────────┘

Fact query (row-level, no aggregation):

Using the shop view from the semantic_view() examples:

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

The expanded SQL shows each fact expression inlined in place of its name, with chained facts resolved recursively (tax_amount is defined over net_price):

-- Expanded SQL:
SELECT
    o.price * (1 - o.discount) AS "net_price",
    (o.price * (1 - o.discount)) * o.tax_rate AS "tax_amount"
FROM "memory"."main"."orders" AS "o"

Pre-aggregation filtering:

SELECT * FROM explain_semantic_view('shop',
    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:

-- Expanded SQL:
SELECT
    o.region AS "region",
    SUM((o.price * (1 - o.discount))) AS "revenue"
FROM "memory"."main"."orders" AS "o"
WHERE (o.ordered_at) >= DATE '2024-01-01'
GROUP BY
    1

Variables:

SELECT * FROM explain_semantic_view('sales_analysis',
    dimensions := ['sale_category'],
    metrics := ['weighted_sales'],
    where_clause := 'region = target_region',
    variables := {'target_region': 'x', 'multiplier': 2}
);

Over the sales_analysis view from VARIABLES, the values this query sets show as parameters in the expanded SQL: WHERE (region) = (CAST(($1) AS VARCHAR)) for target_region and SUM(amount * (CAST(($2) AS DOUBLE))) for multiplier. min_amount is not set, so its default is written in: amount >= (CAST((1000) AS INTEGER)).

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 (plan abridged):

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

-- Expanded SQL:
SELECT
    "region",
    "revenue",
    "order_count"
FROM "memory"."main"."revenue_by_region"

-- DuckDB Plan:
physical_plan
┌───────────────────────────┐
│          SEQ_SCAN         │
│    ────────────────────   │
│           Table:          │
│        memory.main        │
│     .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.