Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
1  
-1 since the question shows no evidence of research, and is a very broad question without a possible concise answer. – Max Vernon Sep 11 '12 at 4:05

2 Answers

up vote 6 down vote

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.
share|improve this answer
1  
And not just for ETL for datawarehouses but ETL for ordinary imports and exports as well. We frequently need to process incoming feeds in a delta fashion (only process the 2000 records of the 21 million record file that have actually changed cutting feed processing time down to minutes when it previously could have taken days.) or provide only changed data to a customer for an outfeed. – HLGEM Mar 13 '12 at 17:42
1  
Generally the auditing for legal or regulartory compliance cannnot use change tracking or CDC as it is not robust enough for the regulartory reuirement (which usually incudes who made the change which CD and CT don't do to my knowledge). In this case a full set of audit tables managed by triggers is usually the best choice. The one place you cannot put regulatory auditing is in the application. It would fail any audit done by a competent auditor because it does not guarantee to record possible fradulent changes to the database. – HLGEM Mar 13 '12 at 17:48
@HLGEM - I agree. In the past I have implemented data change auditing using CDC and additional tables to store other metadata. It was just a small custom solution; so, I certainly agree that neither CT nor CDC can be thought of as "out of the box" auditing tools. Customization is necessary. – ooutwire Mar 13 '12 at 18:09
up vote 0 down vote

I'm going to add one more scenario to @ooutwire's answer. 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.