For me, Simon's Quest is, and has always been, a top 5 Castlevania entry.
Simon's Quest has gotten an inflated bad reputation over the years that it doesn't remotely deserve. (The
N64 entries are similarly and tragically misunderstood, and they have vestiges of this game in them.) Simon's Quest is right next to Dracula's Curse in terms of the entries that first got me interested in the franchise. It's an extremely atmospheric entry, in part because of its great use of music and color, with an ambitious and engrossing game structure that utilizes a night-and-day mechanic. While it can be unintentionally humorous at times, it much more so can be quite eerie and unsettling, and all the while, it also feels organic, with villagers not always telling you the truth or talking to you about everyday things. Now, for whatever reason, localization or otherwise, the game can be overly cryptic, and parts feel almost unfinished with the overall lack of boss battles and the lack of difficulty in those boss battles; also, grinding for hearts can be a pain, especially early on. But think about it: A non-linear Castlevania game with NPCs and character building, along with plenty of surprises and secrets, was well ahead of its time and, together with Vampire Killer on the MSX, you see an almost proto-Symphony of the Night. (IGA has even said recently that SotN was approved by Konami in part because of Simon's Quest's existence and precedent.) However, at the same time, unlike SotN, it creates an interconnected Transylvania bigger in scope than any game since and actually keeps the consequential action-platforming of the original. Much like the N64 games, it's a legit formula that should have been built upon and refined, perhaps.
I'm just discovering now, by myself, some sequence-breaking techniques that add new wrinkles to the game. For instance, you can get the Morning Star whip right at the start of the game if you practice your jumping and attacking skills (and timing) in the poison swamp. That said, newcomers should be encouraged to use a booklet/manual to tell them about the game's systems--what items do and about the game's hearts being currency, experience points, and powering sub-weapons. The game doesn't convey that stuff so great.
But running across the Sacred Flame or the sub-quest to get the Silver Knife are just some of the stuff that sticks with me always.
Bottom line, while I understand the infamy of this game online, and this no intended offense to anyone, I think this memorable game has gotten an unfair reputation to a large extent and it's created jaded or overly concerned attitudes across the internet toward it that sort of miss the point of the ambitious things the game is trying (and often succeeds) to do. It's definitely flawed, but not broken, I'd say. The "un-playability" of this game without a guide is super overstated. There are at most two moments where a guide will likely be required (I had these things spoiled to me as a kid, so I can't judge them). But it's worth noting, there are two clues to the tornado puzzle you were wondering about: "Hit Deborah Cliff with your head to make a hole" (kneeling creates an animation where Simon's head looks like it is against the wall) and "Wait for a soul with a red crystal on Deborah Cliff." (Allegedly this latter clue should have been translated as "The wind waits if you carry a red crystal in front of Deborah Cliff.") One could possibly piece these together, difficult though it may be.
The game requires a lot of wandering...but so does Zelda: Breath of the Wild.