ETL Scenarios

Introduction

This page describes how data could be loaded into OpenActuary's staging database using Motor as an example.

These scenarios start from the assumption that some kind of operational data store contains policy information that is being continuously updated. Periodically, a job is run which identifies records in that data store that have been added or updated since the last time the job was run. That job then needs to upload that data into OpenActuary.

Keep in mind that at all times that the OpenActuary system is a policy logging system - it records policy events and all the data associated with policy when the event happened. It is not a policy administration system. So, it doesn't need to avoid storing the same information twice, in fact for ease of use it is often better that it does store duplicates, and records are never updated once they have been inserted.

Scenario 1: Uploading a policy as new business

This is the simplest scenario, because we don't have to worry about any older records in OpenActuary which relate to the same policy. However, we do still need to make sure that the FKs are correct. We will assume that the insurance company knows this policy as 84848 (that is the PR_POL_SYS_ID in some systems).

First you insert the polPolicy record:

insert into polPolicy (
    polCoverTypeIDcov,
    polInsuredTypeIDins,
    polReasonIDrea,
    polExternalSystemId
    -- (other column names here as necessary)
)
values (
    'COMPREHENSIVE',
    'PERSONAL',
    'NEW BUSINESS',
    '84848'
    -- (other values here as necessary)
);
Notice that we didn't populate polID. polID is a UUID, as are all of the IDs in the major tables, which is automatically populated by a trigger when a record is inserted. It is important that this is a UUID because this record will at some point be merged into the master database. As a UUID we can be sure that no other record in the master will be using this ID.

Now you need to add a vehicle to the policy:

insert into vehVehicle (
    vehPolicyIDpol,
    vehBodyIDbod,
    vehParkedIDpar,
    vehSumInsuredLocalAmount,
    vehSumInsuredLocalIDccy
    -- (other columns here as necessary)
)
values (
    (select polID from polPolicy where polExternalSystemID='84848' and polReplaced is null),
    'CONVERTIBLE',
    'GARAGE',
    15000,
    'GHS'
    -- (other values here as necessary)
);

You might notice a few things here:

1) See how vehPolicyIDpol is populated. This sub-select allows us to avoid having to ever refer to polID directly, we're only using polExternalSystemID - which is the insurance company's ID for the policy

2) The polReplaced=null criteria - in truth this is superfluous for a new business record. It's purpose will become clear in the other scenarios, it is used here just to keep the queries consistent. 

Other tables would be filled in using a similar approach. 

Scenario 2: Uploading a policy as a renewal

In this scenario there already is one (or many more) other records in polPolicy which relate to the policy being uploaded. We will continue use the same insurance company policy in this example, so the existing record in polPolicy will have an polExternalSystemID of 84848.

The first step is to mark any other records for this policy as "replaced":

update polPolicy set polReplaced=NOW() where polReplaced is null and polExternalSystemID='84848';

The fact that polReplaced is not null is taken to mean that that policy records has been replaced.

From now on, the process is extremely to scenario 1. The only difference being the reason column as you would expect:

insert into polPolicy (
    polCoverTypeIDcov,
    polInsuredTypeIDins,
    polReasonIDrea,
    polExternalSystemId
    -- (other column names here as necessary)
)
values (
    'COMPREHENSIVE',
    'PERSONAL',
    'RENEWAL',
    '84848'
    -- (other values here as necessary)
);

Now you need to add a vehicle to the policy. Notice that we are inserting an almost identical record. This is intentional :

insert into vehVehicle (
    vehPolicyIDpol,
    vehBodyIDbod,
    vehParkedIDpar,
    vehSumInsuredLocalAmount,
    vehSumInsuredLocalIDccy
    -- (other columns here as necessary)
)
values (
    (select polID from polPolicy where polExternalSystemID='84848' and polReplaced is null),
    'CONVERTIBLE',
    'GARAGE',
    15000,
    'GHS'
    -- (other values here as necessary)
);

