Query with DuckDB and Fit In-Database

Fit a GAM directly on data held in DuckDB without loading it into Python memory.

DuckDBGAM reads data from a DuckDB connection in Arrow-format chunks, so the Python process never materializes the full dataset. This is well-suited to Parquet files or persistent DuckDB databases that are larger than available RAM.

Set Up a DuckDB Table

Create an in-memory DuckDB connection and populate a table with synthetic sensor readings.

import duckdb
import numpy as np
import whittaker as wk

# Open in-memory DuckDB connection
rng = np.random.default_rng(23)
conn = duckdb.connect()

# Create synthetic sensor table
conn.execute(
    "CREATE TABLE sensor AS "
    "SELECT i AS id, "
    "(i / 500.0) * 2 * pi() AS x, "
    "sin((i / 500.0) * 2 * pi()) + ? * random() AS y "
    "FROM range(50000) AS t(i)",
    [0.3],
)

<_duckdb.DuckDBPyConnection at 0x7f263899e2f0>

Fit from a Table Name

Pass the table name as a string along with the open connection. DuckDBGAM will scan the table in chunks without pulling it into Python memory.

model = wk.DuckDBGAM("y ~ s(x, k=10)").fit("sensor", conn)
model.summary()

GAM fit summary
============================================================
Formula:    y ~ s(x, k=10)
Family:     Gaussian(link='identity')
Inference:  REML
Observations: 0
Coefficients: 10

Parametric coefficients:
  Term                       Estimate    Std.Err    t value    p-value
  ------------------------ ---------- ---------- ---------- ----------
  (Intercept)                  0.1412     0.0000      0.000          1

Approximate significance of smooth terms:
  Term                        EDF Ref.df     Chi.sq    p-value
  ------------------------ ------ ------ ---------- ----------
  s(x, k=10)                 6.51      7      8.710     0.2741

Total EDF:  7.51
Scale est:  0.508334
Deviance:   25412.8907
Null dev:   25413.8092
Dev. expl:  0.0%
GCV score:  0.508409
AIC:        108070.55
BIC:        108136.77

Fit from a Query

Use fit_query() when you want to apply filters, joins, or aggregations before fitting. The full expressiveness of SQL is available at this step.

model2 = wk.DuckDBGAM("y ~ s(x, k=10)").fit_query(
    "SELECT x, y FROM sensor WHERE x < 4.0", conn
)
model2.n_rows

Predict

Prediction takes any dict of arrays (no DuckDB connection is needed after fitting).

new_data = {"x": np.linspace(0, 2 * np.pi, 100)}
preds = model.predict(new_data)
preds.values[:5]

array([0.18341876, 0.18337034, 0.18332192, 0.1832735 , 0.18322508])

Interpret

DuckDBGAM streams data through Arrow batches, keeping the Python process memory footprint proportional to chunk_size rather than to the total number of rows. It is particularly practical for Parquet files on disk: use conn and a Parquet file with duckdb.read_parquet() and the rest of the workflow is identical.