Statement tools

These tools wrap or replace a query: you prefix EXPLAIN / FORMAT, or you run DESCRIBE / SHOW … as the statement. Tools you write inside RETURN are on Tools in the query .

EXPLAIN

Prefix any query with EXPLAIN to see a JSON plan — stage, output schema, features, layers — without executing it:

EXPLAIN (FIELDS (STAGE, OUTPUT_SCHEMA, FEATURES)) RETURN feature1 := 1, feature2 := feature1 + 1;

Use EXPLAIN (FIELDS (…)) to keep only the keys you need. The result stays one OUTPUT VARCHAR cell containing JSON.

More on EXPLAIN — field lists, reading the plan, and request-prefix forms over a temporary overlay. Syntax: EXPLAIN .

FORMAT

FORMAT rewrites a query into canonical layout and syntax without executing it. Options include ESCAPED (newline markers for tests), FIX (migrate STRUCT/LIST and other legacy forms), and WIDTH:

FORMAT (ESCAPED) RETURN F1 := 1 + 2 * 3;

More on FORMAT — FIX caveats, comment attachment, and worked examples. Syntax: FORMAT .

DESCRIBE

The DESCRIBE statement shows compiler metadata about a named feature as a JSON cell:

CREATE TEMPORARY FEATURES AS PERSIST feature1 := 1, feature2 := feature1 + 1;
DESCRIBE (FIELDS (NAME, FUNCTION)) FEATURE2;

Use DESCRIBE (FIELDS (NAME, FUNCTION, …)) to project each feature object before serialization. It works with both temporary and persisted features.

For columns you can project with [field] access, use DESCRIBE() inside RETURN — Tools in the query . Syntax: DESCRIBE .

SHOW CREATE

SHOW CREATE FEATURES returns the FeatureQL code needed to recreate one or more features. Useful for inspecting stored definitions and for exporting them.

For temporary features:

START TRANSACTION;
CREATE TEMPORARY FEATURES AS PERSIST feature1 := 1, feature2 := feature1 + 1;
SHOW CREATE FEATURES FEATURE1, FEATURE2;
COMMIT;

FeatureQL reorders features by dependency layer and writes the canonical spelling. A literal stays := 1. Semantics stay the same.

For persisted features, the output includes full namespace paths:

CREATE OR REPLACE FEATURES AS
PERSIST
    fm.tutorials.querytools.feature1 := 1,
    fm.tutorials.querytools.feature2 := fm.tutorials.querytools.feature1 + 1
;

SHOW CREATE FEATURES FM.TUTORIALS.QUERYTOOLS.FEATURE2;

Features are topologically ordered, so the output is self-contained — you can run it to recreate the features.

Without LANGUAGE, the text is FeatureQL. With (LANGUAGE DUCKDB) (or TRINO, DATAFUSION), you get the SQL that dialect would run to return those features. Serving uses DATAFUSION.

SHOW CREATE FEATURES (LANGUAGE DUCKDB)
    FM.TUTORIALS.QUERYTOOLS.FEATURE1,
    FM.TUTORIALS.QUERYTOOLS.FEATURE2
;

The SQL wrappers around those aliases can change; compare on the feature names, not the full CTE text.

Syntax: SHOW CREATE .

CREATE TEMPORARY overlay

CREATE TEMPORARY builds a request overlay: later RETURN, SHOW, DESCRIBE, and EXPLAIN in the same request see those names, and the registry is unchanged. The SHOW CREATE examples above wrap temporary features that way.

A lone CREATE TEMPORARY plus RETURN can stay unwrapped. Several overlay mutations (DROP TEMPORARY, a second CREATE TEMPORARY) need START TRANSACTION / COMMIT, and the last inner statement must be the result you want.

EXPLAIN START TRANSACTION; …; COMMIT; and FORMAT START TRANSACTION; …; COMMIT; are request prefixes: they explain or format the trailing result against the overlay. They do not write the registry. Detail: EXPLAIN .

Do not mix persistent CREATE / ALTER / DROP with TEMPORARY in the same script (UE/TRANSACTION-INVALID-SHAPE). Publish with a persist-only transaction instead — Create features . The three script shapes are on Transactions .

SHOW DOCS

SHOW DOCS searches the built-in documentation without leaving the query environment:

SHOW DOCS (
    EXCLUDE (CONTENT),
    INCLUDE (LENGTH(NAME) AS LNAME)
)
WHERE LNAME > 30 AND NAME LIKE 'docs/%'
ORDER BY NAME ASC
LIMIT 5

Filter by NAME prefix (docs/% for human docs, llms/% for help-tag pages). URL is the public link when the page is published. Search content with LIKE and sort results. Combined with SHOW FUNCTIONS and SHOW TESTS, these statements make FeatureQL largely self-documenting.

SHOW TESTS

SHOW TESTS lists executable documentation snippets. WHERE / LIKE / LIMIT select the snippets you asked for. Add WITH DEPENDENCIES to also return their transitive # depends: setup. Extra rows set IS_ONLY_DEPENDENCY to TRUE. LIMIT still counts seeds only.

SHOW TESTS (
    COLUMNS (IS_ONLY_DEPENDENCY),
    INCLUDE (SPLIT_PART(SPLIT_PART(NAME, '#', 2), '.', 2) AS SNIPPET)
)
WHERE NAME LIKE '%.show_create_features_persisted'
ORDER BY SNIPPET
LIMIT 1
WITH DEPENDENCIES;

This is a catalog query — it does not run the snippets. To execute the suite, see Conformance tests . The full statement is on SHOW .

SHOW FUNCTIONS

SHOW FUNCTIONS lists functions by name and category. Use LIKE patterns to narrow the results:

SHOW FUNCTIONS (COLUMNS (NAME, CATEGORY)) LIKE 'ARR%' ORDER BY NAME LIMIT 10;

Results include CATEGORY (as an array like ['Math', 'Arithmetic']), DISPLAY_ORDER for sorting, and optional columns like ALIASES, OPERATOR, PARAMETERS, and NOTES. Use INCLUDE (SIGNATURES, EXAMPLES) for nested ARRAY(ROW(...)) overloads and registry examples.

SHOW FUNCTIONS (INCLUDE (SIGNATURES)) returns an ARRAY(ROW(...)) of overloads. Flatten with hybrid SQL:

SELECT
    f.NAME,
    (s).PARAMETER_NAMES AS PARAMETER_NAMES,
    (s).PARAMETER_TYPES AS PARAMETER_TYPES,
    (s).RETURN_TYPE AS RETURN_TYPE
FROM FEATUREQL(SHOW FUNCTIONS (INCLUDE (SIGNATURES)) WHERE NAME = 'ADD' LIMIT 1) f,
UNNEST(f.SIGNATURES) AS t(s);

Each row is one overload. PARAMETER_NAMES and PARAMETER_TYPES are arrays, so ADD shows its numeric overloads and its date/interval overload side by side.

The same playgrounds appear on Operators and functions in the language tour. Syntax: SHOW .

Related

• Tools in the query
• Python library tools