From the beginning, Carlo was never a trusted member of The Family, as evidenced by his low position as bookmaker 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 beats Connie. Carlo's treatment of his wife leads to further distrust by Don Corleone. Additionally, it puts strain on his relationship with Sonny, who once considered him a friend. 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 approaches him and 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. Vito dotes on Connie and doesn't want to see her widowed during his lifetime. 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 that she will call Sonny and tell him that Carlo has beaten her again, and 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 - remember Connie's hysterical rant to Michael when she found out her husband was dead? She specifically 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.