duckdb-polars-boundaries
Use when Python analytical code crosses between DuckDB SQL/relations and Polars DataFrame/LazyFrame objects, especially for Arrow transfer, lazy versus eager execution, registration lifetime, schema c
SKILL.mdskills/duckdb-polars-boundaries/SKILL.md
GitHubDuckDB and Polars execution boundary
Choose one engine to own each stage. DuckDB owns relational SQL, multi-source joins, and file scans that SQL expresses clearly. Polars owns reusable typed expression pipelines and dataframe-native transformations. Cross once at a deliberate consumer boundary rather than alternating after every operation.
Workflow
- Inspect DuckDB and Polars versions, source size/format, desired output grain, schema, null/timestamp/nested types, ordering, and final consumer.
- Decide whether files remain the shared boundary or whether an in-memory Arrow-compatible transfer is justified. Prefer Parquet for reusable or process-crossing artifacts.
- Keep source objects alive while DuckDB replacement scans or registered views refer to them. Use explicit registration when lifetime or naming must be obvious; unregister when a long-lived connection would retain stale state.
- Materialize exactly once when crossing to an eager consumer. Do not call
.collect(),.pl(),.arrow(), or dataframe conversion repeatedly in a loop merely to continue the pipeline in the other engine. - Validate row count, grain, column names/order, dtypes, nulls, timestamps, nested values, dictionary/categorical semantics, and ordering after transfer.
Decision rules
- If DuckDB SQL can scan the original files and produce the final relational result, keep the work there and convert only for the Polars consumer.
- If Polars transformation is the reusable product, scan/transform in Polars and expose Arrow/Parquet to DuckDB only for a SQL stage that adds value.
- Treat conversion as potentially copying or coercing unless current APIs and buffers prove otherwise. Zero-copy is an optimization claim, not a default.
- SQL result order is undefined without
ORDER BY; dataframe transfer does not create a durable ordering guarantee.
import duckdb
import polars as pl
source = pl.scan_parquet("events/*.parquet")
prepared = source.filter(pl.col("status") == "ok").collect()
with duckdb.connect() as connection:
connection.register("prepared", prepared)
result = connection.sql(
"select customer_id, count(*) as n from prepared group by customer_id"
).pl()
Verify the installed conversion APIs and add a round-trip contract test. Read ownership and lifetime, schema transfer, and verification.