Structured Streaming retries: idempotent sink design or a transactional sink for exactly-once writes?

I'm designing a Spark Structured Streaming pipeline (assume Spark 3.4/3.5) that writes micro-batches to an external store via foreachBatch, with checkpointing and the write-ahead log enabled. The hard constraint is that when a batch fails partway and Spark retries it, the target must not end up with duplicate records.

Two documented patterns seem applicable. One is making the sink idempotent — for example upserting by primary key or writing to deterministically named partitions — so replaying a batch is harmless. The other is using a transactional sink such as Delta Lake or a per-batch JDBC transaction, where the commit only happens after the batch succeeds, relying on Spark's checkpoint-driven commit protocol for exactly-once semantics.

My understanding is that foreachBatch plus the write-ahead log alone only gives at-least-once delivery unless the sink itself is idempotent or transactional, but I'm unsure how much that holds across Spark versions and connectors.

• For a sink I control the schema of, is idempotent upsert generally sufficient, or are there retry scenarios where it still duplicates?
• Does the transactional approach add meaningful guarantees beyond idempotency that justify the extra connector complexity?
• How can I verify which semantics a given connector actually provides before relying on it in production?