In most cases, there is no need for NoSQL
Over the years, I learned the hard way that, with the exception of a few niche use cases, NoSQL databases such as AWS DynamoDB or Apache Cassandra are not always a good idea.
Here is why.
With a traditional SQL-based relational database, you design your data model to represent your business objects. Your queries can then evolve and can be ad-hoc. You can even create views, materialized or otherwise, to facilitate even more complex analytical queries.
DynamoDB does not offer the flexibility of traditional SQL. While your data model can evolve and you are not tied to a rigid schema, you have to design your data model around the queries you plan to run.
The problem with that approach is that it is very rare for end-users to say with certainty what they want. Over time their needs change, and so do the queries they want to run. Changes to the storage model in DynamoDB involve running massive data reloads -- or complex code for backward compatibility.
Meanwhile, the ability to get to the application’s data, build reports, and run analytical queries is critical to the developer and business user productivity. It can mean a difference between delivering features in days vs. weeks.
Not all developers are created equal. SQL is a widely accepted and simple query language that business users should be capable of learning and using. Yet, many have trouble with even the most straightforward SQL.
Introducing a whole new mechanism for querying their data, even if it is as mockingly similar to SQL as PartiQL, could be a problem. Traditional SQL databases have well-established libraries and toolsets.
It is worth noting that DynamoDB now supports ACID transactions now as well. Still, I am here to argue that most enterprise application workloads will never reach the physical limitations of traditional RDBMS databases.
Conclusion
NoSQL technology is constantly evolving, as are traditional databases and managed cloud services. What I see happening is a convergence of functionality. There is a lot of cross-pollination of ideas going on in the industry, with NoSQL databases adopting some of the SQL functionality (think: PartiQL and SQL) and SQL databases adopting some of the NoSQL functionality (think: PostgreSQL NoSQL features). It is essential to keep a cool head and not jump on any new tech without understanding your use cases and skillsets.