A post-load procedure is executed after the load table has completed and after any 'after' transformations have occurred.
So what is the post load procedure for?
The post load procedure is primarily available for situations that occur which require non standard solutions. The use of a post load procedure is a handy way of isolating custom code that might otherwise cause generic stage processing to become more complex and require custom modifications.
How do you decide to use it?
In general if you come up against load table transformation processing that meets some or all of the following criteria.
• Cannot be handled during the load from the source system.
• Cannot be handled during 'after' transformations.
• Involves processing that is isolated to the load table itself.
• Does not sit correctly within stage transformation processing.
Examples of situations where you might use a post load procedure are detailed below.
• Delete processing. Filtering on the source system can sometimes cause a performance issue. If this occurs and time permits the 'whole' dataset can be loaded. Stage tables could filter this data out, but if multiple stage tables require data from this table it may be quicker overall to delete the data from the load table prior to stage processing. The post load procedure can be used to do this.
• Data error checking and load validation. Certain data problems may require checking and validation prior to stage processing. The post load procedure can be used to raise errors and halt processing.
• Populate the load table with data. When multiple steps are required to obtain data from a table on the source system and the data warehouse is using database links to source the load data it can occasionally be worth loading the data during post load processing. As an example, when dealing with a header table that lacks a timestamp but the timestamp is available on the detail table. If standard join techniques become problematic the post load procedure can be used to write custom sql that is 'tuned' to load the data as quickly as possible.
There are as many different examples of when and how to use a post load procedure as there are different data warehouses and source system applications