Absolutely - this OKC team has undoubtedly got the talent to knock off any team in the NBA.
1. Russel Westbrook finished top 3 in scoring last year AND averaged a triple double AND won league MVP. He is a guy who is very capable of dominating on both ends of the court (there were years in the past where he was one of the better defensive PG's in the NBA. Bringing in Melo and George will take a great deal of the offensive burden off Westbrook, potentially allowing him go back to being a dominant two-way player. If this happens he could give both Curry and CP3 nightmares on both ends of the floor - and nobody in San Antonio will be able to touch him.
2. Andre Roberson is a defensive specialist who won't do much offensively, but can make things very difficult for Klay Thompson. With Klay being the 3rd best scorer in Golden state, you'd hae to put Roberson on to him. There are many statistics to show that as Klay goes, the Warrirors go. Curry and Durant will always have big nights, but it's when Klay also goes off that they become a real nightmare - if Roberson can slow him down, then that can help turn the tide in the Caltics favour. He can also do a strong job against RudyGay in San Antonio.
3. Paul George is a legit two-way player who has been known to have monster games against bit time players. He's probably one of only a handful of guys in the NBA (Kawhi being another) who has the combination of length and quickness to be able to bother Kevin Durant defensively, while simultaneously making him work extremely hard on the other end of the floor as well.
4. While he is starting to decline a little, Carmelo is legitimately one the most talented scorers this league has ever seen. Like Pierce and Kobe, he's the rare offensive player who can score from anywhere, in any way - midrange, three, foul line, in the post. He's also the type of talented who can explode for 50 or 60 points - and for OKC this guy is their THIRD scoring option. While Melo has a reputation as a terrible defensive player, he actually is capable of playing very good defence when he's motivated to - I've seen him play guys like Lebron and Paul Pierce in their primes about as good as anybody ever has - he's more mobile/athletic then he's given credit for, he has good length, and he's one of the physically strongest wings in the game. We really haven't seen Melo play with anything close to an All-Star calibre player since he played with Amare early in his Knicks tenure, and that Knicks team was a force. He's always been the long star surrounded by crap, and so his need to handle all of the offense has likely made it difficult for him to reserve energy for the defensive end. With much of the offensive responsibility being taken off his hands, Melo can save some of his energy for his defence and rebounding. His All-Star and Team USA performances are proof that Melo can be an absolute elite team player (and a true winner) when he's motivated and when he has help.
5. I've never been a huge fan of Steven Adams simply because he seems to have an ugly personality. But he is the absolute perfect guy to top off this team. An extremely tough, physical and aggressive player, Adam's defensive versatility and rebounding proficiency make him the perfect complement for Melo up front.
This is on of those teams that could got amazingly well or horribly badly depending on whether the three core guys are willing to sacrifice for the team. If there's one thing they won't be lacking it's motivation. Melo is getting older and this could be his last opportunity to win a title - he's going to be hungrier then he's ever been before. Paul George is also without a ring, so he'll be motivated. Russel Westbrook comes across to me as being a bit more selfish, but after Durant's recent sly comment towards OKC, you know he's going to be more motivated then ever before to knock off Golden state.
This team is a very real threat to anybody and everybody if they can get on the same page. Rockets aren't a challenge to those big 3 teams IMO. I can't see either CP3 or Harden playing a major role without having all of the offense run through them.