.. meta::
   :description: Syntax reference for explain_semantic_view(), which shows the generated SQL and query plan for a semantic view query without executing it

.. _ref-explain-semantic-view:

===========================
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.


.. _ref-explain-syntax:

Syntax
======

.. code-block:: sqlgrammar

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


.. _ref-explain-params:

Parameters
==========

.. list-table::
   :header-rows: 1
   :widths: 20 15 65

   * - 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.
   * - ``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 :ref:`DESCRIBE SEMANTIC VIEW <ref-describe-semantic-view>`.


.. _ref-explain-output:

Output
======

Returns multiple rows, each containing a single VARCHAR column:

.. list-table::
   :header-rows: 1
   :widths: 25 15 60

   * - 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.

.. versionadded:: 0.7.0
   The ``-- Materialization:`` header line.


.. _ref-explain-examples:

Examples
========

**Standard expansion (no materialization match):**

.. code-block:: sql

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

Sample output:

.. code-block:: text

   -- 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:**

.. code-block:: sql

   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:

.. code-block:: text

   -- 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.
