ETL Testing - Oracle

Source : SYSTEM.EMP_SOURCE
Target  : SYSTEM.EMP_TARGET

/* TC01: Metadata check - Structure Validation */

SQL> DESC EMP_SOURCE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> DESC EMP_TARGET;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL>


/* TC02: Count Validation */

SQL> SELECT 'SOURCE',COUNT(*) AS CNT FROM SYSTEM.EMP_SOURCE
  2  UNION ALL
  3  SELECT 'TARGET',COUNT(*) AS CNT FROM SYSTEM.EMP_TARGET;

'SOURC        CNT
------ ----------
SOURCE         14
TARGET         14

SQL>

/* TC03: Duplicate Check */

SQL> SELECT EMPNO,COUNT(*) FROM SYSTEM.EMP_TARGET GROUP BY EMPNO HAVING COUNT(*)>1;

no rows selected

SQL>

/* TC04: NOT NULL Validation */

SQL> SELECT EMPNO,ENAME FROM SYSTEM.EMP_TARGET WHERE (EMPNO IS NULL OR ENAME IS NULL);

no rows selected

SQL>

/* TC05: Default Check */

-- NA

/* TC06: Data Validation */

SQL> -- source minus target
SQL> SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_SOURCE
  2  MINUS
  3  SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_TARGET;

no rows selected

SQL> -- target minus source
SQL> SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_TARGET
  2  MINUS
  3  SELECT EMPNO,ENAME,SAL,COMM,HIREDATE,DEPTNO FROM SYSTEM.EMP_SOURCE;

no rows selected

SQL>

/* TC07: Audit Columns Validation */ 

-- NA