1

I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one AddressID per every person in the 'People' table.

The idea was to centralize the data in this single 'Population' database, problem started once duplicated records started to appear due to a poorly executed Import process (Data coming from different sources), this left the table with as much as 7 records for a single Person.

Additionally the Address table started collecting 'all sorts', so let say I live in "24 Wickam Heights" you can found this address in the following ways:

  • 24 Wickam
  • 24 Wickam St
  • 24 Wickam Street
  • 24 Whikam H.
  • 24 Wikam Str.

In some cases as bad as over 20 different versions of the same street...

The most beautiful part is that the data from this database is being referenced from at least 5 other databases in the same server, making every change a very risky process.

So I'm thinking, what steps can be taken to get rid of the duplicates?, What alternatives there is for avoiding the address table accumulating such an amount of data inconsistency?.

Maybe even to ask, is there any salvation for such a big mess? It really is a nightmare.

  • 2
    There are data cleansing products out there, and they're expensive because this problem is hard (start with Melissa Data). There is no built-in bulletproof method to eliminating these "duplicates" with any level of confidence. – Aaron Bertrand Jun 9 '15 at 13:02
  • 1
    You probably need to initially implement some sort of validation checking for addresses before you try to clean up the existing data otherwise you'll be running on that treadmill forever. Once you can stop having bad data being imported, then you can clean up your old data. Most likely identifying the "right" address and then build another table to relate "bad" addresses to "correct" addresses and then update your child tables joining to that table and then delete the "bad" addresses from Address table. – Queue Mann Jun 9 '15 at 13:04
  • @AaronBertrand It is quite expensive... wow, I never thought this was gonna be so complicated, I mean, I did but not up to the 'specialized tool' level... I'm considering getting a new address list, and wait until next census to normalize the whole bunch. In transition prepare the rest of the databases for a slow (really slow) migration process to the new clean master Population database... I think I would like to get this done... – Nelz Jun 10 '15 at 18:43
0

There are some great data cleansing products out there, one in particular that is actually top notch and affordable. I've come across DataMatch by Data Ladder, which is an excellent fuzzy matching and address standardization/address parsing tool used across business and would work really well for this situation. They offer a complimentary trial for new users.

In fact, an independent verified evaluation was done of the software comparing it to major software tools by IBM and SAS. There was a study done at Curtin University Centre for Data Linkage in Australia that simulated the matching of 4.4 Million records. It identified what providers had in terms of accuracy (Number of matches found vs available. Number of false matches)

1.  DataMatch Enterprise, Highest Accuracy (>95%), Very Fast, Low Cost
2.  IBM Quality Stage , high accuracy (>90%), Very Fast, High Cost (>$100K)
3.  SAS Data Flux, Medium Accuracy (>85%), Fast, High Cost (>100K)
  • 1
    Hi Ralph are you connected with Dataladder in any way? – bummi Jun 10 '15 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.