Feature: Support CTE on all metrics checks

[paulhudson]

Goal

Support CTE metrics on all checks.

This would allow great flexibility around what data types could be parsed to any check.
I wouldn't have to model my data to run assertions.
Nor, write user defined SQL checks from scratch.

Reproduce

checks for customer:
  - freshness(foo) < 1m:
      foo expression: from_iso8601_timestamp(time)

Here the time field is in fact a string type containing a date string.
The from_iso8601_timestamp() presto function parses a string into a timestamp which I could then evaluate with freshness.

Result

Soda Core 3.0.4
In configuration "foo expression" the metric name must match exactly the metric name in the check "freshness"

In fact the message tells half the story, the missing detail is that CTE is not supported on freshness checks.

Expected Result

The metric would be evaluated.

This isn't limited to freshness, I can well imagine I might want to assert some derived table belongs to a set:

checks for customer_a_fleet:
  - row_count same as fleet_auth_vehicles:
    fleet_auth_vehicles query: SELECT vehicle_id FROM vehicles WHERE tenant_id = customerA AND authenticated = 1

Here I'd be answering a critical security demand, that some part of my system is not producing data outside of its scoped tenant and that these entities have authenticated.

Perhaps I could achieve these with user defined checks, but;

• what if I'm lazy
• or want another lazy or semi-technical person to understand the checks intent

In both example cases, I am able to satisfy esoteric data issues and business cases. While still enjoying Soda's simplicity and expressive config.

So I'm suggesting:

Given I create a CTE metric
When I pass that metric to any check
Then that metric should be evaluated

[JCZuurmond]

@PaulPreijer and I are also interested in this feature!