In one of my project, I have one stage table with 3500 columns and i need to do left outer join with a lookup table to find matching business description. This lookup is required atleast for 500 columns..
Stage_table : custID , attr1, attr2, attr3, attr4........ attr3500 ( record count: 10million)
Lookup_Table : attr_id, source_value,target_definition ( record count: 10K)
Requirement is to lookup source values from lookup_table and populate target table. Target table is current kept as 200-300column of 15aux tables.
a. As the lookup is required for atleast 500 columns, even if i split equally the no. of lookup columns into 15aux tables, there will min 30 per SQL BTEQ. Is it fine to have 30 left outer joins on the same Lookup table instance (with different logical names) ?
b. What if i need to run multiple jobs in parallel, which in case, the same lookup table will be referenced in more than 100 times assuming 5 tables run in parallel ? will this have any impact on performance ?
c. Is there any better approach for this ?