Pandas vs Traditional Data Engineering Tools

What Pandas Handles Well:

- Load and clean small to medium data:
  df = pd.read_csv("sales_2024.csv")

- Explore datasets quickly:
  df.describe(), df.groupby("region").sum()

- Prepare data for ML:
  X = df.drop(columns=["label"]).values

- Prototype a transform:
  df["revenue"] = df["price"] * df["qty"]

- Ad-hoc analysis:
  df[df["revenue"] > 10000].groupby("region").size()

Typical size:
- Up to ~5-10 million rows on a laptop.
- Under 16GB RAM.

Very common in:
- Data analyst roles
- Data science notebooks
- Initial exploration before productionising

When Pandas Breaks:

1. Data doesn't fit in memory
   - df = pd.read_csv("1B_rows.csv") → MemoryError
   - 50GB CSV on a laptop? Won't work.

2. You need scheduling
   - Pandas has no built-in scheduler.
   - You'd use cron or Airflow around it.

3. You need to run across a team
   - Notebooks don't scale operationally.
   - Handoffs are messy.

4. You need lineage and monitoring
   - "Which CSV did this come from?"
   - Pandas doesn't track lineage.

5. You need speed on big data
   - A single-thread Python loop on 100M rows
     can take hours.
   - Spark does it in minutes across a cluster.

Pandas is a great tool — but it's not a
production data platform.

SQL vs Pandas — Same Question, Different Tools:

Pandas:
  df = pd.read_csv("orders.csv")
  result = (df[df["status"] == "paid"]
              .groupby("region")["revenue"]
              .sum()
              .sort_values(ascending=False))
  print(result.head(10))

SQL:
  SELECT region, SUM(revenue) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY region
  ORDER BY revenue DESC
  LIMIT 10;

Trade-offs:
- Pandas is great for exploration.
- SQL is great for repeatable production queries.
- SQL queries live in version control (dbt).
- SQL pushes computation to the warehouse —
  not to your laptop.

When to Choose SQL:

1. Data lives in a warehouse
   - Snowflake, BigQuery, Redshift, Databricks.
   - Pulling 100GB into Pandas is a bad idea.

2. You need shared, repeatable queries
   - Version-controlled SQL (dbt models).
   - Scheduled reports.

3. You need to run on a team
   - SQL is a shared language for analysts +
     engineers.
   - Documentation lives with the query.

4. Performance matters
   - Warehouses optimise SQL for speed.
   - Columnar storage = fast aggregations.

When Pandas still wins:
- Quick ad-hoc analysis on a small CSV.
- Complex transforms (feature engineering).
- Preparing data for ML training.

Best of both:
- SQL for extraction and aggregation.
- Pandas for model-specific transforms.

Pandas (single laptop, small data):

import pandas as pd
df = pd.read_parquet("orders.parquet")
result = df[df["status"] == "paid"].groupby("region")["revenue"].sum()

Spark (cluster, big data):

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("orders").getOrCreate()
df = spark.read.parquet("s3://bucket/orders/")
result = (df.filter(df.status == "paid")
            .groupBy("region")
            .sum("revenue"))

Same idea:
- Group by a key, sum a column.
- Spark splits work across a cluster.

The workflow many teams use:
1. Develop the logic in Pandas on a small sample.
2. Port it to Spark for production scale.
3. Use tools like Polars for speed on bigger-than-Pandas-but-not-Spark data.

When to Move to Spark:

You should consider Spark when:

□ Data > 50GB or doesn't fit in RAM.
□ Processing takes > 1 hour on a laptop.
□ You need distributed joins.
□ You need streaming (Structured Streaming).
□ Data is already in a data lake (S3/HDFS).

You should stay in Pandas when:

□ Data fits in memory easily.
□ Analysis is exploratory.
□ Team is small and doesn't have Spark infra.
□ Turnaround time is more important than scale.

Modern alternative: Polars
- Faster than Pandas on a single machine.
- No cluster needed.
- Good middle-ground for 50GB-scale work.

Spark alternatives for scale:
- Databricks (managed Spark)
- AWS EMR, Google Dataproc, Azure Databricks

Airflow — orchestration, not computation:

from airflow import DAG
from airflow.providers.docker.operators.docker import DockerOperator
from datetime import datetime

with DAG(
    dag_id="daily_sales_pipeline",
    start_date=datetime(2026, 1, 1),
    schedule="@daily",
    catchup=False,
) as dag:

    extract = DockerOperator(
        task_id="extract",
        image="myorg/extract:latest",
    )

    transform = DockerOperator(
        task_id="transform",
        image="myorg/dbt_run:latest",
    )

    load = DockerOperator(
        task_id="load_warehouse",
        image="myorg/load:latest",
    )

    extract >> transform >> load

Airflow runs the schedule, handles retries, and alerts on failure.
The actual compute happens inside the containers.

Pipeline Patterns:

Classic ETL:
1. Extract → pull from source (Airflow triggers)
2. Transform → dbt or Spark job
3. Load → warehouse

Modern ELT:
1. Extract → Fivetran/Airbyte loads raw data
2. Load → into warehouse (raw schema)
3. Transform → dbt models inside warehouse
4. Orchestrate → Airflow runs dbt

Where Pandas fits:
- Ad-hoc exploration inside a task.
- Small data sources.
- Feature engineering for ML.

Where Pandas doesn't fit:
- Multi-GB extractions.
- Long-running transforms.
- Distributed aggregations.

The right mental model:
- Airflow = schedule.
- dbt = transform (SQL).
- Spark = big data (when needed).
- Pandas = explore / prototype.