load_start_timemillis

Resolved as the load start Unix timestamp in milliseconds in load execution.

See also:

Usage

VARIABLE

<load_start_timemillis>

Notes

The resolved timestamp is the same for all load steps of a load execution. Therefore, load_start_timemillis can be used to identify rows inserted within the current load execution (compare to targetrunid when using Run ID Logic). This can be useful e.g. with smoke tests, see example below.

Supported in all loads.

Examples

Using load_start_timemillis in a duplicate smoke test

Duplicate tests (SMOKE_GREY, SMOKE_BLACK) are often defined simply as grouping a table by the key attribute and checking if there are more than one rows per key:

SQL

SELECT 1
FROM <target_schema>.<target_entity_name>
GROUP BY id
HAVING COUNT(1) > 1;

This can work fine as long as the row count of the table is not too big.

With the load_start_timemillis variable, the duplicate test can be designed more efficiently as follows:

SQL

-- EXISTS operator will stop executing the subquery when the first entry is found from either of the UNION ALL legs
SELECT EXISTS (
    -- Check first for duplicates within the latest batch that was loaded
    SELECT 1
    FROM <target_schema>.<target_entity_name> AS t0
    WHERE t0.load_start_timemillis = <load_start_timemillis>
    GROUP BY t0.id
    HAVING COUNT(1) > 1
    UNION ALL
    -- Check for duplicates for the ids loaded in the latest batch vs. existing rows in the table
    SELECT 1
    FROM <target_schema>.<target_entity_name> AS t1
    JOIN <target_schema>.<target_entity_name> AS t2 ON (t1.id = t2.id)
    WHERE t1.load_start_timemillis = <load_start_timemillis> AND t2.load_start_timemillis <> <load_start_timemillis>
) AS duplicates_do_exist
-- If duplicates are found, then pass the info to the wrapping of the results i.e. cntquery
HAVING duplicates_do_exist = TRUE

Note that load_start_timemillis must be inserted into the target entity in the same load.

This logic works well for incremental loads, reducing the amount of scanning the target database has to do to only the rows related to the latest loaded batch.

If you are using Run ID Logic, you can use the run id attributes and variables instead, see example here.