OPT_USE_TIMESLOT_LOGIC

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

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:

Snowflake:

SQL

SELECT 
	TO_CHAR(MIN(src_entity.event_timestamp)::TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS timeslot_start
	, TO_CHAR(dateadd(second, 1, MAX(src_entity.event_timestamp)), 'YYYY-MM-DD HH24:MI:SS') AS timeslot_end
FROM
	stage.stg_taxi_zone_lookup src_entity
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
  );

Amazon Redshift:

SQL

SELECT 
	TO_CHAR(MIN(src_entity.event_timestamp), 'YYYY-MM-DD HH24:MI:SS') AS timeslot_start
	, TO_CHAR(MAX(src_entity.event_timestamp) + INTERVAL '1' SECOND, 'YYYY-MM-DD HH24:MI:SS') AS timeslot_end
FROM
	stage.stg_taxi_zone_lookup src_entity
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
  );

Azure SQL Database, Azure Synapse SQL and Azure Synapse DW:

SQL

SELECT 
	FORMAT(MIN(src_entity.[event_timestamp]),'yyyy-MM-dd HH:mm:ss') AS timeslot_start
	, FORMAT(DATEADD(second,1, MAX(src_entity.[event_timestamp])), 'yyyy-MM-dd HH:mm:ss') AS timeslot_end
FROM
	[stage].[stg_taxi_zone_lookup] src_entity
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]
  );

Google BigQuery:

SQL

SELECT 
	FORMAT_DATE(\"%F %T\", MIN(src_entity.event_timestamp)) AS timeslot_start
	, FORMAT_DATE(\"%F %T\", DATETIME_ADD(MAX(src_entity.event_timestamp), interval 1 second)) AS timeslot_end
FROM
	stage.stg_taxi_zone_lookup src_entity
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

Databricks SQL:

SQL

SELECT 
	DATE_FORMAT(MIN(src_entity.event_timestamp)::TIMESTAMP, 'yyyy-MM-dd HH:mm:ss') AS timeslot_start
	, DATE_FORMAT(dateadd(second, 1, MAX(src_entity.event_timestamp)), 'yyyy-MM-dd HH:mm:ss') AS timeslot_end
FROM
	stage.stg_taxi_zone_lookup src_entity
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
  );