How to Use Ibis: The Beginner’s Guide to the Portable Python Dataframe Library

CodexFuse Team · 2026-11-02T13:03:46.654Z · 12 min read min read

Ibis is the portable Python dataframe library: write your dataframe logic once in Python, run it on DuckDB locally and on 22 backends in production. This beginner’s guide covers install, the lazy-expression model, your first expression with real DuckDB output, and one-line backend swaps.

What Is Ibis, Exactly?

Learning how to use Ibis starts with one idea: Ibis is the portable Python dataframe library — a Python dataframe API that executes on any query engine. The project describes itself as "the frontend for any backend data platform, with more than 20 backends today" (ibis-project.org), and its PyPI page puts it plainer: Ibis is a Python library that provides a lightweight, universal interface for data wrangling, helping Python users explore and transform data of any size, stored anywhere.

The mental model that makes Ibis click: you write your dataframe logic once in Python, run it on DuckDB on your laptop, and run the identical code against BigQuery or Snowflake in production. Ibis expressions are lazy — nothing executes until you call .to_pandas() or .execute() — so you build the whole query as a chain of Python calls and Ibis compiles it to the backend's native SQL. If your data work happens in notebooks, our Jupyter beginner guide gets you set up with the environment most Ibis users reach for first. And if you searched "ibis python", you are in the right place: this guide covers the Python dataframe library — not the bird, and not the Tolkien character.

Ibis vs Polars vs Narwhals vs Fugue — and Malloy and PRQL

Ibis is not Polars, and that is the most common mix-up. Polars is a single fast dataframe engine; Ibis is a portable API that can target Polars as one of its backends — the Ibis team's own benchmark post explains that Ibis dataframe code generates SQL for the DuckDB and DataFusion backends and generates Polars API dataframe code for the Polars backend. Ibis is also not Narwhals: Narwhals is a thin adapter layer over existing dataframes like pandas, Polars, and PyArrow, while Ibis is a full expression API that compiles your Python to backend-native SQL. Ibis is not Fugue either — Fugue is the distributed-computing sibling in the same portable family, held as a comparison member here rather than a rival. And Ibis is not Malloy or PRQL: those are query languages (semantic modeling and pipelined SQL respectively); Ibis is a Python API, not a query language.

How Do You Install Ibis?

An Ibis install comes down to one pip command plus one backend extra: pip install 'ibis-framework[duckdb]'. The current stable release is 12.0.0 (February 7, 2026, confirmed by a successful install during research for this guide); every backend follows the same extras pattern, and you can stack extras in one command. If you also want the bundled example datasets, the PyPI getting-started guide uses pip install 'ibis-framework[duckdb,examples]'.

pip install 'ibis-framework[duckdb]'
python -c "import ibis; print(ibis.__version__)"

One trap to avoid: pip install ibis installs a different, unrelated package. The Ibis docs warn verbatim that "the ibis-framework package is not the same as the ibis package in PyPI" and that the two cannot coexist in the same Python environment because both are imported with the ibis module name. Always install ibis-framework. Conda users can use conda install -c conda-forge ibis-duckdb instead.

Ibis DuckDB: Your First Backend in Two Minutes

Your first Ibis backend is DuckDB, and it takes two minutes: connect in-memory with con = ibis.duckdb.connect(). DuckDB is Ibis's default backend — the docs note that Ibis "by default... points to an instance of DuckDB backend" — so it is the fastest way to go from install to a running query with nothing else to provision.

import ibis

con = ibis.duckdb.connect()  # in-memory; pass "mydb.duckdb" for a file
t = con.read_parquet("sales.parquet")
expr = t.filter(t.region == "EMEA").select("order_id", "amount")
df = expr.to_pandas()
print(df.head())

Notice what happened: filter and select built an expression, and only .to_pandas() executed it. If you prefer a connection string, the equivalent is ibis.connect("duckdb://") — the docs say that given an empty path it connects to an ephemeral, in-memory database.

The Ibis Lazy Lesson: When Does Your Code Actually Run?

