Ibis: The Portable Python Dataframe API Across Many Backends

Ibis is a Python dataframe library that lets you write analytics code once and run it on many execution engines, from a local DuckDB instance on your laptop to BigQuery, Snowflake, or Spark in production. Instead of rewriting pandas logic into SQL when your data outgrows memory, you describe the transformation in Python, and Ibis compiles it to the dialect of whichever backend you connect to. This tutorial walks through the core ideas: deferred execution, backend portability, and a practical end-to-end analytics example.

The Problem Ibis Solves

Most data teams hit the same wall. You prototype an analysis in pandas because it is fast to write and easy to reason about. The dataset grows, no longer fits in memory, and now you have to translate that pandas code into SQL so it can run inside the data warehouse. The two implementations drift apart, bugs creep in during translation, and you maintain two versions of the same logic.

Ibis offers a single dataframe API that sits in front of the engine. You write expressions in Python, and:

• The same code runs on a local engine (DuckDB) during development and on a warehouse (BigQuery, Snowflake, Postgres) in production.
• The heavy computation happens inside the engine, close to the data, not in your Python process.
• You avoid hand-translating pandas to SQL, and you can scale from a laptop to a warehouse by changing only the connection.

The mental model is closer to a query builder than to pandas. You are composing a description of a computation, not eagerly materializing intermediate results.

Deferred (Lazy) Execution

This is the single most important concept in Ibis. When you chain operations such as filter, select, and groupby, nothing is computed. You are building an expression tree. Execution only happens when you explicitly ask for results.

import ibis

con = ibis.duckdb.connect()

This builds an expression. No query runs yet.
orders = con.table("orders")
expr = (
    orders
    .filter(orders.status == "completed")
    .groupby("country")
    .aggregate(total=orders.amount.sum())
)

Still nothing has executed. expr is just a description.
print(type(expr))  # 

Execution happens here, returning a pandas DataFrame.
df = expr.execute()

The trigger points that actually run the query are:

• .execute() — returns a pandas DataFrame (the default).
• .topandas() — explicit pandas output.
• .topolars() — returns a Polars DataFrame.
• .topyarrow() — returns a PyArrow Table.
• .head().execute() — preview a small sample.

# Core library plus the DuckDB backend (recommended starting point)
pip install 'ibis-framework[duckdb]'

Add other backends as needed
pip install 'ibis-framework[bigquery]'
pip install 'ibis-framework[postgres]'
pip install 'ibis-framework[snowflake]'
pip install 'ibis-framework[polars]'

Multiple backends at once
pip install 'ibis-framework[duckdb,postgres,bigquery]'

import ibis

print(ibis.version)

import ibis

DuckDB in-memory (great for experiments)
con = ibis.duckdb.connect()

DuckDB persisted to a file
con = ibis.duckdb.connect("analytics.ddb")

URL-style connection (works across backends)
con = ibis.connect("duckdb://analytics.ddb")

# BigQuery
con = ibis.bigquery.connect(
    projectid="my-gcp-project",
    datasetid="analytics",

)

PostgreSQL
con = ibis.postgres.connect(
    host="localhost",
    user="analyst",
    password="secret",
    database="warehouse",
)

Snowflake
con = ibis.snowflake.connect(
    user="analyst",
    account="abc-xy123",
    database="ANALYTICS",
    warehouse="COMPUTEWH",
)

Polars (in-memory, no SQL engine)
con = ibis.polars.connect()

Note that some backends, such as Polars and pandas, are not SQL engines. Ibis still gives you the same dataframe API over them, executing through the native library instead of generating SQL.

Reading Data

Ibis can read existing tables in the backend or load files directly. DuckDB makes file reading particularly convenient.

import ibis

con = ibis.duckdb.connect()

Reference a table that already exists in the backend
orders = con.table("orders")

Read files directly (DuckDB, among others)
sales = con.readcsv("data/sales.csv")

events = con.readparquet("data/events/.parquet")


List tables registered in the connection
print(con.listtables())

import pandas as pd
import ibis

regions = pd.DataFrame(
    {
        "country": ["ID", "SG", "MY"],
        "region": ["SEA", "SEA", "SEA"],
    }
)

Wrap a pandas DataFrame as an Ibis table
regiontable = ibis.memtable(regions)

import ibis

con = ibis.duckdb.connect()
orders = con.table("orders")

Select specific columns
expr = orders.select("orderid", "country", "amount")


Filter rows (multiple conditions combine with & and |)
expr = orders.filter(
    (orders.amount > 100) & (orders.status == "completed")
)

