# Help: `mental_model` (concept)

# FeatureQL mental model

Load this BEFORE [`getting_started`](/llms/getting_started.md) if you know SQL.
This page corrects priors. It does not teach syntax.

Every block: ASSERT → WRONG INSTINCT → RIGHT → PROVE IT (runnable).

## 0. The one-line reframe

| You will assume | Actually |
|---|---|
| `FOR` is a `FROM` or a `WHERE` | `FOR` is the **data universe** (every key the graph may touch). Query `WHERE` filters **presented** rows. |
| Query starts at a table | Query starts at a **formula**. Tables enter only at `EXTERNAL_COLUMNS` and `FOR`. |
| Features are columns | Features are **functions**. A column is what you get after binding. |
| `WITH` is a CTE list | `DEFINE` is a **flat DAG of definitions**. Order is irrelevant; the engine topo-sorts. |
| Grain is a primary key | Grain is **declared**. Duplicate `BIND TO` keys silently fan out. |
| `SELECT E, PI` | Nullary constants are **`E()`, `PI()`, `EMPTY()`**. A bare name is an identifier. |

Spreadsheet analogy: a feature is `=AVG(B:B)` — a formula with no data in it.
`FOR` is dropping the sheet onto real cells.

## 1. Purity: features are functions, not queries

**ASSERT:** A feature has no data in it. It is evaluated only at `FOR`.

**WRONG INSTINCT:** "I'll define the query, then filter it."

**RIGHT:** Define unfiltered features; the row filter is itself a named boolean, applied on **each** aggregate with `FILTER (WHERE …)`. Query `WHERE` is presentation-only.

```featureql
-- WRONG: filter baked into the source
ORDERS := EXTERNAL_COLUMNS(... FROM SQL(SELECT * FROM o WHERE d >= '2024-07-01'))

-- RIGHT: map everything, filter as a boolean feature
ORDERS  := EXTERNAL_COLUMNS(... FROM TABLE(schema.orders)),
IS_JULY := ORDERS[order_date] >= DATE '2024-07-01',
JULY_REV := CUSTOMER_ID.RELATED(
    SUM(ORDERS[amount]) FILTER (WHERE IS_JULY) GROUP BY (ORDERS[customer_id])
)
```

**PROVE IT:** lazy evaluation — unreferenced features cost nothing.
```featureql
DEFINE A := INPUT(BIGINT), B := A * 2, C := A / 0
RETURN B FOR A := BIND_VALUES(ARRAY(1, 2))
-- Returns 2, 4. C is never evaluated. Not a query: a graph.
```

**COROLLARY:** non-determinism is illegal inside the graph. `RANDOM()` and
`CURRENT_TIMESTAMP()` must be frozen at compile time or they are rejected.
```featureql
RETURN ID := INPUT(BIGINT), T := @eval_as_literal(CURRENT_TIMESTAMP())
FOR ID := BIND_VALUES(ARRAY(1, 2, 3))
-- Same T on every row. "Varies per evaluation" ≠ "varies per row".
-- Per-row determinism: HASH01(key)
```

## 2. Entity IDs are a type, not a tagged integer

**ASSERT:** `BIGINT#CUSTOMERS` and `BIGINT#ORDERS` are different types.
The compiler rejects joins between them. Both are BIGINT in the warehouse.

**WRONG INSTINCT:** "Both are integers, the join will work."
(In SQL and every semantic layer, it does — and returns silent garbage.)

**PROVE IT — this must fail:**
```sql
RELATED(CUSTOMER_SEGMENT VIA INV_SOURCE[subscription_id])
-- UE/RELATED-ENTITY-MISMATCH: field has entity family 'SUBSCRIPTIONS'
-- on the base side but 'CUSTOMERS' on the bound side
```

**PROVE IT — laundering is blocked too:**
```featureql
LAUNDERED := SUBSCRIPTION_ID + 0
-- Entity keys that hit ANNOTATIONS-NOT-ALLOWED drop the entity with KEY_TO_SCALAR()
-- (omit keeps the entity). Relabel with UNSAFE_CAST().
```

