"[SQL deal for when] "Data consistency and reliability is a primary concern"."
I'm curious: lets say you have a tweeter scale app that must satisfy the consistency and reliability as a primary requirement. Is there really a NoSQL solution that can take you there without (effectively) raising the costs to the point that a scalable (money not an object) SQL solution would provide? (Kinda like how the difficult to extract North Sea oil became economically viable once oil prices crashed through a certain ceiling?)
Well, "reliability" can be sliced in a couple of different ways since that term can cover both the A & P in the CAP options and it can also mean the elimination of single points of failure and an architecture that can degrade gracefully when components fail. Some NoSQL systems let you select the mix of consistency and reliability you need at a rather fine-grained level -- one thing that does distinguish these systems from the traditional RDBMS is that you are almost never in an all or nothing situation regarding any particular part of the data space unless you explicitly want to create that choice to enable other options.
NoSQL gets scalability by giving up a huge feature set, and, yes, cross-object consistency. I question the lower reliability argument though. Dynamo, one of the original NoSQLs, allows each instance to specify how reliable they want writes to be. Most of the NoSQLs allow you to tune the reliability factor, just as MySQL and PostgreSQL allow you to do.
I'm curious: lets say you have a tweeter scale app that must satisfy the consistency and reliability as a primary requirement. Is there really a NoSQL solution that can take you there without (effectively) raising the costs to the point that a scalable (money not an object) SQL solution would provide? (Kinda like how the difficult to extract North Sea oil became economically viable once oil prices crashed through a certain ceiling?)