Measures from Joined Models¶
TL;DR: In a SLayer query, you can include not only dimensions, but also measures from linked models, using the usual dot syntax.
When you query a measure from a joined model, SLayer computes it in a separate sub-query to avoid row multiplication from the JOIN. This guarantees correctness — the measure value is the same whether you query it from the joined model or from the source model directly.
This notebook demonstrates cross-model measures with working code.
See also: Cross-Model Measures | Joins | Formulas
Prerequisites: pip install motley-slayer
import os
import sys
sys.path.insert(0, os.path.join(os.getcwd(), "..", "..", ".."))
sys.path.insert(0, os.path.join(os.getcwd(), "..", "jaffle_data"))
from setup_jaffle import ensure_jaffle_shop
engine, storage, models = ensure_jaffle_shop()
The Problem: JOINs Tie Metrics to the Wrong Grain¶
Suppose you want monthly order revenue alongside a total customer base count — a single global figure shown on every row, not "customers who ordered that month." Orders → customers is many-to-one (each order has exactly one customer), so a naive JOIN orders → customers does not inflate SUM(order_total). But aggregating customer count after that join scopes the metric to the order/month grain — you'd end up counting the customers who placed an order that month, not the global customer base.
(In other join shapes — say a one-to-many like orders → items — the naive join does multiply rows and inflate sums. The same isolation strategy below handles both cases.)
SLayer avoids this by computing the cross-model measure in a separate sub-query, grouped only by dimensions shared between the two models, then joining the result back. Orders and customers don't share a time or grouping dimension here, so the customers sub-query produces a single row — the global customer count — broadcast across every month via CROSS JOIN. The order metrics stay correct, and the customer metric stays at its intended global grain.
orders_model = next(m for m in models if m.name == "orders")
print("orders model joins:")
for j in orders_model.joins:
print(f" -> {j.target_model} ON {j.join_pairs}")
customers_model = next(m for m in models if m.name == "customers")
print(f"\ncustomers measures: {[m.name for m in customers_model.measures]}")
stores_model = next(m for m in models if m.name == "stores")
print(f"stores measures: {[m.name for m in stores_model.measures]}")
orders model joins: -> customers ON [['customer_id', 'id']] -> stores ON [['store_id', 'id']] customers measures: [] stores measures: []
Cross-Model Measure Syntax¶
To reference a measure from a joined model, use dotted syntax in the field formula: "joined_model.measure_name". For example, "customers.*:count" computes COUNT(*) on the customers model, scoped to whatever dimensions the source and target share.
The source model must have an explicit join to the target model.
# Monthly orders alongside the global customer base count.
# `customers.*:count` is a cross-model measure: SLayer computes it in a
# separate sub-query so the LEFT JOIN never multiplies the order rows.
result = engine.execute_sync(
query={
"source_model": "orders",
"time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
"measures": [
"*:count",
"order_total:sum",
"customers.*:count",
],
"order": [{"column": "ordered_at", "direction": "asc"}],
"limit": 6,
}
)
print(f"{'Month':<12} {'Orders':>8} {'Revenue':>14} {'Customers':>10}")
print("-" * 48)
for row in result.data:
month = str(row["orders.ordered_at"])[:7]
count = row["orders._count"]
rev = row["orders.order_total_sum"]
custs = row["orders.customers._count"]
print(f"{month:<12} {count:>8,} ${rev:>13,.2f} {custs:>10,}")
Month Orders Revenue Customers ------------------------------------------------ 2023-05 1,335 $ 15,102.20 2,590 2023-06 2,107 $ 23,098.27 2,590 2023-07 2,671 $ 29,880.94 2,590 2023-08 3,466 $ 38,628.40 2,590 2023-09 3,988 $ 44,370.20 2,590 2023-10 4,352 $ 47,540.71 2,590
print("Generated SQL:")
print(result.sql)
Generated SQL:
WITH _base AS (
SELECT
DATE_TRUNC('MONTH', orders.ordered_at) AS "orders.ordered_at",
COUNT(*) AS "orders._count",
SUM(orders.order_total) AS "orders.order_total_sum"
FROM orders AS orders
GROUP BY
DATE_TRUNC('MONTH', orders.ordered_at)
), _cm_orders__customers___count AS (
SELECT
COUNT(*) AS "orders.customers._count"
FROM customers AS customers
)
SELECT _base."orders.ordered_at", _base."orders._count", _base."orders.order_total_sum", _cm_orders__customers___count."orders.customers._count"
FROM _base
CROSS JOIN _cm_orders__customers___count
ORDER BY _base."orders.ordered_at" ASC
LIMIT 6
Notice the SQL: customers.*:count was computed in a separate CTE (_cm_orders__customers___count) that selects from customers independently of orders. Because orders and customers share no time or grouping dimension, the CTE produces a single row — the global customer count — which is CROSS JOINed back to the per-month order aggregation.
The point: the order metrics are computed without joining customers in, so the join can never multiply the order rows. If both models did share a grouping dimension (e.g., a signed_up_at on customers truncated to month), the CTE would group by that dimension and the join would be a regular LEFT JOIN on it — same correctness guarantee, varying values per row.
Transforms on Cross-Model Measures¶
Window functions like cumsum(), change(), and rank() work on cross-model measures. The transform is applied after the cross-model sub-query is joined back:
result = engine.execute_sync(
query={
"source_model": "orders",
"time_dimensions": [{"dimension": "ordered_at", "granularity": "month"}],
"measures": [
"customers.*:count",
{"formula": "cumsum(customers.*:count)", "name": "cumulative_customers"},
],
"order": [{"column": "ordered_at", "direction": "asc"}],
"limit": 6,
}
)
print(f"{'Month':<12} {'Customers':>10} {'Cumulative':>12}")
print("-" * 36)
for row in result.data:
month = str(row["orders.ordered_at"])[:7]
custs = row["orders.customers._count"]
cum = row["orders.cumulative_customers"]
print(f"{month:<12} {custs:>10,} {cum:>12,}")
Month Customers Cumulative ------------------------------------ 2023-05 2,590 2,590 2023-06 2,590 5,180 2023-07 2,590 7,770 2023-08 2,590 10,360 2023-09 2,590 12,950 2023-10 2,590 15,540
Summary¶
| Feature | Description |
|---|---|
| Dotted syntax | "customers.count" references a measure on a joined model |
| Sub-query isolation | Cross-model measures are computed in a separate CTE, avoiding row multiplication |
| Shared dimensions | The sub-query groups by dimensions shared between the source and target models |
| LEFT JOIN | The CTE result is joined back to the main query, preserving all main-query rows |
| Transforms | cumsum(), change(), rank() etc. work on cross-model measures |
See the Cross-Model Measures docs and Formulas docs for the full reference.