Advice on how to approach data cleaning & importing
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.