> 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.
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.