Historical Comparisons

Tests can be declared so the system evaluates them against the test variable values of previous order runs within the same order (i.e. the same kitchen-recipe-variation combination). These historical balance tests ensure that current data matches previous or expected values and can help alert to problems early.

Note

Historical tests only work when scheduled. Tests using historical comparisons only produce results from scheduled order runs within the same order. On-demand (run once) orders have just a single order run, so there is nothing within the order to compare against.

Declare a historical comparison test

1. Define a variable to capture a value for comparison over time across order runs.
2. Navigate to the Tests tab of the node editor.
   • From the Recipe page, select the Nodes tab, then select a node.
   • From a variation's graph, select a node. Click Edit Node.
3. Click Add Test.
4. Replace the default test name with a more descriptive title.
5. Set the Failure Action. This is typically Stop on failure.
6. In the Test Logic section, select Compare variable against metric.
7. Use the Test Variable field to select the variable defined in step 1, then select a type.
8. Select a Comparison operator.
9. In the Metric field, select Variable History.
10. Select either option from the Variable History drop-down list.
    • Value from previous order run compares the current value with the value of the same variable from the last order run of this order.
    • Running average from previous order runs compares the current value with an average value of the same variable across all previous order runs of this order.
11. When done, select Add notes for changelog , describe the changes, then click Update.

Examples

Below are two situations where historical comparison tests are used for DataOps success.

Product volume remains predictable

The following diagram shows historical balance testing that reveals a discrepancy.

• The tables show data from the same pipeline at different times.
• The tests calculate and compare sales volume in two product groups.
• Test results indicate that product group G2 decreased significantly.
• The expectation is that volume stays the same or increases over time.

A data engineer receives the test alert and reviews the results to find that the product P3 SKU was moved from group G2 to G1. They can then adjust the team's analytics and baselines accordingly.

Sales trend increasing

The following example test in an action node ensures that total orders always increase over the previous run.

actions/load-cleanse-raw.json (ABRIDGED)

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

        },
         "Append_New_Staged_Superstore": {
            "sql": "SET search_path TO {{schema_name}};

CREATE TABLE IF NOT EXISTS stage_demo_superstore_orders
(
\trow_id integer not null ,
\torder_id varchar(100),
\torder_date date encode ,
\tship_date date encode ,
\tship_mode varchar(100),
\tcustomer_id varchar(100),
\tcustomer_name varchar(100) ,
\tsegment varchar(100),
\tcity varchar(100) ,
\tstate varchar(100),
\tcountry varchar(100) ,
\tpostal_code char(5),
\tmarket varchar(100),
\tregion varchar(100) ,
\tproduct_id varchar(100),
\tcategory varchar(100),
\tsub_category varchar(100) ,
\tproduct_name varchar(1000),
\tsales numeric(14,3),
\tquantity integer ,
\tdiscount numeric(14,3) ,
\tprofit numeric(14,3),
\tshipping_cost numeric(14,3) ,
\torder_priority varchar(100)
)
;

INSERT INTO stage_demo_superstore_orders
\tSELECT * FROM new_stage_demo_superstore_orders;

DROP TABLE IF EXISTS demo_superstore_sales_control;

CREATE TABLE demo_superstore_sales_control AS
\tSELECT sum(sales) AS total_sales FROM stage_demo_superstore_orders ;

SELECT COUNT(*) FROM stage_demo_superstore_orders;",
            "query-type": "execute_scalar",
            "set-runtime-vars": {
                "result": "total_staged_orders"
            }
        },
        . . .

        }
    },
    "tests": {
        . . .

        },
        "Ensure_Total_Staged_Orders_Always_Increasing": {
            "action": "stop-on-error",
            "test-variable": "total_staged_orders",
            "test-logic": {
                "test-compare": "greater-than-equal-to",
                "test-metric": {
                    "historic-calculation": "previous-value",
                    "historic-metric": "total_staged_orders"
                }
            },
            "keep-history": true
        },
        . . .

        }
    }
}