[mr_gibbins]

No, that isn't correct. The query planner optimises query execution in a layer of abstraction below the representation of the tables, the relational integrity and structure of the user-accessible objects.

Tables, their attributes, relationships etc. are easily able to be represented visually. Entity-Relationship Diagrams have standard notation (Chen, Crows-foot, UML). We can represent models conceptually, logically or physically, we can even indicate the existence of views and indexes. In terms of queries, DML is essentially multi-filtered, multi-dimensional slicing of tables and can be represented as output sets which are as modellable as the source tables.

On the wider point, DBT looks promising. It seems to be a good middle ground between the purists (like me) who wish back-end devs would just learn how to model data, and the adventurists who prefer to lock everything behind OOP principles.

[vaughan]

The query planner can always change how it runs and is a black box. Nodes in the plan thus cannot be cached. This means that for streaming we are usually re-running the entire query, or doing some custom stuff.

When a source table changes, I want it to automatically and efficiently update anything that would change. I think pretty much every system would prefer real-time stuff like this.

If you are doing this with SQL you will start looking into "Incremental View Maintenance" which is quite complex and still quite heavy.

Then you realize that if you take control instead of handing it off to the planner, you could code your joins and transformations by hand, cache intermediary steps as needed, and have a query that is more efficient than SQL.

But for this I would argue you need a better way to visualize your data flow and the dependency graph, because people can easily write slow imperative stuff.