Redis is not mentioned here at all so maybe the author is thinking mostly of other NoSQL software here, but well, in the case of Redis the whole point was not just the in-memory performance part, but the data model as well. My claim is that you can't really exploit the advantage of using memory if you perpetuate in using the memory to represent the same data model that you were using with relational databases. For instance think at Redis sorted sets in the use case of leaderboards in popular games (the same pattern is used in a number of applications that have nothing to do with games). To use SQL, even an in memory one, in such use case is not going to work. So in the case of Redis the point was to remove the interface between the user and the way the data is actually fetched from data structures, to make the user do the choices in a very direct way. Thus this article does not apply to Redis in my opinion. I've the feeling that many other NoSQL products could argument like that, but in other areas of their research and difference. For instance I've issues to see how modern SQL systems can replace CRDTs based stores.
This is the problem with people that use the term NoSQL.
Do you mean Cassandra (BigTable), MongoDB (Document), Riak (Key/Value), Redis (Mix), Kafka (Log Structured) ? There are dozens of fundamentally different systems many of which are closer to an RDBMS than their NoSQL peers. And many of them have rigid schemas so it definitely isn't that either.
In general understanding performance is way more critical for NoSQL/distributed systems, where one wrong assumption can have a huge performance penalty. SQL as a language and abstract relational model just can't work in this situation well.
> For instance I've issues to see how modern SQL systems can replace CRDTs based stores.
They can't of course, but it's not only CRDTs. SQL is incompatible with a lot of things that came out of distributed systems research. Just like POSIX filesystem APIs or pretty much any legacy stateful tech. And no amount of PR articles can fix it.