Python for Data

When to reach for Python (vs staying in SQL)

• External APIs / sources — fetching from REST, scraping, vendor SDKs.
• Custom transformations SQL can't express cleanly — text parsing, complex string ops, statistical computations.
• Iterative algorithms — sessionization with custom logic, ML preprocessing, graph algorithms.
• One-off analysis in a notebook — exploratory, throwaway, fast feedback.
• Glue between systems — orchestration scripts, alerting, custom ETL steps.

Default principle: if SQL can do it cleanly, do it in SQL. SQL is more testable, more portable, more easily reviewed by analysts. Python is the fallback when SQL can't or shouldn't.

pandas essentials

The lingua franca. Still expected even if you prefer polars.

import pandas as pd

# Read / write
df = pd.read_csv('file.csv')
df = pd.read_parquet('file.parquet')
df.to_parquet('out.parquet', index=False)

# Selection
df[df['amount'] > 100]                           # filter rows
df[['col1', 'col2']]                             # select columns
df.loc[df['col'] == 'x', 'other_col'] = 'y'      # conditional update
df.query("status == 'active' and amount > 100")  # readable filter

# Aggregation
df.groupby('customer_id')['revenue'].sum()
df.groupby(['region', 'date']).agg(
    total_revenue=('revenue', 'sum'),
    order_count=('order_id', 'count'),
    avg_value=('amount', 'mean'),
)

# Joins
pd.merge(left, right, on='customer_id', how='left')
pd.merge(left, right, left_on='lid', right_on='rid', how='inner')

# Window-ish operations
df.sort_values(['customer_id', 'ordered_at'])
df.groupby('customer_id')['revenue'].cumsum()             # running total
df.groupby('customer_id')['revenue'].rolling(7).mean()    # 7-row moving avg
df['rank'] = df.groupby('customer_id')['revenue'].rank(ascending=False)

# Reshape
df.pivot_table(index='customer_id', columns='quarter', values='revenue', aggfunc='sum')
df.melt(id_vars=['customer_id'], var_name='quarter', value_name='revenue')

# NaN handling
df.fillna(0)
df.dropna(subset=['email'])
df['col'].isna().sum()

# Date / time
df['date'] = pd.to_datetime(df['date_str'])
df['date'].dt.month
df['date'].dt.dayofweek

polars — the modern alternative

Polars is pandas-but-faster-and-saner. Built in Rust, uses Arrow, supports lazy evaluation. Many teams are migrating to it.

import polars as pl

df = pl.read_parquet('file.parquet')

# Filter + select + aggregate in one expression chain
result = (
    df
    .filter(pl.col('status') == 'active')
    .group_by(['customer_id'])
    .agg(
        pl.col('revenue').sum().alias('total_revenue'),
        pl.col('order_id').count().alias('order_count'),
    )
    .sort('total_revenue', descending=True)
)

# Window functions feel natural in polars
df = df.with_columns(
    pl.col('revenue').sum().over('customer_id').alias('lifetime_revenue'),
    pl.col('ordered_at').rank().over('customer_id').alias('order_rank'),
)

# Lazy evaluation — query plan optimized
lazy = pl.scan_parquet('huge_file.parquet')
result = (
    lazy
    .filter(pl.col('date') >= '2024-01-01')
    .group_by('region')
    .agg(pl.col('revenue').sum())
    .collect()  # actually execute
)

Mention you've used or are aware of polars. Interview-grade fluency: "polars is the modern alternative; lazy evaluation and the expression API are nicer than pandas for production data work. For interactive exploration, pandas is still fine."

SQL inside Python

Often the cleanest pattern: do as much as possible in SQL, fetch the result, do final transformations in Python.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('snowflake://...')

query = """
SELECT customer_id, SUM(revenue) AS total_revenue
FROM orders
WHERE ordered_at >= %(start_date)s
GROUP BY customer_id
"""

df = pd.read_sql(query, engine, params={'start_date': '2024-01-01'})

Or with parameterized SQL strings — for production code, use the warehouse-specific driver (snowflake-connector-python, google-cloud-bigquery) directly.

DuckDB — SQL on Python data

Reach for DuckDB when you want SQL ergonomics against pandas/polars data, or against local Parquet files. Fast.

import duckdb
import pandas as pd

df = pd.read_csv('orders.csv')

result = duckdb.sql("""
    SELECT customer_id, SUM(revenue) AS total
    FROM df
    GROUP BY customer_id
    QUALIFY ROW_NUMBER() OVER (ORDER BY SUM(revenue) DESC) <= 10
""").df()

DuckDB queries pandas/polars DataFrames natively. Useful when you want window functions or CTEs without leaving Python.

Common analytics-engineering Python patterns

API ingestion

import requests
import pandas as pd
from snowflake.connector.pandas_tools import write_pandas

