spark.sql.shuffle.partitions default of 200, how do you actually tune it

Start with the execution plan, numbers beat guesses.

Yes, the partial index was the win for us too.

In our case the root cause was an implicit cast preventing pushdown.

If you're on Spark 3.x with AQE enabled, spark.sql.adaptive.coalescePartitions.enabled lets Spark pick a reasonable number after the fact, which is more robust than one fixed static setting across jobs of varying size.

Note that merge on Delta still needs unique keys defined correctly.

Rule of thumb: target 100 to 200MB per shuffle partition. Look at the shuffle write size for your stage in the Spark UI and divide by that target to get a partition count.