I would consolidate the consumer claims and policy linked claims into a single table. There would three fields in the table:
Consumer_ID, PolicyWarpolrev (ID) and Claim_Number.
For policy linked claims, all three fields would be populated, for direct consumer linked claims, the PolicyWarpolrev field would be null.
I don't know the structure of your input data sources, so I can only give the skeleton of a load script which you will have to adapt to your data. It would look something like:
DataTable: LOAD consumer_id, claim_number ... other fields ... FROM direct claims data source; Concatenate (DataTable) LOAD consumer_id, policy_warpolrev ... other fields ... FROM policy data source; Join (DataTable) LOAD policy_warpolrev, claim_number ... other fields ... FROM policy claims data source; Join (DataTable) LOAD consumer_id, ... consumer data fields ... FROM consumer masterdata source;
Load the rest of your data normally. The last join may not be necessary, but may improve performance if you have a large data set.
Hope that helps