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:

Load options

Usage

OPTION

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:

OPTION

OPT_HISTORIZED_BY_DATAHASH_AS_INSERT: true

Resulting DML generated by Agile Data Engine:

SQL

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
  );