Data – Changes Audit Trail


What are typical scenarios where the user might require a data-change-audit-trail?

Best Answer

There are many requirements for capturing changed data. I list only three common ones below.

  • Audits (HIPAA, SAS70, etc.) may need to audit SQL Server logins as well as attribute data changes to specific users
  • EDI...some insurance carriers require one to send to them current and previous data when a policy is altered, say, due to a life event. One may need to send one's previous coverage together with the new coverage. This is a good candidate for using SQL Server Change Data Capture (CDC).
  • ETL for DWs...require data change capture in order to add new data to facts and dimensions as well as process updates to slowly changing dimensions. Usually, something like SQL Server's Change Tracking would be right for this, since it is a "lighter" tool than CDC.