**COROLLARY:** `COALESCE` / `IF` / `CASE` / `ARRAY[]` / `GREATEST` / `LEAST` /
`LAG`/`LEAD` default reject different entity families. Different roles of one
family return the shared family. Same entity stays that entity. Drop a key
with `KEY_TO_SCALAR()`. Units already unified this way.

**COROLLARY:** annotations survive composition — through `RELATED` hops,
through `RECURSE` traversal, into persisted features. Check with `TYPEOF()`.
When `#` refuses you (junction `ROW`, `CAST` vs `UNSAFE_CAST`, `DAYS.YEAR`),
load [`entity_mental_model`](/llms/entity_mental_model.md).

## 3. Joins are type-resolved, not written

**ASSERT:** You never write a join. You name a path; types resolve it.
Nothing is implicit: the FK is always explicit in `VIA`, so multiple-join-path
ambiguity (the DAX / semantic-layer failure mode) cannot occur.

**WRONG INSTINCT:** "invoice → subscription → customer needs a pre-joined table."

**RIGHT:** compose `RELATED` hops directly.
```featureql
INVOICE_CUSTOMER_ID := RELATED(SUB[customer_id] VIA INV[subscription_id]),
INVOICE_SEGMENT     := RELATED(CUSTOMER_SEGMENT VIA INVOICE_CUSTOMER_ID)
-- Two hops, no staging table, no materialization.
```

**Direction matters — memorize:**

| Direction | Form | Binding | Reads as |
|---|---|---|---|
| FK → PK (lookup) | `RELATED(target VIA source[fk])` | Bind the fact `INPUT` only. Do not `NESTED` the dimension key (`UE/NESTED-BINDING-SUPERFLUOUS`). | "the customer's segment for this invoice" |
| PK → FK (aggregate) | `PK.RELATED(AGG(...) GROUP BY (source[fk]))` | `NESTED` the child `INPUT`, else `UE/RELATED-UNBOUND-INPUT`. | "sum of this customer's invoices" |
| non-equi | `RELATED(AGG(...) ON pred)` | `NESTED` the inner grain when it is not the selected row. | range / as-of / theta |

## 4. Two scopes. Exactly two.

**ASSERT:** graph scope and `TRANSFORM` inner scope. No third. No nesting.

**WRONG INSTINCT:** "I'll nest a subquery / reference an outer feature inside TRANSFORM."

```featureql
-- WRONG: outer feature invisible inside TRANSFORM
FILTERED := ARR.TRANSFORM(RETURN * WHERE d < WINDOW_END)

-- RIGHT: inject it as a row field first
FILTERED := ARR.CARRY(WINDOW_END AS wend).TRANSFORM(RETURN * WHERE d < wend)
```

**RULE:** fetch and join in graph scope (`EXTEND`, `RELATED`, `CARRY`);
compute in transform scope. `EXTEND`/`RELATED`/`EXTERNAL_*`/nested `TRANSFORM`
are forbidden inside the inner `RETURN`.

**WHY:** you never trace nested scopes to understand a feature.

## 5. Nested rows replace joins

**ASSERT:** a customer's orders are a **field on the customer**, not a table to join.

**WRONG INSTINCT:** "I need a subquery to filter the child rows."

**RIGHT:** it is an array operation.
```featureql
ORDERS_ARR := CUSTOMER_ID.RELATED(
    ARRAY_AGG(ROW(O[amount] AS amount, O[date] AS date)) GROUP BY (O[customer_id])
),
RECENT     := ORDERS_ARR.TRANSFORM(RETURN * WHERE date >= DATE '2024-01-01'),
RECENT_SUM := RECENT.TRANSFORM(RETURN SUM(amount)).UNWRAP_ONE()
```

**COROLLARY:** "customers who bought X" is a filter on a nested array, not a
correlated subquery. There is no `IN (SELECT ...)` because there is no subquery.

## 6. Numeric types: the trap that looks like pedantry

**ASSERT:** numerics have two **categories** (exact, approximate) and
three **families** (integer, decimal under exact; floating under
approximate). Producing operators mix within a category. Keep-forms
(`COALESCE`, `IF`, `CASE`, `ARRAY`) mix within a family only. A
producing operator may mix an exact **literal** with approximate
(`1.5 + 1e0`). Naming or computing the exact value removes that
exception — `CAST` or stay in one category.

