The reasons are:

• Source system documentation: Its generally not possible to have a complete description of :
  • the source system tables
  • inter-linkages between the tables,
  • the same data-elements with different field names in different tables
• The transformation rules: Many transformation rules are fairly complex, and cannot be implemented through standard options given in a tool and have to be programmed into the system.
• The reasoning: While it might be possible for an ETL system to self-document the simple transformational rules, it cannot document the reasons and objectives behind the transformation. For example, why are you splitting a customer_ID (AA302456) into sub-components like Customer_type(AA), customer location (30) and customer_number(2456). The different reasons for this splitting could be:
  • Enabling the specific type of queries around customer type.
  • Two different tables could be having different field structure for customer type (AA v/s XXAA)
• The documentation on risks related to the efficacy of ETL: During the design of an ETL system, one comes to know the limitations of the ETL. For example, you may not be able to achieve 100% perfect extractions or transformations given the:
  • limitations of data quality in source systems
  • limitations of the extraction flexibility,
  • the performance load due to a complex extraction query.

These limitations should always be documented, which give a more realistic view of the level of accuracy around the data and the output information.

• The flow of ETL: A set of data goes sometimes go through multiple transformation routines before it reaches end-state and be ready for loading. A good documentation should be able to provide an end-to-end view of this entire flow so that one can understand the purpose behind this flow. This end-to-end view should be able to answer the following questions:
  • Why we are following X steps and not Y steps to do a transformation?
  • What is the completion criteria related to each step of transformation/Extraction?
• Data Quality checks: An ETL system generally does not document the data quality checks, which need to be done and their reasoning.