Look at it this way. The Canadian Destroyer made Petey Williams in TNA, it was his identity. Hell, before Scott Steiner made him "Lil' Petey Pump', it was his moniker. It was all he was known for. Without it, he would have just been another generic X-Division wrestler. On the flip side, the leg drop as has been discussed did not make Hulk Hogan. Hulk Hogan MADE the leg drop. He could have had any other finishing move but he didn't need it, because Hogan was already a household name. That's why he could get an entire crowd of 80,000 plus people pop to a simple leg drop.
As for whether or not The Canadian Destroyer looks fake? When I first saw it, it looked sick and I was impressed. I had never seen anything like it so it blew my mind. It has the effect. But as I watched it be delivered time and again, I started to wonder how Petey pulled it off and began watching it in slow motion. It was then I discovered how fake it is, as really it's the recipient of the move doing a back flip. Does that hurt Petey? No. He's an X-Division wrestler. His job is to wow the people with high-flying antics and spot matches. However, it makes it difficult for him to ever transition into the world title scene, as it may look sloppy being delivered on bigger guys--if it can even be done on some of the less athletic heavyweights. Notice he's only done it on guys his size or slightly taller. You'll never see Petey do it on Hernandez, Sting, Booker T, Rober Roode, James Storm... maybe Joe and Angle can sell it but I think I made my point. It's ok to have a flashy finisher that doesn't look real as long as you're ok with never making it to the top.
To cement my point, look at some of the guys considered small that made it to the top in the WWE. The first that comes to mind is Shawn Michaels. His finisher is a super kick. Totally believable, as the recipient only receives a jaw-shattering blow that can put anyone down. Chris Jericho. Walls of Jericho, what used to be an elevated boston crab but is still as deadly as a simple boston crab. It's a submission move, again totally believable. Rey Mysterio has the 619. Looks flashy but still totally believable because the recipient just gets kicked in the face then either has a leg dropped across his neck from up high or gets locked in a hurricarana, which is pretty common and almost anyone can sell.
Maybe if TNA ever goes under, and Petey decides to head to the WWE, he'll change his finisher to something more believable that won't keep him grounded to wowing the crowd with an unbelievable finisher.