Same connection to Azure SQL, one flow NOW() returning UTC time while the other returning local machine time

Issue

Same connection to Azure SQL, one flow NOW() returning UTC time while the other returning local machine time. Why?

Environment

Tableau Prep Builder
Tableau Server
Microsoft Azure SQL

Resolution

If NOW() step is before any JOIN step, the flow running will return database time. If NOW() step is after any JOIN step, the time will be Tableau Server time.
User-added image

Cause

Depending on where NOW() is executed, a different timze is used.
This also depends on the type of data sources used. Oracle always return the Tableau Server time regardless of where NOW() is executed.

Additional Information

Not only join, but also DST calculation will impact the final result of NOW().
e.g. The DST calculation as below will result in NOW() returning Tableau Server time.
IF DATETRUNC('week',MAKEDATE(YEAR([DATE TIME UTC]),04,01)) <MAKEDATE(YEAR([DATE TIME UTC]),04,01)
THEN DATEADD('hour',3,(DATEADD('day',7,DATETRUNC('week',MAKEDATE(YEAR([DATE TIME UTC]),04,01)))))
ELSE DATEADD('hour',3,DATETRUNC('week',MAKEDATE(YEAR([DATE TIME UTC]),04,01)))
END