Now it is clear why the polReplaced is null criteria is important. This is the means by which we can be sure that we are linking this vehVehicle to the most recent version of the policy in polPolicy - because only the most recent version will have a null in polReplaced.

Scenario 3: Uploading a policy when a claim is made

Recording a claim, at first sight might appear to present some problems. A claim may relate to the current policy (i.e. the latest one in ARDR that has a null in polReplaced) or it might relate to an old policy that has already been replaced - which might mean that we're writing an older record into the database than ARDR already has? Sounds wrong? But remember, ARDR is a log of policy events not a policy administration system. Events are record in the order that they occurs, without respect to the date of the records they relate to.

Just like in scenario 2, we start by making sure that any old polPolicy records for this policy are marked as replaced:

update polPolicy set polReplaced=NOW() where polReplaced is null and polExternalSystemID='84848';

Uploading the policy information is very similar to the previous scenarios:

insert into polPolicy (
    polCoverTypeIDcov,
    polInsuredTypeIDins,
    polReasonIDrea,
    polExternalSystemId
    -- (other column names here as necessary)
)
values (
    'COMPREHENSIVE',
    'PERSONAL',
    'CLAIM',
    '84848'
    -- (other values here as necessary)
);

You then insert the vehicle involved in the claim, we'll insert the others later:

insert into vehVehicle (
    vehPolicyIDpol,
    vehBodyIDbod,
    vehParkedIDpar,
    vehSumInsuredLocalAmount,
    vehSumInsuredLocalIDccy
    -- (other columns here as necessary)
)
values (
    (select polID from polPolicy where polExternalSystemID='84848' and polReplaced is null),
    'CONVERTIBLE',
    'GARAGE',
    15000,
    'GHS'
    -- (other values here as necessary)
);

Then insert the claim itself:

insert into clmClaim (
    clmPolicyIDpol,
    clmVehicleIDveh,
    clmClaimTypeIDcla,
    clmAmountPaidAmount,
    clmAmountPaidIDccy
    -- (other column names here as necessary)
)
values (
    (select polID from polPolicy where polExternalSystemID='84848' and polReplaced is null),
    (select vehID from vehVehicle join polPolicy on vehPolicyIDpol=polID where polExternalSystemID='84848' and polReplaced is null),
    'DAMAGE TO VEHICLE BY FIRE',
    2500,
    'GHS'
   -- (other values here as necessary)
);

For a claim's details to be complete, as well as the clmClaim record you should also create either a accAccident record or a theTheftFire record to hold the specific details. We'll use a theTheftFire as the example here.

insert into theTheftFire (
   theClaimIDclm,
   theAlarmOn,
   theAnyToolsInVehicle
   -- (other column names here as necessary)
)
values (
    (select clmID from clmClaim join polPolicy on clmPolicyIDpol=polID where polExternalSystemID='84848' and polReplaced is null),
    true,
    false
    -- (other values here as necessary)
);

You can now add any other vehicle records. We had to split the creation of the vehicle records into two parts (one earlier in the scenario and one now). The first insert, above, inserted the details of the vehicle that was involved in the claim. Doing that vehicle alone meant that we could easily link the claim to it without fear of picking up the wrong vehicle. It is safe now though to add any other vehicles. The insert statement is exactly as before, just with other details to match the vehicle.

insert into vehVehicle (
    vehPolicyIDpol,
    vehBodyIDbod,
    vehParkedIDpar,
    vehSumInsuredLocalAmount,
    vehSumInsuredLocalIDccy
    -- (other columns here as necessary)
)
values (
    (select polID from polPolicy where polExternalSystemID='84848' and polReplaced is null),
    '4X4',
    'ON ROAD',
    50000,
    'GHS'
    -- (other values here as necessary)
);
0 Attachments
1216 Views
Average (0 Votes)
Comments
No comments yet. Be the first.