Does it make sense to add sortkeys and compression to staging tables which are truncated daily in Redshift if target tables already have them? Does it make it any difference when you're performing transformations when inserting into target table?
My concern is whether it helps or hurts the performance in any way.
Yes, it does. the sort keys will help you when you are reading data from the staging tables to insert it into the actual tables. (you probably do some sort of join between staging and real table to determine the deltas, right?). Make sure the sort keys are similar and that will be helpful.
Compression has pros and cons as you can imagine. I would test the same process with and without it to get to an actual conclusion - but sort keys (and distribution key - if applicable), definitely.
BTW, number of rows also play a part here - if you only have a few 100s rows, probably it doesn't t make any difference, of course.
