Skip to main content
Skip table of contents

Persistent staging

A persistent staging area (PSA) is a data warehouse zone that:

  • Stores source system data in its original format without transformation

  • Keeps a history of unique changes

  • Is by default insert-only, i.e. records are not deleted or updated

The persistent staging area can be used as a foundation for loading the publish layer where data from different source systems is combined and all the business logic is applied to support different end use cases. It takes care of data storage including change history enabling you to always come back to the original source system data with new use cases and modeling changes.

See the examples below on how the persistent staging approach can be applied with Agile Data Engine.

Notes

A data warehouse should have some form of raw data storage, e.g. a data lake, a persistent staging area or a raw Data Vault model, or a selection of these.

Storing source system data including its change history in its original format in a raw data storage layer allows more flexibility in the further data warehouse layers where data from multiple sources is combined and business logic is applied.

Examples

Configuring default settings for a persistent staging area

Default entity types STAGE or GENERIC could be used for persistent staging tables, but it is recommended to configure a new custom entity type with the desired default attributes, default transformations and load templates. Here we will present an example that you can modify to fit your specific needs.

Transformations

Let’s first define the new transformations needed in our example:

Transformation

Logic

Description

HASH_KEY

Mapped key attributes trimmed, concatenated and hashed.

This transformation will be used for loading a hashed primary key for each persistent staging table. Loads will use this entity key, an insert timestamp and a comparison hash to track change history per key.

ENTITY_KEY

Mapped key attributes trimmed and concatenated (without hashing).

ENTITY_KEY will have the same transformations as HASH_KEY but without the hash function. This transformation will be used to load a human-readable entity key attribute which can be utilised later on other data warehouse layers and for troubleshooting purposes.

COMPARISON_HASH

Mapped source attributes including key attributes concatenated and hashed.

The comparison hash will be used for comparing whether data has changed between loaded source data extracts.

LOAD_NAME

References the loadname variable.

Provides the load name as additional metadata for auditing and troubleshooting purposes.

PACKAGE_VERSION

References the packageversion variable.

Provides the package version as additional metadata for auditing and troubleshooting purposes.

Examples of the transformations configured in CONFIG_LOAD_TRANSFORMATIONS for Snowflake and Synapse:

JSON
JSON
{
    ...
    "attrTransformationTypes": [
        ...
        {
            "transformationType": "HASH_KEY",
            "shortDescription": "Hash key transformation",
            "description": "Hash transformation for keys: Case-insensitive, trimmed, nulls converted to '-1', date/time formats explicitly cast as string, MD5-hashed.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 101
        },
        {
            "transformationType": "ENTITY_KEY",
            "shortDescription": "Entity key transformation",
            "description": "Entity key transformation, same as HASH_KEY, but without hashing: Case-insensitive, trimmed, nulls converted to '-1', date/time formats explicitly cast as string.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 102
        },
        {
            "transformationType": "COMPARISON_HASH",
            "shortDescription": "Comparison hash transformation",
            "description": "Hash transformation for change comparison: Case-sensitive, no trimming, nulls converted to '-1'.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 103
        },
        {
            "transformationType": "LOAD_NAME",
            "shortDescription": "Load name",
            "description": "Inserts load name.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 104
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "shortDescription": "Package version",
            "description": "Inserts version number of deployed package.",
            "isShownInDesigner": true,
            "ordinalPositionInDesigner": 105
        }
    ],
    "dbmsTransformationFormulas": [
        ...
        {
            "transformationType": "HASH_KEY",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "<@compress single_line=true>MD5(<#list sourceAttributes as attribute> <#if attribute.datatype?? && attribute.datatype == \"DATE\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDD'), '-1') <#elseif attribute.datatype?? && (attribute.datatype?lower_case?contains(\"varchar\") || attribute.datatype?lower_case?contains(\"char\"))> NVL(UPPER(TRIM(${attribute.attributeName})), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDDTHH24MISSFF5'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP_TZ\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDDTHH24MISSFF5TZHTZM'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"GEOGRAPHY\"> NVL(ST_ASWKT(${attribute.attributeName}), '-1') <#else> NVL(UPPER(CAST(${attribute.attributeName} AS VARCHAR)), '-1') </#if> <#if attribute_has_next> || '~' ||</#if></#list>)</@compress>"
        },
        {
            "transformationType": "HASH_KEY",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "<@compress single_line=true>CONVERT(CHAR(32),HASHBYTES('MD5',<#list sourceAttributes as attribute><#if attribute.datatype?? && attribute.datatype == \"DATE\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMdd'), '-1') <#elseif attribute.datatype?? && ( attribute.datatype?lower_case?contains(\"varchar\") || attribute.datatype?lower_case?contains(\"char\") )>COALESCE(UPPER(TRIM(${attribute.attributeName})), '-1')<#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMddTHHmmssffffff'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP_TZ\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMddTHHmmssffffffz'), '-1') <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")>COALESCE(CAST(CAST([${attribute.attributeName}] AS INT) AS VARCHAR), '-1')<#else>COALESCE(UPPER(CAST([${attribute.attributeName}] AS VARCHAR)), '-1') </#if><#if attribute_has_next> + '~' + </#if></#list>), 2)</@compress>"
        },
        {
            "transformationType": "ENTITY_KEY",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "<@compress single_line=true><#list sourceAttributes as attribute> <#if attribute.datatype?? && attribute.datatype == \"DATE\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDD'), '-1') <#elseif attribute.datatype?? && (attribute.datatype?lower_case?contains(\"varchar\") || attribute.datatype?lower_case?contains(\"char\"))> NVL(UPPER(TRIM(${attribute.attributeName})), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDDTHH24MISSFF5'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP_TZ\"> NVL(TO_VARCHAR(${attribute.attributeName}, 'YYYYMMDDTHH24MISSFF5TZHTZM'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"GEOGRAPHY\"> NVL(ST_ASWKT(${attribute.attributeName}), '-1') <#else> NVL(UPPER(CAST(${attribute.attributeName} AS VARCHAR)), '-1') </#if> <#if attribute_has_next> || '~' ||</#if></#list></@compress>"
        },
        {
            "transformationType": "ENTITY_KEY",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "<@compress single_line=true><#list sourceAttributes as attribute><#if attribute.datatype?? && attribute.datatype == \"DATE\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMdd'), '-1') <#elseif attribute.datatype?? && ( attribute.datatype?lower_case?contains(\"varchar\") || attribute.datatype?lower_case?contains(\"char\") )>COALESCE(UPPER(TRIM(${attribute.attributeName})), '-1')<#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMddTHHmmssffffff'), '-1') <#elseif attribute.datatype?? && attribute.datatype == \"TIMESTAMP_TZ\"> COALESCE(FORMAT(${attribute.attributeName}, 'yyyyMMddTHHmmssffffffz'), '-1') <#elseif attribute.datatype?? && attribute.datatype?lower_case?contains(\"bool\")>COALESCE(CAST(CAST([${attribute.attributeName}] AS INT) AS VARCHAR), '-1')<#else>COALESCE(UPPER(CAST([${attribute.attributeName}] AS VARCHAR)), '-1') </#if><#if attribute_has_next> + '~' + </#if></#list></@compress>"
        },
        {
            "transformationType": "COMPARISON_HASH",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "<@compress single_line=true>MD5(<#list sourceAttributes as attribute> <#if attribute.datatype?? && attribute.datatype == \"GEOGRAPHY\"> NVL(ST_ASWKT(${attribute.attributeName}), '-1') <#else> NVL(CAST(${attribute.attributeName} AS VARCHAR), '-1') </#if> <#if attribute_has_next> || '~' ||</#if></#list>)</@compress>"
        },
        {
            "transformationType": "COMPARISON_HASH",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "<@compress single_line=true>CONVERT(CHAR(32),HASHBYTES('MD5',<#list sourceAttributes as attribute> COALESCE(CAST(${attribute.attributeName} AS VARCHAR), '-1')<#if attribute_has_next> + '~' + </#if></#list>), 2)</@compress>"
        },
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "LOAD_NAME",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "'<@compress single_line=true><loadname></@compress>'"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "SNOWFLAKE",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        },
        {
            "transformationType": "PACKAGE_VERSION",
            "dbmsProduct": "MS_SQL_DW",
            "transformationFormula": "<@compress single_line=true><packageversion></@compress>"
        }
    ]
}

Making changes to configuration packages

Note that the configurations should be appended to CONFIG_LOAD_TRANSFORMATIONS, i.e. not replacing existing configurations.

More examples available in GitHub

Our latest default configuration contains the PSA entity type. You can find it and other examples in this GitHub repository.

Entity type

Then, let’s create a new entity type that defines the required default attributes and references the transformations created in the previous step:

Default attribute

Transformation

Description

dw_id

HASH_KEY

Entity key, see transformation description above.

dw_entity_key

ENTITY_KEY

Non-hashed entity key, see transformation description above.

dw_hash

COMPARISON_HASH

Change tracking comparison attribute, see transformation description above.

dw_run_id

RUN_ID

Used optionally with Run ID Logic.

meta_insert_time

