Back in the film days, a roll of film had a specific ISO. The higher the ISO, the more sensitive the film was - it could be used in lower light, essentially. But that generally came at the cost of grain. A black and white photo, in a bar or boxing ring, for example, with a lot of grain, can have a pretty classic look.
With digital, it's fairly similar, but we can change the ISO whenever we want to. Make it higher, and you can take images in lower light at the same shutter speed, without changing the exposure. If light levels drop, you probably have no choice but to turn the ISO up, or let the camera do it for you.
The problem is, the images won't look as good. The noise we get with high ISO from a digital sensor isn't as appealing as the grain we get in high-ISO film.
Newer sensors are generally better at higher ISO settings than older ones. And bigger sensors are much better than smaller sensors. So a new full-frame camera can probably get away with pushing the ISO up to, say, ISO 800, without it being too noticeable.
How far can you push the ISO before your photos will just look shit? Nobody can really tell you that, you need to test with your camera. Every camera is a bit different, and some people don't mind a bit of noise, while others do. I'm usually not too fussy about image quality, but noise from high ISO bothers me quite quickly. Maybe you don't find it too noticeable until it gets really high, though, and that's what's important.