How Less Than Field Checks Work

Definition

Asserts that, on every row, the value of the selected field is less than the value of another field on the same row.

Overview

The Less Than Field rule compares two columns of the same row against each other. The selected Field is the left side, and the Field to compare is the right side; the row passes when the left value is less than the right one. Because the reference is a column rather than a constant, the rule keeps holding as the data moves, which is what makes it the right tool for relationships between two measures or two dates.

Typical use cases:

• Order two dates on the same row, such as a start date before an end date.
• Keep a consumed amount under the limit stored alongside it.
• Compare two timestamps with a tolerance for clock skew between systems.

Field Scope

Single: The rule evaluates one target field per check, compared against one other field on the same row.

General Properties

The filter allows you to define a subset of data upon which the rule will operate.

It requires a valid Spark SQL expression that determines the criteria rows in the DataFrame should meet. This means the expression specifies which rows the DataFrame should include based on those criteria. Since it's applied directly to the Spark DataFrame, traditional SQL constructs like WHERE clauses are not supported.

O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'

WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

(L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'

WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
) = 'URGENT'

EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{_qualytics_self}}
    WHERE O_TOTALPRICE > 1000
)

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM ORDERS
    WHERE O_TOTALPRICE > 1000
)

Specific Properties

Less Than Field has the following rule-specific properties:

Anomaly Types

Evaluation Flow

Every Less Than Field check follows the same four-step evaluation flow:

1. Apply the filter clause. If the check has a filter set, only rows matching the filter expression continue to the next step. Rows outside the filter are ignored and cannot contribute to the violation count.
2. Read both values. The platform reads the target field and the compared field from the same row.
3. Compare the two. The row passes when the target value is less than the compared value. When a comparator is configured for the type in play, its margin is applied before the comparison runs.
4. Apply coverage. At 100% coverage, any failing row causes the check to fail. Below 100% coverage, the check fails only when the passing fraction drops below the threshold (see Coverage and Tolerance).

Inclusivity

The Inclusive setting changes what happens when the two values are equal. With it on, the comparison is <= and equal values pass. With it off, the comparison is < and a row where both fields hold the same value is flagged. Decide this explicitly: rows where two dates or two amounts coincide are common in real data.

Comparators

The comparators give the comparison a margin, chosen by the type of the fields being compared:

• Numeric accepts an absolute margin or a percentage of the compared value.
• Datetime accepts a duration, which is what you want when two timestamps are written by different systems and never land on the same instant.

Leave them empty when the relationship is exact. Set a margin only for the noise your pipeline genuinely introduces, and keep it small enough that real defects still fail. Setting either comparator also tightens NULL handling, as described in NULL Handling.

NULL Handling

With no comparator configured, the check passes NULL values: a row where either side is NULL is not counted as a violation, because there is nothing to compare. That makes the rule quiet on incomplete rows, which is usually what you want, but it also means a column that is entirely NULL never fails.

Configuring a comparator changes this. On the comparator path a row passes only when both values are NULL, or both are present and satisfy the comparison, so a row with exactly one NULL is reported as a violation.

When both fields must also be present, pair the check with Not Null on each of them.

Both Fields Must Be Comparable

The target field must be a Date, Timestamp, Integral, or Fractional field, and the compared field must carry the same type as the target. A mismatched pair is rejected when the check is saved rather than failing at evaluation time, so normalize one of the columns upstream, or with a Computed Field, before creating the check.

The Filter Clause

The filter clause is a SQL WHERE expression applied before the evaluation. Filtered-out rows are ignored entirely (they cannot trigger a violation and are not counted in the totals).

When a filter is set, both the Record Anomaly and the Shape Anomaly messages end with [filter: <expression>] so the evaluated scope is visible in the alert.

Coverage and Tolerance

Coverage is a fractional value between 0 and 1 that defines the minimum fraction of evaluated rows that must pass:

• 1.0 (100%, default): every row in the filtered set must pass. Any failing row causes the check to fail. This is the strictest setting.
• < 1.0: the check tolerates a fraction of rows failing. The check fails only when the fraction of passing rows drops below the threshold.

Lower coverage values are useful when a small, known fraction of failures is expected. Use coverage carefully: a 0.5% tolerance can mask a real regression that happens to fall just under the threshold.

See Also