The 12 validation functions
Each Test applies one function to the Table Pair. Every function exceptCOUNT_ROWS maps one source column to one target column, and each Test is
labeled by its function code. Filters on the Table Pair apply to every Test
in it.
Aggregate comparisons reduce each side to one number and compare the two.
They accept an absolute threshold.
Row comparisons match rows across the two Tables by join key and compare
the mapped value row by row. They accept an absolute threshold, Max bad
records, and an Evidence limit, and they need join keys on the Table
Pair. Until the pair has join keys, the Add Test dialog asks you to set
them in the Table Pair’s settings first.
Set comparisons collect the distinct values of the mapped column on each
side and compare the two sets. They have no threshold, and they accept
Max bad records and an Evidence limit.
Pair a
VALUE Test with COUNT_ROWS or OUTER_VALUE when unmatched rows
matter.
How comparisons behave
Verdicts. A Test either passes, fails, or errors. It passes when the two sides agree within its threshold or allowance, and fails when they do not. It errors when the comparison could not run: Could not read the Tables means a source or target could not be read, The Test could not finish means a mapped column is missing, the function cannot be applied to the column’s type, or a value could not be cast, and Could not save Result details means the mismatch evidence could not be written. A read failure on either Table marks every Test in the Run as an error. Each Result shows how many comparisons, rows, or values were compared and how many mismatched. Nulls. Value comparisons use null-safe equality: a null on both sides is a match. Two null aggregate outputs compare equal. Sets treat null as one distinct member and ignore how many times a value appears.COUNT,
COUNT_DISTINCT, MIN, MAX, AVG, and SUM count or compute only
non-null values.
Join keys. Join keys follow ordinary join semantics, including for
duplicate and null key values. A key value that appears on more than one row
multiplies the matches, so choose keys that identify each row once. VALUE
fails with the reason No matching join keys when the keys produce no
common rows. OUTER_VALUE treats a row missing from either side as a
mismatch.
Empty inputs. When a side has no rows, or every value in the mapped
column is null, MIN, MAX, AVG, and SUM produce null and counts
produce zero. Because two null aggregates compare equal, two empty inputs
pass an aggregate Test. OUTER_VALUE on two empty inputs passes; VALUE on
two empty inputs fails because there are no common keys.
Absolute threshold. Numeric aggregates and row comparisons pass when the
absolute difference between the two values is within the threshold. The
default is 0, which requires exact equality; the field says Use 0 for
exact equality. The threshold is an absolute amount, not a percentage.
Count Tests can allow an absolute difference in counts; distinct counts are
computed exactly. Set Tests have no threshold.
Max bad records. For row and set Tests, Max bad records (default 0)
lets that many mismatches remain and still pass. A Test that passes this way
shows the reason Within allowed bad records, and its mismatch count stays
exact. The allowance never excuses an error or a VALUE Test with no common
keys.
Evidence limit. For row and set Tests, Evidence limit caps how many
mismatched rows the Run saves to your Results Lakehouse. The default is 100,
the maximum is 10,000, and 0 turns capture off. The limit changes only how
much evidence is saved; it never changes the verdict or the mismatch count.
The app previews up to 100 of the saved rows; open the Results Lakehouse in
Fabric to see all of them. See
Runs, Results, and evidence.
Add a Test
- On Table Pairs, click the pair’s name to open it, then click Add Test.
- Choose a Test function. The twelve functions are listed by code, from
COUNT_ROWStoSET_A_EQUALS_B; their comparisons are described under The 12 validation functions. - Choose a Source column and a Target column. The dialog reads the
column names and types live from the Tables and does not save them.
Columns reflect the current Tables even when the pair pins an earlier Delta
version.
COUNT_ROWScompares total row counts and has no column fields. - Set the Absolute threshold. It defaults to 0, which requires exact equality, and it must be 0 or greater and at most 1e15. Set Tests have no threshold field. How the threshold applies to each function is described under How comparisons behave.
- For
VALUE,OUTER_VALUE, and the set Tests, set Max bad records and Evidence limit, described below. - Click Add Test.
SUM, and appears
in the pair’s Tests list with its columns, threshold, max bad records, and
evidence setting. Its Latest Result shows Not run until a Run gives it a
Result. Once the pair has at least one Test and the Environment is set up,
Run validation on the pair’s page submits a Run; see
Runs, Results, and evidence.
Edit and delete a Test
Each Test row has a ⋯ menu, on the pair’s page and on Tests, with Edit Test and Delete Test.- Edit Test opens the same dialog as Add Test. Change the settings and click Save Test. Past Runs and their Results are unchanged: every Run keeps the definition it executed. The edited Test shows Not run until the new definition has a Result.
- Delete Test asks for confirmation and removes the Test from the pair. Past Runs and Results remain available.
Limits
Owners and Contributors add, edit, and delete Tests; Readers see the Tests of
each Table Pair without the row menus. Every active Table Pair, with all of its
Tests, counts as one Table Pair toward your plan; see
Plans and trial.