The need seems quite obvious when mixing LTR and RTL languages, e.g. embed an American company or English phrase in a RTL language text.

Why does it need to overwrite or obscure existing characters?

The Right-To-Left Override (U+202E) does not obscure nor overwrite existing characters. It is an instruction to switch the text direction to right-to-left like for Arabic and Hebrew.

In the article it is shown how this can be misused to show executable files as a different file type:

    file[U+202e]fdp.exe shown as fileexe.pdf
        ~~~~~~~~^^^^^^^
           |       |--- read this right-to-left, i.e. exe.pdf     
           |----------- this is the Unicode character Right-To-Left Override

It certainly obscures and overwrites. When you replace "fdp.exe" by "exe.pdf", you're obscuring and overwriting the leading "f" by "e", then the "d" by "x" and so on.

It doesn't overwrite or obscure, it switches rendering direction. E.g. \u202E\u41\u42\u43 starts outputting from the right: \u41 from the right, then 42, then 43. That's CBA, altough in normal English you'd render that as ABC.