Skip to main content
Skip table of contents

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:


Usage

OPTION
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:

OPTION
OPT_USE_TIMESLOT_LOGIC: true
and 
OPT_USE_RUN_IDS: true

Resulting DML generated by Agile Data Engine:

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

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

If this problem persists, please contact our support.