Importance of Staging and Audit Tables in DataWareHouse

 In Data Warehousing its quite common to use Staging tables and Audit tables.

Someone can think 

What's their need in Data Warehousing.

Why do we need staging tables?
Why do we need audit tables?

 Staging Area/Tables

 Staging area is primarily designed to serve as intermediate resting place for data before it is processed and integrated into the target data warehouse.

This staging are serves many purpose above and beyond the primary function:

1. The data is most consistent with the source. It is devoid of any
    transformation or has only minor format changes.
2. The staging area in a relation database can be read/ scanned/ queried
    using SQL without the need of logging into the source system or reading
   files (text/xml/binary).
3. It is a prime location for validating data quality from source or auditing
   and tracking down data issues.
4. Staging area acts as a repository for historical data if not truncated

Auditing Tables

Auditing is the monitoring and recording of selected user database actions.
Auditing is normally used to:
1. Investigate suspicious activity. For example, if an unauthorized user is deleting data from tables, the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
2. Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
3..Audit tables can often be used to track the Lineage, Quality, and Currency of data elements.
 This design tip from Ralph Kimball details an audit dimension that can be used to tag dataYou might also want to audit attributes of the ETL jobs.

The use of audit tables  depend on the project requirements.


Hope this helps