Skip to main content

Data Operations

Dataset comparison and diffing functions. Both functions are also available under short aliases: diff_hashdiff and diff_joindiff.

Quick Reference​

FunctionDescriptionSQL Signature
anofox_tab_diff_hashdiffRow-level diff(source_table, target_table, primary_key) -> TABLE
anofox_tab_diff_joindiffRow-level diff with column selection(source_table, target_table, primary_key [, compare_columns [, include_all]]) -> TABLE

Diffing Functions (2)​

anofox_tab_diff_joindiff​

Join-based row-level diff between two tables or views. Returns rows that were added, removed, or changed. Primary keys are matched NULL-safely (IS NOT DISTINCT FROM), so rows with NULL key values are compared instead of being misreported as added/removed.

Parameters​

ParameterTypeRequiredDefaultDescription
source_tableVARCHARYes-Source table or view name
target_tableVARCHARYes-Target table or view name
primary_keyVARCHAR or VARCHAR[]Yes-Key column(s) for matching rows
compare_columnsVARCHAR[]Noall shared non-key columnsColumns to compare, e.g. ['email', 'name']
include_allBOOLEANNofalseAlso emit unchanged rows

Note that compare_columns is a list (['col1', 'col2']), not a comma-separated string.

Output​

ColumnTypeDescription
diff_typeVARCHAR'added', 'removed', 'changed' (and 'unchanged' with include_all)
key columnsas in source/targetCOALESCE of source/target key values
target columnsas in targetRemaining target columns (NULL for removed rows)

Example​

SELECT * FROM anofox_tab_diff_joindiff(
    'customers',
    'customers_backup',
    ['customer_id']
);

-- Compare only selected columns:
SELECT * FROM anofox_tab_diff_joindiff(
    'customers',
    'customers_backup',
    ['customer_id'],
    ['email', 'name']
);

anofox_tab_diff_hashdiff​

Row-level diff between two tables or views using a primary key.

Honest note: diff_hashdiff currently computes exactly the same result as diff_joindiff (without compare_columns/include_all). The hash/bisection algorithm's bisection_threshold and bisection_factor parameters are accepted in the signature but not implemented — passing them raises a binder error instead of being silently ignored. Prefer diff_joindiff when you need column selection or unchanged rows.

Parameters​

ParameterTypeRequiredDefaultDescription
source_tableVARCHARYes-Source table or view name
target_tableVARCHARYes-Target table or view name
primary_keyVARCHAR or VARCHAR[]Yes-Key column(s) for matching rows

Example​

SELECT * FROM anofox_tab_diff_hashdiff('customers', 'customers_backup', ['customer_id']);

Practical Patterns​

Validate Before ETL​

SELECT COUNT(*) as mismatch_count
FROM anofox_tab_diff_joindiff(
    'raw_import',
    'customers',
    ['customer_id']
);
-- 0 means both tables are identical (unchanged rows are excluded by default)

Change Tracking​

SELECT
    customer_id,
    email
FROM anofox_tab_diff_joindiff(
    'customers_yesterday',
    'customers_today',
    ['customer_id'],
    ['email']
)
WHERE diff_type = 'changed';

Data Sync Verification​

SELECT
    COUNT(*) FILTER (WHERE diff_type = 'unchanged') as matching_rows,
    COUNT(*) FILTER (WHERE diff_type = 'changed') as different_rows,
    COUNT(*) FILTER (WHERE diff_type IN ('added', 'removed')) as missing_rows,
    ROUND(100.0 * COUNT(*) FILTER (WHERE diff_type = 'unchanged') / COUNT(*), 2) as sync_percentage
FROM anofox_tab_diff_joindiff(
    'customers_primary',
    'customers_replica',
    ['customer_id'],
    NULL,
    true  -- include_all: also emit unchanged rows
);

Migration Validation​

SELECT
    COUNT(*) as total_differences,
    COUNT(*) FILTER (WHERE diff_type = 'removed') as lost_in_migration,
    COUNT(*) FILTER (WHERE diff_type = 'added') as added_in_migration,
    COUNT(*) FILTER (WHERE diff_type = 'changed') as data_corruption
FROM anofox_tab_diff_joindiff(
    'customers_legacy_system',
    'customers_new_system',
    ['customer_id']
);

Combining with Validation​

SELECT
    diff.customer_id,
    anofox_tab_vat_is_valid(diff.vat_id) as vat_valid
FROM anofox_tab_diff_joindiff(
    'customers_old',
    'customers_new',
    ['customer_id']
) diff
WHERE diff.diff_type = 'changed'
  AND NOT anofox_tab_vat_is_valid(diff.vat_id);

For assertion-style monitoring rather than row-level diffs, see the check functions: duplicate_count asserts a cap on duplicate rows within one table, match_rate asserts referential integrity between two tables (share of left rows with a join partner on the right), and rel_count_change monitors day-over-day volume changes against a rolling window.

Performance Considerations​

Both functions run a single NULL-safe join over the two inputs, so cost scales with table size and key cardinality. To keep large diffs fast:

-- Restrict the comparison to the columns you care about
SELECT * FROM anofox_tab_diff_joindiff('t1', 't2', ['id'], ['status', 'amount']);

Best for: snapshot comparison, replica verification, and migration sign-off up to tens of millions of rows. For very large tables, diff a filtered view (e.g. one partition or date range at a time).

🍪 Cookie Settings