How Data Diff Checks Work

Definition

Asserts that two datasets match on a chosen set of fields. The check compares a target container (the dataset the check is attached to) against a reference container in another datastore, and reports every row that is added, removed, or changed between the two sides.

Overview

Data Diff is a two-table comparison rule. Use it whenever you need to confirm that one dataset is an exact copy, or a controlled copy, of another:

• Validating that a replica, backup, or warehouse mirror matches its source of truth.
• Comparing pre- and post-migration data after a system move.
• Verifying that a derived table, such as an aggregate, snapshot, or reporting view, still agrees with the upstream system.
• Confirming that an exported file delivered to a partner contains the same rows as the system of record.

Beyond the standard properties, a Data Diff check has three configuration inputs: the reference datastore and container to compare against, an optional list of Row Identifiers (the key the platform uses to match each target row to its reference row), and an optional set of Comparators (per-field tolerances for numeric, duration, and string fields).

When Row Identifiers are set, anomalies appear in the Comparison Source Records view, which shows each differing row side by side as Left (target) vs Right (reference) values.

Field Scope

Multiple: The check evaluates one or more fields by comparing them between target and reference.

Accepted Types

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
)

Anomaly Types

How the Check Evaluates the Two Datasets

Every Data Diff check follows the same five-step evaluation flow:

1. Apply the filter clauses. If the check has a filter set, only the rows in the target container that match it continue to the next step. The reference side has its own Filter Clause (properties.ref_filter); when it is set, only the reference rows matching it are read.
2. Read the reference container. The platform reads the comparison fields from the reference table or file in the configured reference datastore, scoped by ref_filter when one is set.
3. Match rows. With Row Identifiers configured, rows are matched by the combination of identifier values: target rows whose identifier values match a reference row are paired up. Without Row Identifiers, the platform performs a symmetrical set difference on the full set of compared fields.
4. Compare fields on each matched pair. For every paired row, each listed field is compared between left (target) and right (reference). Comparators (numeric, duration, string) define the tolerance for the comparison; without a Comparator the comparison is strict equality.
5. Emit a single Shape Anomaly summarizing the diffs. Every row that differs (added, removed, or changed) becomes part of the anomaly's source records. The dataset-level violation is summarized in a single Shape Anomaly message and the per-row detail is surfaced through the Comparison Source Records view.

The order matters: the filter is applied before matching, so rows the filter excludes on the target side cannot pair with any reference row and cannot contribute to the anomaly.

The Three Diff Statuses

Each differing row carries one of three status values. The status is what drives the Comparison Source Records UI and is exposed in the anomaly payload.

The statuses are named from the target's point of view: the target is the dataset under check, so a row only it has is one it added, and a row only the reference has is one it removed.

Without Row Identifiers, only added and removed are produced, because the platform has no key to match rows on. A row that differs in even one field becomes one added (the row from target) and one removed (the row from reference), in a pure symmetrical set difference.

Restricting Anomalies by Status

The diff_change_types property restricts which of the three diff statuses are allowed to produce an anomaly. By default, every differing row (added, removed, or changed) contributes to the anomaly count. Setting diff_change_types to a subset of statuses keeps the comparison running over all rows but only flags the diffs whose status appears in the list.

Typical use cases:

• Reference is intentionally a superset of the target. The target is a production table whose reference (a staging or canonical source) carries extra QA or onboarding rows that production is not expected to receive. Those extra reference rows arrive as removed, so set diff_change_types to ["added", "changed"] to suppress them.
• Reference is intentionally a subset of the target. A reporting view may exclude soft-deleted or out-of-scope rows. The extra target rows arrive as added, so set diff_change_types to ["removed", "changed"] to suppress them.
• Only the per-field diff matters. When row presence on both sides is guaranteed by an upstream contract and only value drift is interesting, set diff_change_types to ["changed"].

The property is dataDiff-only. Sending it on an isReplicaOf check is rejected at the API with HTTP 422. The property is editable through PUT /api/quality-checks/{id}, so the subset can be tuned without recreating the check.

