# Data Diff Check Examples

Source: https://userguide.qualytics.io/data-quality-checks/data-diff/deep-dive/examples/

Three real-world scenarios that show how the Data Diff check is typically used in production: validating a nightly backup, comparing a system-to-system data transfer, and verifying a post-migration mirror with a scoped filter that suppresses unmatched reference rows.

In every example, the **Sample Data** table is a simplified text version of the anomaly's source records: each row has a status, an identifier, and a `Left` (target) → `Right` (reference) pair for every compared field, with *missing* marking a side that has no row. The app lays the same rows out differently: a **Found in** column with a marker per side, a **Status** icon, and single values for added and removed rows. Only the fields a changed row disagrees on are split into left and right halves. See [Comparison Source Records](https://userguide.qualytics.io/anomalies/deep-dive/anomaly-details/source-record/#comparison-source-records) for the app view.

**Tab: Backup Validation**

**The situation:** Your `ORDERS` table is the system of record for an e-commerce site. Every night at midnight a backup job replicates it to `ORDERS_BACKUP` in the warehouse datastore. The Sales team builds the morning report from `ORDERS_BACKUP`, so any row missing from the backup silently understates revenue. A Data Diff check validates that the backup is complete every morning.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Data Diff |
| Fields | `order_id`, `customer_id`, `amount`, `order_date` |
| Row Identifiers | `order_id` |
| Reference Datastore | `Warehouse` |
| Reference Container | `ORDERS_BACKUP` |
| Filter | *(none)* |
| Comparators | *(none)* |
| Status | Active |
| Tags | `critical`, `backup` |
| Additional Metadata | `jira: DATA-3201` |
| Description | ORDERS must match ORDERS_BACKUP every morning |

**Payload**

```json
{
    "description": "ORDERS must match ORDERS_BACKUP every morning",
    "rule": "dataDiff",
    "fields": ["order_id", "customer_id", "amount", "order_date"],
    "container_id": 145,
    "filter": null,
    "properties": {
        "ref_datastore_id": 22,
        "ref_container_id": 803,
        "id_field_names": ["order_id"]
    },
    "tags": ["critical", "backup"],
    "additional_metadata": {"jira": "DATA-3201"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}
```

**Sample Data** (simplified)

| Row Status | order_id | customer_id (Left → Right) | amount (Left → Right) | order_date (Left → Right) |
| --- | --- | --- | --- | --- |
| added | 10248 | 4451 → missing | 19.00 → missing | 2025-01-15 → missing |
| added | 10249 | 4452 → missing | 12.00 → missing | 2025-01-15 → missing |
| added | 10250 | 4453 → missing | 29.00 → missing | 2025-01-15 → missing |

**What gets flagged**

Three orders that exist on the target side (`ORDERS`) have no matching `order_id` on the reference side (`ORDERS_BACKUP`). A row only the target has is one the target *added*, so each is reported with status `added` and has no values on the reference side. The backup job failed to copy these rows into `ORDERS_BACKUP`.

> **Example: Shape Anomaly**
>
> 3 records differ between ORDERS (Warehouse) and ORDERS_BACKUP (Warehouse)

**Flowchart**

```mermaid
graph TD
    A["No filter, evaluate all target rows"] --> B["Read reference container ORDERS_BACKUP"]
    B --> C["Match rows by order_id"]
    C --> D{"Any target row<br/>without a reference match?"}
    D -->|No| E["All target rows pass"]
    D -->|Yes| F["Flag each unmatched row as 'added'.<br/>3 orders missing from backup."]
```

**Equivalent SQL**

```sql
-- Rows the Data Diff check would flag as 'added' for the ORDERS backup.
SELECT t.order_id, t.customer_id, t.amount, t.order_date
FROM orders t
LEFT JOIN orders_backup r ON t.order_id = r.order_id
WHERE r.order_id IS NULL
ORDER BY t.order_id;
```

**Tab: System-to-System Transfer**

