OPT_INSERT_UPDATE_DELETE_BY_ID_AS_MERGE

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Google BigQuery

Azure Synapse SQL usage requires
That the target table is HASH distributed.

Define the entity distribution style as KEY
Add DISTRIBUTION_KEY for the entity

OPT_INSERT_UPDATE_DELETE_BY_ID_AS_MERGE is a load option that enables 1-1 loading with key

See also:

Load options

Usage

OPTION

OPT_INSERT_UPDATE_DELETE_BY_ID_AS_MERGE: Boolean

Default: false

Notes

OPT_INSERT_UPDATE_DELETE_BY_ID_AS_MERGE 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
either DV_HASHKEY or DV_REFERENCING_HASHKEY
DV_UPDATE_TIME (optional)

Hint: Can be used also with Run ID logic.

Examples

Insert, Update, Delete Load by ID as MERGE option

This example presents how an entity load would work with this logic enabled.

Assumptions:

Data is staged to table stage.stg_taxi_zone_lookup and it will be loaded to tgt.latest_entries

Feature is enabled for the load by setting option:

OPTION

OPT_INSERT_UPDATE_DELETE_BY_ID_AS_MERGE: true

Resulting DML generated by Agile Data Engine:

SQL

-- Amazon Redshift syntax
begin transaction;
-- Removing records those do not exist in source
DELETE FROM
  tgt.LATEST_ENTRIES
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      (
        SELECT
          DISTINCT <key formula> AS latest_entries_id
        FROM
          stage.STG_TAXI_ZONE_LOOKUP src_entity
      ) src
    WHERE
      tgt.LATEST_ENTRIES.latest_entries_id = src.latest_entries_id
  );
 -- Updating records those already exists in target
UPDATE
  tgt.LATEST_ENTRIES trg
SET
  .. = src...
FROM
  (
    SELECT
      DISTINCT <key formula> AS latest_entries_id,
      ...
    FROM
      stage.STG_TAXI_ZONE_LOOKUP src_entity
  ) src
WHERE
  trg.latest_entries_id = src.latest_entries_id;
-- Insert records those do not exist yet in target
INSERT INTO
  tgt.LATEST_ENTRIES (...)
SELECT
  DISTINCT 
  ...
FROM
  (
    SELECT
      <key formula> AS latest_entries_id,
      ...
    FROM
      stage.STG_TAXI_ZONE_LOOKUP src_entity
  ) src
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      tgt.LATEST_ENTRIES trg
    WHERE
      trg.latest_entries_id = src.latest_entries_id
  );
  end transaction;