Ibis in Python: Portable Analytics for DuckDB, BigQuery, and Snowflake (2026)

Updated: July 14, 2026

Ibis is a portable Python DataFrame library that compiles your code to native SQL for 20+ backends, so the same script can run on DuckDB in a laptop notebook and on Snowflake in production without a rewrite. In 2026 the project hit version 12, and honestly, it's become the pragmatic answer to a question I get from teammates almost every month: "how do we stop rewriting analytics logic every time the warehouse changes?" This guide walks through Ibis against DuckDB and Polars, the deferred expression model, warehouse-swap portability, and the real-world limits I've hit shipping pipelines.

• Ibis 12.0 (February 2026) supports 20+ backends including DuckDB, BigQuery, Snowflake, Polars, PySpark, Trino, DataFusion, and ClickHouse.
• Ibis is a query compiler, not an executor. It emits SQL via SQLGlot and defers all computation to the backend.
• Switching from local DuckDB to production Snowflake or BigQuery is usually a one-line ibis.connect() change.
• The deferred placeholder _ lets you build expressions without holding a table variable, which is what makes Ibis fluent inside mutate and filter.
• Ibis solves a different problem than Narwhals: portability across warehouses, not across dataframe libraries.
• Where Ibis breaks: exotic backend-specific SQL, UDFs that need engine-native functions, and any workflow that assumes eager materialization.

What is Ibis in Python, actually?

Ibis is a Python DataFrame library where every operation is deferred. Instead of computing anything in-process, it builds a logical expression tree, and only when you call .execute(), .to_pandas(), or .to_polars() does it compile that tree to the native SQL dialect of your chosen backend and hand it off. Wes McKinney (who also created pandas and co-created Apache Arrow) started Ibis specifically to break the coupling between the DataFrame API and the execution engine. That coupling is the thing that forces you to rewrite analytics whenever the warehouse changes.

In practice: I write a filter, a join, a window function, and a groupby in Ibis. The library uses SQLGlot under the hood to translate that expression tree into DuckDB SQL when I'm prototyping locally, and into Snowflake or BigQuery SQL when the same code runs in production. The translation layer adds essentially zero overhead. Your query performance is the backend's query performance.

For a data engineer, the important framing is this: Ibis isn't a competitor to pandas or Polars for in-memory work. It's a competitor to hand-rolled SQL, and to the mess of dialect-specific f-strings I've seen in every mid-sized data platform. It's closer in spirit to LINQ, Slick, or SQLAlchemy Core than to a DataFrame library, just wearing the DataFrame API as its user-facing skin.

Installing Ibis 12 and picking a backend

Ibis 12.0.0 shipped on February 7, 2026. The package is called ibis-framework (not ibis, which is a different, unrelated project on PyPI). Install with the backends you actually plan to use as extras. The base package is slim; the backend drivers pull in the heavy dependencies.

# Local development: DuckDB + example datasets
pip install "ibis-framework[duckdb,examples]"

# Production warehouses
pip install "ibis-framework[bigquery]"
pip install "ibis-framework[snowflake]"
pip install "ibis-framework[trino]"

# In-memory / lightweight
pip install "ibis-framework[polars]"
pip install "ibis-framework[datafusion]"

Connecting is either an explicit constructor or a URL. I strongly prefer the URL form because it's the piece you inject via environment variable when you promote code from dev to prod.

import ibis
import os

# Local: DuckDB persisted to a file
con = ibis.connect("duckdb:///data/local.ddb")

# Production: Snowflake via URL
# snowflake://USER:PASSWORD@ACCOUNT/DATABASE/SCHEMA?warehouse=WH
con = ibis.connect(os.environ["WAREHOUSE_URL"])

# BigQuery: application-default credentials
con = ibis.connect("bigquery://my-gcp-project/my_dataset")