Row Identifiers and Passthrough Fields

Two optional properties shape what the Comparison Source Records view looks like:

Row Identifiers

A list of fields that form the compound key the platform uses to pair target and reference rows. The identifier tuple must exist on both sides. Typical choices are primary keys (customer_id, order_id) or a composite of business-key columns (order_id, line_number).

When Row Identifiers are set:

• Matched rows produce a changed diff if any listed field differs.
• Unmatched target rows produce added.
• Unmatched reference rows produce removed.

Passthrough Fields

Extra fields the platform should carry into the source-records output for context, even though they are not part of the comparison. Passthrough Fields appear in the Comparison Source Records view alongside the diffed fields but are never themselves a reason for the anomaly to fire. Typical use: showing customer_name or created_at next to the differing column so anomaly triagers can identify the row without leaving the page.

The Filter Clauses

Data Diff has two independent filter clauses, both optional and both SQL WHERE expressions the platform evaluates with its own SQL syntax, which stays the same whatever the underlying datastore is:

• The check's Filter Clause scopes the target container before matching.
• The Filter Clause in the Right Reference panel (properties.ref_filter) scopes the reference container.

A side whose clause is left empty is read in full.

Filtering the target side serves two purposes:

1. Scoping the comparison. Restrict the comparison to a subset of target rows (status = 'active', event_date = current_date(), tenant_id = 42). Rows outside the scope cannot be reported as added and cannot pair with reference rows.
2. Avoiding noise from known divergences. Filter out rows that are intentionally allowed to differ between target and reference (for example, a staging_only = true flag), so the check focuses on the rows that must match.

Both clauses are part of the check definition. Unlike the row-level rule types, Data Diff does not echo them in the anomaly message, so read the check itself to see which slice of each side was evaluated.

Comparators

Comparators apply a per-field tolerance to the equality check between left and right values. Without a Comparator, the platform compares values strictly: 1.00 and 1.000001 differ, "Australia" and "australia" differ.

The Comparators allow you to set margins of error, accommodating slight variations in data validation. This flexibility is crucial for maintaining data integrity, especially when working with different data types such as numeric values, durations, and strings. Here's an overview of how each type of comparator can be beneficial for you:

Numeric

Numeric comparators enable you to compare numbers with a specified margin, which can be a fixed absolute value or a percentage. This allows for minor numerical differences that are often acceptable in real-world data.

Comparison Type

• Absolute Value: Uses a fixed threshold for determining equality. It's ideal when you need consistent precision across measurements.
• Percentage Value: Uses a percentage of the original value as the threshold for equality comparisons. It's suitable for floating point numbers where precision varies.

Threshold

The threshold is the value you set to define the margin of error:

• When using Absolute Value, the threshold represents the maximum allowable difference between two values for them to be considered equal.
• For Percentage Value, the threshold is the percentage that describes how much a value can deviate from a reference value and still be considered equal.

Duration

Duration comparators support time-based comparisons, allowing for flexibility in how duration differences are managed. This flexibility is crucial for datasets where time measurements are essential but can vary slightly.

Unit

The unit of time you select determines how granular the comparison is:

• Millis: Measures time in milliseconds, ideal for high-precision needs.
• Seconds: Suitable for most general purposes where precision is important but doesn't need to be to the millisecond.
• Days: Best for longer durations.

Value

Value sets the maximum acceptable difference in time to consider two values as equal. It serves to define the margin of error, accommodating small discrepancies that naturally occur over time.

String

String comparators facilitate comparisons of textual data by allowing variations in spacing. This capability is essential for ensuring data consistency, particularly where minor text inconsistencies may occur.

Ignore Whitespace

When enabled, this setting allows the comparator to ignore differences in whitespace. This means sequences of whitespace are collapsed into a single space, and any leading or trailing spaces are removed. This can be particularly useful in environments where data entry may vary in formatting but where those differences are not relevant to the data's integrity.

See Also