Integration Patterns: DuckDB ↔ Polars

Zero-Copy Conversions (FASTEST)

DuckDB → Polars (Recommended)

python

import duckdb
import polars as pl

# Direct conversion with .pl() - zero-copy via Arrow
df_polars = duckdb.sql("""
    SELECT * FROM 'data.parquet'
    WHERE amount > 100
""").pl()  # Returns Polars DataFrame directly

# Lazy version for large datasets
lazy_df = duckdb.sql("SELECT * FROM 'data.parquet'").pl(lazy=True)
result = lazy_df.filter(pl.col('status') == 'active').collect()

Polars → DuckDB (Direct Reference)

python

import duckdb
import polars as pl

# DuckDB can query Polars DataFrames directly by name
df = pl.read_parquet('data.parquet')

result = duckdb.sql("""
    SELECT category, SUM(amount) as total
    FROM df
    GROUP BY category
    ORDER BY total DESC
""").pl()  # Query df directly, return as Polars

Via Arrow (When Needed)

python

# Polars → Arrow → DuckDB
df_polars = pl.read_csv('data.csv')
duckdb.register('my_table', df_polars.to_arrow())

# DuckDB → Arrow → Polars
arrow_table = duckdb.sql("SELECT * FROM data").arrow()
df_polars = pl.from_arrow(arrow_table)

WRONG vs RIGHT Patterns

❌ NEVER - Using Pandas

python

# FORBIDDEN - decisively slower than Polars/DuckDB on every operation!
import pandas as pd
df = pd.read_csv('data.csv')
result = df.groupby('category')['amount'].sum()

✅ CORRECT - DuckDB for simple aggregation query

python

import duckdb
# Direct file query - no memory loading!
result = duckdb.sql("""
    SELECT category, SUM(amount) as total
    FROM 'data.csv'
    GROUP BY category
""").pl()  # Fast, memory-efficient

❌ NEVER - Loading file before DuckDB query

python

# WRONG - Unnecessary memory usage
import polars as pl
import duckdb
df = pl.read_csv('data.csv')  # Loads entire file
result = duckdb.sql("SELECT * FROM df WHERE amount > 100").pl()

✅ CORRECT - Let DuckDB query directly

python

import duckdb
# DuckDB queries file directly - much faster!
result = duckdb.sql("""
    SELECT * FROM 'data.csv'
    WHERE amount > 100
""").pl()

❌ NEVER - Eager evaluation in Polars

python

# WRONG - Loads everything immediately
import polars as pl
df = pl.read_csv('large_data.csv')  # Eager load
filtered = df.filter(pl.col('value') > 100)

✅ CORRECT - Lazy evaluation

python

import polars as pl
# Lazy - builds query plan, optimizes, executes once
df = pl.scan_csv('large_data.csv')  # Lazy
result = (
    df
    .filter(pl.col('value') > 100)
    .groupby('category')
    .agg(pl.sum('value'))
    .collect()  # Execute optimized plan
)

❌ NEVER - Unnecessary Conversions

python

# WASTEFUL (DuckDB → Pandas → Polars)
import duckdb, pandas as pd, polars as pl
df_pd = duckdb.sql("SELECT * FROM 'data.csv'").df()  # requires pandas - the skill never ships it
df_pl = pl.from_pandas(df_pd)

✅ CORRECT - Direct conversion

python

# DIRECT (DuckDB → Polars via Arrow)
import duckdb
df_pl = duckdb.sql("SELECT * FROM 'data.csv'").pl()

❌ NEVER - Wrong tool for heavy filtering

python

# SLOW (DuckDB not optimal for filtering)
import duckdb
result = duckdb.sql("""
    SELECT * FROM 'huge.csv'
    WHERE complex_filter = true
""").pl()

✅ CORRECT - Use Polars for filtering

python

# FAST (Polars 128x faster for filtering)
import polars as pl
result = pl.scan_csv('huge.csv').filter(pl.col('complex_filter')).collect()