You can configure full

SQL ELT optimization

to push an Expression transformation to process in Microsoft Azure Synapse SQL.

You can add an Expression transformation to each of the sources in the mapping, followed by a join downstream in the mapping. Additionally, you can add multiple Expression transformations that branch out from a transformation and then branch in into a transformation downstream in the mapping.

When you configure an Expression transformation, consider the following rules to include variables in the expression:

You cannot use variables where you are using the value assigned while processing a previous row for calculations in the current row. if you do, the mapping runs without
SQL ELT optimization
.
The variables can be nested, but you cannot refer to a variable before it is defined in the expression.
If the variables are not defined in that order, the mapping runs without
SQL ELT optimization
.
For example,
 var: AGEPLUS2 = AGEPLUS1 + 1
var: AGEPLUS1 = AGE + 1
out: NEXTAGE = AGEPLUS2 + 1
Here, AGE +1 is defined later. AGEPLUS2 in the first variable refers to AGEPLUS1 and remains unresolved.
To resolve this, specify the variables in the following order:
 var: AGEPLUS1 = AGE + 1
var: AGEPLUS2 = AGEPLUS1 + 1
out: NEXTAGE = AGEPLUS2 + 1
The variables cannot have an expression that is cyclic or refers to itself:
For example,
 var: AGEPLUS1 = AGEPLUS2 + 1
var: AGEPLUS2 = AGEPLUS1 + 1
out: NEXTAGE= AGEPLUS2
Here, AGEPLUS1 refers to AGEPLUS2 and remains unresolved.