What is an artifact anyway?
My head canon has it is the Dwarf gets possessed by their ancestors or a minor deity and forced to make a cool sock.
That is basically an exact description of the "possessed"-type mood, but there are others.
The others don't have explicit canon yet, but can be presumed to be a dwarf magically channeling some other power, such as a deity, and behaving in a trance state to fulfill the whims of that deity or power. Macabre or fell moods are obviously from more evil deities of death or pestilence or the like.
I'm pretty sure Toady has mentioned it somewhere in the devblog years ago when the moods were updated to what they are now, but if you want, you could possibly ask in the FotF thread for a more official response.
It's because of this divine inspiration that artifacts are meant to have unique magical powers sometime down the line, as well as just having their current odd effects and ludicrous value.
That said, it's not like it's unreasonable for an item to become an artifact and gain powers over time, but it shouldn't just be a goblet you, as the player, really like and put a lot of decorations on. It would need to be something more like how weapons gain names and legends, currently, and just make it so that, if an item gains enough of a legend around it for being symbolic of major events, it gains magical powers. (I.E. the hammer of a hammerer that executed half his fortress, or the throne or crown or scepter of a legendary king.)