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;

AMP

row count

% of total rows

Teradata variance %

Teradata threshold (5%)

0

47479010

5.00

0.24

pass

1

47510704

5.01

0.24

pass

2

47395857

4.99

0.24

pass

3

47506480

5.01

0.24

pass

4

47440605

5.00

0.24

pass

5

47400512

5.00

0.24

pass

6

47444403

5.00

0.24

pass

7

47407178

5.00

0.24

pass

8

47476876

5.00

0.24

pass

9

47445181

5.00

0.24

pass

10

47437059

5.00

0.24

pass

11

47399911

5.00

0.24

pass

12

47449336

5.00

0.24

pass

13

47409955

5.00

0.24

pass

14

47461295

5.00

0.24

pass

15

47463932

5.00

0.24

pass

16

47454532

5.00

0.24

pass

17

47413774

5.00

0.24

pass

18

47440080

5.00

0.24

pass

19

47447863

5.00

0.24

pass

 

 

Comments