A bad consultant is one who makes grand promises but fails to deliver on them. They may also invent phantom problems in order to justify their services. In contrast, a good consultant will manage expectations, meet them, and then find ways to improve their core offering. Bad consultants have a tendency to make recommendations without considering the future consequences.
It's important to try and stay away from these types of consultants, but it can be difficult when people have preconceived notions about them. Unfortunately, it's not uncommon for people to hire bad consultants. This can happen due to a superior's request or due to one's own lack of knowledge. Learning about best practices is important, but it's not enough if the consultant doesn't provide specific information about what you consider most important.