**WRONG INSTINCT:** writing `0e0` / `10e0` out of float habit → forces DOUBLE →
every comparison and COALESCE then needs casts → "this language is too strict".

```featureql
-- WRONG (self-inflicted): opts into the non-coercing type
SLA := 10e0, AT_RISK := COALESCE(AVG_DAYS, 0e0) > SLA   -- cast errors follow

-- RIGHT: DECIMAL is the money type and just works
SLA := 10.0,  AT_RISK := COALESCE(AVG_DAYS, 0.0) > SLA
```

**PROVE IT:**
```featureql
RETURN TYPEOF(1.25),        -- DECIMAL(3,2)
       TYPEOF(1.25 + 10),   -- DECIMAL(5,2)    <- integer literal sized by its digits
       1.25 > 10,           -- FALSE           <- comparison fine
       TYPEOF(1e0)          -- DOUBLE          <- 1.5 + 1e0 is legal; RATE := 1.5; RATE + 1e0 is not
```

**WHY strict at all:** implicit widening differs per backend. BIGINT→DOUBLE in
DuckDB may truncate in BigQuery. Explicit casts are what make "same answer
everywhere" true rather than aspirational.

## 7. One definition, two execution paths

**ASSERT:** the same persisted feature compiles for batch and for serving.
Different SQL, identical semantics and output types.

**PROVE IT — diff the two:**
```sql
-- backend=duckdb  → {...} structs, date_diff, BatchClient
-- backend=serving → named_struct(), extract_field(), date_part, ServingClient
-- Same feature. Same result. Same output type, down to DECIMAL(3,1).
```

**HARD INVARIANT — serving is O(keys fetched), not O(table scanned):**
```featureql
CREATE FEATURE X_PS AS PREPARED_STATEMENT(FEATURE_WITH_TABLE_AGGREGATION USING ID);
-- UE/RELATED-UNBOUND-INPUT
```
This is enforcement, not a limitation. A `GROUP BY` over a fact table per
request is a self-DDoS at 20k QPS. It fails at registration, not at 3am.

| Path | Shape | Sources |
|---|---|---|
| Batch | aggregate over tables | `EXTERNAL_COLUMNS FROM TABLE(...)` |
| Serving | look up by key | `EXTERNAL_REDIS`, `EXTERNAL_HTTP`, JDBC `VIEW(...)` |

What travels between them is the **business meaning** (one named
definition of `AT_RISK`), not the access path. Precompute in batch,
materialize to KV, look up at serving.

## 8. Cross-backend gaps are refused, never silently rewritten

**ASSERT:** where backends diverge **in capability**, you get a compile-time error naming the gap.

```sql
-- backend=trino    → UE/FUNCTION-NOT-AVAILABLE: RECURSE() requires DUCKDB
-- backend=bigquery → UE/AGGREGATE-NOT-SUPPORTED-DIALECT (e.g. MEDIAN())
```

**Read the promise precisely:** identical *definitions* everywhere; identical
*behavior* wherever backends agree; a **named refusal** where they do not.
Never a silent **cross-backend** difference. Authoring contracts (declared
grain, duplicate `BIND TO` keys) can still be silent — see section 12.

## 9. Composition is the default workflow

**ASSERT:** you assemble from named features; you rarely write from scratch.

```featureql
-- Health defined once, in the registry
CUSTOMER_AT_RISK := HAS_OPEN_TICKET OR EXCEEDS_SLA

-- Reused at a different grain, by name, never restated
SUB_CUSTOMER_IS_HEALTHY := NOT RELATED(CUSTOMER_AT_RISK VIA SUB_CUSTOMER_ID)

-- Reused as an API, same definition
CREATE FEATURE HEALTH_PS AS PREPARED_STATEMENT(HEALTH_STATUS USING CUSTOMER_ID)

-- Alternative tested without touching the original
VARIANT(CUSTOMER_AT_RISK REPLACING PAYMENT_SLA_DAYS WITH 30.0)
```

**COROLLARY:** `VARIANT` forks the dependency subgraph. Siblings sharing a
dependency keep the original. Identity = name + hash of full dependency tree.

