How to make SCD type 2 example concurrency safe?

[UlrikChristensenGit]

On the documentation site, there is an example on how to make SCD type 2. However, the example is not concurrency safe: Target table may be changed between making the stagedUpdates dataset and using the merge statement. I cannot figure out, if there is a way of making this operation completely conrrency safe. Any ideas?

[nanjeshramesh]

Delta doesn't need you to manually snapshot isolate this, it catches it at commit time instead. When the merge commits, Delta's conflict checker compares the files your transaction read against anything a concurrent writer committed while you were building stagedUpdates. If there's overlap, the commit fails with ConcurrentAppendException (a DeltaConcurrentModificationException subtype) rather than silently applying against stale data.

So the race doesn't corrupt anything, it just means you have to handle the exception. The pattern is a retry loop around the whole read plus merge, not just the merge call, since stagedUpdates itself was built off state that's now stale:

def runMergeWithRetry(maxAttempts: Int = 3): Unit = {
var attempt = 0
while (attempt < maxAttempts) {
try {
val stagedUpdates = buildStagedUpdates()
stagedUpdates.merge(...)
return
} catch {
case _: ConcurrentAppendException if attempt < maxAttempts - 1 =>
attempt += 1
}
}
}

There's no way to make a single non retried attempt fully race free, since another writer can always commit in that gap. What Delta gives you is the guarantee that it'll tell you when that happened instead of letting it through.