What are typical scenarios where the user might require a data-change-audit-trail?
Add a comment
|
2 Answers
There are many requirements for capturing changed data. I list only three common ones below.
- Audits (HIPAA, SAS70, etc.)...one 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.
You may also want to create an audit trail if you need to be able to selectively fix bad data changes.
For instance, suppose I have imports of the sales roster for the client. One time they accidentally remove a name and our process assumes she doesn't work there anymore and removes her access. But she does still work there and in order to immediately correct the problem (I can't just run the previous roster as that would remove the new employees), I can find the data that was changed through the audit log and restore her. Or if someone maliciously changes data or runs a bad table update query (I saw someone update the entire table once because he forgot a where condition, the audit tables saved his job), it is easy to get it back.
I have never worked on an Enterprise system that didn't use and need auditing. This data is critical to the success of the business and the ability to fix it quickly when things go wrong is priceless.
