I am fully aware that the consistent mass model in any type of dynamic analysis will lead to more accurate results; in my case, I have a piping system which is subject to a mild seismic incident. the stress results with a lumped mass model are about 65% of code limits but once I switch to consistent mass model and rerun the calculation, I get insanely high stresses about 9000% of code limits.
what baffles me is when I check the "modes unity normalized" in the corresponding mode, the nodes which are tensed the most have been hardly excited!
can anyone tell me what could cause such gigantic difference in the results?

Could be missing mass; could be seismic anchor motion; could be an input error; etc. Without your input it is just guessing.
So I must also state, based on your described change I the input, such a change in results is not expected.

thank you Dave,

The active forces are over 80% in both load scenarios (SUS+D1 and SUS+D2)the missing mass percentages are pretty high in both horizontal directions however, I have chosen to "include missing mass" in the input setting.
Considering that the aforementioned system is partly buried, can it have anything to do with the wooly results? I assumed the seismic impacts throughout the whole system including the buried part as well.

Your buried portion may be causing your problem here. CAESAR II removes all weight from sections "buried" by the CAESAR II buried pipe modeler. There is no mass in those sections. No mass, no dynamics...

I suggest you reanalyze without buried pipe in your model and see if that is related to your issue here.

Do we keep on guessing?