And I always thought Konami did those the best. What I did was I took 4 sprite explosion pngs with transparent background. Made them into paper sprites. Then made a explosion actor bp and loaded all 4 sprites on top of each other and am playing them sequentially trying to give it that old arcade look from back in the day. You can see it in the enemy ships and player ship. I also added this player ship a plasma energy shield, I was thinking of the version of Asteroids where you could make a shield but not spam it, then it would have to cool down a bit once it stopped.
Nothing beats old school sprite animations imo, they have their own vibes. I always liked pre rendered aesthetics.
For you question:
Create A material and you would find a node called, flipbook. Here is a tutorial explaining it, think you can find many, you can manipulate timing in material with many things.
There are many ways that you can make pixelated or toon stylized vfx in niagara. Here is some tutorials
If you use toon style materials and generally masked blending ,then it is possiible to post process those effects with stencil in low res/ retro look.
Nothing can beat the vibes of the prebaked sprites but niagara can also feel ok, depends what you are looking for.
Let me know if it helps.
PS: In settingsset texture to Filter → Nearest with no mips
