SQL vs Pandas: The Friendly Rivalry
Data people tend to have a home language. Analysts who grew up in business intelligence think in SQL. Analysts who grew up in Python think in pandas. Each side believes the other is doing it the hard way. Both are partly right.
The same question, twice
"Revenue per month for paid orders":
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(total) AS revenue,
COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;(orders
.query("status == 'paid'")
.groupby(orders["created_at"].dt.to_period("M"))
.agg(revenue=("total", "sum"), orders=("id", "count")))Same logic. Filter, group, aggregate. Once you see the mapping, switching languages is mostly vocabulary.
When SQL should win
When the data is big. The database is built to scan, filter and aggregate millions of rows near the data. Don't download 40 million rows over the network to compute one number.
When the result is shared. A SQL view or a dbt model can be reused by dashboards, other analysts and other tools. A pandas cell lives in one notebook.
When joins are complex. Databases are extraordinarily good at joins, with indexes and query planners built for exactly that.
When pandas should win
When the logic gets fiddly. Rolling windows with custom rules, reshaping, string parsing, complex conditional logic. Possible in SQL, often nicer in Python.
When you need the Python ecosystem. Statistics, machine learning, plotting, calling APIs. Once data is in a DataFrame, the whole toolbox is one import away.
When exploring. Quick, iterative "what if I look at it this way" analysis on a manageable sample feels faster in a notebook.
The best of both
The pattern I use most:
- Do heavy filtering, joining and aggregation in SQL.
- Pull the much smaller result into pandas.
- Do the fiddly logic, modelling and visualisation in Python.
import pandas as pd
import sqlalchemy as sa
engine = sa.create_engine(DATABASE_URL)
monthly = pd.read_sql("""
SELECT DATE_TRUNC('month', created_at) AS month, region, SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY 1, 2
""", engine)
pivot = monthly.pivot(index="month", columns="region", values="revenue")
pivot.pct_change().plot()Tools like DuckDB blur the line even more: you can run fast SQL directly on Parquet files or even on pandas DataFrames, right inside Python.
Learn both
If you're early in your career, learn SQL properly. It has outlived dozens of trendy tools, it's in almost every data job description, and it's the language your data actually lives in. Then learn pandas for everything SQL makes awkward. The rivalry is friendly because the winners are the people who speak both.