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 col
columns.
So for Event Type 1234, Col0
has the mapping for the Categories
lookup and Col1
has the mapping for the Companies
lookup.
However, for Event Type 5000, Col0
contains the mapping for the Companies
lookup and Col1
contains the mapping for Locations
.
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.