ETL generation preparation; runtime metadata
As a core component of the ETL generation (the data modelling approach and methodology is the other) this post provides the ETL metadata component of the (to be) generated processes. Recording the runtime metadata overlays all ETL processes and provides the information about the ETL itself. In other words this applies to all processes in all layers of the EDW. It looks after information about the ETL such as:
- When has it run
- What grouping of ETL processes does it belong to
- What environment has been used
- How much data was processed
- Where did it fit in the architecture
- Did everything go all right
This model is all about providing the runtime (operational) information about the processes. This means that every record anywhere in the EDW will be tagged with the module instance ID which can be used to relate the record to the process information. One of the goals of the model is to be low maintenance and all information can be automatically provided and/or updated. This model is a prerequisite for the ETL generation. The information will be typically be added by the ETL processes themselves, except for the static information such as the architecture layers and areas. The auditing tables can be used by ad-hoc queries if required.
The DDL for the physical representation can be found here: Runtime Metadata.