Universal ops compiler — 451 lines, 5 database backends, capability matrix with automatic pandas fallback
The SQL Execution Engine is a 451-line universal operations compiler at the heart of the recipe system. It translates recipe stages — filter, join, aggregate, pivot, impute — into executable SQL for the target database backend. The compiler understands the capabilities and limitations of each backend and chooses the optimal execution strategy.
| Backend | Status | Notes |
|---|---|---|
| DuckDB | Full support | Primary analytical engine. Supports the widest range of operations. Used for uploaded file analysis and virtual datasets. |
| PostgreSQL | Full support | Native SQL pushdown for all supported operations. Rich function library. |
| MySQL / MariaDB | Full support | Native SQL with dialect-specific adaptations. Square bracket identifiers, SELECT TOP, BIT for boolean. |
| SQL Server | Full support | Native T-SQL pushdown. Feature-gated behind mssql_db licence flag. |
| MS Access (Jet/ACE) | Full support | SQL via the MS Access Endpoint. Limited operations — see capability matrix for fallback behaviour. |
Not every backend supports every operation natively. The engine maintains a capability matrix:
| Operation | DuckDB | PostgreSQL | MySQL | MSSQL | Access |
|---|---|---|---|---|---|
| Filter (WHERE) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Sort (ORDER BY) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Aggregate (GROUP BY) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Join (INNER/LEFT/RIGHT) | ✓ | ✓ | ✓ | ✓ | ✓ |
| Window Functions | ✓ | ✓ | ✓ | ✓ | pandas |
| Pivot | ✓ | ✓ (crosstab) | pandas | ✓ (PIVOT) | pandas |
| Impute (fill nulls) | ✓ | ✓ | ✓ | ✓ | pandas |
| Statistical Functions | ✓ | ✓ | Partial | Partial | pandas |
| CAST / Type Conversion | ✓ | ✓ | ✓ | ✓ | Partial |
| String Aggregation | ✓ | ✓ (string_agg) | ✓ (GROUP_CONCAT) | ✓ (STRING_AGG) | pandas |
The engine uses a partial pushdown strategy. Operations that the backend supports natively are compiled to SQL and executed on the database server — minimising data transfer. Operations that the backend cannot handle are executed in pandas after retrieving the partial results.
This means even MS Access — the most limited backend — can participate in complex recipes. The engine pushes down what Jet can handle (basic SELECT, JOIN, WHERE, GROUP BY) and handles the rest in pandas transparently.