# Paginated fetch with retry
def fetch_all_pages(url, params, max_pages=100):
    rows = []
    for page in range(1, max_pages + 1):
        r = requests.get(url, params={**params, 'page': page}, timeout=30)
        r.raise_for_status()
        data = r.json()
        if not data['results']:
            break
        rows.extend(data['results'])
    return rows

rows = fetch_all_pages('https://api.example.com/v1/orders', {'since': '2024-01-01'})
df = pd.DataFrame(rows)

# Write to warehouse
write_pandas(snowflake_conn, df, table_name='RAW_ORDERS')

Deduplication in pandas

df_sorted = df.sort_values(['email', 'updated_at'], ascending=[True, False])
df_deduped = df_sorted.drop_duplicates(subset='email', keep='first')

Sessionization (when SQL is awkward)

df = df.sort_values(['user_id', 'event_ts'])
df['prev_ts'] = df.groupby('user_id')['event_ts'].shift(1)
df['new_session'] = (
    df['prev_ts'].isna()                                    # first event of a user
    | ((df['event_ts'] - df['prev_ts']) > pd.Timedelta(minutes=30))
)
df['session_id'] = df.groupby('user_id')['new_session'].cumsum()

Dates and times

df['ts'] = pd.to_datetime(df['ts'], utc=True)
df['date'] = df['ts'].dt.tz_convert('America/New_York').dt.date
df['month_start'] = df['ts'].dt.to_period('M').dt.to_timestamp()
df['day_of_week'] = df['ts'].dt.dayofweek  # 0 = Monday

✓ Check yourself

Answer from memory before opening each one.

1. Three tasks: monthly revenue by region from warehouse tables, fetching paginated results from a vendor REST API, and parsing free-text addresses with custom rules. Which do you do in SQL and which in Python, and why?

   Show answer

   Revenue by region in SQL. The API pull and the address parsing in Python. SQL is more testable, portable and reviewable by analysts, so it is the default, and Python covers external sources and logic SQL cannot express cleanly. See when to reach for Python.

2. A pandas job that worked on 2 million rows runs out of memory on 80 million. What are your options, in the order you would consider them?

   Show answer

   First ask whether the work belongs in the warehouse as SQL. If not, use polars with lazy evaluation or DuckDB, which handle larger data on one machine. pandas holds everything in memory, so it is the wrong tool at that size. See pandas and polars.

3. What does pl.scan_parquet(...).filter(...).group_by(...).collect() do differently from reading the file first and then filtering?

   Show answer

   It builds a plan and runs nothing until collect(), so the engine can push the filter into the scan and read only the rows and columns needed. Eager reading loads everything before it filters. See polars.

4. In the pandas sessionization snippet, a user has events at 10:00, 10:10 and 12:00. What are the new_session flags and the session ids?

   Show answer

   True, False, True, and ids 1, 1, 2. The first event is flagged by prev_ts.isna(); comparing a missing timestamp gives False rather than missing, so filling the comparison afterwards cannot flag it. See common patterns.

5. Event timestamps are stored in UTC. You need daily revenue by New York date. A colleague truncates to the date first and then converts. What goes wrong?

   Show answer

   Truncating in UTC assigns evening New York events to the next UTC day, so revenue lands on the wrong date. Convert to the target time zone first, then take the date. See dates and times.

6. When would you pick DuckDB over pandas for a task, and when not?

   Show answer

   Pick it when SQL is the clearer way to express the work (windows, CTEs, QUALIFY) over DataFrames or local Parquet, or when data outgrows memory. Skip it when the step is simple row-wise manipulation already clear in pandas. See DuckDB.

Takeaway

• Default to SQL when it can do the job cleanly: it is more testable, portable and reviewable. Python is for external sources, custom parsing and iterative logic.
• pandas is the expected common tongue but lives in memory, so past roughly ten million rows push work to the warehouse or move to polars or DuckDB.
• Polars lazy evaluation lets the engine optimise the plan before reading data.
• The cleanest hybrid does the heavy work in SQL and finishes in Python.
• DuckDB gives you SQL, including windows and QUALIFY, over DataFrames and Parquet files.
• Sessionization, deduplication, API ingestion and time zones are the patterns to know cold; convert time zones before truncating to a date.

Scripts like these need something to run them on a schedule, which is the subject of the next chapter. → Orchestration

Further reading

• User Guide — pandas documentation, accessed 2026. GroupBy, merge, reshaping and time-series behaviour.
• Polars user guide — Polars project, accessed 2026. The expression API and lazy evaluation.
• Python API — DuckDB documentation, accessed 2026. Running SQL over pandas and polars DataFrames.
• QUALIFY Clause — DuckDB documentation, accessed 2026. Filtering on window results, as used in the chapter's example.