SMOKE_BLACK
SMOKE_BLACK load steps can be used to execute data smoke tests as part of load workflows. A SMOKE_BLACK test result will be logged, and a failure will also fail the workflow and stop any subsequent, dependent loads from executing to prevent errors from spreading.
See also:
Usage
SMOKE_BLACK: sql_code
Notes
A SMOKE_BLACK test fails if the test SQL returns any rows.
SMOKE_BLACK steps can be used alone or together with other load steps. They can also be used with automatically generated loads.
SMOKE_BLACK steps can be saved as part of a load template.
SMOKE_BLACK 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 tables without further modification. The SQL clause checks whether there are duplicates in the target table and stops the data load in case rows are found.
SMOKE_BLACK:
-- Duplicates Smoke Test
SELECT
COUNT(*) AS has_duplicates
FROM (
SELECT
DV_ID,
COUNT(1) AS cnt_rows
FROM
<target_schema>.<target_entity_name>
GROUP BY
DV_ID
HAVING
COUNT(1) > 1
) t1
HAVING
COUNT(*) > 0
Agile Data Engine executing the test for S_RATECODE entity in DV schema:
-- Duplicates Smoke Test
SELECT
COUNT(*) AS has_duplicates
FROM (
SELECT
DV_ID,
COUNT(1) AS cnt_rows
FROM
DV.S_RATECODE
GROUP BY
DV_ID
HAVING
COUNT(1) > 1
) t1
HAVING
COUNT(*) > 0