# Composite Keys
source: https://docs.chalk.ai/docs/composite-keys

## Model composite identifiers and joins in Chalk, including filter pushdown, SQL resolver outputs, and materialized aggregations.

A composite key identifies a row or entity using more than one source value, such as
workspace_id and user_id. In Chalk, you can model that relationship in two ways:

- join on the individual component features, or
- compute a single scalar feature that contains the composite key and join on that feature.

Both patterns are valid. The important rule is that Chalk joins and filters on features, not on
the implied parts of a computed string. If WorkspaceMember.id is computed from
workspace_id and user_id, Chalk treats WorkspaceMember.id as its own feature. Chalk does
not automatically split that value back into workspace_id and user_id later.

This matters for both correctness and performance. The query must have the features needed to make
the join possible, and large offline queries must also have join keys that can be pushed down safely
so the warehouse can prune the data it scans.

### Component Joins

Use a component join when both sides naturally expose the same component columns.
This usually gives warehouses the clearest filter predicates because each component
can be pushed down independently.

```
from chalk import DataFrame
from chalk.features import features, has_many

@features
class ActivityEvent:
    id: str
    workspace_id: str
    user_id: str
    amount: float

@features
class WorkspaceMember:
    id: str
    workspace_id: str
    user_id: str
    activity_events: DataFrame[ActivityEvent] = has_many(
        lambda:
            (WorkspaceMember.workspace_id == ActivityEvent.workspace_id)
            & (WorkspaceMember.user_id == ActivityEvent.user_id)
    )
```

If an offline query starts from WorkspaceMember.workspace_id and WorkspaceMember.user_id,
Chalk can push those inputs toward SQL resolvers as filters like:

```
where workspace_id in (...)
  and user_id in (...)
```

This is often the best shape when the underlying table is clustered, partitioned, or indexed
by the component columns.

### Scalar Composite Keys

Use a scalar composite key when the rest of the graph needs a single feature to represent the
identity. This is common when you want the id of a feature class to be derived from multiple
source columns.

```
from chalk import _
import chalk.functions as F
from chalk.features import feature, features

@features
class WorkspaceMember:
    workspace_id: str
    user_id: int
    id: str = feature(
        expression=_.workspace_id + ":" + F.cast(_.user_id, str),
        primary=True,
    )
```

The expression must be deterministic and must produce exactly the same value everywhere the key
is used. When a query provides all of the input features used by the expression, Chalk can
synthesize the primary key from those inputs. For example, if the query provides
WorkspaceMember.workspace_id and WorkspaceMember.user_id, Chalk can compute
WorkspaceMember.id.

All inputs to the expression must be available. If the query provides workspace_id without
user_id, Chalk cannot partially compute the key and will ask for the missing component.

If a child feature class needs to join to this key, define the child-side foreign key as a feature
too:

```
from chalk import _
import chalk.functions as F
from chalk.features import feature, features, has_one

WORKSPACE_MEMBER_ID = _.workspace_id + ":" + F.cast(_.user_id, str)

@features
class WorkspaceMember:
    workspace_id: str
    user_id: int
    id: str = feature(expression=WORKSPACE_MEMBER_ID, primary=True)

@features
class ActivityEvent:
    id: str
    workspace_id: str
    user_id: int
    workspace_member_id: "WorkspaceMember.id" = feature(expression=WORKSPACE_MEMBER_ID)
    workspace_member: WorkspaceMember = has_one(
        lambda: _.workspace_member_id == WorkspaceMember.id
    )
```

The string annotation "WorkspaceMember.id" declares that workspace_member_id is a foreign key
to WorkspaceMember.id. It does not compute the value. The value computation comes from
feature(expression=...).

### SQL Resolver Outputs

SQL resolvers must output the features that Chalk needs to join or filter on. This is the most
common source of confusion with composite keys.

Suppose your relationship joins on _.workspace_member_id == WorkspaceMember.id.
If the resolver for ActivityEvent only emits workspace_id and
user_id, the planner does not have a workspace_member_id column to attach the join or filter to.
In an offline query, that can force Chalk to unload or scan far more of the table than expected.
Even if the resolver SQL computes a composite value internally, Chalk can only push filters to
features that are present in the resolver output and used by the relationship.

For a scalar composite key join, emit the scalar key:

```
-- source: snowflake
-- resolves: ActivityEvent
select
  id,
  workspace_id,
  user_id,
  workspace_id || ':' || cast(user_id as string) as workspace_member_id,
  amount
from activity_events
```

For a component join, emit the component columns:

```
-- source: snowflake
-- resolves: ActivityEvent
select
  id,
  workspace_id,
  user_id,
  amount
from activity_events
```

If a query spine provides only workspace_member.id, but the resolver exposes only
workspace_id and user_id, Chalk cannot infer the component filters from the scalar id.
Either provide workspace_id and user_id directly as inputs, or emit the scalar composite
key as a resolver output and join on that feature.

