When would you recommend to use Append effect and when Modify effect in Azure Policy?

Use Append when you want to just add additional fields to the request resource during creation or update. Append is intended for non-tag properties. For tags it is recommended to use Modify effect instead.

Use Modify when you want to add, update or remove properties or tags on a subscription or resource during create or update. Existing non-compliant resources can be remediated with a remediation task.