OPT_HISTORIZED_BY_DATAHASH_AS_INSERT
Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Microsoft Fabric, Google BigQuery, Databricks SQL
OPT_HISTORIZED_BY_DATAHASH_AS_INSERT is a load option that enables only the non-existing datahashes to be inserted within the load.
See also:
Usage
OPT_HISTORIZED_BY_DATAHASH_AS_INSERT: Boolean
Default: false
Notes
OPT_HISTORIZED_BY_DATAHASH_AS_INSERT can be used in table loads that have a single entity mapping (i.e. one source entity) and attributes with following attribute types defined:
DV_LOAD_TIME
DV_DATAHASH
either DV_HASHKEY or DV_REFERENCING_HASHKEY
Hint: Can be used also with Run ID logic.
Examples
Load entity with historizing datahash option
This example presents how an entity load would work with historizing logic enabled.
Assumptions:
Data is staged to table stage.stg_taxi_zone_lookup and it will be loaded to tgt.all_new_entries
Historizing is enabled for the load setting option:
OPT_HISTORIZED_BY_DATAHASH_AS_INSERT: true
Resulting DML generated by Agile Data Engine:
INSERT INTO
tgt.ALL_NEW_ENTRIES (
...
)
SELECT
DISTINCT ...
FROM
(
SELECT
...
FROM
stage.STG_TAXI_ZONE_LOOKUP src_entity
WHERE
) src
WHERE
NOT EXISTS (
SELECT
1
FROM
(
SELECT
t1.datahash
FROM
(
SELECT
s.datahash,
ROW_NUMBER() OVER(
PARTITION BY s.all_new_entries_key
ORDER BY
s.insert_time DESC
) AS dv_load_time_last
FROM
tgt.ALL_NEW_ENTRIES s
) t1
WHERE
t1.dv_load_time_last = 1
) trg
WHERE
trg.datahash = src.datahash
);