There is one useful exception for root resolvers that return rows for a feature class whose primary
key has an underscore expression. If the resolver returns every component needed by the primary-key
expression, Chalk can synthesize the primary key from those columns. If the primary key does not have
an underscore expression, the resolver must output the primary key itself.

### Materialized Aggregations

For materialized aggregations over a has_many, Chalk keys the materialized values by the join
features used by the relationship. When an aggregation is reached through a has_one prefix,
the has_one join keys and the has_many local keys must overlap.

This can matter in two-hop graphs:

```
from chalk import DataFrame, Windowed, _, windowed
import chalk.functions as F
from datetime import datetime
from chalk.features import feature, features, has_many, has_one

WORKSPACE_MEMBER_ID = _.workspace_id + ":" + F.cast(_.user_id, str)

@features
class ActivityEvent:
    id: str
    workspace_id: str
    user_id: int
    workspace_member_id: "WorkspaceMember.id" = feature(expression=WORKSPACE_MEMBER_ID)
    occurred_at: datetime
    amount: float

@features
class WorkspaceMember:
    workspace_id: str
    user_id: int
    id: str = feature(expression=WORKSPACE_MEMBER_ID, primary=True)
    activity_events: DataFrame[ActivityEvent] = has_many(
        lambda: _.workspace_member_id == WorkspaceMember.id
    )

@features
class MemberRiskFeatures:
    id: str
    workspace_member_id: "WorkspaceMember.id"
    workspace_member: WorkspaceMember = has_one(
        lambda: _.workspace_member_id == WorkspaceMember.id
    )
    event_amount: Windowed[float] = windowed(
        "30d",
        materialization={"bucket_duration": "1d"},
        expression=_.workspace_member.activity_events[
            _.amount,
            _.occurred_at > _.chalk_window,
            _.occurred_at <= _.chalk_now,
        ].sum(),
    )
```

Here, both relationships use WorkspaceMember.id as the key. That lets the materialization
planner line up the has_one prefix with the has_many aggregation. If the has_one used
WorkspaceMember.id but the has_many joined on (workspace_id, user_id), the planner may reject
the materialization because the join keys do not overlap.

### Best Practices

Prefer a source-native stable identifier when one exists. Use a computed composite key only when
the source system does not already provide a durable id.

Use component joins when the warehouse can prune efficiently on the component columns. This is
especially useful for offline queries over large tables.

Use scalar composite keys when the entity identity needs to travel through the graph as one feature,
or when a materialized two-hop aggregation needs overlapping join keys.

Keep the composite key expression in one place. Define a shared expression constant and reuse it
for the parent primary key and any child foreign keys.

Choose an encoding that cannot collide. If you concatenate strings, use a delimiter that cannot
appear in the component values, escape the components, or use a stable hash of a structured value.
Make casts explicit so every resolver and expression produces the same string.

Emit every join key from SQL resolvers. If a relationship joins on a scalar composite key, the
resolver should output that scalar key. If a relationship joins on components, the resolver should
output the components.

For root resolvers, either output the primary key or make sure the primary key has an underscore
expression and the resolver outputs all of the expression inputs.

Treat persisted composite keys as stable cache keys. Persisted results can be read back by the
computed key, but changing the key expression changes the identity of cached rows.

Test with a small offline query and inspect the query plan or generated SQL. You should see filters
on the columns used by the relationship, not a full-table unload followed by a join in Chalk. Check
both that the query can run and that the plan is efficient: a valid composite-key model can still be
too expensive if the relevant filters are not pushed down.

### Common Pitfalls

Assuming Chalk can split a composite id. If an input spine contains values like
workspace-7:123, Chalk does not know that workspace-7 is workspace_id and 123 is user_id.
Provide the component inputs or model and emit the scalar key directly.

Providing only some key components. If WorkspaceMember.id is computed from workspace_id and
user_id, Chalk needs both components to synthesize the id. Supplying only one component is not
enough.

Using a foreign-key annotation as a computation. A type like
workspace_member_id: "WorkspaceMember.id" declares the relationship target. It does not populate
workspace_member_id. Use feature(expression=...), a Python resolver, a SQL resolver column,
or uploaded data to provide the value.

Expecting a resolver to omit an uncomputable primary key. A DataFrame or SQL resolver may omit a
computed primary key only when Chalk has an underscore expression and the resolver outputs all of
that expression's inputs. Otherwise, the resolver must output the primary key feature.

Joining on a key that the resolver does not output. If the planner needs
activity_event.workspace_member_id, the resolver must return a column for
workspace_member_id. Returning only workspace_id and user_id is not equivalent when the
relationship joins on workspace_member_id.

Mixing scalar and component joins across a two-hop materialization. If the first hop joins
on WorkspaceMember.id but the second hop joins on workspace_id and user_id, the
materialization planner may not be able to prove that the keys line up. Use the same join feature
on both hops, or model the relationship with component joins consistently.

Duplicating the key formula by hand. If the parent key changes but the child key expression
does not, joins silently stop matching. Reuse a shared expression or, for SQL resolvers, keep the
SQL projection in lockstep with the Chalk expression.