I work in one of large tech companies, and I can attest that while the idea seems very neat in theory (esp if your schemas are typed), and even if you define an api for defining new building blocks, sooner or later people realize that they need to dynamically adjust parts of the pipeline, and they write components to dynamically set and resolve these, and then other components on top of these components, and then complements for composing components - and now you forced yourself into implementing a weird and hard to debug functional programming language in yaml which is not a place someone wants to find themselves in :'(
one lesson I learned from this: any bit of logic that defines a computation should prefer explicit imperative code (eg python) over configuration, because you are likely to eventually implement an imperative language in that configuration language anyway
> sooner or later people realize that they need to dynamically adjust parts of the pipeline
The customer is the hard part in all of this, but there is respite if you are patient and careful with the tech.
If you are in a situation where you need to go from one SQL database to another SQL database, the # of additional tools required should be zero. Using a merge statement & recursive CTEs per target table, you can transform any schema into any other. Most or all of the actual business logic can reside in the command text - how we filter & project data into the target system.
If we accept the SQL-to-SQL case has a good general solution, I would then ask if it is possible to refactor all problems such that they wind up with this shape in the middle. All of that nasty systems code could then be focused more on loading and extracting data into and out of this regime where it can be trivially sliced & diced. Once you have something in Postgres or SQL Server, you are at the top of the hill. Everything adapts to you at that point. Talking to another instance of yourself - or something that looks & talks like you - is trivial.
The other advantage with this path is that refactoring SQL scripts is something the customer (B2B) can directly manage in many situations. The entire pipeline can live in a single text file that you throw around an email chain. You don't have to teach them things like python, yaml or source control.
In fact, I also converge to sql as a universal data transformation language. External analogs include things like duck db. Unfortunately, even with pipe syntax sql lacks expressiveness causing me to revert to c-style macros in sql (eg making table name dynamic), which in the long run makes things far less maintainable if anything.
yeah, most projects when you spot a config file, its complexity will tend to scale with the increasing complexity of the domain you capture.
so either it's very small/mature and you don't have to worry too much, or in the active development case your config files are pretty much the instruction set of some kind of logical foggy vm... and eventually a whole environment of tools etc. will "compile down" to your config files and you get a pain knot to endlessly massage...
one lesson I learned from this: any bit of logic that defines a computation should prefer explicit imperative code (eg python) over configuration, because you are likely to eventually implement an imperative language in that configuration language anyway