Hi @olusegun83 -
Thank you so much for the details. Please find answers from my side, however we can get some experts advise as well.
Has anyone encountered similar performance or date conversion issues with complex custom SQL in QuickSight?
[ Sanjeeb:] When you are using complex sql ( or custom sql) in QuickSight, essentially the sql is going to run in database server. There is very limited scope in QuickSight in terms of performance. However you can create a final table ( putting all complex sqls) and use the final table as QuickSight source. This is good when your underline data is not changed frequently. Or you can enable SPICE mode and bring the final data to QuickSight SPICE and use SPICE for reporting.
Are there recommended strategies for optimizing such queries when using window functions and multiple CTEs in QuickSight?
[ Sanjeeb:] If you have multiple CTE, I will suggest push to Database layer and create a final table or materialized view on top of that.
Would it be better to push more of the logic to the QuickSight front end (using calculated fields and table calculations) rather than handling everything in custom SQL?
[ Sanjeeb:] It depends. If you have calculation which are dynamic in nature, better to put at reporting layer. if you have any complex calculation, better to push at ETL or processing layer.
Any advice on ensuring QuickSight properly recognizes date/datetime fields when converting from TIMESTAMP WITH TIME ZONE?
[ Sanjeeb:] QuickSight should do the date format. if you can give samples where you are not able to convert or standardize the date format, community members can help you.
Any insights, optimization tips, or best pratices?
[ Sanjeeb :] There are some optimization tips are there in QuickSight community. Please do a search. If you are not able to find, let us know.
Tagging other experts for their advise.
@DylanM @duncan @David_Wong @shravya @Biswajit_1993 @prantika_sinha
Regards - Sanjeeb