Expand SQLCompiler to cover more of the TransformationSpecification

[amc-corey-cox]

Context

The SQLCompiler currently supports a limited subset of the TransformationSpecification:

• Simple populated_from slot derivations
• Basic stringification (JSON cast, delimiter-based aggregation)

The TransformationSpecification is intended to be a declarative IR that can be executed by
different backends. The Python ObjectTransformer supports the full spec; the SQL backend
should grow toward parity over time.

Proposed work

Incrementally expand SQLCompiler to support more spec features, prioritized by utility:

1. Expression compilation — compile Python expressions (expr field) to equivalent SQL where possible (arithmetic, string concatenation, simple conditionals)
2. Enum derivations — compile EnumDerivation / PermissibleValueDerivation to SQL CASE statements
3. Join support — cross-class slot lookups (populated_from: org_id.name) as SQL JOINs
4. Unit conversion — compile unit_conversion to SQL arithmetic where conversion factors are known
5. Copy directives — copy_all / exclude as SELECT * EXCEPT patterns (DuckDB supports this)

Each of these can be a separate PR. The compiler should raise a clear error when it encounters
a spec feature it cannot compile, rather than silently producing incorrect SQL.

Non-goals

This is about expanding what the SQL compiler can express, not about adding new output formats
or changing the streaming/serialization layer.

Related

• src/linkml_map/compiler/sql_compiler.py
• src/linkml_map/transformer/duckdb_transformer.py
• SQL Compilation tutorial notebook: docs/examples/Tutorial-SQLCompiler.ipynb

[github-actions]

Issue Summary

Issue #150 is a feature roadmap for expanding the SQLCompiler to approach parity with the Python ObjectTransformer backend.

---

Current State of SQLCompiler

Looking at src/linkml_map/compiler/sql_compiler.py, the compiler is quite minimal (~140 lines). It currently handles:

• populated_from — maps source slot name to target column (sd.name AS sd.populated_from)
• stringification — two cases:
  • JSON syntax → CAST(expr AS TEXT)
  • Delimiter-based → STRING_AGG(expr, 'delim')

The compile_slot_derivation method (sql_compiler.py:72) only reads sd.populated_from and sd.stringification; all other SlotDerivation fields are silently ignored.

The DuckDBTransformer (duckdb_transformer.py) has:

• map_database() — functional but limited by what SQLCompiler can express
• map_object() — raises NotImplementedError unconditionally

---

Proposed Expansion (5 areas, each a potential separate PR)

Priority	Feature	TransformationSpec field	SQL translation
1	Expression compilation	SlotDerivation.expr	Arithmetic, string concat, simple conditionals → SQL expressions
2	Enum derivations	EnumDerivation / PermissibleValueDerivation	CASE WHEN ... THEN ... END
3	Join support	populated_from: other_class.slot (dotted path)	SQL JOIN clauses
4	Unit conversion	SlotDerivation.unit_conversion	SQL arithmetic with known conversion factors
5	Copy directives	ClassDerivation.copy_directives (copy_all/exclude)	DuckDB SELECT * EXCLUDE (col)

A key design principle noted: the compiler should raise a clear error for unsupported features rather than silently omitting them (currently it silently drops most fields).

---

Related Issues

• Flesh out DuckDBTransformer as a full execution backend #151 — Flesh out DuckDBTransformer as a full execution backend — sister issue; depends on Expand SQLCompiler to cover more of the TransformationSpecification #150's progress. Proposes --backend duckdb CLI flag and a backend parity matrix in docs.
• Document the TransformationSpecification-as-IR architectural vision #152 — Document the TransformationSpecification-as-IR architectural vision — frames the broader architecture: TransformationSpecification is the declarative IR, Python and SQL are two execution backends. Some doc updates already landed (index, README, FAQ).
• Delete or test untested compilers #126 — Delete or test untested compilers — SQLCompiler was listed as untested (no tests at the time); this issue's work would be accompanied by tests and effectively resolves the SQL row in that table.

---

Architectural Notes

The TransformationSpecification is the central IR. Looking at SlotDerivation in the datamodel, the full set of fields the SQL compiler currently ignores includes: expr, value (constants), unit_conversion, inverse_of, cast_collection_as, dictionary_key, aggregation_operation, pivot_operation, offset, value_mappings, expression_to_value_mappings, object_derivations, and copy_directives. This is a significant gap relative to what ObjectTransformer handles.

The SQL backend is inherently set-based (tables) vs Python's row-based approach — these are complementary, not competing. DuckDB is particularly well-suited for this because it supports complex types natively (JSON columns, arrays, SELECT * EXCLUDE), which aligns well with LinkML's rich type system.

---

Suggested Implementation Order

Given the current codebase, a practical first PR would be:

1. Add a mechanism to detect and raise errors for unsupported spec features (guards in compile_slot_derivation)
2. Then tackle expr compilation — highest utility, many real-world specs use expressions
3. Enum → CASE WHEN translation is well-bounded and testable
4. Join support is the most complex (requires understanding AliasedClass / ClassDerivation.joins)

The tutorial notebook at docs/examples/Tutorial-SQLCompiler.ipynb is the natural place to demonstrate new capabilities as they land.