In episode one, when Baltar is seduced by what turns out to be a Cylon in the form of a human, the Cylon's spine glows red and shines through her skin. Was this just a special effect for the benefit of the TV audience? Do any or all of the other human looking Cylons in the 2004 series suffer from this give away indicator?
I don't believe this is ever specifically given a reason. However there are two occurrences as far as I can recall:
The first is with Baltar and number 6, who are apparently deeply in love.
The next is with Boomer and Helo, who again are both apparently deeply in love.
Since the human Cylons were designed to not be detectable to humans, it seems unlikely that it is in their design for their spines to glow during sex. Potentially the fact that these Cylons were expressing love to humans cause this abnormal appearance to manifest itself.
The most likely explanation is probably as you put it "just a special effect", which is backed up by the wiki, though without reference:
Note: Comments from members of the production crew have since suggested that the only reason the glowing spine was included was that it "looked cool" at the time, and in retrospect, may have been a mistake. According to the novelization, the spines glow in the infrared spectrum, which would mean it would require special optical equipment for it to become visual.
I'm going to go out on a limb here, and say that it wasn't a literal depiction so much as a story-telling device meant for the audience. This show was very serious throughout its run, even grim... they weren't doing goofy stuff and breaking the 4th wall or anything like that, and so I know that it might be difficult for some to accept this as an explanation. However, there is no other explanation here. Even if the glow was biological in origin (some sort of bioluminescence), there would be abnormal tissues detectable by a first year med student and a cursory examination.
I'd like everyone to consider how the scene would be viewed if they hadn't included this special effect: is this woman merely human but a traitor? Perhaps everyone understands that she's a cylon, but they take away some other impression such as that these two are in love/lust or the like. There is alot of room for misinterpretation if they do not emphasize her bizarre nature. If it were a novel, the author has many tools to do this, but with film that simply isn't the case, and sometimes it is necessary to depict something that would not actually be happening if they were describing a factual account of the story.
According to the official novelisation, the glow was into various spectra that couldn't be seen with the naked eye. There's no special indication why her spine was glowing, other than it grew with her "sexual fervor", suggesting a physiological response akin to orgasm.
As their lovemaking mounted toward a climax, the wall behind her was warmed slightly by a peculiar, nearly invisible light. The doctor never saw it—and wouldn’t have, even if he had been less distracted. The light was mostly infrared, with just a hint of gamma radiation. If his eyes could have seen it, they would have seen the glow of fiery embers, the glow of heating coils. It was a soft glow, but growing in intensity, growing with the woman’s sexual fervor. Indeed, it came from, and illuminated, the spine of the gorgeous, naked being who was rocking and bobbing as she made love to Gaius Baltar.
The canonicity of this event was addressed by Showrunner Ron Moore in an interview to coincide with the series finale. In short, the "glowing spine" thing wasn't retconned, it just wasn't something that came up again (much).
Q: The glowing red spine (during sex) in the first season. Was it just abandoned?
Ron Moore: It wasn't really abandoned; we just didn't do a lot more Cylon sex scenes.