title: Best way to cleanse huge amounts of data
body: <p>I have a huge table in my Oracle database - around 40 million rows - that I have to check that all data are correct inside fields based on some business rules.</p>
  <p>Let us assume this is my huge table, and the rules are:</p>
  <ol>
  <li>US only can have [national_id_type]=NID.. others must be VISA</li>
  <li>US only with NA can own house.. other nationality with VISA can't own a house</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/urjqj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/urjqj.png" alt="enter image description here" /></a></p>
  <p>What I think</p>
  <ol>
  <li><p>First to land the table inside a landing table to preserve the original data</p>
  </li>
  <li><p>Rewrite error in the column to display the errors and the number of them inside a PowerBI dashboard so the quality department can fix them</p>
  </li>
  </ol>
  <p><a href="https://i.stack.imgur.com/gcdRL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gcdRL.png" alt="enter image description here" /></a></p>
  <p>What I will do is</p>
  <pre><code>case when nationality &lt;&gt; 'US' and NATINAL_ID_TYPE = 'VISA' then '99\US only can have [national_id_type]=NID' ELSE NATINAL_ID_TYPE END as NATINAL_ID_TYPE ,
  case when nationality &lt;&gt; 'US' and NATINAL_ID_TYPE &lt;&gt; 'NA' AND own_house='YES' then '99\US only with NA can own house' ELSE own_house END as own_house
  </code></pre>
  <p>Is this way efficient to detect errors in 40 million Record X 70 Columns</p>
  <p>I think this will be very slow...</p>
  <p>Can you help me with some tips I can use to provide the errors for dashboarding and cleansing that can be used for performance</p>