Query with Pandas, Polars, and Ibis

Use DuckDB's Python client and the DuckDB Altertable extension to query Altertable tables with Pandas, Polars, or Ibis. DuckDB executes SQL against the attached Altertable catalog, then returns results in the dataframe format you choose.

The Altertable community extension requires DuckDB 1.5.4 or later.

Connect DuckDB to Altertable

Load the community extension and attach an Altertable catalog:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import os

import duckdb

con = duckdb.connect()
con.execute("INSTALL altertable FROM community")
con.execute("LOAD altertable")

con.execute(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)

The connection uses the LAKEHOUSE_USERNAME, LAKEHOUSE_PASSWORD, and LAKEHOUSE_CATALOG environment variables. For other connection options and DuckDB secrets, see Query with DuckDB.

Query into Pandas

Copy
pip install 'duckdb==1.5.4' pandas

Call .df() on the result of a DuckDB query to return a Pandas DataFrame:

Copy
1
2
3
4
5
6
7
8
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").df()

print(events)

Query into Polars

Copy
pip install 'duckdb==1.5.4' polars pyarrow

pyarrow is required for DuckDB's Polars integration.

Call .pl() to return a Polars DataFrame:

Copy
1
2
3
4
5
6
7
8
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""").pl()

print(events)

Join remote tables with local DataFrames

Register a local DataFrame with DuckDB to use it in the same SQL query as an attached Altertable table:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import polars as pl

customers = pl.DataFrame(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

con.register("customers_df", customers)
result = con.sql("""
    SELECT
        c.customer_id,
        c.segment,
        COUNT(*) AS event_count
    FROM customers_df AS c
    JOIN remote.main.events AS e
        ON e.customer_id = c.customer_id
    GROUP BY c.customer_id, c.segment
    ORDER BY event_count DESC
""").pl()

The same registration pattern works with Pandas DataFrames.

Query with Ibis

Copy
pip install 'ibis-framework[duckdb]' 'duckdb==1.5.4'

Install pandas or polars pyarrow as well if you plan to call .to_pandas() or .to_polars().

Create an Ibis connection with the same attach workflow:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
import os

import ibis

con = ibis.duckdb.connect()
con.raw_sql("INSTALL altertable FROM community")
con.raw_sql("LOAD altertable")
con.raw_sql(
    f"ATTACH 'user={os.environ['LAKEHOUSE_USERNAME']} "
    f"password={os.environ['LAKEHOUSE_PASSWORD']} "
    f"catalog={os.environ['LAKEHOUSE_CATALOG']}' "
    "AS remote (TYPE ALTERTABLE)"
)

Reference a remote table, filter and sort with Ibis expressions, then materialize the result:

Copy
1
2
3
4
events = con.table("events", database="remote.main")
activated = events.filter(events.event_name == "activated").order_by("event_id")

activated.to_pandas()

Return a Polars DataFrame instead:

Copy
activated.to_polars()

Use sql() when you prefer to write SQL directly:

Copy
1
2
3
4
5
6
7
8
events = con.sql("""
    SELECT event_id, customer_id, event_name
    FROM remote.main.events
    WHERE event_name = 'activated'
    ORDER BY event_id
""")

events.to_pandas()

Join a local memtable to an attached Altertable table in the same expression:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
customers = ibis.memtable(
    {
        "customer_id": ["customer-1", "customer-2"],
        "segment": ["self_serve", "enterprise"],
    }
)

events = con.table("events", database="remote.main")
result = (
    customers.join(events, "customer_id")
    .group_by("customer_id", "segment")
    .aggregate(event_count=events.event_id.count())
    .order_by(ibis.desc("event_count"))
)

result.to_polars()

For the SQL-only workflow, including attachment options and direct table access, see Query with DuckDB.