Hi
@Ray0801
,
Multi/single-table and post load etl allow you to apply your own custom code to execute as part of an ETL Set. The use cases are varied and often depend on your business requirements.
While Compose handles alot of complex transformation requirements automatically, there are cases where you need to write your own code to handle certain transformation requirements. This is where these processes come into play.
For example, I've seen
PostLoad used to execute a stored procedure which then logs a record to a table that triggers a dashboard reload process.
Managing discrete aggregates is also a good use case - I have ORDERS and CUSTOMERS and in my Customer table, I want to keep track of the LastOrderDate. Rather than trying to calc this over and over again, a Post-Load or Multi-Table process could update the Customer_HUB and set the LastOrderDate column based on the table in the TSTG table (multi-table or Post Load) or the data altered in the hub for the current ETL run ( from ORDER_DETAILS_HUB where RUNNO_UPDATE= &&1) for example.
I think the most important part is to simply understand WHERE each of the custom ETL components fit within the ETL SET workflow when the code is generated
The slide below depicts high level where a Multi-table / single table /post load process execute within the generated code for an ETL Set. Hopefully this helps explain where it fits
Hi
@Ray0801
,
Multi/single-table and post load etl allow you to apply your own custom code to execute as part of an ETL Set. The use cases are varied and often depend on your business requirements.
While Compose handles alot of complex transformation requirements automatically, there are cases where you need to write your own code to handle certain transformation requirements. This is where these processes come into play.
For example, I've seen
PostLoad used to execute a stored procedure which then logs a record to a table that triggers a dashboard reload process.
Managing discrete aggregates is also a good use case - I have ORDERS and CUSTOMERS and in my Customer table, I want to keep track of the LastOrderDate. Rather than trying to calc this over and over again, a Post-Load or Multi-Table process could update the Customer_HUB and set the LastOrderDate column based on the table in the TSTG table (multi-table or Post Load) or the data altered in the hub for the current ETL run ( from ORDER_DETAILS_HUB where RUNNO_UPDATE= &&1) for example.
I think the most important part is to simply understand WHERE each of the custom ETL components fit within the ETL SET workflow when the code is generated
The slide below depicts high level where a Multi-table / single table /post load process execute within the generated code for an ETL Set. Hopefully this helps explain where it fits
