Check Data or Primary Index Distribution in Table in Teradata

Teradata recommends a data skew threshold of no more than 5% across the AMPs . It is a rule of thumb, but not absolute. Small tables can be skew by their nature.

select

hashamp(hashbucket(hashrow(<PRIMARY_INDEX>))) as "AMP",

count(*) as "row count",

"row count" * 100.00 / sum("row count") over() as "% of total rows",

cast((max("row count") over() - min("row count") over()) * 100.00 /

nullif(min("row count") over(),0)

as decimal(9,2)) as "Teradata variance %",

case

when "Teradata variance %" < 5

then 'pass'

else 'fail'

end as "Teradata threshold (5%)"

from <DATABASE_NAME>.<TABLE_NAME>

group by 1

order by 1;

Example:

select

hashamp(hashbucket(hashrow(Sales_Tran_Seq_Num))) as "AMP",

count(*) as "row count",

"row count" * 100.00 / sum("row count") over() as "% of total rows",

cast((max("row count") over() - min("row count") over()) * 100.00 /

nullif(min("row count") over(),0)

as decimal(9,2)) as "Teradata variance %",

case

when "Teradata variance %" < 5

then 'pass'

else 'fail'

end as "Teradata threshold (5%)"

from DB.SALES_TRANSACTION

group by 1

order by 1;