target_entity_attribute_list_with_transform_cast_and_positions

Supported databases: Databricks SQL, Azure Fabric, Azure SQL Database, Azure Synapse SQL,Snowflake

First SOURCE type entity mapping will be used for the resolution of this variable. The one to one source-target attribute mappings without transformation will be added with their source entity position

See also:

Variables

Usage

VARIABLE

<target_entity_attribute_list_with_transform_cast_and_positions>

Notes

Supported in all loads.

For Azure databases only the 1-1 mapping will be resolving the position to support column list syntax defined in https://learn.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql .

For Databricks each source column reference will be done through position in form _c<order> (indexing beginning from zero).

For Snowflake each source column reference will be done through position in form $<order> (indexing beginning from one).

Examples

Variable used in an OVERRIDE load step

Given mapping:

Target entity: publish.D_RATECODE

Source entity: src.SOURCE

source attributes	Formula	Target attribute
attribute_A,attribute_B,attribute_C	HASH	key_id
	RUN_ID	run_id
attribute_D		information

OVERRIDE load step in D_RATECODE entity:

SQL

insert into <target_schema>.<target_entity_name>
select <target_entity_attribute_list_with_transform>
from <source_entity_schema>.<source_entity_name>;

Load executed in the target database Snowflake:

SQL

insert into publish.D_RATECODE
select MD5(UPPER(NVL(attribute_A, '-1') || '~' || NVL(attribute_B, '-1')|| '~' || NVL(attribute_C, '-1'))) AS key_id,
       <timemillis> AS run_id,
       $4
from src.SOURCE;

Load executed in the target database Databricks:

SQL

insert into publish.D_RATECODE
select MD5(UPPER(COALESCE(CAST(attribute_A AS STRING), '-1') || '~' || COALESCE(CAST(attribute_B AS STRING), '-1')|| '~' || COALESCE(CAST(attribute_C AS STRING), '-1'))) AS key_id,
       <timemillis> AS run_id,
       CAST(_c3 AS STRING) AS information
from src.SOURCE;

Load executed in the target database Microsoft Fabric, Azure SQL Database, Azure Synapse SQL:

SQL

insert into [publish].[D_RATECODE]
select CONVERT(VARCHAR(32),HASHBYTES('MD5', UPPER(TRIM(COALESCE([attribute_A], '-1'))  + '~' + TRIM(COALESCE([attribute_B], '-1')) + '~' + TRIM(COALESCE([attribute_C], '-1')))),2) AS [key_id],
       <timemillis> AS [run_id],
       information 4
from [src].[SOURCE];