Custom PIT (Point in Time) template (2/2)

...continued from previous post.

Finally, the INSERT GROUP will combine all previous inline views to load our PIT table.
Many things we can note here:

we can call parameters in the template language: @#HASHKEY_DELIMITER# calls the parameter value for the delimiter chosen in the system parameters.
you can split attribute expressions using the GROUP_X keyword, we use this for the hashkey function. Each group can then have its own repeated by loop.
GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#] is a function that will use the target platforms timecast function. See the docs for a full list of functions.
You can also use SQL functions in the template code like COALESCE or LEAD.
for this PIT, we join the SAT inline views using snapshot date between start and end dates of the RAW vault load timestamps. We could use other (business, source) timelines if available, or use combinations of both (for multi-temporal use cases). We could even load these into the same PIT if we add a field that makes a distinction between different timelines loaded in the PIT.
SAT_OBJECT_HKEYS and SAT_LOAD_TIMESTAMPS are repeated for every SAT.
THE UNSAT join takes care of the orphan records, in case we do not have a SAT record for a specific snapshot, we load the SAT key fields with these orphan records. This will allows us to use inner joins when we query the PIT and SAT data downstream.

comp_group_start MAIN_GROUP INS_GRP
componentGroupRepeatedByComponent CPIT

	consists of target table PIT_TGT
	componentrepeatedbycomponent CPIT
 
		Attribute PIT_HKEY
			GROUP_1 expressedBy HASHFUNC[ 
			GROUP_2 expressedBy HUB_SRC.OBJECT_H_KEY || @#HASHKEY_DELIMITER# ||
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY
			GROUP_3 expressedBy CHARCASTFRMTMAX[SNAPSHOTDATES.SNAPSHOT_DATE]
				expressionRepeatedByColumn SNAPSHOTDATES.SNAPSHOT_DATE 
			GROUP_4 expressedBy HASHFUNC]

		Attribute OBJECT_H_KEY
			expressedBy HUB_SRC.OBJECT_H_KEY
				expressionRepeatedByColumn HUB_SRC.OBJECT_H_KEY

		Attribute SNAPSHOT_DATE
			expressedBy SNAPSHOTDATES.SNAPSHOT_DATE
				expressionRepeatedByColumn SNAPSHOTDATES.SNAPSHOT_DATE

		Attribute END_SNAPSHOT_DATE
			expressedBy COALESCE(LEAD(SNAPSHOTDATES.SNAPSHOT_DATE) OVER (PARTITION BY HUB_SRC.OBJECT_H_KEY ORDER BY SNAPSHOTDATES.SNAPSHOT_DATE),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#])
				expressionRepeatedByColumn SNAPSHOTDATES.SNAPSHOT_DATE		

		Attribute $ SAT_OBJECT_H_KEY
		attributerepeatedbycomponent SAT
			expressedBy COALESCE(SAT_TGT$.OBJECT_H_KEY,UNSAT_TGT$.OBJECT_H_KEY)
				expressionRepeatedByColumn SAT_TGT$.OBJECT_H_KEY

		Attribute $ LOAD_TIMESTAMP
		attributerepeatedbycomponent SAT
			expressedBy COALESCE(SAT_TGT$.LOAD_TIMESTAMP,UNSAT_TGT$.LOAD_TIMESTAMP)
				expressionRepeatedByColumn SAT_TGT$.LOAD_TIMESTAMP

	consists of source table HUB_SRC
	componentrepeatedbycomponent HUB		

	consists of inner join JOIN_SNAPSHOTDATES		
	componentrepeatedbycomponent CPIT

					Artifact GENERAL_EXPRESSION				
						expressedBy SNAPSHOTDATES.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY
							expressionRepeatedByColumn SNAPSHOTDATES.OBJECT_H_KEY		

	consists of joined inline_view SNAPSHOTDATES		
	componentrepeatedbycomponent CPIT

    consists of left_outer join JOIN_PIT		
	componentrepeatedbycomponent CPIT

					Artifact GENERAL_EXPRESSION				
						expressedBy PIT_CHECK.OBJECT_H_KEY = HUB_SRC.OBJECT_H_KEY AND SNAPSHOTDATES.SNAPSHOT_DATE = PIT_CHECK.SNAPSHOT_DATE
	

	consists of joined inline_view PIT_CHECK	
	componentrepeatedbycomponent CPIT
			
       comp_subgroup_start $ SAT_TGT_SUBGROUP
	componentsubgrouprepeatedbycomponent SAT

		consists of left_outer join JOIN_SAT_TGT$
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						expressedBy HUB_SRC.OBJECT_H_KEY = SAT_TGT$.OBJECT_H_KEY AND SNAPSHOTDATES.SNAPSHOT_DATE >= SAT_TGT$.LOAD_TIMESTAMP AND SNAPSHOTDATES.SNAPSHOT_DATE < SAT_TGT$.ENDING_LOAD_TIMESTAMP


		consists of joined inline_view SAT_TGT$
		componentrepeatedbycomponent  SAT$

	comp_subgroup_end

       comp_subgroup_start MEX_SUBGROUP
	componentsubgrouprepeatedbycomponent MEX
	
	consists of inner join JOIN_MEX_SRC		
	componentrepeatedbycomponent MEX

				Artifact GENERAL_EXPRESSION				
					expressedBy MEX_SRC.RECORD_TYPE = @#UNKNOWN_RECORD_TYPE#
						expressionRepeatedByColumn MEX_SRC.RECORD_TYPE  

	consists of joined table MEX_SRC		
	componentrepeatedbycomponent MEX
	
	comp_subgroup_end
			
       comp_subgroup_start $ UNSAT_TGT_SUBGROUP
	componentsubgrouprepeatedbycomponent SAT

		consists of inner join JOIN_UNSAT_TGT$
		componentrepeatedbycomponent SAT$
	
					Artifact GENERAL_EXPRESSION
						expressedBy INTCAST[MEX_SRC.LOAD_CYCLE_ID] = UNSAT_TGT$.LOAD_CYCLE_ID
						expressionRepeatedByColumn MEX_SRC.LOAD_CYCLE_ID


		consists of joined table UNSAT_TGT$
		componentrepeatedbycomponent  SAT$

	comp_subgroup_end

    comp_subgroup_start  FILTER_PIT_CHECK_SUBGROUP
    componentrepeatedbycomponent CPIT
       
       consists of filter FILTER_PIT_CHECK
       componentrepeatedbycomponent CPIT


			Artifact GENERAL_EXPRESSION
				expressedBy PIT_CHECK.OBJECT_H_KEY is null

    comp_subgroup_end  
				

comp_group_end

For a TABLE type template, we also need to create a DDL template. DDL templates are fairly straightforward, because they can be easily derived from the ETL template. Note that DDL templates for create table statements contain a CREA_GRP.

Template POINT_IN_TIME
	
    comp_group_start DDL_GROUP CREA_GRP
	componentGroupRepeatedByComponent CPIT

		consists of CREATE_TABLE table PIT_TGT
		componentrepeatedbycomponent CPIT
 
		Attribute PIT_HKEY

		Attribute OBJECT_H_KEY
		
		Attribute SAT_OBJECT_H_KEY

		Attribute SNAPSHOT_DATE				

		Attribute END_SNAPSHOT_DATE

		Attribute LOAD_TIMESTAMP
	
	comp_group_end

When deployed, the data will look like this:

Next post will handle dimension objects for star schema's based on these PITs.

generated SQL code examples in next post...