**COROLLARY:** answers get *shorter* as you go. If your third query is as long
as your first, you are writing SQL in FeatureQL syntax.

## 10. Discover, do not guess

```featureql
SHOW FUNCTIONS (INCLUDE (SIGNATURES)) WHERE NAME = 'RELATED';
SHOW FEATURES   WHERE NAME LIKE 'fm.myns.%';
SHOW FEATURES   WHERE IS_DOWNSTREAM_OF(CUSTOMER_ID);
SHOW CREATE FEATURES FM.MYNS.TOTAL_REVENUE;
SHOW TESTS WHERE CONTENT LIKE '%EXTEND%' LIMIT 20;
SHOW DOCS WHERE NAME LIKE 'llms/%' AND TAGS[1] IN ('mental_model', 'getting_started');
```
The dependency graph is queryable. Registry membership is the API surface:
features in `PERSIST` are addressable; features in `DEFINE` are auditable only.

## 11. FOR is the universe, not a filter

**ASSERT:** `FOR` names every key the graph may read. `WHERE` and `NESTED` restrict
what you return. A short walk from a bound spine is legal; FeatureQL warns.

**WRONG INSTINCT:** "`FOR cat_id := 4` then `RECURSE` will walk the rest of the tree."
(SQL `WHERE id = 4` after `FROM categories`.)

**RIGHT:** bind the full node set (and `NESTED` edges). Filter output with `WHERE`.

```featureql
-- Binding only the selected id scopes the walk to that key
-- Warning: RECURSE-TRAVERSAL-SCOPED-BY-OUTPUT
ROW(cat_id AS start).RECURSE(
    RETURN step[category_id] AS id
    VIA start BIND TO category_id
    FOLLOW parent_category_id
)
FOR cat_id := BIND_VALUES(ARRAY(4))
```

More: [`recurse_pattern`](/llms/recurse_pattern.md), [`for_clause`](/llms/missing_binding.md).

## 12. Grain is declared, not enforced

**ASSERT:** `BIND TO` declares the lookup key. Two source rows per key **fan out**.
There is no implicit primary-key check.

**WRONG INSTINCT:** "The compiler will reject duplicate keys like a warehouse PK."

**RIGHT:** one row per bound key is an authoring contract. Duplicate keys silently
multiply related values. See [`entity_model`](/llms/entity_model.md).

## 13. RELATED COUNT of no children is 0

**ASSERT:** `COUNT()` on an unmatched parent is `0`. A group whose `FILTER`
matches nothing is also `0`. Other aggregates (`SUM`, `MAX`, `MIN`) are `NULL`
for that empty match.

**WRONG INSTINCT:** "An unmatched parent makes `COUNT()` `NULL`, so `n = 0` is unknown."

**RIGHT:** `COUNT() = 0` is the empty match. Use `COUNT() > 0` for exists.

More: [`related_patterns`](/llms/related_aggregation.md).

## 14. Definition GROUP BY is fixed

**ASSERT:** write `GROUP BY (store)` — parentheses are the canonical form even
for one key. A bare `GROUP BY store` is permissive; `FORMAT (FIX)` and
`/* STRICT */` require the parens (`USE-PARENS-GROUP-BY`). Definition grain is `store`.
A `RETURN` that writes `GROUP BY` must project those keys
(`UE/STATIC-ANALYSIS-GROUP-BY-KEYS-NOT-RETURNED` — the silent `ANY_VALUE`
hole). Define the aggregate in `DEFINE` if you want to select only the measure.
A selected sibling must be determined by those keys (`UPPER(store)` is fine).
Independent `region` raises `UE/STATIC-ANALYSIS-RETURN-FINER-THAN-GROUP-BY`.
Finer aggregation is explicit `GROUP BY (store, region)`. Broadcast is
`OVER (PARTITION BY store)` or global `OVER ()`. A single `GROUP BY (src[field])`
stays inline. Two or more `src[field]` keys must be hoisted into named `DEFINE`
features first (`UE/GROUP-BY-ROW-FIELDS-REQUIRE-HOIST`).