Your Ibis code runs only when you ask for results — Ibis is in deferred mode out of the box. The docs state it plainly: "Ibis out of the box is in deferred mode. Expressions display their internal details when printed to the console." The ClickHouse team's introduction adds: "Ibis expressions are evaluated lazily, meaning we can store an expression in a variable and then apply other operations later in our program." PyPI frames the payoff as deferred execution: "Ibis pushes code execution to the query engine and only moves required data into memory when necessary."

In practice this means you chain filter, mutate, and aggregate freely and the query only compiles and runs at .to_pandas() or .execute(). You can inspect the compiled SQL without running anything via ibis.to_sql(expr). For a notebook-style experience where printing an expression executes it immediately, set ibis.options.interactive = True — the docs say this "will cause expressions to be executed immediately when printed to the console."

Ibis Tutorial: Your First Expression, Step by Step

This Ibis tutorial walks one expression from import to real results. Every step below was executed live against DuckDB while researching this guide (Ibis 12.0.0), so the outputs are real computed values, not mock-ups.

1. Import and connect. import ibis, then con = ibis.duckdb.connect() for an in-memory DuckDB backend.
2. Make a table. Use ibis.memtable with a small dict of species and bill lengths — no files needed.
3. Build the expression. Chain group_by("species"), agg(avg_bill=t.bill_length_mm.mean(), n=t.count()), and order_by("avg_bill"). Nothing has executed yet.
4. Inspect the SQL. ibis.to_sql(expr) shows the compiled query — proof that Ibis translates your Python to backend-native SQL without running it.
5. Execute. expr.to_pandas() runs the query on DuckDB and returns the real results below.

import ibis

con = ibis.duckdb.connect()
t = ibis.memtable({
    "species": ["Adelie", "Adelie", "Adelie", "Gentoo", "Gentoo", "Chinstrap"],
    "bill_length_mm": [39.1, 39.5, 40.3, 46.1, 50.0, 51.3],
})
expr = (
    t.group_by("species")
    .agg(avg_bill=t.bill_length_mm.mean(), n=t.count())
    .order_by("avg_bill")
)
print(ibis.to_sql(expr))   # compiles only — no execution
print(expr.to_pandas())    # executes on DuckDB

The real output from step 5:

Swapping Backends: The Same Code on a New Engine

Swapping backends in Ibis means changing one line — the connection. The Ibis docs repeat the rule per example: "Change only your connection to switch between backends." A MotherDuck interview with the Ibis team phrases it for beginners: "Switching backends in Ibis requires changing just one line of code: the connection string. ... The rest of your code stays identical. Ibis uses SQLGlot under the hood to translate between SQL dialects, so the same expressions compile correctly for each backend."

Try it with SQLite, which ships with Python. The expression code from the tutorial above stays byte-identical; only the connection changes:

import ibis

ibis.set_backend("sqlite")          # default backend becomes SQLite
con = ibis.connect("sqlite:///:memory:")
# the same expr from the tutorial now compiles to SQLite SQL

To practice against a real server backend locally, spin up PostgreSQL or ClickHouse in a container — our Docker beginner guide covers that setup — then swap the connection string and rerun the identical expression code.

The 22-Backends Arc: When Ibis Beats Rewriting Pandas to SQL

Ibis wins when your code outgrows one engine: with 22 Ibis backends on the support matrix (21 of them SQL-generating), the query you prototype on DuckDB can run on BigQuery, Snowflake, ClickHouse, Trino, Polars, DataFusion, and more without rewrites. The project's backend page names BigQuery, ClickHouse, DataFusion, Druid, DuckDB, Exasol, Flink, Impala, MSSQL, MySQL, Oracle, Polars, PostgreSQL, PySpark, RisingWave, SingleStoreDB, Snowflake, SQLite, and Trino among the supported engines.