**The situation:** A clinic's scheduling system writes appointments to a SQL Server `APPOINTMENTS` table, and an hourly ETL copies them into the billing system's `APPOINTMENTS_BILLING` table. Patient name and doctor are reliably copied, but the insurance plan code is rewritten in transit by a translation layer. A Data Diff check confirms that for each `appointment_id`, `patient_name`, `doctor`, and `insurance_plan` agree on both sides, and surfaces any field that the transfer mangled.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Data Diff |
| Fields | `patient_name`, `doctor`, `insurance_plan` |
| Row Identifiers | `appointment_id` |
| Reference Datastore | `Billing` |
| Reference Container | `APPOINTMENTS_BILLING` |
| Filter | *(none)* |
| Comparators | *(none)* |
| Status | Active |
| Tags | `clinical`, `billing` |
| Additional Metadata | `jira: DATA-4101` |
| Description | Appointments must match between scheduling and billing |

**Payload**

```json
{
    "description": "Appointments must match between scheduling and billing",
    "rule": "dataDiff",
    "fields": ["patient_name", "doctor", "insurance_plan"],
    "container_id": 212,
    "filter": null,
    "properties": {
        "ref_datastore_id": 31,
        "ref_container_id": 912,
        "id_field_names": ["appointment_id"]
    },
    "tags": ["clinical", "billing"],
    "additional_metadata": {"jira": "DATA-4101"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 18
}
```

**Sample Data** (simplified)

| Row Status | appointment_id | patient_name (Left → Right) | doctor (Left → Right) | insurance_plan (Left → Right) |
| --- | --- | --- | --- | --- |
| changed | A-7781 | Robert Martinez → Robert Martinez | Dr. Smith → Dr. Smith | BlueCross Plan A → BlueCross Plan B |

**What gets flagged**

The pair `(A-7781)` exists on both sides, so the row's status is `changed` rather than `added` or `removed`. Only `insurance_plan` differs, so it is the only field in dispute. `patient_name` and `doctor` agree.

> **Example: Shape Anomaly**
>
> 1 records differ between APPOINTMENTS (Scheduling) and APPOINTMENTS_BILLING (Billing)

**Flowchart**

```mermaid
graph TD
    A["No filter, evaluate all target rows"] --> B["Read reference container APPOINTMENTS_BILLING"]
    B --> C["Match rows by appointment_id"]
    C --> D{"Do all compared fields<br/>match on each pair?"}
    D -->|Yes| E["Row passes"]
    D -->|No| F["Flag the row as 'changed'.<br/>insurance_plan differs for A-7781."]
```

**Equivalent SQL**

```sql
-- Rows the Data Diff check would flag as 'changed' for appointments.
SELECT
    t.appointment_id,
    t.patient_name AS left_patient_name,  r.patient_name AS right_patient_name,
    t.doctor         AS left_doctor,         r.doctor         AS right_doctor,
    t.insurance_plan AS left_insurance_plan, r.insurance_plan AS right_insurance_plan
FROM appointments t
INNER JOIN appointments_billing r ON t.appointment_id = r.appointment_id
WHERE
       t.patient_name   <> r.patient_name
    OR t.doctor         <> r.doctor
    OR t.insurance_plan <> r.insurance_plan
ORDER BY t.appointment_id;
```

**Tab: Scoped Post-Migration Mirror**

**The situation:** The Customers domain is being migrated from a legacy CRM to a new system. The legacy `CUSTOMERS_LEGACY` table is being phased out, and the new `CUSTOMERS_NEW` table is the system of record. For the cutover window, both systems are written to, and the migration team needs daily confirmation that today's writes are identical on both sides. Yesterday's rows are intentionally allowed to differ (the legacy system kept being patched), so the check is scoped to `created_at = current_date()` on the target.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Data Diff |
| Fields | `customer_name`, `email`, `tier` |
| Row Identifiers | `customer_id` |
| Reference Datastore | `Legacy CRM` |
| Reference Container | `CUSTOMERS_LEGACY` |
| Filter | `created_at = current_date()` |
| Diff Change Types | `["added", "changed"]` (ignore `removed`-row noise from the unfiltered legacy reference) |
| Comparators | *(none)* |
| Status | Active |
| Tags | `migration`, `customers` |
| Additional Metadata | `jira: DATA-5240` |
| Description | Today's new customers must mirror the legacy CRM |

**Payload**