from ibis import 

expr = (
    orders
    .filter(.status == "completed")
    .mutate(netamount=.amount - .discount)

    .filter(.netamount > 50)

    .orderby(.netamount.desc())
    .limit(10)
)

expr = (
    orders
    .mutate(amountwithtax=orders.amount  1.11)
    .orderby(ibis.desc("amountwithtax"))

    .limit(20)
)

Aggregation with groupby

from ibis import 

metrics = (
    orders
    .groupby("country")
    .aggregate(
        ordercount=.orderid.count(),

        totalrevenue=.amount.sum(),

        avgorder=.amount.mean(),

    )
    .orderby(.totalrevenue.desc())
)

Joins

con = ibis.duckdb.connect()
orders = con.table("orders")
customers = con.table("customers")

joined = orders.join(
    customers,
    orders.customerid == customers.customerid,
    how="inner",
)

result = joined.select(
    "orderid",

    "amount",
    customers.name,
    customers.segment,
)

from ibis import 

Rank orders by amount within each country
ranked = orders.mutate(
    rankincountry=ibis.rank().over(
        ibis.window(groupby="country", orderby=.amount.desc())

    )
)

from ibis import 

expr = orders.mutate(
    countrylower=.country.lower(),
    nameclean=.customername.strip(),

    haspromo=.notes.contains("PROMO"),

    orderyear=.orderdate.year(),
    ordermonth=.orderdate.month(),

    dayssince=(ibis.now() - .orderdate).cast("int64"),
)

Previewing Lazily and Inspecting Compiled SQL

Because expressions are deferred, you can inspect what Ibis will send to the engine before running anything heavy. This is invaluable for debugging and for understanding cost.

from ibis import 

expr = (
    orders
    .filter(.status == "completed")
    .groupby("country")

    .aggregate(total=.amount.sum())
)

Preview only a few rows (still pushes the query down, with a LIMIT)
print(expr.head(5).execute())

Inspect the compiled SQL without executing
print(ibis.tosql(expr))

import ibis

expr = (
    ibis.table({"country": "string", "amount": "float64"}, name="orders")
    .groupby("country")
    .aggregate(total=ibis..amount.sum())

)

print(ibis.tosql(expr, dialect="duckdb"))
print(ibis.tosql(expr, dialect="bigquery"))

-- duckdb dialect
SELECT
  "country",
  SUM("amount") AS "total"
FROM "orders"
GROUP BY 1

-- bigquery dialect
SELECT
  country,
  SUM(amount) AS total
FROM orders
GROUP BY 1

import ibis
from ibis import 


def revenuebycountry(con, tablename="orders"):

    """Backend-agnostic analytics. Works on any Ibis connection."""
    orders = con.table(tablename)
    return (
        orders
        .filter(.status == "completed")

        .groupby("country")
        .aggregate(
            totalrevenue=.amount.sum(),
            ordercount=.orderid.count(),

        )
        .orderby(.totalrevenue.desc())
    )


Development: local DuckDB
devcon = ibis.duckdb.connect("local.ddb")

print(revenuebycountry(devcon).execute())

Production: BigQuery, same function, different connection
prodcon = ibis.bigquery.connect(projectid="my-project", datasetid="analytics")

print(revenuebycountry(prodcon).execute())

import ibis

con = ibis.duckdb.connect()
orders = con.table("orders")

Out: choose the output format you want
pdf = orders.limit(1000).topandas()

pldf = orders.limit(1000).topolars()
arrowtbl = orders.limit(1000).topyarrow()

In: wrap external dataframes as Ibis tables
import pandas as pd

extra = pd.DataFrame({"id": [1, 2], "label": ["a", "b"]})
extratbl = ibis.memtable(extra)

import ibis
from ibis import 


@ibis.udf.scalar.python
def classifyamount(x: float) -> str:

    if x >= 1000:
        return "large"
    elif x >= 100:
        return "medium"
    return "small"


con = ibis.duckdb.connect()
orders = con.table("orders")

expr = orders.mutate(sizebucket=classifyamount(.amount))

import ibis
from ibis import 


con = ibis.duckdb.connect()

Run raw SQL and get an Ibis table back
top = con.sql(
    """
    SELECT country, SUM(amount) AS total
    FROM orders
    WHERE status = 'completed'
    GROUP BY country
    """
)

Continue building with the Ibis API on the SQL result
final = top.filter(.total > 10000).orderby(.total.desc())

