Skip to main content

Rowset Derivation Statements

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.

Rowsets Are Isolated

A rowset is a self-contained set of rows. Its outputs do not inherit the relationships of the concepts they were selected from: reading recent_orders.total beside customer.name does not pair them through the model's order.customer_id -> customer.id relationship. A query that mixes rowset outputs with concepts from outside the rowset must declare how they relate, with a query-scoped join on a key the rowset projects. Without one, planning fails with a DisconnectedConceptsException:

Discovery error: cannot merge all concepts into one connected query.
The requested concepts split into 2 disconnected subgraphs:
{customer.id, customer.name}; {order_count}.
Are you missing a join or merge statement to relate them?

When the rowset projects the key the pairing would use, the error message also includes the subset join to add.

Three ways to use a rowset's rows beside the rest of the model:

  • Declare the join. subset join recent_orders.customer_id = customer.id pairs each customer with its rows; customers with none keep their key and get null for the rowset's values. The join key is read from the complete side, so it is never null on those rows.
  • Project the outside concept inside the rowset and read it through the handle (recent_orders.customer_id). A query that reads only the rowset's own outputs needs no join.
  • Use membership. where customer.id in recent_orders.customer_id tests the set of values and does not pair rows, so it needs no join either.

Join on a key at the grain you aggregate to. A rowset projecting one row per order joined on its order id and then summed per customer does not re-group the rows; project the customer id in the rowset and join on that instead.

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
;