DuckDB over Polars DataFrames: replacement scans, parquet roundtrip, group-by parity: working versions (tested 2026-10-15)

duckdb==1.5.6
polars==2.0.0
pyarrow==26.0.0
pytest==9.1.1

from pathlib import Path

import duckdb
import polars as pl

def revenue_by_region(con: duckdb.DuckDBPyConnection, df: pl.DataFrame) -> pl.DataFrame:
    # register() is explicit; a bare table name only resolves via replacement scan in the caller's frame
    con.register("sales", df)
    return con.sql("""
        SELECT region,
               CAST(COALESCE(SUM(amount), 0) AS BIGINT) AS total,  -- SQL SUM of all NULLs is NULL; polars gives 0
               AVG(amount) AS avg_amount,
               COUNT(*) AS n                                       -- BIGINT in DuckDB
        FROM sales
        GROUP BY region
    """).pl()

def revenue_by_region_polars(df: pl.DataFrame) -> pl.DataFrame:
    return df.group_by("region").agg(
        pl.col("amount").sum().alias("total"),
        pl.col("amount").mean().alias("avg_amount"),
        pl.len().cast(pl.Int64).alias("n"),  # pl.len() is UInt32, cast to match DuckDB
    )

def parquet_roundtrip(con: duckdb.DuckDBPyConnection, df: pl.DataFrame, path: Path) -> pl.DataFrame:
    df.write_parquet(path)
    # pass the path as a bound parameter, not an f-string; convert Path to str
    return con.sql("SELECT * FROM read_parquet(?)", params=[str(path)]).pl()

import duckdb
import polars as pl
import pytest
from polars.testing import assert_frame_equal

from analytics import revenue_by_region, revenue_by_region_polars, parquet_roundtrip

@pytest.fixture
def sales():
    return pl.DataFrame({
        "region": ["eu", "us", "eu", "apac", "us"],
        "amount": [10, 20, 30, 5, 15],
    })

@pytest.fixture
def con():
    with duckdb.connect() as c:
        yield c

def test_group_by_matches_polars(con, sales):
    got = revenue_by_region(con, sales).sort("region")
    want = revenue_by_region_polars(sales).sort("region")
    assert_frame_equal(got, want)  # checks dtypes too: BIGINT vs UInt32 would fail here

def test_nulls_aggregate_like_polars(con):
    df = pl.DataFrame({"region": ["eu", "eu", "us"], "amount": [10, None, None]})
    got = revenue_by_region(con, df).sort("region")
    want = revenue_by_region_polars(df).sort("region")
    assert_frame_equal(got, want)  # all-NULL group "us": COALESCE makes total 0 like polars

def test_parquet_roundtrip_preserves_frame(con, sales, tmp_path):
    got = parquet_roundtrip(con, sales, tmp_path / "sales.parquet")
    assert_frame_equal(got, sales)

def test_filter_pushdown_matches_polars(con, sales, tmp_path):
    path = tmp_path / "sales.parquet"
    sales.write_parquet(path)
    got = con.sql(
        "SELECT region, amount FROM read_parquet(?) WHERE amount > ? ORDER BY amount",
        params=[str(path), 10],
    ).pl()
    want = sales.filter(pl.col("amount") > 10).select("region", "amount").sort("amount")
    assert_frame_equal(got, want)

def test_replacement_scan_sees_local_frame(con, sales):
    # bare names resolve to Python locals in the calling frame (no register needed)
    assert con.sql("SELECT count(*) FROM sales").fetchone()[0] == 5

[pytest]
pythonpath = .