SQL or pandas? Push the heavy lifting to the database

SELECT * FROM events. Forty million rows cross the network into a dataframe, and the groupby, the filter and the join all happen on the laptop. It works — pandas is forgiving — but it’s slow, fragile, and one dataset away from not fitting in memory at all.

The resolution isn’t “SQL good, pandas bad.” It’s a division of labor.

The rule

Reduce where the data lives; explore where your tools live. Databases are ruthlessly optimised for scanning, filtering, joining, and aggregating enormous tables — with indexes, column pruning, and parallel execution you get for free. Pandas (and friends) are optimised for iteration speed: plotting, reshaping, feature experiments, model prep, the twenty-questions phase of analysis.

So the query’s job is to shrink the data to the unit of analysis — one row per customer, per day, per session — and the dataframe’s job is everything after. Instead of shipping raw events:

-- One row per customer: the reduction happens in the warehouse
SELECT
  customer_id,
  COUNT(*)                                         AS n_purchases,
  SUM(amount)                                      AS revenue,
  MAX(occurred_at)                                 AS last_purchase_at,
  COUNT(DISTINCT DATE_TRUNC('month', occurred_at)) AS active_months
FROM events
WHERE event_type = 'purchase'
  AND occurred_at >= DATE '2025-01-01'
GROUP BY customer_id

Forty million rows became two hundred thousand before touching the network. The pandas session that follows is fast because it starts from the right shape. That query runs as written on the sample data — a miniature events table of the same shape, where 40,000 rows come back as 752.

Signals you’ve drawn the line wrong

Too much on the laptop side:

• You SELECT * and immediately drop most columns and rows in pandas — that filter belonged in the WHERE clause, where it can also use an index.
• A pandas merge joins two large raw tables — databases join better than pandas essentially always; join in SQL, return the result.
• You wait minutes for the download, not the computation. Network transfer of raw data is pure waste; aggregate first.
• MemoryError, or the ritual of chunksize= loops re-implementing what GROUP BY does natively.

Too much on the database side:

• A 400-line SQL monster with nine CTEs implementing feature engineering, seeded splits, and pivot gymnastics nobody can review. SQL beyond a certain complexity is write-only; that logic wants to be tested Python.
• You rerun a heavy query fifty times a day varying one parameter — pull the reduced table once, iterate locally.
• Anything needing loops, regressions, or a real library: the database is the wrong place for a model, whatever the vendor’s ML extension promises.

Keep the boundary clean

Treat extraction queries as code: in files, in git, with explicit time bounds — not pasted into a notebook cell as an f-string with today’s date interpolated. A tidy pattern is one function per dataset:

def load_customer_features(conn, since: str) -> pd.DataFrame:
    sql = Path("sql/customer_features.sql").read_text()
    return pd.read_sql(sql, conn, params={"since": since})

Notebook stays readable, query stays reviewable, and when the analysis graduates to a scheduled job the extraction layer already exists.

Two closing notes. First, learn window functions — most “I had to do it in pandas” cases (previous-event deltas, per-group top-N, running totals) are one window function in SQL. Second, when local data does outgrow memory, modern single-node engines like DuckDB run SQL over parquet files right inside your Python process — the “reduce in SQL, iterate in pandas” pattern works even when there’s no server anywhere.

The split has a cost worth naming: business logic now lives in two places. A definition of “active user” that exists in a SQL file and again in a notebook cell will drift apart, and the drift surfaces as two charts that disagree in a meeting. Keep the definitions in the query layer, and let the notebook take what the query already decided.