OPT_SKIP_DUPLICATE_PREVENTION

OPT_SKIP_DUPLICATE_PREVENTION is a load option that if set to true will skip duplicate prevention in automatically generated loads.

See also:

Usage

OPTION

OPT_SKIP_DUPLICATE_PREVENTION: Boolean

Default: false

Notes

OPT_SKIP_DUPLICATE_PREVENTION can be used in loads that have a single entity mapping (i.e. one source entity) and no OVERRIDE load steps. OPT_SKIP_DUPLICATE_PREVENTION will not have an effect if the load is overridden with an OVERRIDE load step.

Examples

Hub load

OPT_SKIP_DUPLICATE_PREVENTION: false

In the default situation OPT_SKIP_DUPLICATE_PREVENTION is set to false (or not set at all) a hub load will filter out entries already existing in the hub with the WHERE NOT EXISTS clause and insert DISTINCT values into the hub.

DML generated by Agile Data Engine:

SQL

INSERT INTO
  dv.H_TRIP (
    ...
  )
SELECT
  DISTINCT ...
FROM
  (
    SELECT
      ...
    FROM
      stage.STG_TRIPDATA_YELLOW src_entity
  ) src
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      dv.H_TRIP trg
    WHERE
      trg.dv_id = src.dv_id
  );

OPT_SKIP_DUPLICATE_PREVENTION: true

With OPT_SKIP_DUPLICATE_PREVENTION set to true the WHERE NOT EXISTS clause and DISTINCT are dropped from the generated DML:

SQL

INSERT INTO
  dv.H_TRIP (
    ...
  )
SELECT
  ...
FROM
  (
    SELECT
      ...
    FROM
      stage.STG_TRIPDATA_YELLOW src_entity
  ) src;