Young Sheldon's season 4 finale subtly explains why Sheldon didn't know his dad was having an affair before he actually caught him in the act, based on his story in The Big Bang Theory. The Coopers were constant guests in the long-running CBS sitcom, with Mary having the most appearances. The only member of the family who didn't appear physically was George, who was already dead by the time his middle son moved to Pasadena. That said, the Cooper patriarch was regularly mentioned, either via Mary or Sheldon's stories about him.
Admittedly, their anecdotes about George were less than glowing as he was mostly described as an irresponsible father - something that has been contradicted by Young Sheldon. From a narrative standpoint, Mary citing her husband's flaws was purely for comedic purposes. That said, looking back on it, this seems unfair considering how much George sacrificed for her and their family. In The Big Bang Theory season 10, however, Sheldon revealed a genuinely sad and dark experience with his father when he accidentally walked in on him having sex with another woman.
Based on Sheldon's story, he was unaware his father was cheating on his mother. The only reason he found out is that he caught George in the act after coming home early from school. That said, the prequel series has made it clear the Coopers' marriage had long been having troubles before this. It might even be possible the rest of the family knew about George's extramarital affairs before Sheldon found out. Assuming this is the case, the spinoff has subtly explained why. In the Young Sheldon season 4 finale, Sheldon struggled approaching Missy who was going through her very first love issue. The socially inept genius reached out to his father, who was dealing with his own dilemmas. Instead of going through a draining conversation, George simply advised his son to let Missy be. Ironically, this excited Sheldon as he took it as an opportunity to ignore her sister’s emotions — something he’s really good at. When Sheldon walked away, George begrudgingly agreed with his son's sentiments, saying "clearly" referencing the child's failure to sense that he, too, was going through something.
It's no secret Sheldon has difficulties processing human emotions, especially those that are not his own. As someone who is self-obsessed, he usually only thinks of his own well-being, which can make Sheldon insensitive to what's happening around him. This was a defining trait for the character in earlier seasons of The Big Bang Theory. He hassled everyone for his own benefit and was fairly inconsiderate about their respective situations. Leonard was almost always his victim as he typically heeded to his "friend's" needs, knowing that he didn't have anyone else. It wasn't until Penny treated Sheldon with tough love and Amy directly demanded he became more empathetic that he started addressing this issue.
Considering what lies ahead for the Coopers, it's sad that young Sheldon never realized how much of a hard time George was going through when he told the story about catching him cheating in The Big Bang Theory. Granted that being unfaithful to Mary is still wrong, but the prequel spinoff is slowly revealing that there are more underlying reasons for George's infidelity than him being a bad family man. Given what happened in the Young Sheldon season 4 finale, it's safe to assume the typically lighthearted series is ready to tackle this darker narrative after dancing around it for years.