I had multiple ancestors who served on both sides of the Civil War. My "founder" ancestor joined up with the South along with his many brothers-in-law, while living in North Carolina. He had a miserable time and eventually went AWOL from the Confederates. The Union Army was offering a bounty of land to any Confederate soldiers who would switch sides, so he took them up on that. The land was in rural Kentucky, which is how our family came to be "from" there. He seemed to be quite well-liked and his children were fruitful and multiplied, so there doesn't seem to have been much if any stigma attached to it. I expect a lot of the people around him were in the same situation, having gotten their bounty land for changing sides. Later in life he tried for the Union army pension (but not the Confederate!) but died before he could get it.
I find that story really fascinating. A relative of my parents' generation was retelling it to me and commented as though she found it negative, like, "Not our proudest family moment, but..." I was kind of
because it was 150 years ago. It's not like, I don't know, he started a massacre of unarmed civilians or something. Dude was in a bad situation and found a way to get out of it, along with thousands of other people.
One of his brothers-in-law also switched sides. He was captured in battle and sent to a Union prison, and apparently they had a deal where they would let you out if you switched sides, so he did that.
On the other hand someone also tried to claim that my "founder" ancestor was an officer in the Confederate Army and here's a picture of him. It's not him. He had a very common name and was never an officer, and the photo doesn't look anything like other photos we have. Yet this picture keeps getting circulated on family trees attached to his name. So actually both outcomes--switching sides and getting people mixed up--are very common.