The two main things to think about are simplicity and scale. As for the former, key-value stores from the basic BerkeleyDB (though I think there's more modern and better software now) up to three-dimensional bigtables are really conceptually simple when it comes to your data. It's (mostly) a blob, and it's got a key. Couldn't be easier. Of course, when it comes to using the data, once you want to add new uses it becomes harder because now you have to write actual code in a procedural language and that's usually not very efficient. That's where the power of relational databases comes in. Very very easy there to use the data in a new way you hadn't envisioned easily. It's just a matter of a different SQL query!
Then there's scale. Most people don't really have this problem. Think about the point where you want to stop using sqlite in favor of a separate-process database like Postgresql or MySQL. That's a difficult spot to pinpoint, right? But you have a general idea of where it is. The time where you can't scale postgresql out and start needing a lot of Cassandra or Accumulo servers is a good order of magnitude farther than that. It would really be premature optimization to worry about it before you know it's going to be a problem. Especially since once you cross that bridge, you lose out on scale in a different dimension: querying and using the data becomes tougher!
I haven't really talked about hierarchical schemaless structures like MongoDB because I don't really like them / don't really think they're that useful. Sorry!
Most people talk about Mongo when it comes to NoSQL. Mongo is a very nice, high availability, document store. It has eventual consistency and can be horizontally scaled in a number of ways. I won't go into all of the uses but one I can think of is in an environment with very very high read with extremely low writes where you need low latency on reads and a document based data structure.
To answer this thoroughly would require quite a bit of time to build a curriculum around the various types of NoSQL stores, what their strengths and weaknesses are, and how to know whether your data fits. In other words, it's a big topic.
For example a key/value store like Cassandra is very different from a schemaless hierarchical structure found within MongoDB. They're both NoSQL, but extremely different from one another.
I'd only suggest you study the different options so you'll know if a specific NoSQL (or SQL) database solution is a good fit for your projects.
The main different between NoSQL/SQL DBMS is about auto scaling. NoSQL scales well on multiple servers, as they were designed to work that way, while SQL dbs do not.
while there is many big platforms out there that have big nosql solutions that are autoscaled, there is probably way more that are sql based. companies such as Facebook and Twitter were using large scale sql data stores.
auto scaling is also a tricky word. as it can mean many things. possibly you mean how things like riak and mongo can automatically scale out without having to write code to do it. certainly nothing prevents folks from doing this with any data store. and there is utilities like Twitter gizzard, tumblr jet pants, and YouTube vitess that help.
often in my experience by the time you get to the point of needing a database that can scale itself your use case and production needs often demand a very well understood data model and flow. often it's easy to shoot yourself in the foot worrying about that early on. auto scaling can be an expensive mistake that can lead to even more technical debt and monetary debt too.
