SQLAlchemy and Superset for DuckDB at scale: pandas, Polars and PyTorch

A data scientist's first hour on a new dataset is rarely spent on the data. It goes on access: an extract request, a CSV that is already a week stale, a bucket path someone pastes in Slack, a local copy that quietly becomes the version of record. The analysis is the easy part. Getting to a governed, current table from a Python process is the part that takes a week.

thunderduck-sqlalchemy removes that hour. It is a SQLAlchemy dialect and PEP 249 driver for Thunderduck, published on PyPI, so every tool in the Python data stack that already speaks SQLAlchemy can read your lakehouse directly — pandas, Polars, PyTorch, Jupyter, Streamlit, Airflow, Dagster, Great Expectations. And because Apache Superset speaks SQLAlchemy too, the same connection string turns Thunderduck into the engine behind a full BI tool.

No extract, no local copy, no second version of the truth. The notebook queries the same catalog the dashboard does.

Install it, connect to it

It installs on Python 3.10+ and works with both SQLAlchemy 1.4 and 2.x — deliberately, because Superset still pins 1.4 while your notebook environment is almost certainly on 2.x. Create an API token in the console under Settings → API Tokens (it starts with tdk_ and is shown once), and that is the whole setup:

pip install thunderduck-sqlalchemy

import os

from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool

# Keep the token in the environment, never in the notebook.
engine = create_engine(
    f"thunderduck://:{os.environ['TD_TOKEN']}@api.thunderduck.ai/",
    poolclass=NullPool,   # a connection holds no server-side state, so pooling buys nothing
)

There is no driver to compile, no JVM, no ODBC manager, no tunnel to a warehouse VPC. Statements go over HTTPS to Thunderduck's API, run as isolated jobs against your data where it already lives, and page back as rows.

What a data scientist actually gets out of it

• No extract step. You read the table, not last Tuesday's copy of it. Federated queries mean a single SELECT can join Iceberg or Parquet in your bucket with a live PostgreSQL or MySQL replica — the join a notebook usually fakes with two DataFrames and a merge.
• Push the heavy lifting down. Filtering, joining and aggregating happen on the engine, next to the data; only the result travels. A GROUP BY that scans 200 GB returns a few thousand rows to your laptop.
• The governance is the connection, not a policy document. Your token is scoped and revocable, every query lands in query history with its plan, timings and cost, and an over-budget scan is checked before it runs rather than billed after.
• Reflection is free. Browsing schemas, tables and columns reads Thunderduck's catalog API rather than issuing SQL, so inspect(engine) and DataFrame-shaped discovery cost nothing and start no compute.
• Cancel means cancel. Stop a runaway query from Superset or your own code and the underlying job is genuinely killed — you stop paying for it at that moment.
• The same catalog your agents see. An MCP client and your notebook resolve the same tables under the same guardrails, so a model's answer and an analyst's answer come from the same place.

One naming detail worth knowing up front: Thunderduck names tables catalog.schema.table (flat-file catalogs) or catalog.table (Iceberg/Nessie catalogs), while SQLAlchemy only models two levels. So the whole dotted prefix is the schema — Table('lego_sets', md, schema='lego.public') for a three-level name, schema='thunder_duck_demo' for a two-level one.

pandas: read_sql and you are done

pandas has spoken SQLAlchemy for years, so there is no Thunderduck-specific API to learn. Hand it the engine and a query:

import pandas as pd

REVENUE_BY_SEGMENT = """
SELECT
  c.segment,
  date_trunc('month', o.order_ts) AS month,
  count(*)                        AS orders,
  sum(o.amount)                   AS revenue
FROM lake.raw.orders             AS o    -- Parquet in S3
JOIN pg_replica.public.customers AS c    -- live PostgreSQL replica
  ON c.id = o.customer_id
WHERE o.order_ts >= DATE '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2
"""

df = pd.read_sql(REVENUE_BY_SEGMENT, engine)

df.pivot(index="month", columns="segment", values="revenue").plot(
    title="Monthly revenue by segment",
)

That query scans the lake and the replica together and returns a few dozen rows. The equivalent notebook without Thunderduck is an export job, a CSV, a psycopg2 connection and a merge you have to keep in sync with two schemas.

Parameters are supported, and you should use them: the driver renders bound values into SQL with strict escaping and refuses types it does not recognise, which is exactly the safety net string formatting does not give you.

from sqlalchemy import text

df = pd.read_sql(
    text(
        "SELECT order_id, amount, order_ts "
        "FROM lake.raw.orders "
        "WHERE segment = :segment AND order_ts >= :since "
        "ORDER BY order_ts DESC LIMIT 1000"
    ),
    engine,
    params={"segment": segment_from_user_input, "since": "2026-06-01"},
)

Polars: the same query, a faster frame

