As you did not explain how the DE_1 - DE_2 comes about, I assumed, you need to use all possible combination of DE_Type to match with the parameter table.

The first CTE finds the combination of DE_Type after that join to the parameter table to get the Score .

For the case of tbl_customers it will not have any match with the parameter table at all. And it handled separately and UNION with the matching result.

with 
combi as
(
  select t1.Table_Name, 
         t1.Data_Element_Counts, 
         DE_1 = t1.DE_Type, 
         DE_2 = t2.DE_Type
  from   meta t1
         inner join meta t2 on  t1.Table_Name = t2.Table_Name
                           and  t1.DE_Type   <> t2.DE_Type
),
final as
(  
  select c.Table_Name, 
         c.Data_Element_Counts, 
         DE_Score = sum(p.Score)
  from   combi c
         inner join para p on c.DE_1 = p.DE_1
                          and c.DE_2 = p.DE_2
  group by c.Table_Name, c.Data_Element_Counts
)
select Table_Name, Data_Element_Counts, DE_Score
from   final 
union all
select Table_Name, Data_Element_Counts, DE_Score = 1
from   meta m
where  not exists
       (
           select *
           from   final x
           where  x.Table_Name = m.Table_Name
       )