--- title: "Build a Calculated/Computed Satellite Table" canonical: "https://docs.vaultspeed.com/space/VPD/3038314504/Build%20a%20Calculated%2FComputed%20Satellite%20Table" format: markdown --- > Macro (toc) To build views on satellites that calculate the load end dates when using insert only, the base types SAT - LKS(Satellite on Link) - LDS (Satellite on Many to Many Link) can be used. In the following example, we will use VaultSpeed Studio to build these views for the SATs. The logic that will be written in the template: DDL (not needed for views): ```sql create table bv_layer.sat_ms_addresses_calc ( addresses_hkey varchar(64) NOT NULL, load_date timestamp NOT NULL, load_cycle_id numeric NOT NULL, hash_diff varchar(64), delete_flag char(1) NOT NULL, trans_timestamp timestamp, address_number numeric, street_name varchar(150), street_number numeric, postal_code varchar(18), city varchar(150), end_load_timestamp timestamp ); ``` ETL/ELT/VIEW ```sql create or replace view bv_layer.sat_ms_addresses_calc as select addresses_hkey, load_date, load_cycle_id, hash_diff, delete_flag, trans_timestamp, address_number, street_name, street_number, postal_code, city, coalesce (lead (sat_src.load_date,1) over ( partition by sat_src.addresses_hkey order by sat_src.load_date), to_date ('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as end_load_timestamp from dv_layer.sat_ms_addresses sat_src; ``` # **Step 1: Define a new template:** Click the +Add button to add a new template: ![image](media://dcfbbf3c-ce59-4305-872f-061cb0c42a61) - **Name/Description:** computed_satellite / End dating - **Prefix/suffix:** sat / comp This will use the original object name without the pre - and suffixes and add these new ones. Table sat_ms_addresses will be transformed into sat_ms_addresses_comp - **Signature object:** SAT_TGT - **Object type**: View. It can be set to a table if it needs to be persisted. If a view is chosen, no DDL should be uploaded. - **Load type**: Choose between initial and incremental load types or just all when using the same logic. In this case, the ALL option is selected, which refers to all loads, since a view can be used for initial and incremental loads. - **Signature schema:** BUSINESS_VAULT_LAYER - **Base type**: A computed satellite will be based upon Satellites on hubs. - **Upload ETL:** This will be done afterwards. - **Upload DDL:** This is only needed to persist the objects. Click the **Create** button when ready to create a first template definition.   ![image](media://0b0bc1de-deb9-496e-837b-1b5311b2433f) As seen in the screenshot above, a template should appear in the overview. # **Step 2: Configure dependent objects of the template** Continuing with the template setup, in the dependencies screen we can define for which objects the template should generate a mapping. Since we have chosen SAT as the base type in this example, this screen will show all SATs in the Data Vaults. A set of unlinked Satellite objects can be included in the template, as shown in the screenshot below (Linked Objects table). ![image](media://dad9b270-3317-4f13-b2ad-749438c28c5d) ![image](media://6c07bc46-4fe0-428d-aca0-ce7bb7214daa) Select the sat(s) to use in this template and click the **Link** button to link them to the template. ![image](media://1b69f4a6-6edf-4c47-a803-1af24a4382b6) # **Step 3: Configure the target attribute definition** There is a separate window for configuring target definitions, as shown below. ![image](media://52e888ad-ce25-45cc-8ed8-bfb7e57d1b83) ![image](media://6bb5bb87-76ff-49e6-a824-a676ab2d8a62) The computed satellite needs all the attributes from the satellite itself and one new END_LOAD_TIMESTAMP attribute type. | **TAB_TYPE** | **Signature attribute** | | --- | --- | | SAT | All signature attributes | ## 3.1 Existing attributes To add all sat attributes, click the ![image](media://fc54d615-bdd8-4765-abe1-9723b5b6e7fb) button and select SAT ![image](media://ba90db1a-b068-49f2-abf2-75f74cfe6401) ![image](media://42c7b608-aeb5-41a4-9498-774270bcc911) ## 3.2 New attributes In this example, a new END_LOAD_TIMESTAMP Signature attribute will be added. As it is based on LOAD_TIMESTAMP, it's not unique as it is related to another attribute type. Therefore, a prefix or suffix has to be used to make it distinct, i.e, END_LOAD_TIMESTAMP. So, it is based on the LOAD_TIMESTAMP of the Satellite and has a timestamp data type. If something is based on another attribute, it is not unique. ![image](media://51b55138-5289-4adc-b165-b6ca5bcb22ed) Note that in this case, the LOAD_END_TIMESTAMP could also be defined as a unique attribute as, of course, it's only one attribute, and then you can choose the name. # Step 4: Build an ETL template To show how to write a template that compiles into that query, the query will split be split into different blocks, and we will show how each of these blocks can be defined in the template language: ```sql Create or replace view bv_layer.sat_ms_addresses_comp ``` A view with the prefix sat and suffix comp based on a specific satellite was achieved by configuring the template properties: In the template file (.dvt file format), nothing special is needed to trigger this, only a single template definition (only when generating tables can we add multiple templates, more about this in the template documentation): ``` Template SAT_COMP ``` Next step: ```sql select addresses_hkey, load_date, load_cycle_id, hash_diff, delete_flag, trans_timestamp, address_number, street_name, street_number, postal_code, city, coalesce (lead (sat_src.load_date,1) over ( partition by sat_src.addresses_hkey order by sat_src.load_date), to_date ('31/12/2999 23:59:59','DD/MM/YYYY HH24:MI:SS')) as end_load_timestamp from dv_layer.sat_ms_addresses sat_src; ``` This block is the central part of the query. There is no CTE in this example. In the template language, a view is defined by the COMPONENT_GROUP VIEW_GRP. ``` Template SAT_COMP comp_group_start MAIN_GROUP VIEW_GRP RepeatedByObject SAT comp_group_end ``` This will now trigger the following code: ```sql DROP VIEW IF EXISTS "".""; CREATE VIEW ""."" AS ; ``` There are still some crucial elements missing. In the VSS templates, the target will have the Signature attribute as defined in the VaultSpeed studio**, **so in the template, we need to add a source and a target table so attributes can be added. In this case, the source will be a standard satellite and the target will be a computed satellite. In the template, this is defined in the following way: ``` Template SAT_COMP comp_group_start MAIN_GROUP VIEW_GRP RepeatedByObject SAT_TGT consists of target table SAT_COMP_TGT RepeatedByObject SAT_TGT connectsFrom (SAT_SRC) consists of source table SAT_SRC RepeatedByObject SAT comp_group_end ``` SAT_COMP_TGT and SAT_SRC are aliases and can be changed. This will trigger the following code in SQL: ```sql DROP VIEW IF EXISTS "moto_scn02_bv"."sat_ms_addresses_comp"; CREATE VIEW "moto_scn02_bv"."sat_ms_addresses_comp" AS SELECT FROM "moto_scn02_fl"."sat_ms_addresses" "sat_src" ; ``` Optionally, a hub could be joined by adding a Join statement to the template: ``` Template SAT_COMP comp_group_start MAIN_GROUP VIEW_GRP RepeatedByObject SAT_TGT consists of target table SAT_COMP_TGT RepeatedByObject SAT_TGT connectsFrom (JOIN_SAT_SRC_HUB_SRC) consists of source table SAT_SRC RepeatedByObject SAT consists of inner join JOIN_SAT_SRC_HUB_SRC RepeatedByObject HUB connectsFrom (SAT_SRC) connectsFrom (HUB_SRC) Artifact GENERAL_EXPRESSION expressedBy HUB_SRC.OBJECT_H_KEY = SAT_SRC.OBJECT_H_KEY consists of joined table HUB_SRC RepeatedByObject HUB comp_group_end ``` This would trigger the next code: ```sql DROP VIEW IF EXISTS "moto_scn02_bv"."sat_ms_addresses_comp"; CREATE VIEW "moto_scn02_bv"."sat_ms_addresses_comp" AS SELECT FROM "moto_scn02_fl"."sat_ms_addresses" "sat_src" INNER JOIN "moto_scn02_fl"."hub_addresses" "hub_src" ON "hub_src"."addresses_hkey" = "sat_src"."addresses_hkey" ; ``` The template has a few new elements: a join and a joined table. The join defines what type of join to use (e.g. inner, left, right), and it contains the join expression. In this case, it will be the hash key of the HUB and SAT. These have OBJECT_H_KEY as Signature Attribute. Signature attributes can be checked in the Signature attribute definition screen. After the join, there is a joined table component, which defines which table needs to be joined. The only thing left now is the attributes themselves. The easiest way is to list all the Signature attributes in the sat and define them individually. However, for the END_LOAD_TIMESTAMP, a calculation is needed. For the regular attributes, define their attribute type (same Signature attribute as the expression Signature attribute and exact as repeated by Signature attribute): ``` Attribute OBJECT_H_KEY expressedBy SAT_SRC.OBJECT_H_KEY expressionDefinedByAttribute SAT_SRC.OBJECT_H_KEY ``` The Attribute Signature attribute will trigger the compiler to look into the list of available attributes in the Signature Object which were defined as a target. In this case, the tab type SAT_TGT was defined in the tool. The ExpressedBy statement refers to the expression to be used. The RepeatedBy statement is used to repeat it on every attribute of this Signature attribute. Adding all existing SAT attributes to the view, the template looks like this: ``` Template SAT_COMP comp_group_start MAIN_GROUP VIEW_GRP RepeatedByObject SAT_TGT consists of target table SAT_COMP_TGT RepeatedByObject SAT_TGT connectsFrom (JOIN_SAT_SRC_HUB_SRC) Attribute OBJECT_H_KEY expressedBy SAT_SRC.OBJECT_H_KEY expressionDefinedByAttribute SAT_SRC.OBJECT_H_KEY Attribute LOAD_TIMESTAMP expressedBy SAT_SRC.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_SRC.LOAD_TIMESTAMP Attribute LOAD_CYCLE_ID expressedBy SAT_SRC.LOAD_CYCLE_ID expressionDefinedByAttribute SAT_SRC.LOAD_CYCLE_ID Attribute HASH_DIFF expressedBy SAT_SRC.HASH_DIFF expressionDefinedByAttribute SAT_SRC.HASH_DIFF Attribute DELETE_FLAG expressedBy SAT_SRC.DELETE_FLAG expressionDefinedByAttribute SAT_SRC.DELETE_FLAG Attribute TRANS_TIMESTAMP expressedBy SAT_SRC.TRANS_TIMESTAMP expressionDefinedByAttribute SAT_SRC.TRANS_TIMESTAMP Attribute PRIMARY_KEY expressedBy SAT_SRC.PRIMARY_KEY expressionDefinedByAttribute SAT_SRC.PRIMARY_KEY Attribute OTHER_ATTR expressedBy SAT_SRC.OTHER_ATTR expressionDefinedByAttribute SAT_SRC.OTHER_ATTR consists of source table SAT_SRC RepeatedByObject SAT consists of inner join JOIN_SAT_SRC_HUB_SRC RepeatedByObject HUB connectsFrom (SAT_SRC) connectsFrom (HUB_SRC) Artifact GENERAL_EXPRESSION expressedBy HUB_SRC.OBJECT_H_KEY = SAT_SRC.OBJECT_H_KEY consists of joined table HUB_SRC RepeatedByObject HUB comp_group_end ``` Finally, adding the newly calculated Signature Attribute can be done by adding the following expression in the template language: ``` Attribute END_LOAD_TIMESTAMP expressedBy COALESCE ( LEAD ( SAT_SRC.LOAD_TIMESTAMP , 1 ) OVER ( PARTITION BY SAT_SRC.OBJECT_H_KEY ORDER BY SAT_SRC.LOAD_TIMESTAMP) , GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) ``` It almost looks identical to the original expression except in cases of using the Signature attributes instead of the actual attribute names. CURRENT_RECORD_LOAD_END_DATE is a parameter in the tool and can be used in the template to get the LOAD_END_DATE fallback. Putting an `@` symbol in front of it tells the compiler that this is a parameter code, replacing it with the parameter value. Putting hashes around it will generate string quotes `'` around it. You can write your cast function in the expression. However, using the built-in functions will automatically consider the target technology. Functions should always contain the function name and, between the square brackets, the function parameters. For all built-in functions, see Chapter 3.4, “Function list” on this page: [https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3011182712](https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3011182712). The complete ETL template is shown below and is ready for upload. ``` Template SAT_COMP comp_group_start MAIN_GROUP VIEW_GRP RepeatedByObject SAT_TGT consists of target table SAT_COMP_TGT RepeatedByObject SAT_TGT connectsFrom (JOIN_SAT_SRC_HUB_SRC) Attribute OBJECT_H_KEY expressedBy SAT_SRC.OBJECT_H_KEY expressionDefinedByAttribute SAT_SRC.OBJECT_H_KEY Attribute LOAD_TIMESTAMP expressedBy SAT_SRC.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_SRC.LOAD_TIMESTAMP Attribute LOAD_CYCLE_ID expressedBy SAT_SRC.LOAD_CYCLE_ID expressionDefinedByAttribute SAT_SRC.LOAD_CYCLE_ID Attribute HASH_DIFF expressedBy SAT_SRC.HASH_DIFF expressionDefinedByAttribute SAT_SRC.HASH_DIFF Attribute DELETE_FLAG expressedBy SAT_SRC.DELETE_FLAG expressionDefinedByAttribute SAT_SRC.DELETE_FLAG Attribute TRANS_TIMESTAMP expressedBy SAT_SRC.TRANS_TIMESTAMP expressionDefinedByAttribute SAT_SRC.TRANS_TIMESTAMP Attribute PRIMARY_KEY expressedBy SAT_SRC.PRIMARY_KEY expressionDefinedByAttribute SAT_SRC.PRIMARY_KEY Attribute OTHER_ATTR expressedBy SAT_SRC.OTHER_ATTR expressionDefinedByAttribute SAT_SRC.OTHER_ATTR Attribute END_LOAD_TIMESTAMP expressedBy COALESCE ( LEAD ( SAT_SRC.LOAD_TIMESTAMP , 1 ) OVER ( PARTITION BY SAT_SRC.OBJECT_H_KEY ORDER BY SAT_SRC.LOAD_TIMESTAMP) , GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) expressionDefinedByAttribute SAT_SRC.LOAD_TIMESTAMP consists of source table SAT_SRC RepeatedByObject SAT consists of inner join JOIN_SAT_SRC_HUB_SRC RepeatedByObject HUB connectsFrom (SAT_SRC) connectsFrom (HUB_SRC) Artifact GENERAL_EXPRESSION expressedBy HUB_SRC.OBJECT_H_KEY = SAT_SRC.OBJECT_H_KEY consists of joined table HUB_SRC RepeatedByObject HUB comp_group_end ``` # Step 5: Build a DDL template The ETL template would stay the same if the target object weren’t a view but a persisted table. The only difference is the different DDL templates needed to create this structure. The structure of the DDL template completely matches the structure of the ‘target table’ in the ETL template, so to transform this into a DDL template: - Take the target table and its attributes and write a DDL component group shell around it. - Change the target into create_table. - Remove the connectsfrom and all other components. - Remove the expressedBy, expressionDefinedByAttribute, and expressionConditionedBy. This will give you the following template: ``` Template SAT_COMP comp_group_start DDL_GROUP CREA_GRP RepeatedByObject SAT_TGT consists of CREATE_TABLE table SAT_COMP_TGT RepeatedByObject SAT_TGT Attribute OBJECT_H_KEY Attribute LOAD_TIMESTAMP Attribute LOAD_CYCLE_ID Attribute HASH_DIFF Attribute DELETE_FLAG Attribute TRANS_TIMESTAMP Attribute PRIMARY_KEY Attribute OTHER_ATTR Attribute END_LOAD_TIMESTAMP comp_group_end ``` Important to notice that an attribute can have multiple signatures, but the interpreter will ensure every attribute will only be printed once in the script. # **Step 6: Upload the template(s)** If you didn’t already do this, in step 1, to upload the templates, click the View button of the ETL template in the template overview, this will bring you to the editor. (it is a very basic editor and not meant for full-on development, it will be improved in upcoming releases) and click on the upload button. ![image](media://f3f54b76-50d0-4cf5-8184-72a1211512cd) ![image](media://28126792-2b99-4985-8ff2-de9b724e4e71) Choose your template (.dvt) file. ![image](media://17c0e617-186a-445f-8517-0fc69d0f59e3) ![image](media://39edcfb2-8ee8-4e2d-8a2c-54d5d11e9a1a) Now click save or upload the DDL next. Automatically a test is performed. Testing a template will warn you if something is not according to the language. So it will complain about the line number if something is wrong. ![image](media://d94e44f7-b520-4991-b785-50f767454e16) If your test passed ![image](media://90309d42-a3ec-4055-b1d0-cc1f6714377d) Once all your templates are ok, you can lock your business Vault and generate your code. The ETL and DDL for your custom templates will be generated together with the other Business Vault objects during the ETL generation. Note that the DDL for the custom templates is included in the ETL generation instead of a separate DDL generation.   # **Extra: Building an Effectivity Satellite** Effectivity Satellites are objects which store only the active records. What follows is a description of how you could build such an object using Vaultspeed Studio. The only change to the template configuration compared to the computed satellite from before is that we do not need to define the END_LOAD_TIMESTAMP attribute in the target definition. The suffix could also be changed to eff instead of comp. This is the template for an effectivity satellite (when insert only = N): ``` template eff_sat comp_group_start eff_sat_group VIEW_GRP RepeatedByObject SAT_TGT consists of target table eff RepeatedByObject SAT_TGT connectsFrom (sat_filter) Attribute OBJECT_H_KEY expressedBy sat_src.OBJECT_H_KEY expressionDefinedByAttribute sat_src.OBJECT_H_KEY Attribute LOAD_CYCLE_ID expressedBy sat_src.LOAD_CYCLE_ID expressionDefinedByAttribute sat_src.LOAD_CYCLE_ID Attribute LOAD_TIMESTAMP expressedBy sat_src.LOAD_TIMESTAMP expressionDefinedByAttribute sat_src.LOAD_TIMESTAMP Attribute TRANS_TIMESTAMP expressedBy sat_src.TRANS_TIMESTAMP expressionDefinedByAttribute sat_src.TRANS_TIMESTAMP Attribute BUSINESS_SRC_KEY expressedBy sat_src.BUSINESS_SRC_KEY expressionDefinedByAttribute sat_src.BUSINESS_SRC_KEY Attribute PRIMARY_KEY expressedBy sat_src.PRIMARY_KEY expressionDefinedByAttribute sat_src.PRIMARY_KEY Attribute FOREIGN_KEY expressedBy sat_src.FOREIGN_KEY expressionDefinedByAttribute sat_src.FOREIGN_KEY Attribute OTHER_ATTR expressedBy sat_src.OTHER_ATTR expressionDefinedByAttribute sat_src.OTHER_ATTR consists of source table sat_src RepeatedByObject SAT consists of filter sat_filter RepeatedByObject SAT connectsFrom (sat_src) Artifact GENERAL_EXPRESSION group_1 expressedBy sat_src.LOAD_END_TIMESTAMP = GCASTFRMT[@#CURRENT_RECORD_LOAD_END_DATE#] expressionDefinedByAttribute sat_src.LOAD_END_TIMESTAMP group_2 expressedBy and sat_src.DELETE_FLAG = GCASTFRMT[@#DELETE_FLAG_NEGATIVE_VALUE#] expressionDefinedByAttribute sat_src.DELETE_FLAG comp_group_end ``` This template contains a filter, which will be translated into a where clause in SQL. Let's take a closer look at the filter expression: In this case, the expression consists of 2 parts, the `group_i` in front of the `expressedBy` tells the compiler that it should first add the group_1 expression for every LOAD_END_TIMESTAMP and then the second expression for every DELETE_FLAG. We again use parameters in the expressions, this time also the `DELETE_FLAG_NEGATIVE_VALUE`. For casting, we now use the `GCASTFRMT` function. This function will take the data type of the `DefinedByAttribute` into account to determine which cast function to use. The resulting SQL code is: ```sql DROP VIEW IF EXISTS "moto_bv"."ms_addresses_eff"; CREATE VIEW "moto_bv"."ms_addresses_eff" AS SELECT "sat_src"."addresses_hkey" AS "addresses_hkey" , "sat_src"."load_cycle_id" AS "load_cycle_id" , "sat_src"."load_date" AS "load_date" , "sat_src"."trans_timestamp" AS "trans_timestamp" , "sat_src"."street_name" AS "street_name" , "sat_src"."street_number" AS "street_number" , "sat_src"."postal_code" AS "postal_code" , "sat_src"."city" AS "city" , "sat_src"."address_number" AS "address_number" , "sat_src"."update_user" AS "update_user" FROM "moto_fl"."sat_ms_addresses" "sat_src" WHERE "sat_src"."load_end_date" = TO_TIMESTAMP('31/12/2399 23:59:59' , 'DD/MM/YYYY HH24:MI:SS'::varchar) and "sat_src"."delete_flag" = CAST('false' AS BOOLEAN) ; ``` We could have also done the delete flag without casting: ``` group_2 expressedBy and sat_src.DELETE_FLAG = @DELETE_FLAG_NEGATIVE_VALUE expressionDefinedByAttribute sat_src.DELETE_FLAG and "sat_src"."delete_flag" = false ```  If we have sources with insert only enabled and some with it disabled, we can modify the template as follows: Add an inline view/common table expression(CTE) where we calculate the latest load timestamp for each key: ``` comp_group_start max_ld_group INL_V_GRP componentGroupConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = Y)] RepeatedByObject SAT consists of Aggregated inline_view max_ld RepeatedByObject SAT connectsFrom (SAT_MLD_SRC) Aggregated Artifact MAX_LOAD_TIMESTAMP expressedBy MAX(SAT_MLD_SRC.LOAD_TIMESTAMP) expressionDefinedByAttribute SAT_MLD_SRC.LOAD_TIMESTAMP Attribute OBJECT_H_KEY expressedBy SAT_MLD_SRC.OBJECT_H_KEY expressionDefinedByAttribute SAT_MLD_SRC.OBJECT_H_KEY consists of source table SAT_MLD_SRC RepeatedByObject SAT comp_group_end ``` Note that the component group type is INL_V_GRP. We have also added a condition to the group so that it is only generated when `INSERT_ONLY_LOGIC = Y`, the `TAB SAT` condition type contains all source, tab_src parameters, and some extra properties (see part 3.5 Object Properties of the DVT language page: [https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3011182712](https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3011182712) ). The target definition now contains `inline_view` as type instead of `table,` and there is an extra `Aggregated`. This indicates that this inline view needs a group by clause. The first attribute is the max load timestamp calculation. It has the type `Artifact` instead of `Attribute`. This indicates that it is a temporary attribute and is not stored in the target object. The `Aggregated` modifier for an attribute indicates that it contains an aggregation and thus does not need to be added to the group by clause. Now that we have the last load timestamps per key, we can use that to select the last records when insert only is enabled. The full template is as follows: ``` template eff_sat comp_group_start max_ld_group INL_V_GRP componentGroupConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = Y)] RepeatedByObject SAT consists of Aggregated inline_view max_ld RepeatedByObject SAT connectsFrom (SAT_MLD_SRC) Aggregated Artifact MAX_LOAD_TIMESTAMP expressedBy MAX(SAT_MLD_SRC.LOAD_TIMESTAMP) expressionDefinedByAttribute SAT_MLD_SRC.LOAD_TIMESTAMP Attribute OBJECT_H_KEY expressedBy SAT_MLD_SRC.OBJECT_H_KEY expressionDefinedByAttribute SAT_MLD_SRC.OBJECT_H_KEY consists of source table SAT_MLD_SRC RepeatedByObject SAT comp_group_end comp_group_start eff_sat_group VIEW_GRP RepeatedByObject SAT_TGT consists of target table eff_sat RepeatedByObject SAT_TGT connectsFrom (sat_filter) Attribute OBJECT_H_KEY expressedBy sat_src.OBJECT_H_KEY expressionDefinedByAttribute sat_src.OBJECT_H_KEY Attribute LOAD_CYCLE_ID expressedBy sat_src.LOAD_CYCLE_ID expressionDefinedByAttribute sat_src.LOAD_CYCLE_ID Attribute LOAD_TIMESTAMP expressedBy sat_src.LOAD_TIMESTAMP expressionDefinedByAttribute sat_src.LOAD_TIMESTAMP Attribute TRANS_TIMESTAMP expressedBy sat_src.TRANS_TIMESTAMP expressionDefinedByAttribute sat_src.TRANS_TIMESTAMP Attribute BUSINESS_SRC_KEY expressedBy sat_src.BUSINESS_SRC_KEY expressionDefinedByAttribute sat_src.BUSINESS_SRC_KEY Attribute PRIMARY_KEY expressedBy sat_src.PRIMARY_KEY expressionDefinedByAttribute sat_src.PRIMARY_KEY Attribute FOREIGN_KEY expressedBy sat_src.FOREIGN_KEY expressionDefinedByAttribute sat_src.FOREIGN_KEY Attribute OTHER_ATTR expressedBy sat_src.OTHER_ATTR expressionDefinedByAttribute sat_src.OTHER_ATTR consists of source table sat_src RepeatedByObject SAT consists of inner join sat_max_ld_join componentConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = Y)] RepeatedByObject SAT connectsFrom (sat_src) connectsFrom (max_ld) Artifact GENERAL_EXPRESSION expressedBy sat_src.OBJECT_H_KEY = max_ld.OBJECT_H_KEY AND sat_src.LOAD_TIMESTAMP = max_ld.MAX_LOAD_TIMESTAMP consists of joined inline_view max_ld componentConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = Y)] RepeatedByObject SAT consists of filter sat_filter RepeatedByObject SAT connectsFrom (sat_max_ld_join) connectionConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = Y)] (sat_src) connectionConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = N)] Artifact GENERAL_EXPRESSION group_1 expressedBy sat_src.LOAD_END_TIMESTAMP = GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#] and expressionConditionedBy [(TAB SAT : INSERT_ONLY_LOGIC = N)] expressionDefinedByAttribute sat_src.LOAD_END_TIMESTAMP group_2 expressedBy sat_src.DELETE_FLAG = GCASTFRMT[@#DELETE_FLAG_NEGATIVE_VALUE#] expressionDefinedByAttribute sat_src.DELETE_FLAG comp_group_end ``` We have added a join with the max_ld inline view. If `INSERT_ONLY_LOGIC = Y,` then both the join and joined inline view need to be conditioned. In the filter, the expression with the end timestamp is filtered out. Note that this is not strictly needed since, in that case, `sat_src.LOAD_END_TIMESTAMP` does not exist, so the repeated by will ensure that nothing is printed for group_1. Adding the condition is mainly for readability. The connections of the filter now need to be conditioned to guarantee the proper data flow. You can play around with the connection. For example, the filter could always connect from the `sat_src`, but then the join connects from the filter instead of the sat, and then the target connection would have to be conditioned to connect to the filter or join depending on insert-only. > ℹ️ When generating SQL, the connectsFrom is not needed and can be left out of the templates. > ℹ️ component group names need to end with _group, but are referenced without the _group (this limitation will be removed in the future). The resulting SQL code for non-insert-only objects is the same as before, and for insert-only objects, it is: ```sql DROP VIEW IF EXISTS "moto_bv"."af_dag_eff"; CREATE VIEW "moto_bv"."af_dag_eff" AS WITH "max_ld" AS ( SELECT MAX("sat_mld_src"."load_date") AS "max_load_timestamp" , "sat_mld_src"."dag_hkey" AS "dag_hkey" FROM "moto_fl"."sat_af_dag" "sat_mld_src" GROUP BY "sat_mld_src"."dag_hkey" ) SELECT "sat_src"."dag_hkey" AS "dag_hkey" , "sat_src"."load_cycle_id" AS "load_cycle_id" , "sat_src"."load_date" AS "load_date" , "sat_src"."dag_id" AS "dag_id" , "sat_src"."pickle_id" AS "pickle_id" , "sat_src"."is_paused" AS "is_paused" , "sat_src"."is_subdag" AS "is_subdag" , "sat_src"."is_active" AS "is_active" , "sat_src"."last_scheduler_run" AS "last_scheduler_run" , "sat_src"."last_pickled" AS "last_pickled" , "sat_src"."last_expired" AS "last_expired" , "sat_src"."scheduler_lock" AS "scheduler_lock" , "sat_src"."fileloc" AS "fileloc" , "sat_src"."owners" AS "owners" , "sat_src"."description" AS "description" , "sat_src"."default_view" AS "default_view" , "sat_src"."schedule_interval" AS "schedule_interval" FROM "moto_fl"."sat_af_dag" "sat_src" INNER JOIN "max_ld" ON "sat_src"."dag_hkey" = "max_ld"."dag_hkey" AND "sat_src"."load_date" = "max_ld"."max_load_timestamp" WHERE "sat_src"."delete_flag" = CAST('false' AS BOOLEAN) ; ```