How I would approach the problem:

1. do you really need to migrate your logic from onprem to cloud? if not, use query federation to query your "gold" on-prem (dw?), once the onprem dw has reached end of life, no need for complex and potentially wrong re-implementation of sql

2.if 1. is not possible and you still want to run the same sql in the cloud, assuming the sql is ansi compatible...), I would put all the sql queries in a config file as text strings and replace the temp tables references with proper DLT notation (assuming tsql, e.g. #mytemp1 with live.mytemp1) then use a python script to produce the DLT pipeline by looping through the config (DLT will figure out the order and graph by itself). I would not recommend this for the simple reason that this will prevent a lot of optimizations (partitioning, clustering, watermarking, streaming etc ...)

3. I you have a lot of "gold" code onprem, I'm assuming you are talking about a legacy entreprise data model (probably not datavault though), in which case the best solution is, if the data source remain the same, to do a domain-based approach to building the dw as a lakehouse in the cloud with a medallion architecture (assuming you have the first layers in cloud as well, or are they onprem?). Once you have bronze and silver tables (usually these can be easily automated with configuration files), you can build the gold layer as data domains, which means dividing the tons of complicated onprem code in manageable chunks which you can independantly build specifically for the cloud. Make one or more DLT pipeline for each domain and then you can use jobs if the DLT pipelines have interdependencies. At some point, delta table triggers are coming to databricks as well in addition to schedule and file triggers, so you'll be able to update downstream tables as soon as an upstream table update is completed. Or you can use the REST api to produce the job that maintains interdepencies, so that also saves you a lot of clicks.