Data Warehouse: Concepts on ETL Processes (Transforming)

Data Warehouse: Four (4) typical transformation and loading scenarios

(a) Key Lookup Scenario
(b) Business Rule Violation Scenario
(c) Data error scenario
(d) Pivoting Scenario



(a) key Lookup Scenario

• You need to load sales transaction data into a retail Data Warehouse
• In the operational system, each product is identified by Uniform Price Codes (UPCs);
• In the Data Warehouse, product is identified by product_id.
• You need to

(1) Map UPC --> PRODUCT_ID; and
(2)As you load the source data, lookup the map table and transform the data accordingly

(b) Business Rule Violation Scenario

• Some UPC Codes are invalid
• In which ways can you implement the transformation and handle the invalid UPCs?
• i.e: new sales data might not have valid UPC codes

(1) use Create Table As Select (CTAS) to identify and log invalid UPCs
(2) use an outer join (+) and convert invalid UPCs to NULL
(3) use multi-table INSERT and separate the invalid UPCs to a different table

(1) use CTAS to identify and log invalid UPCs

(2) use an outer join (+) and convert invalid UPCs to NULL

(3)use multi-table INSERT and separate the invalid UPCs to a different table

(c) Data Error Scenarios

• If the quality of the data is unknown, you may face unexpected errors, such as a a failed datatype conversion, or a constraint violation.
• Such errors can happen independently of whether the UPC for the particular record is or not valid.
• Here you can use a DML error logging table to capture unexpected errors.