What is the best approach to deduplicate records in which, by a exceptionally bad ETL design approach, two natural keys are transposed as surrogates for the other, or when one of those natural keys might be malformed?
A test data set and SQL example can be found here:
http://sqlfiddle.com/#!9/a36281/3/0
Understanding the example requires an explanation, here abstracted from the original problem into an amusing scenario.
Santa's reindeer had a little too much fun on Bourbon Street recently. Wouldn't you know -- every one of those reindeer were caught by police (except Rudolph, of course, who is the paragon of moral virtue). They were all issued citations for various defilements of moral propriety -- or, at least, they were issued citations for the infractions of the law for which they were actually caught ;)
Naturally, the North Pole Inspector General keeps an updated list to report to Santa who's naughty and nice -- even when the naughty ones are Santa's reindeer.
It appears that the IG's database of bad behavior has a record load process with a serious design flaw which causes duplicate records. This leads to a situation in which Santa's reindeer appeared to be doubly bad.
Well, Santa was about to bring a wrath down upon those reindeer the likes of which would frighten even the Abominable Snow Monster when, luckily, the Inspector General found the error.
There are two natural keys associated with a bad behavior record:
- Citation number: This is the unique number on each citation issued by police.
- Court docket number: This is the unique number assigned to each court case.
The IG's bad behavior database expected that both of these values were accurately provided by Bourbon Street police who originated the citations, and by the local court where the cases were tried. Unfortunately, that expectation was violated.
There were many times when the citation number wasn't provided. In these instances, the IG's record load process used the known value (docket number) as a surrogate for the missing value (citation number).
Similarly, there were many times when the court docket number wasn't provided, and similarly, the record load process used the known value (citation number) as a surrogate for the missing value (docket number).
In still other instances, there were times when the citation number or the court docket number was malformed.
A serious problem arises later, when a record update is sent to the IG for a case already in his bad behavior database. Forget that the IG database isn't normalized, or that it doesn't simultaneously attempt to fuzzy match both record keys. No, when his load process finds a new citation number, or a new docket number, instead of finding and updating the original record, it inserts a new record - and therefore, produces duplicate records.
Now arises the question of how to untangle this puzzle of duplicates.
There are other fields that can be used to help the IG obtain a unique set of records:
- CourtDivision: a letter associated with a unique court room.
- DefendantName: simplified for this exercise.
- CitationOffense: the charge on the citation against a defendant.
- EventDate: an event date associated with any update by the court to a defendant's case record.
These fields, plus CitationNumber and CourtDocketNumber, are all the IG has to work with to produce a list of deduplicated records.
Has the IG succeeded in deduplicating his records? More importantly, when he applies this solution to a much larger set of records, is there a more efficient way to deduplicate these records than what the IG came up with?
It's a vexing puzzle. Some assistance would be very welcome.