Optimizing Delta Tables & Azure Monitor

Delta table maintenance

OPTIMIZE

-- Compact small files into larger ones
OPTIMIZE prod_sales.curated.daily_revenue;

-- Compact with Z-ordering on specific columns
OPTIMIZE prod_sales.curated.daily_revenue
  ZORDER BY (region, product_category);

What OPTIMIZE does:

1. Identifies files smaller than the target size (~1 GB)
2. Reads the small files
3. Writes new, larger files
4. Updates the Delta log to point to new files
5. Old small files remain until VACUUM removes them

When to run OPTIMIZE

Scenario	Frequency
Table with frequent small appends (streaming)	After each batch or on a schedule (hourly/daily)
Table with infrequent large writes	Rarely needed
After MERGE operations	After each MERGE (creates small files)

Predictive optimization

Databricks can automatically optimise tables:

-- Enable auto-optimization
ALTER TABLE prod_sales.curated.daily_revenue
  SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
  );

• optimizeWrite: coalesces small output files during writes
• autoCompact: runs OPTIMIZE automatically after writes

VACUUM

-- Remove files older than 7 days (default retention)
VACUUM prod_sales.curated.daily_revenue;

-- Remove files older than 30 days
VACUUM prod_sales.curated.daily_revenue RETAIN 30 HOURS;

-- Dry run: see what would be deleted without deleting
VACUUM prod_sales.curated.daily_revenue DRY RUN;

Safety rules:

• Default retention: 7 days (168 hours)
• Setting retention below 7 days requires disabling the safety check
• VACUUMed files cannot be recovered — time travel to deleted versions becomes impossible

OPTIMIZE	VACUUM
Compacts small files into larger ones	Deletes old, unreferenced files
Improves READ performance	Reduces STORAGE cost
Creates new files (doesn’t delete old)	Deletes old files
Safe to run anytime	Irreversible — breaks time travel for deleted versions
Run frequently for streaming tables	Run on a schedule (daily/weekly)

Azure Monitor integration

Log streaming

Stream Databricks logs to Azure Log Analytics for centralised monitoring:

1. Configure diagnostic settings on the Databricks workspace
2. Select log categories to stream:
   • Cluster events (start, stop, resize)
   • Job run events (start, complete, fail)
   • Notebook events (execution logs)
3. Destination: Log Analytics workspace

Once configured, query Databricks logs with KQL (Kusto Query Language) in Log Analytics:

// Find failed jobs in the last 24 hours
DatabricksJobs
| where TimeGenerated > ago(24h)
| where ActionName == "runFailed"
| project TimeGenerated, JobId, RunName, ErrorMessage
| order by TimeGenerated desc

Azure Monitor alerts

Configure alerts that fire when conditions are met:

Alert Condition	What It Monitors
Job failure count > 0	Any pipeline failure
Cluster CPU > 90% for 10 minutes	Resource bottleneck
DBU consumption > daily budget	Cost overrun
No job runs in 2 hours	Missed scheduled runs

Alert flow:
Databricks logs → Log Analytics → Alert Rule → Action Group → Notification
                                                (email, Teams, PagerDuty)

Ravi sets up Azure Monitor alerts at DataPulse: if any nightly ETL job fails or if daily DBU consumption exceeds the budget, the team gets an immediate Teams notification.

Question

What is the difference between OPTIMIZE and VACUUM?

Click or press Enter to reveal answer

Answer

OPTIMIZE compacts small files into larger ones (improves read performance). VACUUM deletes old unreferenced files (reduces storage cost). OPTIMIZE creates new files; VACUUM removes old ones.

Click to flip back

Question

Why is VACUUM's default retention 7 days?

Click or press Enter to reveal answer

Answer

The 7-day retention protects time travel and concurrent readers. Files younger than 7 days might still be referenced by active queries or time travel requests. Reducing below 7 days risks breaking these operations.

Click to flip back

Question

How do you stream Databricks logs to Azure Monitor?

Click or press Enter to reveal answer

Answer

Configure diagnostic settings on the Databricks workspace, select log categories (jobs, clusters, notebooks), and set the destination to a Log Analytics workspace. Query with KQL in Log Analytics.

Click to flip back

🎬 Video coming soon

Knowledge check

Knowledge Check

Mei Lin's Freshmart streaming pipeline appends thousands of small files per hour to a Delta table. Query performance has degraded significantly. What should she run?

AVACUUM to remove the small filesBOPTIMIZE to compact small files into larger onesCANALYZE TABLE to update statisticsDDROP and recreate the table

Check Answer

Knowledge Check

Dr. Sarah Okafor wants to be alerted within 5 minutes whenever any production Databricks job fails at Athena Group. The alert should go to the on-call team's Microsoft Teams channel. What should she configure?

ALakeflow Job alerts with email notificationBAzure Monitor alert rule on Databricks job failure logs with a Teams action groupCA custom notebook that checks job status every 5 minutesDDatabricks SQL alert on a dashboard

Check Answer

---

🎉 Congratulations! You’ve completed all 28 modules of the DP-750 study guide. Ready to test your knowledge? Head to the Practice Questions to prepare for exam day.