Advice on how to approach data cleaning & importing

Question

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.

Answer 1

I think the biggest challenge will be doing the event type transformations efficiently, so I'm going to focus on that and ignore the schema completely.

My first thought (read: I've never actually tried this) is to use dynamic SQL to build up a SELECT statement that will do the data transformation such that values end up in their correct columns (i.e., Col0 --> CompanyId, or whatever). This boils down to a mapping table consisting of the event type, the source column, the target column, and a default value if required.

Once you have the mapping table (or tables, depending on how complex this actually is), you can use it to dynamically build a SQL statement of the form:

SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

Build the statement as part of the ETL process. If you need to add a new event type, just add the required column mappings to the mapping table, and they'll be handled the next time the ETL process runs.

Once the source data is in this kind of state, the rest of the import process should be pretty standard.

Answer 2

OK so after a bit of jiggery pokery I finally came out with this solution.

I created a mapping table like this:

Create Table dbo.MappingTable
(
EventTypeId int
IsCompanyPresent bit,
CompanyLocation varchar(50),
CompanyDefault varchar(50),
IsCategoryPresent bit,
CategoryLocation varchar(50),
CategoryDefault varchar(50),
...
)

And then for each Event Type populated the mapping table with the relevant values.

As an example if the Category was present then the IsCategoryPresent value would be 1, the CategoryLocation value would be Col2 and the CategoryDefault value would be 1.

The CategoryLocation value corresponds to the column in the source table and the CategoryDefault value corresponds to the default value used in the new lookup table.

If the Category is not present then the IsCategoryPresent value would be 0, the CategoryLocation value would be SourceTableId and the CategoryDefault value would be 1. I have to set the location to a valid column in the SourceTable or SQL Server throws an error.

The SQL that I now have is this stored procedure (which I execute for each event type in the mapping table) The load times for about 2.5 - 3 millions rows have been less than 1 minute - which I am more than happy with.

Create Proc [dbo].[XXXXXXX]

@EventType int

As Begin

Declare @Sql varchar(max)

Select @sql = 'Insert Into dbo.Destination
                Select  re.SourceTableId,
                        et.pkyEventTypeId,
                        c.pkyCustomerId,
                        bci.CompanyId,
                        rec.pkyCategoryId,
                        s.pkyLocationId,
                        Case 
                        When rem.IsAmountPresent = 0 Or Len(IsNull(re.' + rem.AmountLocation + ', space(0))) = 0 Then rem.AmountDefault 
                        Else re.' + rem.AmountLocation + '
                        End as [Amount]

                From dbo.SourceTable re
                Left Join dbo.MappingTable rem on re.Event_Type = rem.EventType
                Join dbo.EventTypes et on rem.EventType = et.EventTypeId

                Join dbo.Companies bci on bci.SourceCompanyId =  Case 
                                                                        When rem.IsCompanyPresent = 0 Or Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0 Then rem.CompanyDefault 
                                                                        Else  re.' + rem.CompanyLocation + ' 
                                                                        End

                Join dbo.Categories rec on rec.SourceCategoryId =   Case 
                                                                        When rem.IsCategoryPresent = 0 Or Len(IsNull(re.' + rem.CategoryLocation + ', space(0))) = 0 Then rem.CategoryDefault 
                                                                        Else  re.' + rem.CategoryLocation + ' 
                                                                        End
                Left Join dbo.Customers c on c.CustomerId = Case 
                                                        When rem.IsCustomerIdPresent = 0 Or Len(IsNull(re.' + rem.CustomerIdLocation + ', space(0))) = 0 Then rem.CustomerDefault
                                                        Else re.' + rem.CustomerIdLocation + '
                                                        End

                Join dbo.Locations s on s.SourceLocationId = Case
                                                        When rem.IsLocationPresent = 0 Or Len(IsNull(re.' + rem.Location + ', space(0))) = 0 Then rem.LocationDefault
                                                        Else re.' + rem.Location + '
                                                        End


                Where rem.EventType = ' + Cast(rem.EventType as Varchar)

From dbo.SourceTable re
Join dbo.MappingTable rem on re.Event_Type = rem.EventType
Where rem.EventType = @EventType

Exec (@sql)

End

I am joining on to the Lookup tables and then using a case statement along with dynamic SQL to determine which column to use. So if a value is present then I pick out the column from the source table. If a value isn't present then I use a default value which ensures that I always get a FK value to insert in to my destination table.

You will also note that I have this check in my join too

Len(IsNull(re.' + rem.CompanyLocation + ', space(0))) = 0

This is because I found out that some columns weren't null but blank values. Which caused me a bit of grief too.

I have been running this for a few days and it seems to work pretty well.

Having said that, any comments would be greatly appreciated!