Support cross-table slot lookup in class_derivation slot_derivations

[amc-corey-cox]

Summary

When a class_derivation has a top-level populated_from: TableA, all slot_derivation blocks within it can only reference columns from TableA. There is currently no way to pull a value from a different source table (e.g., a demographics table) into a slot derivation that lives under a class derivation targeting a different table.

This is a common need in tabular data harmonization where related variables live in separate tables — for example, linking an age_at_observation from a demographics table to measurement observations loaded from a different data table.

Current behavior

populated_from at the slot level resolves within the same source class/table as the parent class_derivation. The existing cross-class support (#101, resolve_fk_path in fk_utils.py) handles dot-notation FK traversal (e.g., org_id.name) but requires a foreign key relationship defined in the schema. It does not support arbitrary cross-table lookups where the join key must be specified in the transformation spec.

Desired behavior

Within a class_derivation block with populated_from: TableA, a slot_derivation should be able to reference columns from TableB — for example:

class_derivations:
  MeasurementObservation:
    populated_from: lab_results
    slot_derivations:
      analyte_value:
        populated_from: result_value
      age_at_observation:
        populated_from: demographics.age_at_exam  # cross-table lookup
        # join key: shared participant_id

The exact syntax and join-key specification mechanism need design work. Options include:

• Extending dot-notation (OtherClass.slot) with an implicit or explicit join key
• A new attribute on slot_derivation specifying the foreign table and join column
• Making cross-table references available in expr: blocks via bindings

Use cases

• Longitudinal study data: Age variables live in a demographics/visit table but need to be attached to measurement observations from separate data tables
• Reference data joins: Coded values in one table need to be enriched with labels from a reference/lookup table
• Multi-table harmonization: Any scenario where the target schema combines attributes that originate from different source tables

Related

• Allow populated_from to lookup from external class #101 — Added dot-notation populated_from: OtherClass.slot for FK-based slot lookup
• src/linkml_map/utils/fk_utils.py — Existing FK resolution infrastructure
• src/linkml_map/transformer/object_transformer.py — Runtime slot derivation logic
• mapping age vars to phv's for yaml writing RTIInternational/NHLBI-BDC-DMC-HV#365 — Downstream use case (age variable pairing)

[github-actions]

Summary / Analysis

What this issue is requesting

This feature request asks for the ability to reference columns from a different source table within a slot_derivation block, when the parent class_derivation is already bound to a specific source table via populated_from.

The canonical use case (from biomedical data harmonization, specifically NHLBI BDC DMC-HV#365) is:

A measurement observation row comes from lab_results but age_at_exam lives in a separate demographics table. Both share a participant_id key that links them.

Current state of the code

The existing FK traversal mechanism (added via PR #101, resolve_fk_path in fk_utils.py) handles dot-notation like org_id.name, but it works by:

1. Looking up org_id as a slot on the source class in the LinkML schema
2. Following its range to find the target class
3. Retrieving the nested attribute from the linked object

This means it requires:

• An FK slot already defined in the source schema
• The FK value to point to an object present in the current source object

It cannot support arbitrary cross-table lookups where the join key exists in the transformation spec rather than the schema, or where the second table is loaded as a separate data source.

In object_transformer.py (around line 280–315), the populated_from branch calls resolve_fk_path, and if that fails (no FK slot in the schema), it falls through to a simple source_obj.get(populated_from, None) — which would fail silently for cross-table references.

Design considerations

Three approaches are proposed (each has tradeoffs):

Option A — Extend dot-notation with an explicit join key:

age_at_observation:
  populated_from: demographics.age_at_exam
  join_key: participant_id   # new field on SlotDerivation

• Minimal YAML surface change; fk_utils.py / the schema model needs a new join_key field on SlotDerivation
• The transformer needs to index the secondary dataset by join key at transform time

Option B — New cross_table_source attribute on SlotDerivation:

age_at_observation:
  cross_table_source:
    table: demographics
    lookup_key: participant_id
    slot: age_at_exam

• More explicit and less ambiguous than overloading dot-notation
• Schema model change required; transformer needs multi-dataset context

Option C — expr: block with bindings:

age_at_observation:
  expr: "lookup('demographics', 'participant_id', src.participant_id, 'age_at_exam')"

• Zero schema model change; uses existing eval_expr_with_mapping path
• Exposes a lookup() function in the expression evaluator that can query secondary datasets
• Most flexible but least declarative; harder to invert/introspect

Runtime requirement (key challenge)

For any of these approaches, the ObjectTransformer must have access to multiple loaded datasets simultaneously at row-transformation time. Currently, transformation is per-object. A lookup into demographics requires indexing that table by the join key before (or during) transformation.

The MultiTableDataLoader in data_loaders.py already understands the multi-table concept at load time, so the data is potentially available — the gap is threading it through to map_object().

A concrete approach: build a Dict[str, Dict[join_key_value, row_dict]] lookup index per secondary table at TransformationEngine setup time, then pass it as context into map_object.

Related issues for context

Issue	Status	Relevance
#101	Closed (merged)	Added FK-based dot-notation populated_from; foundation this issue builds on
#111	Closed (completed)	Added list-based class_derivations for multi-source → same target class
#112	Open	Deprecate object_derivations; clean-up opportunity
#124	Open	Consolidate spec normalization pipeline — any new SlotDerivation fields need to survive normalization

Suggested path forward

The pragmatic path (minimal schema change, maximum expressivity) would be:

1. Add join_key (or a lookup: sub-block) to SlotDerivation in the linkml-map schema
2. In _derive_from_populated_from, detect cross-table dot-notation (where the prefix is a known class/table name rather than an FK slot on the source class)
3. Build a lookup index in the transformer and resolve via the join key
4. Add a test using the scaffold-based pattern in tests/ with a demographics + lab_results fixture pair

Option C (expr-based lookup() function) could be delivered sooner as a stopgap while the declarative design is finalized.

@dragon-ai-agent please implement cross-table slot lookup for slot_derivation blocks. The preferred approach is Option A (extend dot-notation with a join_key field on SlotDerivation) combined with Option C (a lookup() function available in expr: bindings as a more flexible fallback). Use scaffold-based tests with a demographics + lab_results fixture. See issue #134 for full context, and pay attention to issue #124 (normalization pipeline) to ensure new fields survive spec loading.

[amc-corey-cox]

Reopening — additional architectural context from refactor work

PR #136 landed cross-table joins on main. The implementation works but needs revision. Key issues identified during the map_object refactor planning:

1. Join resolution should not be coupled to Bindings

Currently, cross-table lookups only work through expr: derivations because the join resolution lives in Bindings._resolve_join(). Bindings exists solely because simpleeval wants a Mapping — the user's choice of expr: vs populated_from: shouldn't determine whether joins work.

Proposed: Resolve joins before the slot derivation loop in map_object, merging joined data into the available source data as nested objects (e.g., source_data["demographics"] = DynObj(**joined_row)). Then both paths work:

• expr: "{demographics.age_at_exam}" — simpleeval resolves attribute access
• populated_from: demographics.age_at_exam — resolve_fk_path walks the dot path

This also opens the question of whether Bindings should continue to exist as a separate class or be simplified/removed. It currently duplicates cross-object resolution that populated_from does via resolve_fk_path + object_index.

2. Backward compatibility is preserved

Dot notation only triggers for joined aliases. populated_from: simple_slot (no dot) resolves against the primary source class exactly as it does today. No spec changes needed.

3. 1-to-1 joins first, error on multi-match

Current LookupIndex.lookup_row() silently returns the first match on non-unique keys. Should raise NotImplementedError (or similar) when the join key is not unique in the secondary table. 1-to-many joins can be a future enhancement.

4. Memory and scale constraints

Some data sources have ~1M participants. The implementation must not force all secondary tables into memory simultaneously. The current LookupIndex uses DuckDB (good for this), but we need to ensure:

• Secondary tables can be dropped after a class_derivation block finishes (engine.transform_spec already does this via finally: drop())
• The DuckDB connection is cleaned up properly (see PR Add failing tests for LookupIndex context manager protocol (#143) [ON HOLD] #144 — LookupIndex context manager)
• No unnecessary materialization of large result sets

5. Relationship to map_object refactor

The map_object refactor (issue #104) is being coordinated with this work, since the join mechanism directly affects the structure of the slot derivation loop and whether Bindings continues to exist.

Connected PRs

• PR Add cross-table lookup support for join-based transformations #136 — Current implementation (on main, needs revision per above)
• PR Add failing tests for LookupIndex context manager protocol (#143) [ON HOLD] #144 — LookupIndex context manager (draft, builds on Add cross-table lookup support for join-based transformations #136)