Let $Q$ be an $N \times N$ unitary matrix (its columns are orthonormal). Since $Q$ is unitary, it would preserve the norm of any vector $X$, i.e., $\|QX\|^2 = \|X\|^2$.

My confusion comes when the columns of $Q$ are orthogonal, but not orthonormal, i.e., if the columns are weighted by weights $w_1,\dots,w_N$, the dot product of any two different columns would still be zero, but $Q^H Q \neq I$ anymore.

What are these matrices called? The literature always refers to matrices with orthonormal columns as orthogonal, however I think that's not quite accurate.

Would a square matrix with orthogonal columns, but not orthonormal, change the norm of a vector?