Testing DWH & DM Tables : column Fullness and daily record trend

Hi ,

One of our current projects we are developing a DataWarehouse.

We already developed our ETL processes and reports , but it is time for deploy that in prepord environment .

The first test set is contains check null control of all the columns in DWH and DM tables. And the second test is hourly /daily / monthly (depends on time increment of the table ) record count trend test.

For doing these tests , I use this scripts below ;

#1 : FULLNESS TEST : First create this table for keeping the result of query. Second generate all the queries automatically

CREATE TABLE ETL_USER.DM_TABLES_FULLNESS  (
SCHEMA_TABLE_NAME VARCHAR(255 OCTETS) ,  
schema_table_column_name VARCHAR(255 OCTETS) , 
COLUMN_NULL integer  , 
COLUMN_FULL integer  )    
SELECT 'insert into ETL_USER.DM_TABLES_FULLNESS select '''||trim(TABLE_SCHEMA)||'.'||TABLE_NAME||''' as SCHEMA_TABLE_NAME  ,''' ||trim(TABLE_SCHEMA)||'.'||TABLE_NAME||'.'||column_name ||''' as schema_table_column_name  , sum ( CASE WHEN ' || column_name ||' is null then 1 else 0 end ) as COLUMN_NULL , sum ( CASE WHEN  '  
 || column_name || ' is not null  then 1 else 0 end ) as  column_FULL  from ' || trim( table_schema )|| '.' || table_name  ||' ; '
 FROM all_tab_columns  
 WHERE table_schema = 'DM' 

#2 : INCREMENTAL RECORD COUNT TREND :

CREATE TABLE 
 ETL_USER.DM_TABLE_COUNTS
 (  RECORD_COUNT integer ,
 SK_DATE_HOUR integer ,
 DM_TABLO_ADI varchar(255)
 ) ; 
SELECT DISTINCT  'insert into ETL_USER.DM_TABLE_COUNTS  select count(*) as RECORD_COUNT , sk_date_hour , '''||  table_name   ||''' as DM_TABLO_ADI from  DM.'||TABLE_NAME  || ' group by SK_DATE_HOUR  ; ' FROM all_tab_columns 
 WHERE TABLE_SCHEMA= 'DM' 
 AND column_name ='SK_DATE_HOUR' 
SELECT DISTINCT  'insert into ETL_USER.DM_TABLE_COUNTS  select count(*) as RECORD_COUNT , sk_date_hour , '''||  table_name   ||''' as DM_TABLO_ADI from  DM.'||TABLE_NAME  || ' group by SK_DATE_HOUR  ; ' FROM all_tab_columns 
 WHERE TABLE_SCHEMA= 'DM' 
 AND table_name IN 
 (  
 SELECT DISTINCT table_name  FROM all_tab_columns 
 WHERE TABLE_SCHEMA= 'DM' 
 AND column_name ='SK_DATE'
 minus 
 SELECT DISTINCT table_name  FROM all_tab_columns 
 WHERE TABLE_SCHEMA= 'DM' 
 AND column_name ='SK_DATE_HOUR'
 ) 

I think it will be a better soluiton create a stored procedure doing all this process automatically , I will add also SP codes when I do that.

I hope this scripts can give you some idea.

Bests ,