All excellent points. The best is when most workloads on the cluster use a temp table with an identical name. Then someone comes along and accidentally drops the TEMP from the their temp table ctas and it gets smashed into the public schema.
“Table already exists…”scheduled job run failure galore, fun times.
2. Temp tables do not participate in k-safety, so you avoid that performance cost.
3. I suspect I've seen something odd happening with compression with temp tables. I've not investigated.
4. Using `CREATE TABLE AS` is, for me, verboten. Absolutely forbidden. This is because Redshift selects column encodings, and does a very, very poor job of doing so. Never let Redshift select column encodings (or sort keys, or distribution keys, or rely on auto-vacuum, or auto-analyze, and above all, never use AutoWLM).