The Hour of Twilight is the fulfillment of a long chain of events that were planned by the Old Gods.
A very ancient prophecy speaks of the Hour of Twilight, the final days of the world and if the Old Gods that were chained long ago had their way set in motion, this chain of events would enact their final vision to remake the world in their image.
It possibly started with the reopening of Ahn'Qiraj where C'Thun rose beneath the lands of Silithus.
There is a chain of events in motion where the Old Gods are rising, their ancient machinations are finally paying off, and Deathwing ultimately is their greatest hammer. While he has his own machinations, trying to settle his own domination over Azeroth, he was corrupted by the Old Gods from the start and in this final hour, he is pretty much the greatest weapon for the Old Gods to unleash.
After the Hour of Twilight, every lifeform on Azeroth will be dead and rotting, except twilight dragons. In Ysera's vision of the Hour of Twilight, all five Aspects, including Deathwing, lay dead at Wyrmrest Temple, killed by their own powers.
Hour of Twilight is the third 5-player dungeon added in Patch 4.3. Players must aid Thrall as he travels to Wyrmrest Temple to eventually meet up with the blue, bronze, green, and red dragonflights. Along the way though, he encounters many members of the Twilight Hammer that must be dispatched.
As the bosses drop ilvl 378 loot, this dungeon is more challenging than other heroics and requires ilvl 353 to queue.
A neat part of the dungeon is how Thrall fights besides you and gives your party special buffs.
From the Blizzard Community site:
"With the Dragon Soul retrieved from the past, the Heroes of Azeroth now prepare for the final confrontation with Deathwing. In the frozen wastes of Northrend, the towering spire of Wyrmrest Temple is under the assault of Deathwing's Twilight forces. In order to save Azeroth, Thrall must arrive safely at the temple with the Dragon Soul in hand."
A very ancient prophecy speaks of the Hour of Twilight--Azeroth's final moments of existence. At the last sunset, the Old Gods, sealed away within the depths of the world long ago, will enact their judgment and irrevocably twist the mortal world to reflect their essence. It possibly started with the reopening of Ahn'Qiraj, when the Old God C'Thun stirred from its slumber beneath the lands of Silithus.
The Dragon Aspects were created to stop such an event from occurring, where they would give their powers to prevent it. Using this to their advantage, the Old Gods corrupted Deathwing as a way to make their plan "foolproof", using one of the very aspects destined to stop the Hour of Twilight, to instead usher it.
A chain of events is set in motion where the Old Gods are rising, their ancient machinations are finally paying off, and Deathwing is their greatest pawn upon the board. While the insane Aspect believes himself to have his own purposes, his mind and soul were devoured by the Old Gods from the onset, and in this final hour the former Earth-Warder of the Titans has become the ultimate weapon for the Old Gods to unleash.
Deathwing's attempt at fulfilling the Hour of Twilight was finally stopped when he was vanquished at the Maelstrom by the Dragon Aspects, Thrall and mortal champions of the Horde and the Alliance. However, this victory came at a great cost, with the dragonflights' losing the majority of their power and immortality, as they had at last fulfilled their ancient purpose.
In Ysera's vision of the Hour of Twilight, all the five Aspects, including Deathwing himself, lay dead and broken at the ruins of the Wyrmrest Temple, having been destroyed by their own powers. Every lifeform on Azeroth will be dead and rotting, except twilight dragons. This outcome was also a possible future if Thrall would not step in the place of Neltharion as the aspect of earth, and remained in the Horde instead of appointing Garrosh as his replacement. 
The grim aftermath of the Hour of Twilight can be witnessed first hand by players within the End Time instance. When the Hour of Twilight shown in the End Time has come to pass, all life on Azeroth forever has faded, and only time-twisted echoes (and possibly some newly risen ghouls) are left to aimlessly wander a dead and empty world.