**WRONG INSTINCT:** "Selecting `region` next to a store aggregate silently
widens the group." Or "omit `store` from SELECT and SQL will pick one."

**RIGHT:** project the grouping keys, or keep the aggregate in `DEFINE`. Prove
the sibling is determined by those keys, or write the finer `GROUP BY` /
`OVER` explicitly.

More: [`window_functions`](/llms/window_functions.md), help('aggregates').

## 15. VARIANT replaces dependencies, not the base

**ASSERT:** each `REPLACING` origin must be in the base feature's graph.
Replacing the base with itself is an error (`UE/VARIANT-ORIGIN-NOT-A-DEPENDENCY`).

**WRONG INSTINCT:** "`VARIANT(F REPLACING F WITH G)` swaps the formula." (silent no-op in older docs)

**RIGHT:** `ALIAS := F` then `VARIANT(ALIAS REPLACING F WITH G)`.

More: [`variant`](/llms/variant.md).

## 16. ACROSS survives `*`; do not invent a discharge

**ASSERT:** `stock NOT ADDITIVE ACROSS (day) * price` stays non-additive on `day`.
`DETERMINED BY` is not discharged by `*`. Plain `NOT ADDITIVE` through `*` is
unchanged (intensive × extensive is not a lattice special case).

**WRONG INSTINCT:** "Multiplying a snapshot by a price makes it summable."

**RIGHT:** use `ACROSS` for snapshots. `UNSAFE SUM` is the escape, not a rewrite
of the type.

More: [`metric_additivity`](/llms/metric_additivity.md), [`semantic_modeling`](/llms/semantic_modeling.md).

## Self-check before writing

| If you are thinking… | Stop. Instead… |
|---|---|
| "which tables do I join" | which entity am I asking about |
| "I'll filter in the source" | filter is a boolean feature |
| "`FOR` is WHERE / I'll bind only the output id" | bind the universe; `WHERE` / `NESTED` restrict |
| "`WHERE shipdate` then `SUM` `GROUP BY` flag" | `SUM(…) FILTER (WHERE shipdate …)` on **each** measure; query `WHERE` stays at RETURN grain |
| "duplicate keys will error" | grain is declared; duplicates fan out |
| "`COUNT` of no children is NULL" | unmatched RELATED `COUNT()` is 0; other aggregates stay NULL |
| "`GROUP BY store` plus finer RETURN columns" | rejected unless determined; use `GROUP BY (store, region)` or `OVER` |
| "`RETURN SUM(x) GROUP BY (k)` without `k`" | project `k`, or define the aggregate in `DEFINE` |
| "I need a subquery" | nested array + `TRANSFORM` |
| "I'll compute this again here" | it is probably already a named feature |
| "I'll write `0e0`" | write `0.0` |
| "`SELECT E`" / bare `PI` | write `E()`, `PI()`, `EMPTY()` |
| "`COALESCE(cust_id, order_id)`" | mixed `#E` is rejected; erase or same entity |
| "`VARIANT(F REPLACING F WITH …)`" | replace a dependency; alias the base |
| "I'll aggregate a table in serving" | precompute in batch, look up by key |
| "the join key is just an int" | it is a distinct type; the compiler will tell you |
| "`NULL = NULL` is true" / "sort uses `<`" | three-valued `=`; use `DISPLAYS AS`; see [`null_handling`](/llms/null_handling.md) |

Next: [`getting_started`](/llms/getting_started.md) for syntax · [`getting_started_more`](/llms/getting_started_more.md) for
modeling · [`query_methodology`](/llms/query_methodology.md) for approaching a real problem.

**Related tags**: [`getting_started`](/llms/getting_started.md) [`mistakes_index`](/llms/mistakes_index.md) [`entity_model`](/llms/entity_model.md) [`entity_mental_model`](/llms/entity_mental_model.md) [`related_aggregation`](/llms/related_aggregation.md) [`recurse_pattern`](/llms/recurse_pattern.md) [`variant_substitution`](/llms/variant_substitution.md) [`metric_additivity`](/llms/metric_additivity.md) [`transform_scope`](/llms/transform_scope.md) [`where_vs_filter`](/llms/where_vs_filter.md)