From the beginning, Carlo was never a trusted member of The Family, as evidenced by his low position as bookmaker (i.e., running a gambling office and not being given a higher post in the family's crime dealings) and Vito's instructions to Tom to allow Carlo to earn a living but to never discuss Family business in front of him. Carlo knows that he is not valued by Don Corleone, but he cannot take his frustrations out on Vito, Sonny, or any of the other high-ranking members of the Family, so he does the next best thing - he is abusive toward Connie. Carlo's treatment of his wife leads to further distrust by Don Corleone. Additionally, it puts strain on his relationship with Sonny -- though the relationship isn't fully explained in the movie, in the novel we learn that Sonny and Carlo have been lifelong friends. After Sonny beats Carlo on the street in his own neighborhood and in front of his men, Carlo feels completely humiliated and seeks vengeance against Sonny. When Barzini asks for his help in setting up Sonny, Carlo is more than happy to comply.
Vito himself likely suspected Carlo's role in Sonny's murder, but since he could not prove it (and he didn't want to tip his hand so early), he pretends not to know. Connie, being the youngest child of Vito, had been doted upon all her life and Vito doesn't want to see her widowed during his lifetime, something that, despite how horribly Carlo treats her, would upset Connie greatly. Michael is informed of the sequence of events that took place the day of Sonny's murder - a mystery woman calls Connie and Carlo's apartment to ask for him, Connie gets angry and starts to throw a tantrum giving Carlo an "excuse" to beat his wife knowing full well that she will call Sonny and tell him that Carlo has beaten her again. Carlo lets the Barzini people know that Sonny is on his way to the city giving them the opportunity to trap him at the toll booth on the Causeway. It's all a little too convenient, and yet Michael still cannot be 100% certain that Carlo was involved.
When Michael sits down with Carlo in the final scenes of the movie, he needs to know without a doubt that Carlo was responsible before giving the go-ahead. He decides to bluff to get Carlo to admit to his role in Sonny's assassination. It works, and once Michael has confirmation that he has good reason to make his sister a widow, he gives the order to kill Carlo. Carlo's position as son-in-law was the only thing that allowed him to live as long as he did. When Connie finds out her husband is dead, she hysterically says to Michael "you waited until Poppa died so no one could stop you, and you killed him!!" Had Carlo been anyone else in ranking, he would have been taken out right after Sonny's murder.