---
description: Performance Optimization
alwaysApply: false
---

# Performance Optimization

Patterns for efficient, cost-effective data pipelines.

## Partitioning

- Partition by columns used in most query filters (usually date)
- Target 100K–10M records per partition; avoid over-partitioning
- Compact small files: `OPTIMIZE table` and set `maxRecordsPerFile`

```python
# Good: single partition column matching query patterns
df.write.partitionBy("order_date").saveAsTable("curated.orders")

# Bad: over-partitioning creates millions of tiny files
df.write.partitionBy("order_date", "customer_id", "product_id").saveAsTable(t)
```

## Query Optimization

- **Predicate pushdown** — filter on native columns; UDFs block pushdown (full scan)
- **Column pruning** — `.select()` needed columns before transforms, not after
- **Broadcast joins** — `broadcast(small_df)` for dimension tables (<100MB)
- **Aggregate before join** — reduce shuffle by aggregating first, joining second
- **Avoid unnecessary sorts** — only `orderBy` when needed; `limit` first when possible

```python
# Good: native filter pushes to storage
df.filter(F.col("order_date") > F.date_sub(F.current_date(), 7))

# Bad: UDF blocks pushdown, causes full scan
df.filter(is_recent_udf(F.col("order_date")))
```

## Caching

- Cache intermediate DataFrames reused multiple times
- Always `unpersist()` in a `finally` block
- Use `MEMORY_AND_DISK` for large datasets that may spill

## Shuffle Optimization

- Aggregate before join to reduce shuffle volume
- `coalesce(n)` before write (no shuffle); `repartition(n)` only when increasing parallelism
- Tune `spark.sql.shuffle.partitions` (default 200; use `"auto"` on Spark 3.0+)

## Z-Ordering (Delta Lake)

Co-locate data for multi-column filter queries:

```sql
OPTIMIZE curated.orders ZORDER BY (customer_id, product_id)
```

## Cost Management

- Use incremental processing; avoid full-refresh when unnecessary
- Apply data retention: `DELETE` + `VACUUM` old partitions
- Monitor pipeline compute costs and bytes scanned weekly
- Prefer `zstd` compression for better ratio

## Anti-Patterns

- Filtering on derived columns that prevent partition pruning
- Reading all columns then selecting late in the pipeline
- Caching without cleanup — memory leaks across jobs