I work for a Third Party Administrator. One of my primary job duties is converting data from other sources. When our company buys another company or we get a new client, I convert their data into our system. The new client’s we receive can have anywhere from 2,000 to 40,000 claims, 5,000 to 600,000 payments, and 5,000 to 600,000 notes.

I receive data in every format you can imagine. I have found the following steps useful when converting new data.

1. Import the data into Conversion Database worktables.
2. Place an identity column on the end of each record.
3. Setup Staging Tables that are similar to you production file tables. This staging area tables have blank primary key fields, that will be filled in when you import the data into a pre-production or production database.
4. In the Staging Area, you create a standard set of stored procedures that you run against this data. The stored procedures report any inconsistencies within the data and also inconsistencies between the new data and your production data.

Here is a sampling of what my stored procedures look for.

1. Does the SSN you are importing match any SSNs with in your database. If so, are they the same name
2. Does all instances of a SSN have the same first name / last name combination
3. Is this a valid SSN
4. Do the Payment detail amounts sum up to the Payment Header Amounts
5. Are all From Dates before To Dates
6. Are all necessary fields filled in
7. Are there any invalid codes in the new data

1. Once inconstancies have been fixed, create standard stored procedures that transfer the new data to a Pre-production database. Have code in these stored procedures to update the staging tables with the primary key values that are generated.
2. Verify the data imported correctly.
3. Have a standard test plans and signoff sheet for the business user to test the data, within the application.

1. Verify data in the claims application
2. Verify payment details and totals in the payments application
3. Verify Account Information in the account application.

1. Verify payments against a loss run report and other reports.
2. Have a policy in place that no data is imported into production until the proper signatures are received in Pre-Production.
3. Run the Conversion process to convert the data into production.

Since I have used this method of performing conversions, I am able to clean up the data in a pre-production phase, before it gets into production. Also, by using identify columns and primary key columns as explained in step 2 and 3, I am able to trace my data back to the original source.