Rowset Derivation Statements

Rowset derivation statements create named subqueries (CTEs) that can be referenced in subsequent queries within the same script.

Syntax

Two equivalent forms:

syntax-example
rowset_derivation_statement:
    ("rowset" IDENTIFIER "<-" select_statement)
    | ("with" IDENTIFIER "as" select_statement)

Rowset Form


rowset high_value_customers <-
select
    customer.id,
    customer.name,
    sum(order.total) -> lifetime_value
having
    lifetime_value > 10000
;

With Form

CTE-style syntax familiar from SQL:


with high_value_customers as
select
    customer.id,
    customer.name,
    sum(order.total) -> lifetime_value
having
    lifetime_value > 10000
;

Using Rowsets

After definition, reference the rowset in subsequent queries. Alias fields that you want to access directly under the rowset name:


rowset active_users <-
where user.last_login > date_add(current_date(), day, -30)
select
    user.id as id,
    user.last_login as last_login,
;
select
    active_users.id,
    active_users.last_login,
;

Without an alias, a field retains its source path under the rowset namespace (for example, active_users.user.id). Use query-scoped joins to connect rowset outputs to another model or rowset on explicit keys.

Use Cases

Breaking complex queries into steps
Reusing intermediate results
Improving query readability
Staging filtered or aggregated results before joining

Rowsets are logical query results. Use Persist Statements when you need to materialize results in storage.

Example


import customer as customer;
import order as order;
# Define a rowset for recent orders
rowset recent_orders <-
where order.date > date_add(current_date(), day, -90)
select
    order.customer_id as customer_id,
    order.id as id,
    order.date as order_date,
    order.total as total,
;
# Use the rowset in analysis
select
    customer.id,
    customer.name,
    count(recent_orders.id) -> order_count,
    sum(recent_orders.total) -> recent_spend
subset join recent_orders.customer_id = customer.id
order by
    recent_spend desc
;