I tend to agree. SQL may have been harder to write in the past (worse autocomplete than pandas/polars), but now that AI is writing the code, SQL is usually much easier to read. So DuckDB is another interesting alternative to pandas.
I find that SQL is only easier to read with minimal abstraction, but as soon as the project gets bigger SQL becomes an unwieldy island of different that has served its purpose after we’re done with reading/writing the data.
import polars as pl
# 1. Base Dataset
lazy_df = pl.LazyFrame(
{
"store_id": ["S01", "S02", "S03", "S04", "S05"],
"revenue": [5000.0, 2400.0, 15000.0, 900.0, 3200.0],
"margin": [0.45, 0.30, 0.60, 0.15, 0.50],
"tx_count": [120, 45, 300, 20, 85],
"returns": [5, 12, 45, 2, 8],
}
)
# 2. Define Layer Abstractions
def get_kpi_layer() -> list[pl.Expr]:
return [
(pl.col("returns") / pl.col("tx_count")).alias("return_rate"),
(pl.col("revenue") / pl.col("tx_count")).alias("avg_order_value"),
]
def get_threshold_layer(thresholds: dict[str, list[float]]) -> list[pl.Expr]:
return [
(pl.col(col) > limit).alias(f"is_{col}above{int(limit)}")
for col, limits in thresholds.items()
for limit in limits
]
def get_interaction_layer(numeric_cols: list[str]) -> list[pl.Expr]:
return [
(pl.col(a) / (pl.col(b) + 1e-5)).alias(f"ratio_{a}per{b}")
for i, a in enumerate(numeric_cols)
for b in numeric_cols[i + 1 :]
]
def get_segmentation_layer() -> list[pl.Expr]:
return [
pl.when(pl.col("margin") > 0.4)
.then(pl.literal("High"))
.otherwise(pl.literal("Low"))
.alias("margin_profile")
]
# 3. Consolidate and Execute Single Graph Pass
thresholds = {"revenue": [1000.0, 5000.0, 10000.0], "tx_count": [50, 100, 200]}
numeric_cols = ["revenue", "margin", "tx_count", "returns"]
expr_pool = [
*get_kpi_layer(),
*get_threshold_layer(thresholds),
*get_interaction_layer(numeric_cols),
*get_segmentation_layer(),
]
final_df = lazy_df.with_columns(expr_pool).collect() WITH raw_data AS (
SELECT * FROM (
VALUES
('S01', 5000.0, 0.45, 120, 5),
('S02', 2400.0, 0.30, 45, 12),
('S03', 15000.0, 0.60, 300, 45),
('S04', 900.0, 0.15, 20, 2),
('S05', 3200.0, 0.50, 85, 8)
) AS t(store_id, revenue, margin, tx_count, returns)),
base_data AS (
SELECT
store_id,
revenue,
margin,
CAST(tx_count AS DOUBLE) AS tx_count,
CAST(returns AS DOUBLE) AS returns
FROM raw_data
)
SELECT
store_id,
revenue,
margin,
CAST(tx_count AS BIGINT) AS tx_count,
CAST(returns AS BIGINT) AS returns,
-- KPI Layer
returns / tx_count AS return_rate,
revenue / tx_count AS avg_order_value,
-- Threshold Layer (matching original alias names)
revenue > 1000.0 AS is_revenueabove1000,
revenue > 5000.0 AS is_revenueabove5000,
revenue > 10000.0 AS is_revenueabove10000,
tx_count > 50 AS is_tx_countabove50,
tx_count > 100 AS is_tx_countabove100,
tx_count > 200 AS is_tx_countabove200,
-- Interaction Layer (preserving exact numeric formula & aliases)
revenue / (margin + 1e-5) AS ratio_revenuepermargin,
revenue / (tx_count + 1e-5) AS ratio_revenuepertx_count,
revenue / (returns + 1e-5) AS ratio_revenueperreturns,
margin / (tx_count + 1e-5) AS ratio_marginpertx_count,
margin / (returns + 1e-5) AS ratio_marginperreturns,
tx_count / (returns + 1e-5) AS ratio_tx_countperreturns,
-- Segmentation Layer
CASE WHEN margin > 0.4 THEN 'High' ELSE 'Low' END AS margin_profile
FROM base_data;And now write it such that all the conditions and transformations are injected into the string (somehow) rather than written in explicitly. Much worse.