Expectations: now with custom SQL and dynamic parameters

Expectations were already the most expressive way to build your data tests, but they just got even better. Here are two major upgrades to your Expectation experience:

• Dynamically-defined test parameters

• A custom SQL option

Dynamic parameters: define tests relative to historical results

Lots of GX Cloud users have asked for the ability to set their Expectation parameters based on historical results. We’re pleased to announce that this is now available!

How it works

When you create a statistics- or count-based Expectation, you now have the option to define its parameters dynamically.

Get the complete list of which Expectations support dynamic parameters.

Currently, the dynamic parameter of a count (or statistic) Expectation is defined relative to the mean of that count (or statistic) from the column’s previous runs. The number of prior runs included is defined by you.

Here are a couple of examples.

Example #1

Table row count should be within 5% of the average row count over the last 7 runs. 

Or, expressed another way: the table row count should be greater than or equal to 95%, and less than or equal to 105%, of the average of the last 7 row counts.

Example #2

Column sum should be at least equal to and no more than 110% of the previous run’s column sum.

Or: the column sum should be greater than or equal to and less than or equal to 110% of the previous column sum.

As with the fixed range parameters, excluding your range’s boundaries from the passing values— in essence, using ‘less than’ and ‘greater than’ instead of ‘less than or equal to’ and ‘greater than or equal to’—is as simple as selecting the “Strict” checkbox!

Find out all the details about how dynamic parameters work in GX Cloud in our documentation.

Custom SQL Expectations

The Expectations in GX Cloud can do a lot, but we heard your requests for even more customizability—especially for cross-column and cross-table testing.

That’s why you can now specify a custom Expectation using SQL! 

How it works

In a custom SQL Expectation, you provide a SQL query that describes problematic rows. A sample query simplifies your query construction process.

The

query represents however you defined your Data Asset, whether that’s as a complete Asset (always validating all rows) or using batches (validating rows based on a datetime parameter). GX Cloud will automatically replace

{batch}

with the appropriate SQL structure for your Data Asset’s definition. 

Using

is recommended. However, you can also define your query directly if you want. One scenario in which you might want to do this is defining an Expectation to validate the complete Asset in a Data Asset that otherwise uses batches.

If a custom SQL query returns any rows, the Expectation fails.

Read the custom SQL Expectation docs.

---

To try out dynamic parameters and custom SQL Expectations, log into your GX Cloud account! If you don't already have one, signing up for GX Cloud is free, and it's quick and easy to get started.