Skip to main content
Skip table of contents

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

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
  );
JavaScript errors detected

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

If this problem persists, please contact our support.