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 target table

The following SQL uses variables, so the test can also be templated and used in similar entities without further modification (other than possibly the group by key). The SQL clause checks whether there are duplicates on newly loaded rows, but does not stop the load if duplicates are found.

SMOKE_GREY step defined in a load:

SQL

-- Duplicates Smoke Test
SELECT 1
FROM <target_schema>.<target_entity_name>
GROUP BY dv_id
HAVING COUNT(1) > 1;

Test executed in the target database for example entity DV.S_RATECODE:

SQL

SELECT COUNT(*) AS cntresult FROM (
  -- Duplicates Smoke Test
  SELECT 1
  FROM DV.S_RATECODE
  GROUP BY dv_id
  HAVING COUNT(1) > 1;
) AS cntquery

Note that Agile Data Engine wraps smoke tests in an additional count query during execution to avoid large result sets.

There is also a more efficient way to test for duplicates in large entities. If you are using Run ID Logic see this example, and if not, see this example.