amazon redshift - Optimize SQL query for better cluster performance and reduce disk utilization

Question

I have a very big query with quite a few case statements. This is bring the cluster performance down with 100% disc consumption. Processing data is ~16Tb and it take ~24hrs to compute often failing due to no disk space. Creating external tables reducing dependencies on other table doesn't seem to be helping much.

CREATE TEMP TABLE Merge_result 
AS
(SELECT DISTINCT id,
       id2,
       variant,
       department_name,
       pptt,
       aa1,
       CASE
         WHEN desc = 'lang_1' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND aa1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND aa1 >= 0.76 THEN 0
         ELSE 1
       END AS aa1_score,
       bb1,
       CASE
         WHEN desc = 'lang_1' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND bb1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND bb1 >= 0.76 THEN 0
         ELSE 1
       END AS bb1_score,
       cc1,
       CASE
         WHEN desc = 'lang_1' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND cc1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND cc1 >= 0.76 THEN 0
         ELSE 1
       END AS cc1_score,
       dd1,
       CASE
         WHEN desc = 'lang_1' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND dd1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND dd1 >= 0.76 THEN 0
        ELSE 1
       END AS dd_score,
       ee1,
       CASE
         WHEN desc = 'lang_1' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND ee1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND ee1 >= 0.76 THEN 0
         ELSE 1
       END AS ee1_score,
       ff1,
       CASE
         WHEN desc = 'lang_1' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_19' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_20' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_21' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_22' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_23' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_24' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_25' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_26' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_27' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_28' AND ff1 >= 0.76 THEN 0
         WHEN desc = 'lang_29' AND ff1 >= 0.76 THEN 0
         ELSE 1
       END AS ff1_score,
       gg1,
       CASE
         WHEN desc = 'lang_1' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_2' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_3' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_4' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_5' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_6' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_7' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_8' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_9' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_10' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_11' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_12' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_13' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_14' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_15' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_16' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_17' AND gg1 >= 0.76 THEN 0
         WHEN desc = 'lang_18' AND gg1 >= 0.76 THEN 0
       

Answer 1

Firstly, I would suggest you change the distinct to a group by. A distinct is way more expensive when running a query. See below for more info.

https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct#:~:text=Essentially%2C%20DISTINCT%20collects%20all%20of,performing%20any%20of%20that%20work.

Secondly, check your indexing on the source table.

I wouldn't worry too much about the case statements, other than being a maintenance nightmare it shouldn't be the cause of such a slow performance.