I am importing and cleaning data from a datasource that have records like this:
The data needs sorting and rearranging based on the Event Type.
The destination schema would be something like this:
Create Table dbo.Destination ( DestinationId int not Null primary key clustered, EventType int not null, fkyCustomerId int not null, Constraint FK_Destination_Customers References dbo.Customers (pkyCustomerId), fkyCategoryId int not null Constraint FK_Destination_Categories References dbo.Categories (pkyCategoryId), fkyCompanyId int not null Constraint FK_Destination_Companies References dbo.Companies(pkyCompanyId), fkyLocationId int not null Constraint FK_Destination_Locations References dbo.Locations(pkyLocationId), Amount money not null Constraint DF_Destination_Amount Default(0) ) -- Lookup table Create Table dbo.Categories ( pkyCategoryId int not null primary key clustered, SourceCategoryId char(10) Not Null, CategoryName varchar(50) Not Null ) -- Lookup table Create Table dbo.Companies ( pkyCompanyId int not null primary key clustered, SourceCompanyId varchar(10) Not null, CompanyName varchar(50) Not Null ) -- Lookup Table Create Table dbo.Locations ( pkyLocationId int not null primary key clustered, SourceLocationId varchar(10) Not Null, LocationName varchar(50) Not Null )
For each Event Type, and there could be hundreds of event types, there are rules on what data exists in each of the
So for Event Type 1234,
Col0 has the mapping for the
Categories lookup and
Col1 has the mapping for the
However, for Event Type 5000,
Col0 contains the mapping for the
Companies lookup and
Col1 contains the mapping for
Also, there are situations when there will be null values for all
Col columns which means I need to map these to default category, company etc values.
I need to design an import process that can handle the existing rules and potentially new rules. The import will handle approx 2.5 millions rows per day and increasing.
What would be a good way to implement this kind of import. Nothing is set in stone here, apart from the the source data, so I am more than willing to entertain the idea of changing the schema etc.
The final goal is to transform the source data so it can be easily used for reporting purposes.