Ibis

Builds an abstract syntax tree (AST) of dataframe operations without executing them, using Ibis's core expression system (ibis/expr/operations and ibis/expr/types) to represent table selections, projections, filters, and aggregations as composable symbolic objects. Expressions are constructed through method chaining on Table and Column types, with each operation creating a new immutable expression node that references its inputs, enabling deferred execution and optimization before compilation to backend-specific code.

Translates Ibis expression trees into backend-specific SQL dialects using SQLGlot as the compilation engine (ibis/backends/sql/compiler.py integration). Each backend registers its own SQL compiler that walks the expression DAG, applies backend-specific type mappings (via ibis/expr/operations type registry), and generates optimized SQL strings. The compilation layer handles dialect differences (e.g., window function syntax, string functions, date arithmetic) transparently, allowing a single Ibis expression to produce valid SQL for DuckDB, PostgreSQL, BigQuery, Snowflake, Spark SQL, and 15+ other engines.

Applies automated query optimization using an e-graph (equality graph) data structure (ibis/common/egraph.py) that represents equivalent expressions and enables rewriting rules to find more efficient query plans. The optimizer applies algebraic transformations (e.g., pushing filters down before joins, eliminating redundant projections, constant folding) to the expression DAG before compilation. Rewriting rules are defined declaratively and applied iteratively until a fixed point is reached, with cost-based selection to choose the most efficient equivalent expression.

Provides a unified testing framework (ibis/backends/tests/) that runs the same test suite against all 20+ backends using Docker containers for database services. Tests are organized by feature (SQL, aggregation, window functions, etc.) and automatically skipped for backends that don't support a feature. The test infrastructure includes base test classes (e.g., BackendTestBase) that define test methods, and backend-specific test classes that override methods for backend-specific behavior. Docker Compose is used to spin up database services (PostgreSQL, MySQL, BigQuery emulator, etc.) for testing.

Supports loading data incrementally from files (Parquet, CSV, JSON), databases (via SQL), and cloud storage (S3, GCS, Azure Blob) using backend-specific readers that stream data without loading it all into memory. Ibis abstracts the loading logic behind a unified API (ibis.read_parquet(), ibis.read_csv(), ibis.read_sql()) that returns a Table expression. For backends that support it (e.g., DuckDB), data is read lazily and only materialized when .execute() is called. For backends that don't support lazy reading, data is materialized locally and pushed to the backend.

Caches expression objects to enable efficient reuse of intermediate results without recomputation. When the same expression is used multiple times in a query (e.g., a filtered table used in two different aggregations), Ibis detects the duplication and generates SQL that computes the expression once and reuses it (via CTEs or subqueries). The caching system uses expression hashing and structural equality to detect duplicates, and is transparent to the user — no explicit caching API is required.

Implements string operations (substring, length, upper, lower, replace, split, concatenate, regex matching) that compile to backend-specific string function syntax. The system abstracts over differences in string function names and behavior across backends (e.g., SUBSTR vs SUBSTRING, regex syntax differences), providing a unified API for text manipulation.

Supports operations on complex types (arrays, structs) including element access, flattening, unnesting, and aggregation of nested data. The system compiles array/struct operations to backend-specific syntax (UNNEST in SQL, explode in Spark, LATERAL FLATTEN in Snowflake), handling differences in nested data support across backends.

Provides a unified connection interface (ibis.backends.Backend base class) that abstracts away backend-specific connection logic, authentication, and execution details. Developers call ibis.duckdb.connect(), ibis.bigquery.connect(), or ibis.snowflake.connect() with backend-specific credentials, which returns a Backend instance with a standard API (.sql(), .execute(), .to_pandas()). The Backend class handles query compilation, parameter binding, result fetching, and type conversion, allowing code to switch backends by changing a single line (e.g., from DuckDB to BigQuery) without modifying query logic.

Automatically infers and validates data types for all expressions using Ibis's type system (ibis/expr/types/core.py, ibis/common/typing.py). When a table is created (via .sql(), .memtable(), or backend connection), Ibis introspects the schema and maps backend-specific types (e.g., BigQuery's BIGNUMERIC) to Ibis types (int64, float64, string, timestamp, etc.). All operations (filter, select, join, aggregate) validate that operands have compatible types at expression construction time, catching type errors before execution. Type coercion rules are applied automatically (e.g., int + float → float) and can be customized per backend.

Provides a fluent API for building complex queries through method chaining on Table objects, where each method (select, filter, join, group_by, order_by, limit) returns a new Table expression. Operations are composable and can be chained arbitrarily (e.g., t.filter(...).select(...).join(...).group_by(...).aggregate(...)), with each step creating a new expression node in the DAG. The API mirrors SQL semantics but uses Python idioms (e.g., filter instead of WHERE, select instead of SELECT), making it accessible to Python developers unfamiliar with SQL.

Enables joining tables from different backends (e.g., DuckDB table joined with BigQuery table) by materializing one side locally and performing the join in the target backend, with automatic type alignment and schema reconciliation. Implements set operations (union, intersection, difference) across heterogeneous backends by converting schemas to a common type representation and handling NULL semantics correctly. The join logic (ibis/expr/operations/relations.py) validates that join keys have compatible types and generates backend-specific join SQL with proper type casting.

Provides aggregation operations (sum, mean, count, min, max, etc.) and window functions (row_number, rank, lag, lead, etc.) that compile to backend-specific SQL. Aggregations are applied via .aggregate() or .group_by() methods, which generate GROUP BY clauses with proper type handling for aggregate functions. Window functions are constructed via .over() method, specifying partition and order clauses, and compile to OVER (PARTITION BY ... ORDER BY ...) syntax. The implementation handles edge cases like NULL aggregation, empty groups, and frame specifications (ROWS BETWEEN ... AND ...) correctly across backends.

Allows embedding raw SQL strings directly into Ibis expressions via ibis.sql() function, enabling developers to use backend-specific SQL features (e.g., BigQuery ML, Snowflake stored procedures) that aren't exposed through the Ibis API. SQL fragments are parsed and type-annotated, then composed with other Ibis operations in the expression DAG. The system validates that SQL fragments produce tables/columns with compatible schemas and types, and compiles them into the final backend-specific query without modification.

Defers execution until explicitly requested via .execute(), .to_pandas(), .to_pyarrow(), or .to_csv() methods, allowing developers to build complex queries without triggering computation. When materialization is requested, the expression DAG is compiled to backend-specific SQL, executed on the backend, and results are fetched and converted to the requested format (Pandas DataFrame, PyArrow Table, CSV file, etc.). The system handles result streaming for large datasets, type conversion between backend types and Python types, and NULL value representation correctly.

Maintains a registry of backend-specific type mappings (e.g., BigQuery NUMERIC → Ibis decimal128) and operation implementations (e.g., string functions, date arithmetic) that vary across backends. Each backend registers its type mapper (ibis/backends/*/datatypes.py) and operation compiler (ibis/backends/*/compiler.py) that define how Ibis types and operations map to backend-specific SQL. When an operation is not supported by a backend, the registry falls back to Python evaluation or raises NotImplementedError, allowing graceful degradation or explicit error messages.