Once connected, tables are just Python objects. con.list_tables() gives you the schema, con.table("orders") returns an Ibis table expression, and con.sql("SELECT ...") is your escape hatch when you need raw SQL for something Ibis doesn't yet cover. See the DuckDB Ibis integration guide for the full API surface.

From DuckDB to Snowflake in one line: the portability payoff

This is the feature that sells Ibis on any data engineering team I've worked on. You develop against DuckDB on a laptop with real production-shaped data (because DuckDB happily reads Parquet directly from S3, GCS, or a local disk) and then promote the exact same transformation to Snowflake or BigQuery by swapping the connection string. Here's a concrete example I use to compute a rolling 7-day revenue window with a churn flag.

import ibis
from ibis import _

def build_revenue_features(con):
    orders = con.table("orders")

    # Daily revenue with a 7-day trailing average and churn flag
    daily = (
        orders
        .group_by(["customer_id", _.order_date.truncate("D").name("day")])
        .aggregate(revenue=_.amount.sum())
    )

    windowed = daily.mutate(
        rolling_7d=_.revenue.mean().over(
            ibis.window(
                group_by="customer_id",
                order_by="day",
                preceding=6,
                following=0,
            )
        ),
        churn_risk=(_.revenue == 0).cast("int64"),
    )
    return windowed

# Local iteration
local_con = ibis.connect("duckdb:///dev.ddb")
features = build_revenue_features(local_con)
print(features.limit(10).to_pandas())

# Production: same function, same expression, different SQL under the hood
prod_con = ibis.connect(os.environ["SNOWFLAKE_URL"])
features = build_revenue_features(prod_con)
features.to_pyarrow_batches(chunk_size=100_000)

Under the hood, DuckDB gets a query using DuckDB-flavoured date_trunc and window syntax, while Snowflake receives its own dialect. Neither of those SQL strings ever touches your codebase. To see what Ibis is generating (which you absolutely should during code review), call ibis.to_sql(features). It returns the compiled SQL for whichever backend the expression is bound to.

The deferred underscore: expressions without a table object

The _ (underscore) is the piece of Ibis syntax that trips up people coming from pandas. Import it as from ibis import _. It's a deferred placeholder, meaning it doesn't refer to any specific table until Ibis binds it to one at execution time. That decoupling is what lets you write clean method chains without stashing intermediate tables as variables.

from ibis import _

# Instead of this (repeated table variable, error-prone):
t = con.table("events")
result = (
    t.filter(t.event_type == "purchase")
     .mutate(revenue_usd=t.amount * t.fx_rate)
     .group_by(t.customer_id)
     .aggregate(total=t.revenue_usd.sum())  # NameError - revenue_usd is new
)

# Write this. _ resolves against whatever table is in scope at that step
result = (
    con.table("events")
        .filter(_.event_type == "purchase")
        .mutate(revenue_usd=_.amount * _.fx_rate)
        .group_by(_.customer_id)
        .aggregate(total=_.revenue_usd.sum())  # works: _ now sees revenue_usd
)

The important detail: after .mutate(), the new column revenue_usd is visible to _ in the next step. If you'd used a stored table variable, that variable still points at the pre-mutate schema and would raise. Ibis 12 improved DerefMap to make this lazy and to support multiple value inputs. Small change, but it means chained expressions inside mutate and select no longer surprise you when column names collide. The Ibis 12 release notes document this along with a handful of API removals worth reading before you upgrade.

You'll also see _ used inside window functions (_.amount.sum().over(...)), joins (t.join(u, _.id == _.id)), and case-when logic (ibis.cases((_.age < 18, "minor"), else_="adult")). It's the same object everywhere, resolved by context.

Ibis vs pandas, Polars, and Narwhals: which one when?

These libraries get lumped together because they all present as "DataFrame-shaped" Python APIs, but they solve different problems. Here's how I think about the trade-offs when I sit down to design a new pipeline.

