Flesh out DuckDBTransformer as a full execution backend

[amc-corey-cox]

Context

DuckDBTransformer currently has:

• map_database() — works, but delegates everything to SQLCompiler which supports a limited subset
• map_object() — raises NotImplementedError

As the SQLCompiler grows (see #150), DuckDBTransformer should become a viable alternative
backend for executing transformations — particularly for large tabular datasets where set-based
SQL operations would be significantly faster than Python row-by-row interpretation.

Proposed work

1. Wire up expanded SQLCompiler output — as Expand SQLCompiler to cover more of the TransformationSpecification #150 adds features, ensure map_database() exercises them correctly
2. Consider map_object() semantics — does it make sense to support single-object transforms via DuckDB, or should this backend only operate at the database/table level? If database-only, make that explicit in the API rather than leaving a NotImplementedError.
3. CLI integration — evaluate whether map-data should support a --backend duckdb flag for cases where input is a DuckDB database or large tabular dataset. This is a future consideration, not immediate work.
4. Backend parity tracking — document which TransformationSpecification features are supported by which backend (Python vs SQL), possibly as a matrix in the docs or compliance suite.

Design notes

The SQL backend is fundamentally set-based (operates on tables) while the Python backend is
row-based (operates on individual objects). These are complementary rather than competing — the
right backend depends on the data source and scale. The TransformationSpecification should remain
the single source of truth regardless of backend.

Related

• Expand SQLCompiler to cover more of the TransformationSpecification #150 (SQLCompiler expansion)
• src/linkml_map/transformer/duckdb_transformer.py
• src/linkml_map/compiler/sql_compiler.py

[github-actions]

Summary and Context

This issue tracks making DuckDBTransformer a first-class execution backend, complementing the existing Python ObjectTransformer. It is closely related to the #150/#151/#152 cluster opened 2026-03-10.

---

Current State of the Code

DuckDBTransformer (src/linkml_map/transformer/duckdb_transformer.py, 77 lines):

• map_database() — works, but is a thin wrapper: it calls SQLCompiler.create_ddl() + SQLCompiler.compile() and executes the result against a DuckDB connection. No logic lives in the transformer itself.
• map_object() — raises NotImplementedError with message "DuckDBTransformer.transform".

SQLCompiler (src/linkml_map/compiler/sql_compiler.py, 140 lines):

• Compiles ClassDerivation → INSERT INTO … SELECT statements.
• compile_slot_derivation handles only two cases: bare populated_from column rename, and basic stringification (JSON cast or STRING_AGG with delimiter).
• create_ddl() generates CREATE TABLE IF NOT EXISTS for every concrete class with DuckDB-aware types (arrays, JSON for inlined objects).
• Silently falls back to sd.name when populated_from is absent — no error for unsupported spec features.

Tests:

• tests/test_compiler/test_duckdb_compiler.py — exercises compile() + create_ddl() end-to-end against an in-memory DuckDB. Passes today.
• tests/test_transformer/test_duckdb_transformer.py — contains a single test test_compile that calls pytest.skip("TODO") and never runs.

---

Relationship to Sibling Issues

Issue	Title	Status
#150	Expand SQLCompiler to cover more of TransformationSpecification	Open — defines the incremental work items that unblock this issue
#151	Flesh out DuckDBTransformer as a full execution backend	Open — this issue
#152	Document the TransformationSpecification-as-IR architectural vision	Open — documentation side; some docs already written, backend parity matrix still TODO

This issue is downstream of #150: each new capability added to SQLCompiler (#150) should be wired through and exercised by DuckDBTransformer (#151), and then documented in the parity matrix (#152).

---

Key Design Tensions to Resolve

1. map_object() semantics — DuckDB is fundamentally set-based. Options:

   • Implement map_object() by inserting the single object into an in-memory table, running the SQL transform, and returning the result row. This preserves API symmetry but adds overhead per object.
   • Remove NotImplementedError and replace with a clear UnsupportedOperationError (or similar) with a message explaining that this backend only operates at the table/database level. This is the cleaner design signal.
   • Make it explicit in the class docstring/API that DuckDBTransformer is a bulk transformer.

2. Error behavior in SQLCompiler — the current compile_slot_derivation silently falls back when it doesn't understand a spec feature. Issue Expand SQLCompiler to cover more of the TransformationSpecification #150 proposes raising a clear error instead. This is a prerequisite for Flesh out DuckDBTransformer as a full execution backend #151 being trustworthy.

3. CLI integration — --backend duckdb flag on map-data is mentioned as a future concern, not immediate.

---

Suggested Incremental Path

1. Expand SQLCompiler to cover more of the TransformationSpecification #150 first: Add expression compilation, enum→CASE, join support, copy directives to SQLCompiler — each as a separate PR with tests.
2. Un-skip test_compile in test_duckdb_transformer.py and make it actually validate output, not just execute without assertion.
3. Resolve map_object() design question — document the decision in the class docstring and make the error message actionable.
4. Backend parity matrix — once SQLCompiler covers a meaningful subset, add a compliance table (perhaps in docs/) listing feature → Python ✅ / SQL ✅/⚠️/❌.

---

Notes

The TransformationSpecification as a backend-agnostic IR is the right architectural framing (captured in #152). The SQL backend is a performance-oriented alternative for bulk tabular data, not a replacement for the Python interpreter. Keeping that framing clear in the API surface (class names, docstrings, error messages) will help users choose the right backend.