I need a straight answer on the Snapdragon cpus for Windows

I have been waiting to buy a Windows tablet for quite a while for various reasons. I am finally ready to consider buying one and almost all of the available ones are now exclusively made with Snapdragon chips.

I know that snapdragon chips, especially in their early release as windows CPUs had and incredibly large suite of compatibility issues, And this is a very big problem for me as I use a large amount of more niche programs and I need to be able to use programs that I have designed for Windows be able to be run on Windows.

I cannot get any straight answers online about the compatibility and I always get told that it has an emulation software if I have any compatibility issues by reviewers and the companies themselves.

If I use programs like aseprite with a ton of scripts and plugins will it work? Will complex art apps with large live brushes like rebelle need to emulate to completely stifle performance and make using them impossible? Will an open source program like krita work, or an incredibly niche software like GrafX2 work?

I'm also questioning whether the NPU that's touted on every single advertisement of them will do anything at all if I don't use A.I. or is it just gonna be sitting there pretty and doing nothing? Will there be a significant drawback in performance in the main chip that they excused by putting power in the NPU?

I came here because I cannot find any real answers in person or online elsewhere and I'd really appreciate if anyone has any knowledge or experience on this, thank you so much :)