Semi-Transparent Meshes Overlap Issue

I’m trying to make a tornado mesh by using a special mesh with a transparent background texture. This is what I have achieved so far.

However, as you can see in the video the part in the back that was supposed to be covered by the part in front of it got rendered in front instead.

This is what the mesh’s normal looks like in Blender, I already flipped the normal so I don’t think this is the cause of this issue.

Then I tried using a mesh part with surface appearance instead and the problem is gone.

surface appearance
image

special mesh
image

The first issue is gone when I use surface appearance but I can’t find a way to make the mesh glow, since the first one I can set the vertex color number to be above 1 to make it glow but I can’t do the same thing with surface appearance since there’s no vertex color property and it doesn’t support emission map.

So I’m just wondering if there’s a solution to this problem. Thanks.