```json
{
    "description": "Today's new customers must mirror the legacy CRM",
    "rule": "dataDiff",
    "fields": ["customer_name", "email", "tier"],
    "container_id": 318,
    "filter": "created_at = current_date()",
    "properties": {
        "ref_datastore_id": 17,
        "ref_container_id": 661,
        "id_field_names": ["customer_id"],
        "diff_change_types": ["added", "changed"]
    },
    "tags": ["migration", "customers"],
    "additional_metadata": {"jira": "DATA-5240"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 24
}
```

**Why the filter and `diff_change_types` matter**

The filter runs **before** the reference is read into the comparison. The target is scoped to today's `CUSTOMERS_NEW` rows, and only those rows are matched against `CUSTOMERS_LEGACY`. A customer created yesterday with a mismatch in either system stays out of scope and does not contribute to the anomaly.

This check leaves the reference filter empty, so `CUSTOMERS_LEGACY` is read in full and carries every legacy customer ever created. Any legacy row not matched by a `CUSTOMERS_NEW` row created today is a row only the reference has, which surfaces as `removed`. That noise has nothing to do with the cutover verification, so the check sets `diff_change_types` to `["added", "changed"]`, and only today's NEW customers missing from LEGACY, or today's NEW customers whose values differ in LEGACY, are reported.

**Sample Data** (filtered to `created_at = current_date()`, simplified)

| Row Status | customer_id | customer_name (Left → Right) | email (Left → Right) | tier (Left → Right) |
| --- | --- | --- | --- | --- |
| changed | C-9012 | Lin Wei → Lin Wei | lin@example.com → lin@example.com | gold → silver |

**What gets flagged**

`C-9012` exists on both sides today. Only `tier` differs, so the row is `changed` and `tier` is the only field in dispute. No `added` rows are reported in this batch because every target row from today found a match in the legacy CRM. Legacy customers not present in today's `CUSTOMERS_NEW` writes would be reported as `removed`, but `diff_change_types` filters that status out for this check.

> **Example: Shape Anomaly**
>
> 1 records differ between CUSTOMERS_NEW (New CRM) and CUSTOMERS_LEGACY (Legacy CRM)

**Flowchart**

```mermaid
graph TD
    A["Apply filter: created_at = current_date()"] --> B["Read reference container CUSTOMERS_LEGACY"]
    B --> C["Match filtered target rows<br/>against reference by customer_id"]
    C --> D{"Any row added or changed?<br/>(removed is suppressed by diff_change_types)"}
    D -->|No| E["All filtered rows pass"]
    D -->|Yes| F["Flag each differing row with its status:<br/>C-9012 (changed)."]
```

**Equivalent SQL**

```sql
-- Rows the Data Diff check would flag for today's customers,
-- with diff_change_types = ["added", "changed"]
-- (legacy-only rows are intentionally not flagged).
WITH target AS (
    SELECT * FROM customers_new WHERE created_at = current_date()
)
SELECT
    CASE
        WHEN r.customer_id IS NULL THEN 'added'
        ELSE 'changed'
    END AS row_status,
    t.customer_id,
    t.customer_name AS left_customer_name, r.customer_name AS right_customer_name,
    t.email         AS left_email,         r.email         AS right_email,
    t.tier          AS left_tier,          r.tier          AS right_tier
FROM target t
LEFT JOIN customers_legacy r ON t.customer_id = r.customer_id
WHERE
       r.customer_id IS NULL
    OR t.customer_name <> r.customer_name
    OR t.email         <> r.email
    OR t.tier          <> r.tier
ORDER BY t.customer_id;
```

## See Also

- **How It Works**

  The complete reference: definition, field scope, properties, diff statuses, Row Identifiers, Comparators, and the filter clauses.

  [How It Works](https://userguide.qualytics.io/data-quality-checks/data-diff/deep-dive/how-it-works/)
- **Anomaly Reporting**

  The anomaly message the check produces, what the counts mean, and how differing rows appear side by side.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/data-diff/deep-dive/anomaly-reporting/)
- **Best Practices**

  Guidelines for choosing Row Identifiers, tuning Comparators, scoping both sides, and keeping the cost down.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/data-diff/deep-dive/best-practices/)
- **Permissions**

  The team permission each action needs: view, create, edit, archive, restore, and delete.

  [Permissions](https://userguide.qualytics.io/data-quality-checks/data-diff/deep-dive/permissions/)

*Last updated: October 21, 2026.*