CURRENT_TS

Record insert timestamp

meta_update_time

CURRENT_TS

Record update timestamp. For regular use cases this will be the same as meta_insert_time as persistent staging is insert only.

However, in special cases where you do not want to preserve change history, you can use a different loading pattern and this timestamp indicates when the record was updated.

meta_load_name

LOAD_NAME

Metadata attribute, see transformation description above.

meta_package_version

PACKAGE_VERSION

Metadata attribute, see transformation description above.

meta_source_system

-

Source system name

meta_source_entity

-

Source entity name

meta_file_name

-

Source file name

Example entity type configuration in CONFIG_ENTITY_DEFAULTS:

JSON
JSON
{
    ...
    "entityTypes": [
        ...
        {
            "entityType": "PSA",
            "description": "Persistent staging entity",
            "namePrefix": "PSA_",
            "dvEntity": false,
            "byDefaultAutoManaged": false,
            "defaultSchema": "psa",
            "defaultZone": "RAW",
            "defaultPhysicalType": "TABLE"
        }
    ],
    "attributeTypes": [
        ...
        {
            "attributeType": "HASH_KEY"
        },
        {
            "attributeType": "ENTITY_KEY"
        },
        {
            "attributeType": "COMPARISON_HASH"
        },
        {
            "attributeType": "META_LOAD_NAME"
        },
        {
            "attributeType": "META_PACKAGE_VERSION"
        },
        {
            "attributeType": "META_SOURCE_SYSTEM"
        },
        {
            "attributeType": "META_SOURCE_ENTITY"
        },
        {
            "attributeType": "META_FILE_NAME"
        }
    ],
    "defaultAttributes": [
        ...
        {
            "defaultAttributeId": "201",
            "attributeNameTemplate": "dw_id",
            "datatype": "CHAR",
            "charUsed": false,
            "dataLength": 32,
            "description": "Hash key",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_HASHKEY"
        },
        {
            "defaultAttributeId": "202",
            "attributeNameTemplate": "dw_entity_key",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 200,
            "description": "Entity key",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "ENTITY_KEY"
        },
        {
            "defaultAttributeId": "203",
            "attributeNameTemplate": "dw_hash",
            "datatype": "CHAR",
            "charUsed": false,
            "dataLength": 32,
            "description": "Comparison hash",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_DATAHASH"
        },
        {
            "defaultAttributeId": "204",
            "attributeNameTemplate": "dw_run_id",
            "datatype": "INTEGER8",
            "charUsed": false,
            "description": "ADE run id",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "RUN_ID"
        },
        {
            "defaultAttributeId": "205",
            "attributeNameTemplate": "meta_insert_time",
            "datatype": "TIMESTAMP",
            "charUsed": false,
            "description": "Insert timestamp",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_LOAD_TIME"
        },
        {
            "defaultAttributeId": "206",
            "attributeNameTemplate": "meta_update_time",
            "datatype": "TIMESTAMP",
            "charUsed": false,
            "description": "Update timestamp",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "DV_UPDATE_TIME"
        },
        {
            "defaultAttributeId": "207",
            "attributeNameTemplate": "meta_load_name",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Load name",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "META_LOAD_NAME"
        },
        {
            "defaultAttributeId": "208",
            "attributeNameTemplate": "meta_package_version",
            "datatype": "INTEGER8",
            "charUsed": false,
            "description": "ADE package version",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "META_PACKAGE_VERSION"
        },
        {
            "defaultAttributeId": "209",
            "attributeNameTemplate": "meta_source_system",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 50,
            "description": "Source system name",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "META_SOURCE_SYSTEM"
        },
        {
            "defaultAttributeId": "210",
            "attributeNameTemplate": "meta_source_entity",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Source entity name",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "META_SOURCE_ENTITY"
        },
        {
            "defaultAttributeId": "211",
            "attributeNameTemplate": "meta_file_name",
            "datatype": "VARCHAR",
            "charUsed": false,
            "dataLength": 100,
            "description": "Source file name",
            "isNullable": false,
            "isPersistent": true,
            "attributeType": "META_FILE_NAME"
        }
    ],
    "entityTypeDefaultAttributes": [
        ...
        {
            "entityTypeDefaultAttributeId": "101",
            "entityType": "PSA",
            "defaultAttributeId": "201",
            "position": "1",
            "transformationType": "HASH_KEY"
        },
        {
            "entityTypeDefaultAttributeId": "102",
            "entityType": "PSA",
            "defaultAttributeId": "202",
            "position": "2",
            "transformationType": "ENTITY_KEY"
        },
        {
            "entityTypeDefaultAttributeId": "103",
            "entityType": "PSA",
            "defaultAttributeId": "203",
            "position": "3",
            "transformationType": "COMPARISON_HASH"
        },
        {
            "entityTypeDefaultAttributeId": "104",
            "entityType": "PSA",
            "defaultAttributeId": "204",
            "position": "4",
            "transformationType": "RUN_ID"
        },
        {
            "entityTypeDefaultAttributeId": "105",
            "entityType": "PSA",
            "defaultAttributeId": "205",
            "position": "5",
            "transformationType": "CURRENT_TS"
        },
        {
            "entityTypeDefaultAttributeId": "106",
            "entityType": "PSA",
            "defaultAttributeId": "206",
            "position": "6",
            "transformationType": "CURRENT_TS"
        },
        {
            "entityTypeDefaultAttributeId": "107",
            "entityType": "PSA",
            "defaultAttributeId": "207",
            "position": "7",
            "transformationType": "LOAD_NAME"
        },
        {
            "entityTypeDefaultAttributeId": "108",
            "entityType": "DW",
            "defaultAttributeId": "208",
            "position": "8",
            "transformationType": "PACKAGE_VERSION"
        },
        {
            "entityTypeDefaultAttributeId": "109",
            "entityType": "PSA",
            "defaultAttributeId": "209",
            "position": "9"
        },
        {
            "entityTypeDefaultAttributeId": "110",
            "entityType": "PSA",
            "defaultAttributeId": "210",
            "position": "10"
        },
        {
            "entityTypeDefaultAttributeId": "111",
            "entityType": "PSA",
            "defaultAttributeId": "211",
            "position": "11"
        }
    ],
    "entityTypeDefaultLoadTypes": [
        {
            "entityPhysicalType": "TABLE",
            "entityType": "PSA",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "METADATA_ONLY",
            "entityType": "PSA",
            "loadType": "TRANSFORM_PERSIST"
        },
        {
            "entityPhysicalType": "VIEW",
            "entityType": "PSA",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        {
            "entityPhysicalType": "MATERIALIZED_VIEW",
            "entityType": "PSA",
            "loadType": "TRANSFORM_SQL_VIEW"
        },
        {
            "entityPhysicalType": "METADATA_ONLY",
            "entityType": "FACT",
            "loadType": "TRANSFORM_PERSIST"
        }
    ]
    ...
}

Making changes to configuration packages

Note that the configurations should be appended to CONFIG_ENTITY_DEFAULTS, i.e. not replacing existing configurations. Also, the example is referencing some existing default attribute and transformation types.

More examples available in GitHub

Our latest default configuration contains the PSA entity type. You can find it and other examples in this GitHub repository.

Creating a persistent staging table

Finally, let’s create a persistent staging entity using the configurations done in the previous example:

  1. Select or create a package and create a new entity, choose type PSA and physical type TABLE.

  2. Navigate to the source entity (staging table) and create an outgoing load selecting the PSA entity as target entity.

  3. Create an entity mapping as follows:

Source attribute

Transformation

Target attribute

Notes

Primary key attribute(s) in the source system entity.

HASH_KEY

dw_id

Will be used in the entity load, can also be used for e.g. smoke tests and later on other data warehouse layers.

Primary key attribute(s) in the source system entity.

ENTITY_KEY

dw_entity_key

Non-hashed and human-readable entity key, can be used later on other data warehouse layers and for troubleshooting purposes.

All mapped source entity attributes including the key attributes.

COMPARISON_HASH

dw_hash

Change tracking comparison attribute used in the entity load.

Map all mapped source entity attributes including the key attributes here. Consider excluding e.g. metadata attributes when you do not want them to affect the change capture.

-

RUN_ID

dw_run_id

Used optionally with Run ID Logic.

-

CURRENT_TS

meta_insert_time

Record insert timestamp

-

CURRENT_TS

meta_update_time

Record update timestamp (see notes in entity type configuration above)

-

LOAD_NAME

meta_load_name

Metadata attribute, see transformation description above.

-

PACKAGE_VERSION

meta_package_version

Metadata attribute, see transformation description above.

stg_source_system

-

meta_source_system

Source system name mapped from stage.

stg_source_entity

-

meta_source_entity

Source entity name mapped from stage.

stg_file_name

-

meta_file_name

Source file name mapped from stage.

All source entity attributes.

NONE

Multiple, depending on source entity.

All source entity attributes mapped to target attributes.

Staging metadata attributes

Note that staging tables in this example have metadata attributes stg_source_system, stg_source_entity and stg_file_name. It is a good practice to define the needed metadata attributes in staging tables so that they can be easily mapped to further data warehouse zones in loads. See more in Customizing default attributes and transformations

4. Set desired load options:

Load option

Notes

OPT_HISTORIZED_BY_DATAHASH_AS_INSERT

Enables the insert-only load logic that compares incoming data against the latest version in the entity by entity key using the comparison hashes.

Note that for this to work, you need to ensure that the following technical attribute types are set:

  • dw_id: DV_HASHKEY

  • dw_hash: DV_DATAHASH

  • meta_insert_time: DV_LOAD_TIME

These were configured in the defaults above.

OPT_INSERT_UPDATE_BY_DATAHASH_AS_MERGE

ALTERNATIVELY use the merge logic in special cases where you do not need to store historical changes.

OPT_USE_RUN_IDS

OPTIONALLY enable Run ID Logic.

OPT_NUM_RUN_IDS_PER_TRANSACTION

OPTIONALLY use with Run ID Logic, set value 1 to load one run id per transaction ensuring that source data batches are inserted one at a time and in the correct order. This way there will not be duplicates over entity key and insert timestamp as long as there are no duplicates within one batch.

OPT_USE_TIMESLOT_LOGIC

OPTIONALLY enable Timeslot Logic.

Note that you need to set technical attribute type TIMESLOT for a timestamp attribute in the target entity. When executing the load, Agile Data Engine will get the minimum and maximum values in the incoming data over this attribute and use them to filter down the existence check when inserting into the target entity.

OPT_SCAN_ONLY_THE_LATEST_BY_KEY

OPTIONALLY scan the latest version of the data from the source ensuring that no duplicates will be inserted into the target entity.

OPT_SCAN_ONLY_THE_LATEST_WITH_SOURCE_ATTRIBUTE_TYPE

OPTIONALLY use with OPT_SCAN_ONLY_THE_LATEST_BY_KEY to define over which key the latest version is scanned.

Use the Export SQL feature to preview entity SQL & load SQL.

Example load SQL using OPT_HISTORIZED_BY_DATAHASH_AS_INSERT:

SQL
INSERT INTO psa.PSA_DEPARTMENT (
    dw_id
    , dw_entity_key
    , dw_hash
    , dw_run_id
    , meta_insert_time
    , meta_update_time
    , meta_load_name
    , meta_package_version
    , meta_source_system
    , meta_source_entity
    , meta_file_name
    , departmentid
    , name
    , groupname
    , modifieddate
)
SELECT DISTINCT
    dw_id
    , dw_entity_key
    , dw_hash
    , dw_run_id
    , meta_insert_time
    , meta_update_time
    , meta_load_name
    , meta_package_version
    , meta_source_system
    , meta_source_entity
    , meta_file_name
    , departmentid
    , name
    , groupname
    , modifieddate
FROM (
    SELECT
        MD5(NVL(UPPER(CAST(departmentid AS VARCHAR)), '-1')) AS dw_id
        , NVL(UPPER(CAST(departmentid AS VARCHAR)), '-1') AS dw_entity_key
        , MD5(NVL(CAST(departmentid AS VARCHAR), '-1') || '~' || NVL(CAST(name AS VARCHAR), '-1') || '~' || NVL(CAST(groupname AS VARCHAR), '-1') || '~' || NVL(CAST(modifieddate AS VARCHAR), '-1') ) AS dw_hash
        , <targetrunid> AS dw_run_id
        , CURRENT_TIMESTAMP::timestamp_ntz AS meta_insert_time
        , CURRENT_TIMESTAMP::timestamp_ntz AS meta_update_time
        , 'load_psa_department_from_stg_adventureworks_department' AS meta_load_name
        , 1001274 AS meta_package_version
        , stg_source_system AS meta_source_system
        , stg_source_entity AS meta_source_entity
        , stg_file_name AS meta_file_name
        , departmentid AS departmentid
        , name AS name
        , groupname AS groupname
        , modifieddate AS modifieddate
    FROM
        staging.STG_ADVENTUREWORKS_DEPARTMENT src_entity
) src
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM (
            SELECT
                t1.dw_hash
            FROM (
                SELECT
                    s.dw_hash
                    , ROW_NUMBER()
                OVER (
                    PARTITION BY
                        s.dw_id
                    ORDER BY
                        s.meta_insert_time DESC
                )
                AS
                    dv_load_time_last
                FROM
                    edw.DW_TEST s
            ) t1
            WHERE
                t1.dv_load_time_last = 1
        ) trg
        WHERE
            trg.dw_hash = src.dw_hash
    )
;
JavaScript errors detected

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

If this problem persists, please contact our support.