Ingest with Pandas, Polars, and Ibis

Use DuckDB's Python client and the DuckDB Altertable extension to load local data into Altertable. DuckDB exposes registered DataFrames and Ibis tables as SQL relations, and the extension sends the resulting CREATE TABLE AS SELECT or INSERT ... SELECT operation to the attached Altertable catalog.

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 Ingest with DuckDB.

Load a Pandas DataFrame

Copy
pip install 'duckdb==1.5.4' pandas

Register a DataFrame with a SQL name, then use it as the source of a remote table:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import pandas as pd

events = pd.DataFrame(
    {
        "event_id": [1, 2, 3],
        "customer_id": ["customer-1", "customer-2", "customer-1"],
        "event_name": ["signed_up", "signed_up", "activated"],
    }
)

con.register("events_df", events)
con.execute("""
    CREATE TABLE remote.main.events AS
    SELECT *
    FROM events_df
""")

To append another DataFrame to the existing table, use INSERT INTO. BY NAME matches columns by name instead of relying on their order:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
new_events = pd.DataFrame(
    {
        "customer_id": ["customer-3"],
        "event_name": ["signed_up"],
        "event_id": [4],
    }
)

con.register("new_events_df", new_events)
con.execute("""
    INSERT INTO remote.main.events BY NAME
    SELECT *
    FROM new_events_df
""")

Load a Polars DataFrame

Copy
pip install 'duckdb==1.5.4' polars pyarrow

pyarrow is required for DuckDB's Polars integration.

Polars DataFrames use the same DuckDB relation interface:

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

events = pl.DataFrame(
    {
        "event_id": [1, 2, 3],
        "customer_id": ["customer-1", "customer-2", "customer-1"],
        "event_name": ["signed_up", "signed_up", "activated"],
    }
)

con.register("events_df", events)
con.execute("""
    CREATE TABLE remote.main.events AS
    SELECT *
    FROM events_df
""")

Append Polars data to an existing table in the same way:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
new_events = pl.DataFrame(
    {
        "event_id": [4],
        "customer_id": ["customer-3"],
        "event_name": ["signed_up"],
    }
)

con.register("new_events_df", new_events)
con.execute("""
    INSERT INTO remote.main.events BY NAME
    SELECT *
    FROM new_events_df
""")

Load with Ibis

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

Ibis compiles dataframe expressions to SQL and uses the same DuckDB attach workflow underneath:

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)"
)

Build a local table with memtable, then create a remote table from it:

Copy
1
2
3
4
5
6
7
8
9
events = ibis.memtable(
    {
        "event_id": [1, 2, 3],
        "customer_id": ["customer-1", "customer-2", "customer-1"],
        "event_name": ["signed_up", "signed_up", "activated"],
    }
)

con.create_table("events", obj=events, database="remote.main", overwrite=True)

database="remote.main" targets the attached Altertable catalog and its main schema.

Append rows with insert:

Copy
1
2
3
4
5
6
7
8
9
new_events = ibis.memtable(
    {
        "customer_id": ["customer-3"],
        "event_name": ["signed_up"],
        "event_id": [4],
    }
)

con.insert("events", new_events, database="remote.main")

When column order differs from the remote table, use INSERT INTO ... BY NAME through raw_sql:

Copy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
new_events = ibis.memtable(
    {
        "customer_id": ["customer-3"],
        "event_name": ["signed_up"],
        "event_id": [4],
    }
)

con.create_table("new_events_df", obj=new_events, overwrite=True)
con.raw_sql("""
    INSERT INTO remote.main.events BY NAME
    SELECT *
    FROM new_events_df
""")

For large tables, load data in batches so each transaction stays within your available memory and transaction limits. If you need to define the remote schema explicitly before loading, create the table with CREATE TABLE and then use INSERT INTO ... BY NAME.