In storytelling contexts, the words turned back into a will be, nine times out of ten, an allusion to the fairy tale known as Cinderella.
If you do an ngram search for turned back into a *
, pumpkin is very high on the list. At the stroke of midnight, the magical carriage in that tale turns back into the pumpkin from which it is made. The heroine is partying at a ball at the castle, where she hopes to encounter her Prince Charming. She too will transform, back into a drudge.
The suffix of pumpkin is actually -kin. It is a diminutive. {noun}-kin is a "little" {noun}. NOTE: pkin is not a bona-fide morphological suffix here but simply an allusion to the spelling of the word pumpkin, so that the word pumpkin will come to mind, and summon up the Cinderella story where, at the stroke of midnight, the female heroine's magical carriage turns back into a pumpkin and she herself turns from party-girl back into drudge.
P.S. Since @Lawrence asks "If pumpkin is the little guy, what's the big version :)"
If I were to answer Lawrence's jest, I'd say the big guy is occupied by Peter Pumpkin-Eater's wife. But seriously, diminutives can express both appreciation (tenderness, affection) and depreciation (irony, disparagement, derision—the idea that something is inferior). The pumpkin was regarded as fodder and as a rather crude food for humans, or as food for rather crude humans.
I should add that it is not always clear to native speakers that -kin is a diminutive. For example, a "manikin", a shop-window display dummy, is more-or-less life-size (though usually not realistically so), and few native speakers recognize the -kin as a diminutive there. Though since the dummy is only a dummy, no more than a basic human shape, often lacking hands and feet, the diminutive could be understood to mean "a lesser human", "a mere human shape". More importantly, unlike cognate -chen in German, -kin is not very productive in Modern English; that is to say, relatively few words get formed with it.