Dimension	Ibis	pandas	Polars	Narwhals
Execution model	Compiles to backend SQL	In-memory eager	In-memory lazy or eager	Wraps other libraries' APIs
Handles data larger than RAM	Yes (backend does)	No	Streaming for some ops	Only if wrapped backend does
Runs on Snowflake / BigQuery	Yes (native SQL)	No	No	No
Zero-copy Arrow interop	Yes	Partial	Yes	Passthrough
Portable across libraries	No (single API)	No	No	Yes (primary use case)
Best fit	Warehouse-first analytics	Ad-hoc, small data	Big local ETL	Library authors

The most common confusion is Ibis vs Narwhals. Narwhals is for library authors who want their package to accept pandas or Polars or PyArrow inputs without a hard dependency. It's a compatibility shim. Ibis is for application code that wants to run analytics on a database, and treats the database (not a Python array) as the source of truth. If you're building a data application, you probably want Ibis. If you're building a library that reads user-provided DataFrames, you probably want Narwhals.

Ibis vs Polars is simpler. Polars is unbeatable for CPU-bound single-node ETL on data that fits (or nearly fits) in memory. Ibis is what you reach for when the data lives in a warehouse and moving it into Python is the bottleneck.

Ibis with dbt and SQLMesh: hybrid pipelines that stay testable

I've not seen a team rip out dbt or SQLMesh to replace it with Ibis, and honestly I'd push back if someone proposed it. dbt owns model dependency graphs, incremental materialization strategies, tests, and documentation. Ibis explicitly does not do those things. What Ibis does add to a dbt shop is a way to generate SQL for models that are annoying to hand-write: dozens of similar aggregations, window-heavy feature tables, or transformations that need to run identically on the local DuckDB used by unit tests and the Snowflake used in production.

# Python-based dbt model (models/analytics/customer_features.py)
import ibis
from ibis import _

def model(dbt, session):
    dbt.config(materialized="incremental", unique_key="customer_id")

    orders = dbt.ref("stg_orders")
    if dbt.is_incremental:
        max_updated = session.execute("SELECT MAX(updated_at) FROM {{ this }}")
        orders = orders.filter(_.updated_at > max_updated)

    return (
        orders
        .group_by("customer_id")
        .aggregate(
            total_orders=_.count(),
            lifetime_value=_.amount.sum(),
            avg_ticket=_.amount.mean(),
        )
    )

That Python model is one dbt-adapter setting away in an Ibis-aware dbt project, and it means the SQL that dbt compiles is written in Python and version-controlled as an expression tree, but the model still participates in dbt's DAG, tests, and incremental materialization. Same idea works with SQLMesh's @model Python models, where you can return an Ibis expression directly and SQLMesh will compile it against the target dialect.

The pipeline test that matters here, and the one I write first: assert that ibis.to_sql(expr, dialect="snowflake") and ibis.to_sql(expr, dialect="duckdb") both produce identical logical results against the same fixture data. If a version bump or dialect quirk breaks that invariant, CI fails before backfill.

When Ibis is the wrong tool

Ibis isn't a silver bullet, and I've watched a couple of migrations stall because someone reached for it in the wrong place. The failure modes:

• You need a specific backend feature. Snowflake's QUALIFY, BigQuery's APPROX_QUANTILES, DuckDB's list comprehensions. Some of these have Ibis bindings, some don't. Check the backend operation matrix in the docs before you commit to a rewrite; escape hatches (con.sql(...)) exist but they defeat the portability guarantee.
• You're doing heavy in-Python computation. If your pipeline is 90% NumPy, scikit-learn, or PyTorch, Ibis buys you nothing. The compute isn't running in the warehouse anyway. Read the data with a plain SQL query and use pandas or Polars.
• You need low-latency single-row lookups. Ibis compiles to analytical SQL. If you're serving an OLTP-style endpoint, use a proper ORM or a driver directly.
• Your team already writes fluent modern SQL. The productivity delta over hand-written SQL is smaller than the delta over hand-written SQL in six dialects. Single-warehouse shops sometimes just want Jinja and dbt.

