OPT_USE_TIMESLOT_LOGIC

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

OPT_USE_TIMESLOT_LOGIC is a load option that enables timeslot based helping condition for event data loading.

Requirements:

• Target entity contains non-updating timestamp attribute with technical attribute type TIMESLOT.

• TIMESLOT attribute mapped to a source entity timestamp attribute.

• Optionally Run ID logic to be in use to get most out of the feature.

See also:

• Load options

• OPT_USE_RUN_IDS

Usage

OPT_USE_TIMESLOT_LOGIC: Boolean

Default: false

Notes

OPT_USE_TIMESLOT_LOGIC can be used in table loads that have a single entity mapping (i.e. one source entity) and attributes with following attribute types defined:

• TIMESLOT

• DV_RUN_ID

• either DV_HASHKEY or DV_REFERENCING_HASHKEY

Examples

Insert select with TIMESLOT logic

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:

OPT_USE_TIMESLOT_LOGIC: true
and 
OPT_USE_RUN_IDS: true

Resulting DML generated by Agile Data Engine:

-- Amazon Redshift syntax
SELECT 
	TO_CHAR(MIN(source.event_timestamp), 'YYYY-MM-DD HH24:MI:SS') AS timeslot_start
	, TO_CHAR(MAX(source.event_timestamp) + INTERVAL '1' SECOND, 'YYYY-MM-DD HH24:MI:SS') AS timeslot_end
FROM
	stage.stg_taxi_zone_lookup
WHERE
	run_id IN (...)
;

-- 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.event_time >= '<timeslot_start>'
		AND 
		trg.event_time <= '<timeslot_end>'
        AND
        trg.latest_entries_id = src.latest_entries_id
  );