That screenshot is from MarkV WINQUAKE variant which renders like old software-mode WinQuake (or original DOS Quake) did - in 256 colors and no support for advanced things that came later. I made it just to demonstrate how these things might look.
The regular variant of Mark V uses DirectX 9 and renders these transparent textures just fine. It has some useful built-in tools for mappers, as shown in this video from 2:50 -
https://www.youtube.com/watch?v=dMOF3l2MtlI
I also quickly tried DarkPlaces, it supports transparency, and DirectQ - it does also, but not in all the cases, I don't know the details.
And these days I'm not sure there are any other frequently used sourceports, for single player at least. Well, there's vkQuake but it does everything QuakeSpasm does, because it was based on it.