Add a note about executemany or executebatch inserts into DuckLake

[staticlibs]

DuckLake behaviour with client's "batch insert" methods, when a separate Parquet file is written for each incoming record, may be confusing. There are at least 2 reports on this from users (1, 2).

It is suggested to add a note like the following somewhere to the docs:

Bulk data import

When input data is already stored in Parquet files with the same schema as the destination DuckLake table, then it is advised to use ducklake_add_data_files to import this data.

For input data from other formats/sources the most performant way to import it to DuckLake is to first insert the records into the in-memory "staging" table (for example, using the Appender interface) and then perform the bulk insert into the DuckLake table:

INSERT INTO ducklake.tab1 FROM memory.tab1

Warning:

"batch insert" functionality in DuckDB clients can be misleading when used with DuckLake. For example, when Python DB-API method executemany or JDBC method executeBatch are used with DuckLake, the specified INSERT query will be prepared and then executed in a loop for every parameter tuple in the batch, resulting in a sequence like this:

PREPARE p AS INSERT INTO tab1 (col1, col2) VALUES (?, ?);
BEGIN TRANSACTION;
EXECUTE p(1, 'foo');
EXECUTE p(2, 'bar');
EXECUTE p(3, 'baz');
...
EXECUTE p(n, 'boo');
COMMIT;

From the DuckLake point of view incoming INSERT queries are separate and not a part of any "batch", so a separate Parquet file will be created for every record in the batch. That is unlikely desired, so the usage of executemany()/executeBatch() directly with DuckLake is discouraged. These methods still can be used to insert the records into the in-memory "staging" table before copying it to a DuckLake table.

TODO: Polars data frame direct copy example.

@guillesd I wonder if you have a minute to look at this note - how to structure it better and in what section it is appropriate to put it into.

[jordandakota]

The executemany/executeBatch warning is valuable and should absolutely be documented. But recommending ducklake_add_data_files as the primary bulk import path puts the burden on the user to produce Parquet files, manage schema compatibility, and handle file placement. That's a niche use case (Iceberg migrations, frozen DuckLakes), not a general recommendation.

For the common case, DuckDB's native file reading directly into a DuckLake table already does the right thing — no staging, no manual Parquet conversion:

INSERT INTO ducklake.tab1 SELECT * FROM 'data.csv';
INSERT INTO ducklake.tab1 SELECT * FROM read_json('data.json');
INSERT INTO ducklake.tab1 SELECT * FROM read_parquet('existing/*.parquet');
-- Also works with dataframes:
INSERT INTO ducklake.tab1 SELECT * FROM df;  -- polars/pandas

Proof: file creation by method

Tested with DuckDB 1.4.4 + DuckLake, 500 identical rows per method:

Method	Parquet Files	Total Size
INSERT...SELECT FROM 'data.csv'	1	9,228 B
COPY table FROM 'data.csv'	1	9,228 B
INSERT...SELECT FROM polars_df	1	9,228 B
Stage in memory → INSERT into DuckLake	1	9,228 B
executemany()	500	229,450 B

executemany creates 500x more files and 25x more storage for the same data.

Reproduction script

import duckdb, os, glob, polars as pl

conn = duckdb.connect()
conn.execute("INSTALL ducklake; LOAD ducklake;")

# Create test CSV
conn.execute("""
    COPY (SELECT i AS id, 'name_' || i AS name, random() AS value
          FROM range(500) t(i)) TO 'test_data.csv' (HEADER)
""")

# Test 1: INSERT...SELECT FROM CSV → 1 file
conn.execute("ATTACH 'ducklake:t1.ducklake' AS t1 (DATA_PATH 't1_files/');")
conn.execute("CREATE TABLE t1.tbl (id BIGINT, name VARCHAR, value DOUBLE);")
conn.execute("INSERT INTO t1.tbl SELECT * FROM 'test_data.csv';")
print("CSV bulk:", len(glob.glob('t1_files/**/*.parquet', recursive=True)), "files")

# Test 2: INSERT...SELECT FROM Polars df → 1 file
df = pl.DataFrame({"id": range(500), "name": [f"n_{i}" for i in range(500)], "value": [0.5]*500})
conn.execute("ATTACH 'ducklake:t2.ducklake' AS t2 (DATA_PATH 't2_files/');")
conn.execute("CREATE TABLE t2.tbl (id BIGINT, name VARCHAR, value DOUBLE);")
conn.execute("INSERT INTO t2.tbl SELECT * FROM df")
print("Polars df:", len(glob.glob('t2_files/**/*.parquet', recursive=True)), "files")

# Test 3: executemany → 500 files
conn.execute("ATTACH 'ducklake:t3.ducklake' AS t3 (DATA_PATH 't3_files/');")
conn.execute("CREATE TABLE t3.tbl (id BIGINT, name VARCHAR, value DOUBLE);")
conn.executemany("INSERT INTO t3.tbl VALUES (?,?,?)", [(i,f"n_{i}",0.5) for i in range(500)])
print("executemany:", len(glob.glob('t3_files/**/*.parquet', recursive=True)), "files")

[staticlibs]

Suggested page structure:

1. new table:

CREATE TABLE data1 AS FROM 'data.csv';

2. existing table, matching schema:

COPY data1 FROM 'data.csv';
COPY data1 FROM 'data.parquet';
COPY data1 FROM df;

3. grey note on ducklake_add_data_files with a link to its section

4. incompatible schema, data needs to be prepared - in-memory staging table:

ATTACH ':memory:' as memory;
INSERT INTO ducklake.tab1 FROM memory.tab1;

5. grey note on Appender for writing to a staging table

6. orange warning on executemany and executeBatch with an explanation

[guillesd]

Hey @staticlibs I think this is a good point, we can make some clarification in the documentation. I also think that the appender should interact better with DuckLake... we can discuss this internally.