---
title: "ETL Settings Details"
canonical: "https://docs.vaultspeed.com/space/VPD/3012755484/ETL%20Settings%20Details"
format: markdown
---
VaultSpeed can add specific settings to your ETL code, according to what is available for your specific target technology.

> 📝 This page describes some examples of the ETL-settings.  How to work with the screen in general is explained in the Walkthrough article that you can find here: [https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3013050377](https://vaultspeed.atlassian.net/wiki/spaces/VPD/pages/3013050377)

Some in-dept details and example settings are shown below.

> Macro (toc)

### ETL Settings Examples & Special Keywords

For certain settings, you might want to reference certain attributes, objects. This can be done by entering specific keywords or attribute types in the settings for example.

#### Example 1: (SNOWFLAKE)

Adding Execute as owner and storing the load_cycle_id of the load_cycle_info_table in a separate customer-created table.

Two special settings were done in this case:

1. Add ‘execute as owner’ to the Function Properties

```
Execute as owner
```

1. Add a lookup of the VaultSpeed load cycle id + insert in the begin mapping command

```
var pload_cycle_id = 0;
// Lookup the load_cycle_id
var res = snowflake.createStatement({sqlText: 'select "@#LOAD_CYCLE_ID#" from "@#SCHEMA_MTD#"."@#LOAD_CYCLE_INFO_TABLE_NAME#"'}).execute();
res.next()
pload_cycle_id = res.getColumnValue(1);

// insert this load into customer created log table
var ptab_nm = "@#DB_OBJECT#"
snowflake.execute({sqlText:'insert into "my_own_schema"."my_own_table"(object_name,load_date,load_cycle_id)values(?, current_timestamp, ?)',binds: [ptab_nm,pload_cycle_id]});
```

 

The code block above will use VaultSpeed parameters + a special keyword (DB_OBJECT) to make the setting flexible

When using the @#....# with in between a parameter code, it will use the parameter value like set in the tool on the lowest available level.

In this case LOAD_CYCLE_ID, SCHEMA_MTD, and LOAD_CYCLE_INFO_TABLE are VaultSpeed parameters.  
Also for the special keywords, the same @#....# is used. DB_OBJECT for example just will be replaced by the target table of this mapping.

![image](media://0425382f-ded2-42e1-be98-baace4cf343e)

Once we apply this ETL setting (in this case to the HUB tables), in our next generation, the SQL code will look like the example below:

```
CREATE OR REPLACE PROCEDURE "moto_scn01_proc"."hub_mm_channels_init"()
 RETURNS varchar 
LANGUAGE JAVASCRIPT 
execute as owner 
AS $$ 

var pload_cycle_id = 0;
// Lookup the load_cycle_id
var res = snowflake.createStatement({sqlText: 'select "load_cycle_id" from "moto_mktg_scn01_mtd"."load_cycle_info"'}).execute();
res.next()
pload_cycle_id = res.getColumnValue(1);

// insert this load into customer created log table
var ptab_nm = "hub_channels"
snowflake.execute({sqlText:'insert into "my_own_schema"."my_own_table"(object_name,load_date,load_cycle_id)values(?, current_timestamp, ?)',binds: [ptab_nm,pload_cycle_id]});
 
var truncate_hub_tgt = snowflake.createStatement( {sqlText: `
	TRUNCATE TABLE "moto_scn05_fl"."hub_channels";
`} ).execute();


var hub_tgt = snowflake.createStatement( {sqlText: `
	INSERT INTO "moto_scn05_fl"."hub_channels"(
		 "channels_hkey"
		,"load_date"
		,"load_cycle_id"
		,"channel_code_bk"
	)
	SELECT DISTINCT 
		  "stg_src"."channels_hkey" AS "channels_hkey"
		, "stg_src"."load_date" AS "load_date"
		, "stg_src"."load_cycle_id" AS "load_cycle_id"
		, "stg_src"."channel_code_bk" AS "channel_code_bk"
	FROM "moto_mktg_scn05_stg"."channels" "stg_src"
	;
`} ).execute();

return "Done.";$$;
```

 

#### Example 2: (SQL SERVER)

Adding variables and storing the load_cycle_id of the load_cycle_info_table in a separate customer-created table together with a row count

Three special settings were done in this case:

1. Add Declaration

```
@Load_cycle_id  integer;
```

1. Add a lookup of the VaultSpeed load cycle id in the begin mapping command

```
SET @Load_cycle_id = (
    SELECT
        [@#LOAD_CYCLE_ID#]
    FROM
        [@#SCHEMA_MTD#].[@#LOAD_CYCLE_INFO_TABLE_NAME#]
	);
```

1. Add insert in the end mapping command

```
INSERT INTO MY_OWN_SCHEMA.MY_OWN_TABLE(LOAD_CYCLE_ID,OBJECT,IMPACTED_ROWS)values(@Load_cycle_id,'@#FULL_DB_OBJECT#',@@ROWCOUNT);
```

The code blocks above will use VaultSpeed parameters + a special keyword (DB_OBJECT) to make the setting flexible

When using the @#....# with in between a parameter code, it will use the parameter value like set in the tool on the lowest available level.

In this case, LOAD_CYCLE_ID, SCHEMA_MTD, and LOAD_CYCLE_INFO_TABLE are VaultSpeed parameters.  
Also for the special keywords, the same @#....# is used. FULL_DB_OBJECT for example just will be replaced by the target table of this mapping.

 

Once we apply this ETL setting (in this case to the HUB tables), in our next generation, the SQL code will look like the example below:

```
CREATE PROCEDURE [moto_scn01_proc].[hub_mm_addresses_init]

AS  

BEGIN  
DECLARE 
 @Load_cycle_id  integer;  
SET @Load_cycle_id = ( 
    SELECT 
        [load_cycle_id] 
    FROM 
        [moto_mktg_scn01_mtd].[LOAD_CYCLE_INFO_TABLE] 
	);  
BEGIN -- hub_tgt 

	INSERT INTO [sqlserverdb_fl].[hub_addresses]( 
		 [addresses_hkey] 
		,[load_date] 
		,[load_cycle_id] 
		,[street_name_bk] 
		,[street_number_bk] 
		,[postal_code_bk] 
		,[city_bk] 
	) 
	SELECT DISTINCT  
		  [stg_src].[addresses_hkey] AS [addresses_hkey] 
		, [stg_src].[load_date] AS [load_date] 
		, [stg_src].[load_cycle_id] AS [load_cycle_id] 
		, [stg_src].[street_name_bk] AS [street_name_bk] 
		, [stg_src].[street_number_bk] AS [street_number_bk] 
		, [stg_src].[postal_code_bk] AS [postal_code_bk] 
		, [stg_src].[city_bk] AS [city_bk] 
	FROM [moto_mktg_scn05_stg].[addresses] [stg_src] 
	LEFT OUTER JOIN [sqlserverdb_fl].[hub_addresses] [hub_src] ON  [stg_src].[addresses_hkey] = [hub_src].[addresses_hkey] 
	WHERE  [hub_src].[addresses_hkey] IS NULL 
	; 
END; 


INSERT INTO MY_OWN_SCHEMA.MY_OWN_TABLE([load_cycle_id],OBJECT,IMPACTED_ROWS)values(@Load_cycle_id,'@#FULL_DB_OBJECT',@@ROWCOUNT); 

END; 
```

#### **Special Keywords**:

| **Keyword** | **Result** | **Example** |
| --- | --- | --- |
| DB_OBJECT | Target table name | HUB_COUNTRIES |
| FULL_DB_OBJECT | Target table name and schema | SCHEMA_DV.HUB_COUNTRIES |
| QUOTED_DB_OBJECT | Quoted target table name based upon database type | “HUB_COUNTRIES” |
| QUOTED_FULL_DB_OBJECT | Quoted target table name and schema based upon database type | “SCHEMA_DV”.”HUB_COUNTRIES” |
| MAP_NAME | Mapping name | HUB_MM_COUNTRIES_INIT |
| FULL_MAP_NAME | Mapping name and schema | SCHEMA_PROC.HUB_MM_COUNTRIES_INIT |
| QUOTED_MAP_NAME | Quoted mapping name based upon database type | “HUB_MM_COUNTRIES_INIT” |
| QUOTED_FULL_MAP_NAME | Quoted mapping name and schema based upon database type | “SCHEMA_PROC”.”HUB_MM_COUNTRIES_INIT” |
| SELECT_OBJECTS | Only available for Oracle in the select hint setting. This need to be specified in a very special way: example:<br>```
{Expression:USE_HASH(?) RepeatedBy:SELECT_OBJECTS ConcatenatedBy:,} APPEND PARALLEL
```<br>The special clause needs to start with {Expression and end with }<br>**Expression** will be the part to repeat<br>**RepeatedBy** what to repeat, currently only SELECT_OBJECTS<br>**ConcatenatedBy** will be the concat symbol.  
Before and after the { }, normal text can be added. | The result of the example in the Result column in the case of the query:<br>select   
column1,column2  
from obj1  
join obj2 on obj1.key = obj2.key  
where 1= 1<br>will be<br>select   
/* USE_HASH(OBJ1),USE_HASH(OBJ2) APPEND PARALLEL */  
column1,column2  
from obj1  
join obj2 on obj1.key = obj2.key  
where 1= 1 |

#### Signature Attributes:

Also, if you specify a signature attribute as can be done in the DDL settings, it is replaced by the real attribute names.

Important note when using signatures in the relationship based link tables :

- in DDL the Link hash key is the OBJECT_H_KEY, in ETL, this is the OBJECT_L_H_KEY
- in DDL the hub hash key of the child table in the relation is the OBJECT_P_H_KEY, in ETL this is the OBJECT_H_KEY.