You're not asking questions, but making statements about why you think dbt isn't useful.
> To me it just looks like some shiny bells and whistles on top of gluing a bunch of sql execution together
So is Pentaho, Matillion, Tableau Prep, and most tooling that enables this kind of work on an RDBMS. At the end of the day, they're all just generating SQL strings from (usually proprietary) metadata. dbt lets me skip the UI bullshit and focus on soup-to-nuts data modeling in pure SQL, and doesn't try to hide the fact that we're working with an RDBMS or that we're spitting out SQL strings via Jinja.
> The other thing it offers is composition via string templates
Here's an example of one of those statements. What it does do is provide the ability to generate dynamic SQL using a mustache-style template syntax via Python/Jinja, giving SQL authors the ability to further exploit that combination to aid in dynamic SQL generation on an as needed-basis. You can use as much-or-little dynamic generation as you want, it's up to the SQL author to be disciplined in how it's applied.
Most models I've seen have little-to-no-dynamic SQL, outside of incremental load predicates in the WHERE clause. The few instances I can think of top-of-mind are using things like information_schema to dynamically generate lists of columns for self-updating models.
What you're thus "composing" is a DAG of views/tables generated via dynamic SQL. It would not be accurate to say you're composing an individual SQL model, since you're really just stitching strings together. This is an important distinction, because "composition" is a terribly overloaded term in software and in this context might lead the reader to believe dbt is trying to re-invent SQL via a DSL that supports composition, which is clearly not the case.
You won't even find the word "compose" on the dbt docs that isn't in reference to Docker.
> some level of orchestration as a direct result
Each dbt model becomes either a view or table, and dbt SQL authors can build models using other models as if they were existing views/tables. Under the hood, dbt parses models pre-exec to build a DAG to determine what order views/tables need to be created. The SQL author doesn't need to do anything more than include the model via {{ ref(model_name) }} macro in a template.
dbt is about embracing the database and exploiting it to the best of its capabilities, rather than delegating to an intermediary to fill in the missing pieces. I don't need Java to split a string or concat an array of values in 2022, when most vendors now have incredibly robust built-in functions or UDF support. I don't need Airflow to define workflow order. I don't need to bug IT to stand up infra to run software they have to manage to support my workloads.
What you get is:
- Project structure and conventions for a team to collaborate on SQL-based data models
- Tooling that ensures data & transformations don't need to leave the database
- Tooling to generate SQL and execute statements in the order of dependencies, including parallelization
- Built-in support for incremental loading and snapshots
- Ability to control how models are managed (view vs. table) and optimize as needed
- Conventions for enriching metadata via YAML (such as for docs)
I currently have a small 2TB database on Snowflake with ~100 models that I can rebuild from scratch in about ~5 mins. I had to spend more time learning Snowflake syntax than I did having to learn or support dbt.