Node references

CML lets you reference fields from other nodes inside SQL expressions. This is how you build cross-table calculations, derived dimensions, and join-dependent measures without duplicating logic.

The ${node.field} syntax

Inside any sql: expression in CML, you can reference a dimension from another node:

node: orders
  label: Orders
  sql_table: analytics.orders

  dimension: customer_lifetime_value_tier
    type: string
    label: CLV Tier
    sql: |
      CASE
        WHEN ${customers.lifetime_value} > 10000 THEN 'High'
        WHEN ${customers.lifetime_value} > 1000  THEN 'Medium'
        ELSE 'Low'
      END

Celiq resolves the reference to the lifetime_value dimension in the customers node and generates the appropriate JOIN in the SQL it sends to your warehouse.

The ${TABLE} shorthand

dimension: order_id
  sql: ${TABLE}.order_id

This is equivalent to sql: analytics.orders.order_id but is more portable — if you rename the underlying table, you only update sql_table in one place.

Referencing measures from other nodes

You can reference measures (not just dimensions) from other nodes in a custom measure:

measure: revenue_per_customer
  type: custom
  label: Revenue per Customer
  sql: ${orders.total_revenue} / NULLIF(${customers.customer_count}, 0)

When a measure is referenced cross-node, Celiq enforces that the two nodes are joined in a domain. If they are not, CML validation will flag the reference as invalid.

Join dependencies

Node references create implicit join dependencies. Celiq tracks these and uses them to:

1. Automatically select the correct domain when a user queries a field that has cross-node dependencies
2. Warn in CML validation if a referenced node is not available in the current domain
3. Suggest missing joins in the Orion-generated domain definitions

To see the join graph for your workspace, open Forge → Graph view (the icon in the top-right toolbar).

Self-references (${view_name.field})

You can reference another dimension in the same node using its full qualified name:

dimension: order_date_month
  type: string
  label: Order Month
  sql: DATE_TRUNC('month', ${orders.created_at})

Or using just ${field} when the reference is within the same node:

dimension: order_date_month
  type: string
  label: Order Month
  sql: DATE_TRUNC('month', ${created_at})

Both forms are equivalent. The short form is preferred within a node for readability.

Templated SQL

For complex cross-node logic, use the sql_template: block to write multi-line SQL with named substitutions:

measure: cohort_retention_rate
  type: custom
  label: 30-day Retention Rate
  sql_template: |
    WITH first_orders AS (
      SELECT customer_id, MIN(${TABLE}.created_at) AS first_order_date
      FROM ${TABLE}
      GROUP BY 1
    )
    SELECT
      COUNT(DISTINCT CASE WHEN DATEDIFF('day', fo.first_order_date, ${TABLE}.created_at) <= 30
        AND ${TABLE}.customer_id != fo.customer_id THEN ${TABLE}.customer_id END)
      / NULLIF(COUNT(DISTINCT fo.customer_id), 0)
    FROM ${TABLE}
    JOIN first_orders fo ON ${TABLE}.customer_id = fo.customer_id

Avoiding fan-out

When joining a one-to-many relationship, measures on the "one" side can be double-counted. Celiq detects this and warns when:

• A measure from the "one" side (e.g. customers.lifetime_value) is summed in a query that fans out over the "many" side (e.g. orders)
• The domain join's relationship field is set to one_to_many or many_to_many

To fix fan-out issues:

1. Change the measure to use COUNT(DISTINCT ...) if you are counting
2. Use type: sum with a ${TABLE}.field reference instead of a cross-node reference
3. Or restructure the domain so the aggregation happens before the join