ETL test reference document

 

This document describes the ETL testing process and general project conditions to describe the ETL testing method.

ETL test Flowchart

Test phase

1,Requirement Analysis

Familiar with business processes and business rules, analyze the ing relationship between the source table and the target table as required, and parse the business data flow diagram:

 

1,Test Analysis

Test point:

ETL routine check:

1. Whether the ETL script has run errors and the script running time (view the Execution Plan)

2. Whether the error handling mechanism of ETL scripts is complete (Code review)

3. Does ETL script support rollback?

Business Logic check:

1. Check the data volume. Check whether the number of records is as expected

2. uniqueness check.

Duplicate primary key (cookie_id, member_id)

3. Check the correctness of business field conversion. Whether the indicator calculation is correct. sampling check.

The source table and target table each take a certain number of records to determine whether the field ing is correct (ing fields ).

Is the metric calculated correctly (index calculated field)

4. Random verification (obtain several data records randomly to check whether there are garbled data or abnormal data.

 

Test focus

Key business in the project, and the complex logic part as the test focus

Description of Case Division:

Use Cases are designed based on each indicator.

Test policy:

Test Strategy: incremental test (submit for test)

Test method: Query-based testing (the expected results are displayed based on SQL, so that the data changes and the results remain unchanged. Facilitate regression)

2,Standard dataset Construction

Data creation is divided into two aspects. One is to directly extract online data, and the other is to create abnormal data using scripts.

3.1 Use dblink to extract online data. When extracting online data, you must note that the test data is comprehensive. That is, the test data is fully covered. For example, for the sex field, when extracting online data, you need to extract male and female, not just male or female, so that the test data will be missing. When extracting data from associated tables, you can extract the primary table first, and then extract the child table based on the data in the primary table.

3.2 Create exception data. The exception data can be considered from the following aspects: field type, field length, null value, business exception value, unique constraint Value

3,Test Case Design

The test cases can be designed separately or with the scheduling idea. When the scheduling method is used for design, multiple cases can be verified at a time, and regression is also convenient. Here we will talk about the design of the test case of the scheduling idea.

Design Concept:

 

Total tune script:

CREATE OR REPLACE PACKAGE BODYPKG_KPI_TCIS
PROCEDURESCHEDULER (
 /*************************************** ******************************
* Parameter:
* Authoer XIANGMIN. MENGXM
* Time 2009-6-26
*
**************************************** *****************************/
P_DATEIn Date DefaultTRUNC (Sysdate))
 Is
V_DATEDate: = TRUNC (P_DATE );
 Begin
Delete FromTest_mapWhereYyyymmdd = v_date;
Commit; -- Empty the test table before scheduling
Ref_kpi_tc_001 (v_date); -- Use Case 1
Ref_kpi_tc_002 (v_date); -- Example 2
End;
EndPkg_kpi_tc;

Test Case 1

Create Or Replace ProcedureRef_kpi_tc_001 (p_dateIn Date DefaultTrunc (Sysdate))Is
V_idNumber;
V_dateDate: = Trunc (p_date );
 Begin
SelectA. ID into v_idFromSrc_a;

Insert IntoTest_map (yyyymmdd, ID, X)Values(V_date, v_id, X );
 Commit;
End;

 

1,Test result Verification

Exec procedure _ (); -- returns the result of the script under test.

Exec ref_kpi_tc_001 (); the test result is obtained.

 

Result Verification:

The first step is to verify whether the number of records is consistent. If the number of records is inconsistent, there must be a problem. Check the problem and find out the cause.

Step 2 check whether the value is correct when the number of records is consistent.

Method 1: use minus

Select*FromTarget_a
Minus
Select*FromTest_map;

-- Note: You must change the positions of the two tables for comparison. When the Minus function is compared, take the first table as the standard to find out the inconsistency between the first table and the second table. That is, the result of finding the inconsistency between the sumdt0 table and the map table

Method 2: write a script for verification

2,Post-Release

Observe data trends and monitor data on related platforms. After the project is released, we can observe the data fluctuation trend. Generally, the data fluctuation is within a certain range and follows certain principles. If we find that the data fluctuation exceeds the expected range, this requires special attention.