Audit Columns On Every Table

I use a set of audit columns on every table to help troubleshoot issues. Different types of tables have different subsets of the standard audit columns. There are audit columns for:

1. Staging tables

2. Master data management tables

3. Dimension tables

4. Fact tables

5. API tables

An example of an audit column is ErrorReason in staging tables. It is not good enough to mark a record as errored. I want to know WHY it errored. I populate ErrorReason with a plain text explanation of just what went wrong.

And while we are talking about errors, if I want to find all the records that errored out, I do not dump those records to a different table or output them to a file. They stay in staging with the ErrorRecord audit column set to 1. Finding those records is a simple three-line SQL statement.

Once the error is identified and fixed, you can simply set the ErrorRecord column to 0 and run the normal load with no other adjustments. No importing a file. No moving data from an error table back to staging. Just hit the start switch and watch the magic happen. No muss. No fuss.