Skip to main content
Skip table of contents

target_entity_attribute_list_with_transform

First SOURCE type entity mapping will be used for the resolution of this variable.


See also:


Usage

VARIABLE
<target_entity_attribute_list_with_transform>

Notes

Supported in all loads.


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,
       attribute_d AS information
from src.SOURCE;

Load executed in the target database BigQuery:

SQL
insert into publish.D_RATECODE
select TO_HEX(MD5(UPPER(CONCAT(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,
       attribute_d AS information
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,
       attribute_d 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],
       attribute_d AS [information]
from [src].[SOURCE];

Load executed in the target database Redshift:

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,
       attribute_d AS information
from src.SOURCE;

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.