Pandas vs Polars vs DuckDB vs SQL — When to Reach for Each

# Goal: monthly revenue from a 5GB Parquet file. Four legitimate ways:

# 1. Pandas — fits in memory, easiest for someone who knows Pandas
import pandas as pd
df = pd.read_parquet('orders.parquet', columns=['order_date', 'amount_usd'])
pandas_result = (df.assign(month=lambda d: d['order_date'].dt.to_period('M'))
                   .groupby('month', as_index=False)['amount_usd'].sum())

# 2. Polars (lazy) — streams, doesn't load whole file
import polars as pl
polars_result = (pl.scan_parquet('orders.parquet')
                   .group_by(pl.col('order_date').dt.truncate('1mo').alias('month'))
                   .agg(pl.col('amount_usd').sum())
                   .sort('month')
                   .collect())

# 3. DuckDB — SQL directly on the file, no DataFrame loaded at all
import duckdb
duck_result = duckdb.sql('''
    SELECT date_trunc('month', order_date) AS month,
           SUM(amount_usd) AS revenue
    FROM 'orders.parquet'
    GROUP BY 1 ORDER BY 1
''').to_df()

# 4. Pure SQL in a real warehouse — leave the data where it lives
warehouse_sql = '''
    SELECT date_trunc('month', order_date) AS month, SUM(amount_usd) AS revenue
    FROM analytics.orders
    GROUP BY 1 ORDER BY 1
'''  # run via your warehouse client; let dbt own the file.