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
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
);