Custom SQL usage recommendation

Is Custom SQL being proposed as a solution to replace ETL jobs or only a part of it? Trying to understand if there is any limitations on performance when we are using Custom SQL to deal with huge volumes of data and multiple data sources

it all depends on the complexity of the ETL processes. Many ETL processes can be handled by Custom SQL in Quicksight. However, if you require data orchestration, large amounts of data, tons of complex calculations, complicated joins, many CTEs etc. then other services can be used (DataBrew, DataGlue etc.)

As a rule of thumb, it is always better to move ETL processes to the database layer and let Quicksight handle the visualization. Sometimes however you cannot do the ETL in the database layer, and this is where Quicksight can help with the Custom SQL