Name (alias) a table expression for reuse or for use inside con.sql
labeled = top.alias("countrytotals")

import ibis
import pandas as pd
from ibis import 

ibis.options.interactive = True  # auto-execute and pretty-print in a REPL

con = ibis.duckdb.connect()

ordersdf = pd.DataFrame(

    {
        "orderid": [1, 2, 3, 4, 5, 6],
        "customerid": [10, 10, 11, 12, 11, 12],

        "amount": [120.0, 80.0, 500.0, 30.0, 220.0, 90.0],
        "status": [
            "completed",
            "completed",
            "completed",
            "cancelled",
            "completed",
            "completed",
        ],
        "orderdate": pd.todatetime(

            [
                "2026-01-05",
                "2026-01-09",
                "2026-02-01",
                "2026-02-03",
                "2026-02-15",
                "2026-03-02",
            ]
        ),
    }
)

customersdf = pd.DataFrame(
    {
        "customerid": [10, 11, 12],

        "name": ["Andi", "Budi", "Citra"],
        "segment": ["retail", "wholesale", "retail"],
    }
)

orders = con.createtable("orders", ordersdf)

customers = con.createtable("customers", customersdf)


1) Join orders to customers, keep completed orders only
joined = (
    orders
    .filter(.status == "completed")
    .join(customers, "customerid", how="inner")

)

2) Grouped metrics per customer segment
segmentmetrics = (
    joined
    .groupby("segment")

    .aggregate(
        revenue=.amount.sum(),
        ordercount=.orderid.count(),

        avgorder=.amount.mean(),

    )
    .orderby(.revenue.desc())

)

print(segmentmetrics.execute())

3) Rank each order by amount within its segment (window function)
ranked = joined.mutate(
    rankinsegment=ibis.rank().over(
        ibis.window(groupby="segment", orderby=.amount.desc())

    )
).select("orderid", "name", "segment", "amount", "rankinsegment")

print(ranked.orderby(["segment", "rankinsegment"]).execute())


Inspect the SQL DuckDB will actually run for the ranking step
print(ibis.tosql(ranked))

This pipeline reads as ordinary Python, yet every step executes inside DuckDB as a single compiled query. Swapping con for a BigQuery connection would run the identical logic in the warehouse.

• Use Ibis when you need one analytics codebase to run across multiple engines, when your data lives in a warehouse and you want to push computation down to it, or when you want to develop locally on DuckDB and deploy to BigQuery or Snowflake without a rewrite. Ibis is a frontend, not an engine.
• Use Polars when your data fits comfortably on a single machine, you want maximum in-process performance, and you do not need backend portability. Polars is an engine and is excellent at fast local dataframe work.
• Use raw SQL when a query is naturally expressed in SQL, is backend-specific, or when your team already maintains SQL assets. You can still wrap that SQL in Ibis via con.sql(...) to compose further.

A reasonable hybrid: prototype and explore with Polars or pandas, express the production pipeline in Ibis so it runs in the warehouse, and drop to con.sql(...) for the occasional query that needs hand tuning.

• Develop on DuckDB, deploy on your warehouse. DuckDB gives a fast, server-free local loop while keeping behavior close to production SQL engines.
• Inspect the SQL with ibis.tosql(expr) before running expensive queries. It reveals exactly what the engine will execute and helps you reason about cost.
• Keep heavy computation in the engine. Filter and aggregate before calling .topandas(), so only small results cross into Python.
• Use from ibis import for readable, fluent chains that reference intermediate columns, and prefer built-in operations over UDFs since they compile to native engine functions and run much faster than per-row Python.
• Pass the connection as a parameter to your analytics functions so the same logic stays backend-agnostic and testable.
• Pin your Ibis and backend versions in production, since dialect compilation can evolve between releases.

• Ibis is a frontend over execution engines, not an engine itself. It builds expressions and hands compiled queries to backends like DuckDB, BigQuery, Postgres, Snowflake, and Spark.
• Execution is deferred. You compose an expression tree, and nothing runs until you call .execute(), .topandas(), .topolars(), or .topyarrow().
• The same code runs on different backends by changing only the connection, which is the central portability benefit.
• You can inspect compiled SQL with ibis.to_sql(expr) and confirm the same expression produces different dialects per backend.
• Ibis interoperates cleanly with pandas, Polars, and PyArrow, and offers a con.sql(...)` escape hatch for raw SQL when needed.
• Choose Ibis for portability and warehouse-scale pushdown, Polars for fast local single-machine work, and raw SQL where it fits best, mixing them as the project requires.