I'm going to go with human, but I don't know exactly how that decision scene happens, so I may change my mind if it means outright embracing revenge.
Cole will be spirit-enough as long as he remembers his existence in the Fade and likes to do his 'helping' of others. This is interesting, as it furthers the (to many, uncomfortable) truth that much of the 'Fade' and 'Thedas' and Beyond that, is but a state of mind at the end of the day. If we consider things immutable, especially if we're created (by the Maker? heh) to consider things in such a way, thenthe world is more immutable. The Fade is ever changing, but the entities within it do not consider themselves as beings that can change and grow, but just change to reflect the mortals they interact with (whether those mortals or conscious or dreaming).
Thedas, imo, needs to have more magic in it that most are comfortable with, but this doesn't mean Cole himself needs to be a spirit flying around. It is clear that the path he wanted to be on, as he gained more and more self-determination, was one of humanity, and his difficulty was that he was a spirit trying to gain humanity in a world with the Veil still up. What/who do we blame for that? Some will go for Cole-Spirit because the Veil still exists and making spirits more human is just playing with something of great danger, at least for the time being. Cole may as well stick to what he definitely knows, and this is his existence as a Spirit of Compassion. Going human, on the other hand, lessens the breadth of that compassion, but opens him up to the possible depths of compassion. With his spiritual history, he can be human-like, but retain the ability to love to a level that may be utterly unconditional. In a way, it may be even more helpful that he goes human and sticks around the Inquisition AND they get to remember him, identify with him as a fellow person, and gradually learn that there is nothing to be afraid of, in itself, about spirits.
Solas is more knowledgeable and powerful but we shouldn't take his knowledge as the best path. He has character flaws.
Varric is less knowledgeable and prone to more frequent mistakes, but he also seems to be more open to grow from them and be a better person from them.
This decision may come down to whether you want to take the path of 'personal growth' for Cole, even with the pain that will require, and the sacrifice of some of his more useful spiritual abilities.
He's a better entity regardless of what you choose. No longer in a still-weird space between worlds (he was worse in Asunder but still). You just decide how. I like to think he'll end up enriching the Fade in his own way (after Inquisition+Expansions) as a 'Spirit', but end up enriching Thedas in his own way as a 'Human'. Regardless, he can be an optional part of bridging the gap between worlds. The Breach may get fixed, but the experience of it may have cleaned out the Fade a little of its mass of demons building up (personal theory), and it may have ascended much of Thedas beyond the millenia-long status quo of how it interacts with the Fade. This may only be the Breach of things, but I think we can look forward to much more in the games ahead, assuming Bioware gets the green light (hehe) for them.