The minimum you need from an advisor is to (a) sign your dissertation, and (b) write you a good letter of recommendation. Everything else can be helpful, but whether it occurs or not depends on too many things like personality, time, field, etc.
Whether a "helpful" or "hands off" advisor is better for you depends, again on a lot of things, but primarily on you and your field. But that really only matters up to the point at which the dissertation is ready to be signed and submitted. Some people, actually, have a better grasp of their research problem than their advisor and don't need any assistance. Others work closely together with the advisor on carrying forward on the research. Either can work, either can fail.
A close "working" relationship can fail if the advisor dominates the student too much, not leaving them time to develop proper insights in the field, or forcing them into work on something the student doesn't find interesting.
The only serious problem arises when the student truly needs help and the advisor is unable or unwilling to give it. There are quite a few questions on this site exploring that problem.
But you would like your advisor to be "hands on" for a few years after you finish your degree to help you along with your career. This can be in writing letters, of course, but also in introducing you to her/his circle of contacts, perhaps at conferences and meetings. That is when it can really count.
Ultimately, if you want to succeed, especially in academia, you need to become your own guide and to be able to guide others. An advisor can help bring you to that point either by helping you directly, or by just letting you work and then giving you feedback so you don't get too far off course.
My own advisor was helpful when he needed to be, but otherwise let me explore the problems I was working on in math.