The case against the two usual alternatives comes from the Ibis docs themselves: "The pandas API inherently does not scale due to its single-threaded design, ordered index, and a lot of API baggage… PySpark is a great API for Spark, but not very Pythonic and tightly coupled to the Spark execution engine. Ibis takes inspiration from pandas and PySpark – and R and SQL – but is designed to be scalable from the start." And against hand-written SQL per backend, the docs' SQL-user tutorial argues Ibis "provides a full-featured replacement for SQL SELECT queries, but expressed with Python code that is: Type-checked and validated as you go… Easier to write… More composable… Easier to reuse." Independent coverage agrees: codecut.ai summarizes Ibis as using "its own expression API and compiling it to backend-native SQL," best for data scientists working across SQL systems.

Two production touchpoints worth bookmarking: if ClickHouse becomes your backend, our ClickHouse beginner guide walks through its setup, and when the prototype needs a transform layer in the warehouse, the dbt beginner guide covers the SQL-modeling side.

Sources

Every fact in this guide was fetched from its source on November 2, 2026:

• Ibis project — Why Ibis (portable dataframe framing, backend count, pandas/PySpark comparison): https://ibis-project.org/why.html
• Ibis project — DuckDB backend docs (install command, connect forms): https://ibis-project.org/backends/duckdb.html
• Ibis project — Backend support matrix (22 backends, 21 SQL): https://ibis-project.org/backends
• Ibis project — Configuration basics (deferred mode, interactive option, default backend): https://ibis-project.org/how-to/configure/basics.html
• Ibis project — Release notes (12.0.0 stable, February 7, 2026): https://ibis-project.org/release_notes
• Ibis project — Benchmark post (SQL vs Polars codegen per backend): https://ibis-project.org/posts/ibis-bench/
• Ibis on PyPI (install extras, deferred-execution framing): https://pypi.org/project/ibis-framework/
• ClickHouse — Introduction to Ibis (lazy evaluation, SQL as intermediate representation): https://clickhouse.com/blog/introduction-to-ibis
• codecut.ai — Ibis vs Narwhals vs Fugue comparison (write-once framing, 22+ backends): https://codecut.ai/ibis-vs-narwhals-vs-fugue-dataframe-portability/
• MotherDuck — "Ibis: One Library To Query Any Backend" interview (one-line backend swap, SQLGlot translation): https://motherduck.com/videos/ibis-one-library-to-query-any-backend/
• Hamilton docs — Ibis integration (expressions compute on .to_pandas()): https://hamilton.dagworks.io/en/latest/integrations/ibis/
• Live verification — pip install 'ibis-framework[duckdb]' and memtable group-by executed against DuckDB in a fresh environment this run (Ibis 12.0.0), with ibis.to_sql() confirming compile-without-execution.

Frequently Asked Questions

Is the pip package "ibis" the same as Ibis?

No — and mixing them up is the classic beginner error. The docs warn verbatim that "the ibis-framework package is not the same as the ibis package in PyPI" and that the two cannot coexist in the same Python environment because both are imported with the ibis module name. Always pip install ibis-framework plus a backend extra.

Can Ibis replace pandas?

It can take over pandas' job once data outgrows one machine, but the Ibis docs frame it as designed to be scalable from the start rather than a drop-in clone — pandas "inherently does not scale due to its single-threaded design, ordered index, and a lot of API baggage." Ibis also works with pandas objects directly, so the two coexist: prototype in pandas, scale the same logic in Ibis.

Does Ibis replace SQL?

For SELECT-style querying, yes — the docs' SQL-user tutorial says Ibis "provides a full-featured replacement for SQL SELECT queries, but expressed with Python code" that is type-checked as you write, easier to compose, and easier to reuse. Under the hood it still compiles to SQL (via SQLGlot), so your database sees a normal query.

Which backend should a beginner start with?

DuckDB. It is Ibis's default backend, installs with a single extra, runs in-memory with zero configuration, and the expression code you write against it runs unchanged on every other backend. Only reach for a server backend when you have a server to point at.

Should I learn Ibis, Polars, or Narwhals?

Learn the one that matches your problem. Polars is a fast single engine; Narwhals is a thin adapter for writing library code that runs on any existing dataframe; Ibis is a full expression API that compiles your Python to backend-native SQL across 22 backends. If your work spans DuckDB locally and a warehouse in production, Ibis is the one built for exactly that.