The biggest challenges about metadata driven ETL/ELT is supporting versioning and DTAP based development (development-test-acceptance-production).

I’ve worked before with versioning inside a SQL server and moving configurations using manual input, manual scripts or SQL scripts as part of deployment. This way of working gives a lot of challenges, like configuration mismatch, typing errors, minimal retrace ability.

A better solution is in maintaining the version of truth inside GIT and pushing it, using CI/CD principles, to a SQL Database or Cosmos database. This way of working naturally supports DTAP development.

 The controlling and process definition parts of the meta data are governed in json files inside GIT. These definitions are pushed to a database that is accessible by the orchestration and ETL processes.

The metadata base can contain other data that are environment specific. Like queuing and logging mechanism’s.