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 is a cowardly thug who 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. When Sonny confronts Carlo about abusing Connie, Carlo instead flees and Sonny brutally beats him up on the street in his own neighborhood in front of Carlo's own men. In the scene, Carlo is clearly displayed as a coward since he makes no attempt to fight Sonny or hit him back. After Sonny beats him up, Carlo feels completely humiliated and seeks vengeance against Sonny. When Barzini (apparently hearing about the incident) 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 spoiled and 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 and make her resent him. A little later, Michael is informed of the sequence of events that took place the day of Sonny's murder by Vito, Tom, Connie and others: 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. It is possible that Carlo may have somehow the Barzini people know that Sonny is on his way to the city, thus 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 the cowardly 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 Pappa 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.