If your stack has moved to Polars, nothing changes on the Thunderduck side — read_database takes a SQLAlchemy connection, so the engine object above is all it needs:

import polars as pl

with engine.connect() as conn:
    df = pl.read_database(query=REVENUE_BY_SEGMENT, connection=conn)

top = (
    df.group_by("segment")
      .agg(pl.col("revenue").sum().alias("revenue"))
      .sort("revenue", descending=True)
)
print(top)

The division of labour is the useful part. Thunderduck does the distributed, expensive half — scanning object storage, joining across systems, aggregating — and Polars does the fast in-memory half on a result set that now fits comfortably in RAM. Neither tool is being asked to do the thing it is bad at.

PyTorch: from lakehouse table to DataLoader

Training data is where the local-copy habit does the most damage: a feature set gets exported once, lands in someone's home directory, and six weeks later nobody can say which snapshot the production model was trained on. Reading features straight from a governed table fixes the provenance problem for free — the query is the lineage.

import polars as pl
import torch
from torch.utils.data import DataLoader, TensorDataset

FEATURES = """
SELECT
  tenure_months,
  monthly_spend,
  support_tickets,
  discount_rate,
  churned
FROM analytics.gold.customer_features
WHERE snapshot_date = DATE '2026-08-01'
"""

with engine.connect() as conn:
    df = pl.read_database(query=FEATURES, connection=conn)

x = torch.tensor(df.drop("churned").to_numpy(), dtype=torch.float32)
y = torch.tensor(df["churned"].to_numpy(), dtype=torch.float32).unsqueeze(1)

loader = DataLoader(TensorDataset(x, y), batch_size=256, shuffle=True)

model = torch.nn.Sequential(
    torch.nn.Linear(x.shape[1], 32),
    torch.nn.ReLU(),
    torch.nn.Linear(32, 1),
)
loss_fn = torch.nn.BCEWithLogitsLoss()
optimizer = torch.optim.Adam(model.parameters(), lr=1e-3)

for epoch in range(10):
    for xb, yb in loader:
        optimizer.zero_grad()
        loss = loss_fn(model(xb), yb)
        loss.backward()
        optimizer.step()
    print(f"epoch {epoch}  loss {loss.item():.4f}")

Two honest caveats, because they change how you write the query. First, every statement runs as an isolated job, so expect seconds before the first row — this is built for analytics, not for a tight per-batch fetch inside the training loop. Load the feature set once, as above. Second, result sets are row-capped: a page has a ceiling and so does a stored result. Aggregate and filter in SQL rather than pulling raw rows.

For a training set genuinely too large to page back, do what you would do anyway: materialize it once with CREATE TABLE AS into your own bucket, and let PyTorch read those Parquet files directly. Thunderduck built the table, your GPU box reads it at disk speed, and the definition of the feature set still lives in version-controlled SQL rather than in a notebook cell.

Worth remembering what the driver will not do: it is read-only. No DML, no DDL, no transactions — commit() and rollback() are no-ops. Writes belong in a scheduled job or a dbt model, not in the middle of an experiment.

Superset: the same URL, now with dashboards

Because it is a real SQLAlchemy dialect rather than a notebook helper, anything that connects to a database through SQLAlchemy can use it — and the most useful example of that is Apache Superset. Installing the package into your Superset image is the entire integration: it registers a Superset database engine spec through an entry point, so there is no configuration file to edit.

# In your Superset image (or superset-worker), one line:
pip install thunderduck-sqlalchemy

# Then in the UI:
#   Settings -> Database Connections -> + Database -> Other
#   SQLAlchemy URI:
thunderduck://:[email protected]/

From there Superset behaves exactly as it does on any warehouse. SQL Lab browses your catalogs and runs ad-hoc SQL; save a query as a virtual dataset and build charts on it; time grains, LIMIT handling and column types are correct because the engine spec inherits DuckDB's — which is the SQL dialect Thunderduck actually executes. Superset's stop button is wired to a real cancel endpoint, so killing a slow chart kills the job behind it.

What that combination is worth: BI on open files in your own storage, with no warehouse to load them into first. Analysts get a self-service tool they already know, ad-hoc exploration is charged in query-seconds instead of idle compute, and every chart refresh appears in Thunderduck's Monitoring page with its plan and cost — so the answer to which dashboard costs us the most is a query, not a guess.

One engine, three audiences

The point of shipping a dialect rather than a bespoke Python client is that there is nothing Thunderduck-specific to learn, and nothing Thunderduck-specific to unlearn if you leave. A data scientist reads a DataFrame. An analyst builds a dashboard. An agent asks over MCP. Three tools, one catalog, one set of guardrails, one bill — and the data never moved.

The package is on PyPI as thunderduck-sqlalchemy. If you are wiring it into something we have not tried yet — Dagster, Metabase via SQLAlchemy, a feature store — tell us what broke and we will fix it.