The difference in behaviour between using foreachPartition and data.write.jdbc(...) after dropDuplicates() could be due to how Spark handles data partitioning and operations on partitions. When you use foreachPartition, you are manually handling the process of writing each partition to the Oracle DB. This means that the deduplication (dropDuplicates) is performed on each partition separately, and not on the entire data. This could lead to duplicates if the same record exists in different partitions. On the other hand, when you use data.write.jdbc(...), Spark's built-in JDBC writer handles the writing process. It performs the deduplication operation on the entire data before writing it to the Oracle DB, which ensures that all duplicates are removed. To ensure that duplicates are removed correctly when using foreachPartition, you could try performing the dropDuplicates operation after repartitioning the data based on the column(s) you want to deduplicate. Here's an example:

scala
val data = spark.read // ... load data
val deduplicatedData = data.repartition($"column_name").dropDuplicates("column_name")
deduplicatedData.foreachPartition { partition =>
  // Code to write each partition to Oracle DB
}

In this code, repartition($"column_name") ensures that all records with the same column_name are in the same partition. Then, dropDuplicates("column_name") can correctly remove all duplicates. However, please note that this approach could have performance implications, as repartitioning can be an expensive operation.