Migration and testing playbook for adopting Ibis

Here's the sequence I've used on two migrations from hand-rolled SQL and one migration from pandas to Ibis. It's deliberately incremental. Ibis coexists with your existing stack, so there's no big-bang cutover.

1. Pick one model. Choose a transformation that already has good input/output fixtures and runs in your CI. Rewrite just that model in Ibis, keep the original as the oracle, and diff the outputs on real production data.
2. Snapshot the emitted SQL. Add a golden-file test that captures ibis.to_sql(expr, dialect="duckdb") and ibis.to_sql(expr, dialect="snowflake"). This is what protects you from silent regressions on Ibis or backend upgrades.
3. Test on DuckDB first, always. DuckDB is the fastest feedback loop you'll ever have. Write a fixture, run the expression, assert. Only then run the same expression against the Snowflake dev warehouse.
4. Backfill in shadow mode. Run the new Ibis pipeline in parallel with the old SQL for a week. Compare row counts and hashes. This is the step most teams skip, and most teams regret skipping it.
5. Wire it into dbt or SQLMesh, don't replace them. The orchestration, lineage, and incremental logic those tools give you is worth more than the SQL uniformity you get from an Ibis-only pipeline.
6. Document backend-specific escape hatches. Anywhere you had to drop into raw con.sql(...), comment why, and add a test that fails loudly if the escape hatch is used against an unsupported backend.

Follow that playbook and Ibis becomes a boring, reliable piece of infrastructure, which is exactly what you want a portable analytics layer to be. The real payoff comes six months in, when the finance team asks "can we move this from BigQuery to Snowflake?" and the answer is a one-line change plus a CI run.

Frequently Asked Questions

Is Ibis faster than pandas?

Ibis performance is whatever your backend's performance is. Running Ibis on DuckDB is roughly DuckDB performance; running it on Snowflake is Snowflake performance. The translation layer adds negligible overhead. For pandas-shaped workloads that fit in memory on one machine, pandas can be faster than Ibis on a remote warehouse simply because there's no network. For anything larger than RAM, Ibis on DuckDB or a warehouse crushes pandas.

Can Ibis replace SQL entirely?

For most analytical transformations, yes. Ibis covers filters, joins, group-bys, window functions, CTEs, and unions across all major backends. But it doesn't cover 100% of every dialect's surface area. Things like Snowflake's QUALIFY, engine-specific hints, and advanced JSON operations sometimes require dropping into raw SQL via con.sql(). Treat Ibis as the default and hand-written SQL as the escape hatch, not the other way around.

How does Ibis compare to Narwhals?

They solve different problems. Narwhals is a compatibility shim for library authors, so your Python package can accept pandas, Polars, or PyArrow inputs without a hard dependency. Ibis is for application code that wants to run analytics on a database and treats the database as the source of truth. If you're building a library, use Narwhals. If you're building a data pipeline that talks to Snowflake or BigQuery, use Ibis.

Does Ibis work with dbt?

Yes. Ibis complements dbt rather than replacing it. You can render Ibis expressions into dbt Python models, and the model still participates in dbt's DAG, tests, and incremental materialization strategies. SQLMesh has similar support via Python models that return Ibis expressions. Neither pattern is officially blessed by dbt Labs, but both are stable enough to run in production.

Which backends does Ibis 12 support?

Over 20, including DuckDB, BigQuery, Snowflake, PostgreSQL, MySQL, MSSQL, Trino, Athena, Databricks, PySpark, Polars, pandas, PyArrow, ClickHouse, DataFusion, Impala, Druid, Flink, SingleStoreDB, SQLite, and Oracle. Not all backends implement every operation. The docs publish a support matrix, and it's the first place you should look before committing to Ibis for a specific transformation.