There can be many situations where you'd want to test nitrate, but generally you'll find that the longer the tank has been running, the less often the owner tends to test nitrate. Once you find stability it becomes less of a concern from an owner's perspective.
If the tank has a lot of coral, it's good to test more often so it doesn't get depleted. If you're trying to breed fish, it can help to keep nitrate levels low. Nitrate isn't acutely-toxic to fish (at least not the levels we talk about) but it's an endocrine disruptor. If you want their hormones to operate optimally, it's safest to keep an eye on nitrate. The effects largely depend on species and concentration though.