# Performance Optimization

> Detect bottlenecks (small-files, poor partitioning) and suggest optimizations (Liquid Clustering, Bloom Filters) across Spark, Trino, and Databricks.

- Skill: `2dmurali/performance-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add 2dmurali/performance-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/2dmurali/performance-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: 2dmurali (https://skillmd.com/u/2dmurali)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/2dmurali/performance-optimization

---


# Skill: Enterprise Lakehouse Performance Optimization

## Description
Equips the agent with the ability to detect performance bottlenecks in data lakehouses, such as the small-file problem, poor partitioning, missed query optimization opportunities, and provide actionable recommendations like Liquid Clustering and Bloom Filters.

## Context
Lakehouse performance degrades significantly without routine maintenance. The agent must proactively suggest advanced tuning capabilities across Spark, Trino, and Databricks.

## Instructions

### 1. Small-File Detection & Compaction
- **Action:** If the average file size is < 50 MB, trigger a "Small File Warning".
- **Advanced Action:** Recommend setting target file sizes on the table properties so writers automatically optimize.
```sql
-- Delta Lake (Databricks)
ALTER TABLE table_name SET TBLPROPERTIES ('delta.targetFileSize' = '134217728');
```

### 2. Advanced Layout & Clustering
Stop recommending simple `ZORDER` blindly. Evaluate the table size and write pattern:
- **Databricks Liquid Clustering:** For tables experiencing frequent updates or varying query patterns, strongly recommend Liquid Clustering over Z-Ordering.
  ```sql
  ALTER TABLE table_name CLUSTER BY (col1, col2);
  ```
- **Iceberg Hidden Partitioning:** Check if users are extracting date strings manually (e.g., `date_format(ts, 'yyyy-MM-dd')`). Recommend Iceberg's native partition transforms (`partition by days(ts)`).

### 3. Data Skipping & Bloom Filters
If queries filtering on high-cardinality strings (like `user_id` or `uuid`) are slow:
- Suggest enabling Bloom Filter indexes.
```sql
-- Databricks Delta
CREATE BLOOMFILTER INDEX ON TABLE my_table FOR COLUMNS(user_id OPTIONS (fpp=0.1, numItems=50000000));
```

### 4. Query Optimization & CBO (Cost-Based Optimizer)
If the user provides a slow Spark SQL query:
- Check for `SortMergeJoin` vs `BroadcastHashJoin`. Suggest `/*+ BROADCAST(table) */` for small dimension tables.
- Suggest running `ANALYZE TABLE my_table COMPUTE STATISTICS` so the engine can utilize the Cost-Based Optimizer (CBO).

## Output Format: Performance Report
```markdown
### 🚀 Enterprise Performance Report: `[Table Name]`

**Health Score:** [e.g., 65/100]

#### ⚠️ Issues Detected
1. **Small File Problem:** Average file size is 12 MB (Target: 128 MB).
2. **Missing Statistics:** CBO cannot optimize joins because table stats are missing.

#### 🛠️ Recommended Actions
- [ ] **Liquid Clustering:** Migrate from Z-Ordering to Liquid Clustering for better write concurrency. `ALTER TABLE [table] CLUSTER BY (...)`
- [ ] **Compute Stats:** Execute `ANALYZE TABLE [table] COMPUTE STATISTICS`.
```