The characters combine literally to mean “vividly, as if alive.” The structure `[Adjective] + 如 + [Noun/Verb]` is a classical pattern for forming descriptive idioms.
The soul of 栩栩如生 comes from one of the most famous allegories in Chinese philosophy: 庄周梦蝶 (Zhuāng Zhōu mèng dié), or “Zhuangzi Dreams of a Butterfly,” from the Daoist text *Zhuangzi*. In the story, the philosopher Zhuangzi dreams he is a butterfly, “fluttering about vividly” (栩栩然). Upon waking, he is unsure if he is Zhuangzi who just dreamed of being a butterfly, or a butterfly now dreaming he is Zhuangzi. This story explores the nature of reality, perception, and transformation. Consequently, 栩栩如生 carries a deeper, more philosophical weight than its English equivalent, “lifelike.”
This term reflects the cultural appreciation for art that connects with the viewer on an emotional and spiritual level, making the inanimate feel animate.
栩栩如生 is a formal and literary idiom (成语, chéngyǔ). While it's understood by everyone, you won't hear it used in very casual, everyday slang. It carries a sense of appreciation and education.
The connotation is always highly positive and complimentary. Using it shows a good command of the Chinese language and an appreciation for culture.