In our explores we are using multiple joins on several views with derived tables. These joins range from 7 to 12 on an average/explore and Model contains 4 to 5 of such explores. I am looking to merge these tables and form a large physical table on redshift or have a PDT defined to cache it and triggered every 24hours to increase query performance and also reduce validation time.
Does anyone have any thoughts on this? or a better approach?