Cost & Performance Optimisation
Layout before hardware
File layout dominates. Small files inflate metadata and task counts; fix with OPTIMIZE (compaction) and, where appropriate, VACUUM with a retention that respects readers. Partitioning works for low-cardinality, frequently filtered columns; high-cardinality partitioning produces the small-file problem. Liquid clustering supersedes Hive-style partitioning and Z-ORDER for most new tables - you declare CLUSTER BY and the engine maintains layout, and it can be changed without rewriting history.
Compute choices
Job clusters (ephemeral, cheaper DBUs) for pipelines; all-purpose clusters for interactive work. Autoscaling helps spiky batch, not steady state; Photon accelerates SQL/Delta scan-heavy workloads but not Python UDF-heavy code (a favourite trap); serverless SQL warehouses bill per query with fast start. Spot instances save money but can be reclaimed mid-shuffle - avoid for critical streaming unless you accept retries.
Query-level levers
Adaptive query execution coalesces shuffle partitions and splits skewed joins; skew hints and broadcast hints target specific joins. Broadcast the small side; never broadcast a table that cannot fit on the driver/executor. Cache only when a DataFrame is reused many times in one session. Predictive optimisation can auto-compact and auto-analyse managed tables.
Rule of thumb: before adding nodes, answer three questions - is the table clustered/compacted, is the small side of the join being broadcast, and are we scanning columns/partitions we do not need?