Recency Test Example

• Use case: Verify that a vendor file does not contain stale data.
• Example recipe: the recipe uses an action node to load a global superstore data set to a Redshift instance. A recency test has been written to ensure that data set is last week's data.
• Node type: action node.

Step 1: Define runtime variable

When configuring steps in the action node, a SQL script produces a runtime variable for the age of the data set.

Visual example in Automation

A scalar output from a SQL script records the age of the data set.

A SQL script calculates the difference in weeks between current data and the latest order. The value resulting from the SQL execution is assigned to a runtime variable. A value of -1 indicates the data is current. Older data would result in less than -1.

Summary of how-to

1. Open the node editor.
2. From the Steps tab, use the radio button to select SQL Inline Query, then enter the SQL script.
3. Define the Scalar Result.
4. Select Add notes for changelog , describe the changes, then click Update.

Step 2: Configure test

Write a recency test for thc node, confirming the data timeframe.

Visual example in Automation

The test verifies that the data set is not more than one week old. The test uses the variable declared previously in the action node and compares it to the control value -1. If the data is more than a week old, the variable value will be less than -1 and the test will fail because the operator is set to =.

Summary of how-to

1. In the same node, select the Tests tab.

2. Create a recency test.

   1. Click Add Test.

   2. Fill out the fields Test Name, Failure Action, Test Logic, and Control Value.

      Where Test Logic is Compare variable against metric and the Test Variable is the runtime variable scalar result from Step 1.

3. Select Add notes for changelog , describe the changes, then click Update.

File contents

The system records the test configurations shown above in the actions/load-and-cleanse-raw.json file (named after the action node name).

{
    "name": "load-and-cleanse-raw",
    "type": "DKDataSource_PostgreSQL",
    "config-ref": "redshiftConfig",
    "keys": {
        . . .

        },
        "Compute_Latest_Order": {
            "description": "Compute and store the difference in weeks between current system date and latest sales order date.",
            "sql": "SET search_path TO {{schema_name}};\n\nCREATE TABLE new_raw_demo_superstore_orders\n(\n\trow_id integer not null ,\n\torder_id varchar(100),\n\torder_date date {% if not Use_Azure %}encode{% endif %},\n\tship_date date {% if not Use_Azure %}encode{% endif %},\n\tship_mode varchar(100),\n\tcustomer_id varchar(100),\n\tcustomer_name varchar(100) ,\n\tsegment varchar(100),\n\tcity varchar(100) ,\n\tstate varchar(100),\n\tcountry varchar(100) ,\n\tpostal_code char(5),\n\tmarket varchar(100),\n\tregion varchar(100) ,\n\tproduct_id varchar(100),\n\tcategory varchar(100),\n\tsub_category varchar(100) ,\n\tproduct_name varchar(1000),\n\tsales numeric(14,3),\n\tquantity integer ,\n\tdiscount numeric(14,3) ,\n\tprofit numeric(14,3),\n\tshipping_cost numeric(14,3) ,\n\torder_priority varchar(100)\n);\n\nSELECT datediff(week,sysdate,max(order_date)) as new_raw_superstore_orders;\n\n\n",
            "query-type": "execute_scalar",
            "set-runtime-vars": {
                "result": "data_latest_week"
            }
        },
        . . .
        }
    },
    "tests": {
        "Ensure_Latest_Week": {
            "action": "warning",
            "test-variable": "data_latest_week",
            "test-logic": {
                "test-compare": "equal-to",
                "test-metric": -1
            }
        },
        . . .
        }
    }
}