Going head down straight into real time VFX is going to be hard. Unlike almost any other discipline in video games it’s the one that seemingly requires you to learn everything possible before you can accomplish anything. You need to know all of the technical aspects and tools of the engine you’re using, to understand all the parts of how real time rendering works, how to write shaders, how to write code, how to use 2D art applications, how to use 3d modeling tools, how to use complex fluid sims and animation tools, how to use high end video editing software, everything.
Here’s a tip; You don’t need to know most of that.
You need to know enough to make the effect you want, or something close enough to it. Then go make it. Someone with Gimp and Unity’s Shuriken or Unreal’s Cascade with no additional coding can make beautiful things. Heck, even if you don’t have any art skills of your own you can download some effects from the asset stores and use the textures from those. Will it be as amazing as something you see from a AAA game? Probably not, and that’s okay. You’re not going to be an expert overnight. My experience has been most of the top real time VFX artists have been at this for years, decades even. I’ve been doing it for 15 years and am always learning new things.
Don’t try to learn everything, just try to learn one new thing every so often and slowly build up your bag of tricks. Ever effects artist is going to have their own tricks they like or know well, and how many they know is almost purely a metric of how long they’ve been doing it. When you look at a big AAA game, realize even a simple effect isn’t likely the work of one person, but of many individuals building off each other. Someone might be an expert at shaders, another of fluid dynamics, and another is someone just like you who might have just enough knowledge to use the basic tools and is building off of the others’ work.
Also, your comment about most tutorials not being for real time VFX … that’s wrong, they’re all tutorials for real time VFX. Any tutorial relevant to video game dev is another’s tool. It’s kind of up to you to figure out how those tools work best for you.