Vire-se na direção do movimento

transform.LookAt precisa de um ponto para olhar. Muitas vezes, o que você tem é uma direção: uma velocidade, o vento, o caminho que algo percorreu. Quaternion.LookRotation(direction) retorna a rotação cujo eixo frontal (a seta azul do eixo z) aponta nessa direção, mantendo o eixo para cima do objeto orientado para cima no mundo. Uma flecha disparada para o ar aponta para cima no início e se inclina ao cair, porque sua velocidade faz o mesmo:

Rigidbody rb;

void Start()
{
    rb = GetComponent<Rigidbody>();
}

void Update()
{
    if (rb.linearVelocity != Vector3.zero)
    {
        transform.rotation = Quaternion.LookRotation(rb.linearVelocity);
    }
}

Só a direção importa, não o comprimento: (1, 0, 2) e (3, 0, 6) resultam na mesma rotação. Um vetor zero não tem direção, então LookRotation(Vector3.zero) registra Look rotation viewing vector is zero e retorna Quaternion.identity, que faz o objeto virar de repente para +z. É por isso que a flecha verifica primeiro: quando para, ela mantém a orientação que tinha. LookAt(target.position) dá a mesma rotação que LookRotation(target.position - transform.position).

A parte da direção que aponta para cima ou para baixo inclina o eixo frontal para cima ou para baixo junto com ela. Para algo que precisa permanecer na vertical, como um caminhante subindo uma ladeira, defina primeiro o y da direção como 0, para que o objeto gire apenas em torno de y. Um segundo argumento, LookRotation(direction, Vector3.up), indica qual direção é para cima; Vector3.up é o valor usado quando ele não é fornecido.