> If I understand you correctly, you can easily get the same with ephemeral models in dbt or CTEs generally?
You can bolt on any number of 3rd party features or extensions to get some extra thing, that goes for any tool in the world. The point of something like Datalog is that it can express a similar class of relational programs that SQL can, but with a smaller set of core ideas. "Do more with less."
> I guess, but how can you guarantee correctness with flexibility involved?
How do you guarantee the correctness of anything? How do you know any SQL query you write is correct? Well, as the author, you typically have a good idea. The point of being compositional is that it's easier to stick together arbitrary things defined in Datalog, and have the resulting thing work smoothly.
Going back to the previous example, you can define any two "tables" and then just derive a third "table" from these, using language features that you already use -- to define relationships between rows. Datalog can define relations between rules (tables) and between facts (rows), all with a single syntactic/semantic concept. While SQL can only by default express relations between rows. Therefore, raw SQL is kind of "the bottom half" of Datalog, and to get the upper half you need features like CTEs, VIEWs, etc, and apply them appropriately. You need more concepts to cover both the bottom and top half; Datalog covers them with one concept. Datalog also makes it easy to express things like e.g. queries on graph structures, but again, you don't need extra features like CTEs for this to happen.
There are of course lots of tricky bits (e.g. optimization) but the general idea works very well.
> Could you share a more specific example or scenario where you have seen Datalog/ Logica outperform a modern SQL setup?
Again, Datalog is not about SQL. It's a logic programming language. You need to actually spend time doing logic programming with something like Prolog or Datalog to appreciate the class of things it can do well. It just so happens Datalog is also good for expressing relational programs, which is what you do in SQL.
Most of the times I'm doing logic programming I'm actually writing programs, not database queries. Trying to do things like analyze programs to learn facts about them (Souffle Datalog, "can this function ever call this other function in any circumstance?") or something like a declarative program as a decision procedure. For example, I have a prototype Prolog program sitting around that scans a big code repository, figures out all 3rd party dependencies and their licenses, then tries to work out whether they are compatible.
It's a bit like Lisp, in the sense that it's a core formulation of a set of ideas that you aren't going to magically adopt without doing it yourself a bunch. I could show you a bunch of logic programs, but without experience all the core ideas are going to be lost and the comparison would be meaningless.
For the record, I don't use Logica with SQL, but not because I wouldn't want to. It seems like a good approach. I would use Datalog over SQL happily for my own projects if I could. The reasons I don't use Logica for instance are more technical than anything -- it is a Python library, and I don't use Python.