For me, it has to be Riven and Obsidian.
The puzzles in Riven are well-integrated into the game environment. Many switches, levers, buttons and doors serve actual, practical purposes other than being obstacles to progress (the boiler on Book Assembly Island, Gehn's throne on Temple Island, the sub and wahrk counting game on Jungle Island and of course the Survey throne on Survey Island.) The most difficult puzzles in the game require putting together information from various sources (and using a bit of intuition) to connect the dots. Solving these puzzles rewards the player with stunning cutscenes, especially the Animal Stone puzzle, the "waffle iron" puzzle and of course, the finale!
Obsidian has some of my favourite puzzles ever, mostly because of their surreal setting. The entire Bureau Realm has a notable collection of weird and wacky puzzles (Filing Cabinet, Department of Pre-Approvals, clockwork puzzle). The Spider Realm puzzles were quite challenging too (Fire, Air, Metal, Oil), as were the ones in the Bismuth Realm (trapping "inspiration", The Church of the Machine programming puzzle
). The player was also rewarded with breathtaking cutscenes for completing each realm.
