title: Classify or match similar patients together with machine learning techniques based on SSN, Date of Birth, Name, Gender, Last Updated Time
body: <p>My environment is DataBricks and Apache Spark. I have a large medical patient dataset. I like to create a master patient table that keep track of patients that might change their name or gender over time. Date of birth and social security number do not change. In this large dataset, there are very similar records but different Patient IDs. Below is an example dataset.</p>
  <pre><code>CREATE OR REPLACE VIEW PatientTable AS
  SELECT '1'  PatientID, '100-10-fake' SSN, 'abby' FirstName, 'black' LastName, '2000-01-01' DateOfBirth, 'female' Gender, '2020-02-01' LastUpdateDateTime
  UNION
  SELECT '1a' PatientID, ''            SSN, 'abby' FirstName, 'black' LastName, '2000-01-01' DateOfBirth, 'female' Gender, '2020-01-01' LastUpdateDateTime
  UNION
  SELECT '2'  PatientID, '200-10-fake' SSN, 'ben'  FirstName, 'white' LastName, '2000-02-01' DateOfBirth, 'male'   Gender, '2020-01-01' LastUpdateDateTime
  UNION
  SELECT '3'  PatientID, '300-10-fake' SSN, 'ben'  FirstName, 'white' LastName, '2000-02-01' DateOfBirth, 'male'   Gender, '2020-02-01' LastUpdateDateTime
  UNION
  SELECT '3a' PatientID, ''            SSN, 'ben'  FirstName, 'white' LastName, '2000-02-01' DateOfBirth, 'male'   Gender, '2020-01-01' LastUpdateDateTime
  UNION
  SELECT '4'  PatientID, ''            SSN, 'chip' FirstName, 'smith' LastName, '2000-03-01' DateOfBirth, 'male'   Gender, '2020-01-01' LastUpdateDateTime;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/8Kuck.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8Kuck.png" alt="enter image description here" /></a>
  Using SQL, I have created the Master Patient Table by using a Master Patient Reference Table first.</p>
  <p>Master Patient Table:
  <a href="https://i.stack.imgur.com/q0Q3S.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q0Q3S.png" alt="enter image description here" /></a></p>
  <p>Master Ref Patient Table
  <a href="https://i.stack.imgur.com/FoxEJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FoxEJ.png" alt="enter image description here" /></a></p>
  <p>For the two Abby(s), I classify them as one patient because the second Abby is deemed as a duplicate because of the older LastUpdatedDateTime. The first two Ben(s) of Patient IDs of '2' and '3' are different because of different SSN. The third Ben (3a) is deemed a duplicate of Ben (3) because Ben (3) is the latest record. The end goal is to have Master Patient Table be updated if there are changes. If the Patient Table have new records that had Abby with a sex change to Male and a SSN for Chip, here is what the Master Patient Table should look like:</p>
  <p><a href="https://i.stack.imgur.com/xUWaO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xUWaO.png" alt="enter image description here" /></a></p>
  <p>My SQL solution works well enough. However, I will start getting millions of patients from different medical institutions. Are there machine learning techniques for this? How should I implement ML into this?</p>