I have use case where there are 50 transformations , basically compare two fields in the table and create a new field
field_1=field_1_0 as field1_match
field_2=field_2_0 as field 2- match
when i create this transformation Dremio is creating a quite large SQL as each step a new SQL Subquery and ends up with massive nested Subqueries.
My Question is , is there any utility which can collapse / optimise all the nested into one single Query. The issue i am facing the big nested query is pushed down to the database which is quite slow.
Understand that my collapsing, we miss the traceability of transformation , which is fine with me.
Is there any external tool which can take big nested SQL into one SQL non-nested one ?
Thanks in advance