OPT_INSERT_UPDATE_BY_ID_AS_MERGE

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

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

Azure SQL Database does not allow usage of load option OPT_ORDER_BY with MERGE

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

See also:

Load options

Usage

OPTION

OPT_INSERT_UPDATE_BY_ID_AS_MERGE: Boolean

Default: false

Notes

OPT_INSERT_UPDATE_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 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_BY_ID_AS_MERGE: true

Resulting DML generated by Agile Data Engine:

Snowflake, Google BigQuery and Databricks SQL:

CODE

MERGE INTO tgt.LATEST_ENTRIES trg USING (
    SELECT DISTINCT
        <key formula> AS latest_entries_id,
        ...
    FROM
        stage.STG_TAXI_ZONE_LOOKUP src_entity
) src
ON (
    trg.latest_entries_id = src.latest_entries_id
)
WHEN MATCHED
    THEN UPDATE SET
        ... = src....
WHEN NOT MATCHED
    THEN INSERT (
        ...
    )
    VALUES (
        src....
    )
;

Amazon Redshift:

SQL

begin transaction;

-- Update existing target records with source data based on key
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 new entries from source to target  
INSERT INTO
  tgt.LATEST_ENTRIES (
    ...
  )
SELECT
  ...
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;

Azure Sql Database and Azure Synapse SQL:

CODE

MERGE INTO [tgt].[LATEST_ENTRIES] trg USING (
    SELECT DISTINCT
        <key formula> AS latest_entries_id,
        ...
    FROM
        [stage].[STG_TAXI_ZONE_LOOKUP] src_entity
) src
ON (
    trg.[latest_entries_id] = src.[latest_entries_id]
)
WHEN MATCHED
    THEN UPDATE SET
        ... = src....
WHEN NOT MATCHED
    THEN INSERT (
        ...
    )
    VALUES (
        src....
    )
;