I need to design an error handling framework for an ETL engine which uses SQL Loader to load data to staging tables, and uses PL/SQL procedures to load data into the warehouse, and java interaces which will call sql loader scripts and stored Procedures.
I load only deltas into the warehouse every day, by comparing the record check sums against the check sums which i saved in the previous version of load,and i use MULTI TABLE insert statements to load data into warehouse schema.
Now what i want to know is, what would be the best approach for handling, DATA ERRORS and Business Rule Validations.
i am also attaching a sample procedure i use to load data....
reate or replace PROCEDURE SP_LOAD_Financial_Consultants (in_version IN NUMBER, in_source_id IN VARCHAR2) AS
1. Check for deltas using Checksum
2. Insert into Advisor History tables all the new advisors and changes.
3. Insert into event table changed record id, along with old and new versions
-- Include Exception handling
v_fc_prefix constant VARCHAR2(20) := mas.advisor_prefix;
v_fc_version_new NUMBER := in_version;
v_fc_source VARCHAR2(20) := in_source_id;
Outer join Staging and Materialized tables to retrive
all the records that mathed External IDs but did not match check sums
along with their META IDs and also the records which did not match the
External IDS with META IDs Null, and generate META IDs at the time of insertion.
Records with Meta ID value Null are considered New records and the rest are changed ones.
WHEN meta_fc_id IS NULL THEN
-- conditional insert if the advior is a new advisor also generate new meta ID
INTO financial_consultant(version, source_id, meta_fc_id, ext_fc_id, fc_number, fc_name, fc_firm_name, fc_branch_code, fc_status, phone_no, addr1, addr2, addr3, addr4, city, state, country, zip_code, email, fax_no, checksum)
VALUES(v_fc_version_new, v_fc_source, v_fc_prefix||advisor_seq.nextval, ext_fc_id, fc_num, fc_name, firm_name, branch_code, fc_status, phone_no, addr1, addr2, addr3, addr4, city, state, country, zip_code, email, fax_no, stg_cksum)
WHEN meta_fc_id IS NOT NULL THEN
-- conditional insert for changed advisors into advsor and event table.
INTO financial_consultant (version, source_id, meta_fc_id, ext_fc_id, fc_id, fc_name, fc_firm_name, fc_branch_code, fc_status, phone_no, addr1, addr2, addr3, addr4, city, state, country, zip_code, email, fax_no, checksum)
VALUES(v_fc_version_new, v_fc_source, meta_fc_id, ext_fc_id, fc_num, fc_name, firm_name, branch_code, fc_status, phone_no, addr1, addr2, addr3, addr4, city, state, country, zip_code, email, fax_no, stg_cksum)
INTO fc_event(old_version, new_version, meta_fc_id, entity)
VALUES(old_version, v_fc_version_new, meta_fc_id, 'FIN_CONSULTANT')
-- query to fetch the deltas required to insert (only the changed and new ones from staging)
WITH fc AS
SELECT mv.version old_version, mv.meta_fc_id, stg.ext_fc_id, stg.fc_status, stg.firm_name,
stg.fc_name, stg.phone_no, stg.addr1, stg.addr2, stg.addr3,
stg.addr4, stg.city, stg.state, stg.country, stg.zip_code,
stg.branch_code, stg.fc_num, stg.email, stg.fax_no,
checksum(stg.ext_fc_id || stg.fc_status || stg.firm_name || stg.fc_name || stg.phone_no || stg.addr1 || stg.addr2 || stg.addr3 || stg.addr4 || stg.city || stg.state || stg.country || stg.zip_code || stg.branch_code || stg.fc_num || stg.email || stg.fax_no) stg_cksum,
FROM fc_stg stg,
WHERE stg.ext_fc_id = mv.ext_fc_id(+)
WHERE fc.stg_cksum <> fc.fd_cksum;
-- inline FA table calculates the staging check sums, also retrives MV checkums into its columns.
WHEN no_data_found THEN