Add ibis executors to circepy

[egillax]

I think there are multiple ways this can be done.

Optional extra:
Add code in 'circe/ibis' or 'circe/engine/ibis' or 'circe/execution/ibis'. Users would install with pip install ohdsi-circepy[ibis], for a specific backend would be ohdsi-circepy[postgres]. ohdsi-circepy[databricks] or whatever. Core package would have no hard dependency on ibis.

Separate package:
Something like circepy-ibis, would import circepy models and implements execution using ibis.

Monorepo:
So separate package but in one repo.

I'm leaning towards optional extra, could consider a separate package later.

What do you think ?

[azimov]

the optional extra approach seems to be the most flexible to me. One question is would we want a separate API for the newer backend calls? The current approach basically uses the CirceR format which can remain for the T-SQL hand generated queries (its not very pythonic and pretty clunky)

[egillax]

I'd be in favor of a new (as simple as possible) API.

I’ve prepared a draft Ibis execution API and would appreciate feedback before/with PR review.

API surface (experimental)

Core class:

• IbisExecutor(connection, options: ExecutionOptions | None = None)

Methods on IbisExecutor:

• build(expression) -> ibis.Table (lazy relation, no execution)
• to_pandas(expression) -> pandas.DataFrame
• to_polars(expression) -> polars.DataFrame
• write(expression, cohort_table, cohort_id=..., schema=..., append=..., overwrite=...)

Options:

• ExecutionOptions(...) controls execution behavior (e.g., schema, materialization, SQL capture),
  passed to IbisExecutor.

Module-level convenience wrappers (thin wrappers over the class):

• build_ibis(expression, connection, options=...)
• to_polars(expression, connection, options=...)
• write_cohort(expression, connection, cohort_table, cohort_id=..., ...)

Example

import ibis
from circe import cohort_expression_from_json
from circe.execution import (
    IbisExecutor,
    ExecutionOptions,
    build_ibis,
    to_polars,
    write_cohort,
)

expr = cohort_expression_from_json("my_cohort.json")
con = ibis.duckdb.connect("omop.duckdb")

# 1) Convenience wrapper: lazy ibis relation
rel = build_ibis(expr, con)

# 2) Convenience wrapper: materialize to polars
df = to_polars(expr, con)

# 3) Convenience wrapper: write OHDSI cohort table rows
write_cohort(
    expr,
    con,
    cohort_table="cohort",
    cohort_id=123,
    schema="results",
    overwrite=True,
)

# 4) Class-based usage with explicit options
opts = ExecutionOptions(
    source_schema="main",
    result_schema="results",
    capture_sql=True,
)

with IbisExecutor(con, options=opts) as ex:
    rel = ex.build(expr)
    pdf = ex.to_pandas(expr)
    ex.write(expr, cohort_table="cohort", cohort_id=123, schema="results")

I ported the executor implementation from mitos, simplified it in this package, and layered this API on
top.

Validation so far:

• DuckDB parity checks against CirceR row counts on phenotype-library cohorts
• Run on both my synthetic dataset and local real dataset

I’ll open a PR so the API can be reviewed directly in code.