@jorperort 

Creating and using a staging table followed by a MERGE operation in SQL Server is considered the best practice especially when working with distributed systems like Spark

the issue often arises not from duplicates in the DataFrame itself but due to transactional or concurrency states in SQL Server combined with Spark retries. This causes inserts of records that are logically excluded by DataFrame operations but attempted at the database level because multiple distributed Spark tasks retry inserts. These conflicts typically cannot be resolved by DataFrame de-duplication alone.

Thus, the staging table plus a database-level MERGE remains the most robust solution to guarantee consistency, avoid primary key violations, and handle concurrent write attempts reliably

Databricks documentation on JDBC writes recommends routing batch writes to a staging table for subsequent MERGE:
https://docs.databricks.com/aws/en/archive/connectors/jdbc.html#write-to-staging-table-and-use-merge

controlling parallelism and avoiding retries:
https://docs.databricks.com/aws/en/archive/connectors/jdbc.html#control-parallelism-for-jdbc-queries

Summary: Unless your environment guarantees zero-retry, perfect concurrency and data uniqueness across all distributed tasks, creating a staging table followed by a controlled MERGE operation is effectively mandatory