SMOKE_GREY

SMOKE_GREY load steps can be used to execute data smoke tests as part of load workflows. SMOKE_GREY test results are logged only, i.e. load workflow executions will continue regardless of SMOKE_GREY results.

See also:

Usage

OPTION

SMOKE_GREY: sql_code

Notes

A SMOKE_GREY test fails if the test SQL returns any rows.

SMOKE_GREY steps can be used alone or together with other load steps. They can also be used with automatically generated loads.

SMOKE_GREY steps can be saved as part of a load template.

SMOKE_GREY steps can use variables inside the SQL code.

Examples

Test to check whether duplicates exist on newly loaded rows

The following SQL uses variables, so the test can also be templated and used in similar tables without further modification. The SQL clause checks whether there are duplicates on newly loaded rows, but does not stop the load if duplicates are found. Row counts are logged into Logger component.

OPTION

SMOKE_GREY:

-- Duplicates Smoke Test based on the newly loaded rows
SELECT 
           COUNT(*) AS has_duplicates
FROM (
           SELECT
                       DV_ID, 
                       COUNT(1) AS cnt_rows
           FROM
                       <target_schema>.<target_entity_name>
           WHERE
                       DV_ID IN (SELECT DV_ID FROM <target_schema>.<target_entity_name> WHERE dv_run_id IN (<targetbatchid>))
           GROUP BY
                       DV_ID
           HAVING
                       COUNT(1) > 1
) t1
HAVING
           COUNT(*) > 0

Agile Data Engine generated test SQL for S_RATECODE entity in DV -schema for 123456 batch.

SQL

-- Duplicates Smoke Test based on the newly loaded rows
SELECT 
           COUNT(*) AS has_duplicates
FROM (
           SELECT
                       DV_ID, 
                       COUNT(1) AS cnt_rows
           FROM
                       DV.S_RATECODE
           WHERE
                       DV_ID IN (SELECT DV_ID FROM DV.S_RATECODE WHERE dv_run_id IN (123456))
           GROUP BY
                       DV_ID
           HAVING
                       COUNT(1) > 1
) t1
HAVING
           COUNT(*) > 0