Validity Tests¶
Validity tests verify that data values conform to expected formats, ranges, value lists, and business rules. A value can be present and non-null but still invalid — for example, a zip code with too few digits, an email with no domain, or a date that predates the dataset's known history. These tests catch values that fail to meet structural or semantic expectations for their column.
For the identifiers used in pharmaceutical claims, prescribing, and clinical trial data — NPI, DEA, NDC, ICD, and others — see Commercial Pharma Tests Enterprise.
Minimum Date¶
Tests that the earliest date referenced in a column is no earlier than the baseline minimum.
Auto-generated when profiling finds a date column with a non-null minimum date and more than one distinct value.
| Scope | Column |
| Measures | Count of dates before the baseline minimum |
| Threshold | Maximum acceptable count of dates before the baseline minimum (default: 0) |
| On failure | Earliest date value found is before earliest value at baseline. |
| Default severity | Fail |
How it works: Counts the number of date values that fall before the baseline minimum date. The test fails when the count exceeds the threshold.
When to use: This test is appropriate for cumulative datasets where old dates should not change. It works well where new records are added with more recent dates but old dates remain stable. For an exact earliest date, use Match Minimum Date.
Minimum Value¶
Tests that the minimum numeric value in a column is no lower than the baseline minimum.
Auto-generated when profiling finds a numeric measurement column with a non-null minimum value and at least two distinct values.
| Scope | Column |
| Measures | Count of values below the baseline minimum |
| Threshold | Maximum acceptable count of values below the baseline minimum (default: 0) |
| On failure | Minimum column value less than baseline. |
| Default severity | Fail |
How it works: Counts the number of values that fall below the baseline minimum. The test fails when the count exceeds the threshold.
When to use: This test is appropriate for cumulative datasets where all prior values are still present, or for any measure with an absolute minimum that makes sense for valid data. For an exact minimum, use Match Minimum Value.
Alpha Truncation¶
Tests that the maximum count of characters in column values has not dropped compared to baseline data.
Auto-generated when profiling finds a text column with consistent or near-consistent value lengths.
| Scope | Column |
| Measures | Maximum character length found |
| Threshold | Minimum expected maximum length (default: 95% of longest value at baseline) |
| On failure | Maximum length of values has dropped compared to baseline. |
| Default severity | Fail |
How it works: Computes MAX(LENGTH(column)) and fails when the result drops below the threshold. A decrease in maximum length in a cumulative dataset suggests that previously existing longer values were altered or removed.
When to use: This test is appropriate for cumulative datasets where prior values should still exist unchanged. A failure suggests that some process changed data you would expect to remain at its original length. It would not be appropriate for an incremental or windowed dataset. For an exact maximum length, use Match Maximum Length.
Decimal Truncation¶
Tests that the sum of fractional (decimal) values in a numeric column has not decreased compared to baseline, which could indicate truncation.
Auto-generated when profiling finds a numeric column with non-zero fractional values in a cumulative table.
| Scope | Column |
| Measures | Sum of all fractional parts of column values |
| Threshold | Minimum expected sum of fractional values (default: rounded baseline fractional sum) |
| On failure | Sum of fractional values is under baseline sum, which may indicate decimal truncation. |
| Default severity | Fail |
How it works: Extracts the fractional part of each value (the portion after the decimal point), sums them, and compares against the baseline threshold. The test fails when the current sum falls below the expected minimum.
When to use: This test catches cases where decimal precision is lost — for example, when a float column is inadvertently cast to integer, or when an ETL process truncates decimal places. It is most appropriate for cumulative datasets where prior values should remain unchanged. For an exact sum of fractional values, use Match Fractional Sum.
Value Count¶
Tests that the count of distinct values in a column has not dropped compared to baseline.
Auto-generated when profiling finds a Code, Category, Attribute, or Description column with 2 to 10 distinct values and more than 50 non-null records.
| Scope | Column |
| Measures | Count of unique values |
| Threshold | Minimum expected distinct value count (default: count at baseline) |
| On failure | Count of unique values in column has changed from baseline. |
| Default severity | Fail |
How it works: Counts the distinct values in the column and compares the result to the threshold. The test fails when the current count falls below the expected minimum.
When to use: This test is relevant for cumulative datasets where old records are retained, or for any dataset where you expect a set number of distinct values to be present. A failure indicates missing records or a change in categories or value assignment. For an exact count of distinct values, use Match Distinct Count.
Constant Match¶
Tests that all values in the column still match the single constant value identified during baseline profiling.
Auto-generated when profiling finds a column with only one distinct value.
| Scope | Column |
| Measures | Count of values not matching the constant |
| Threshold | Maximum acceptable count of mismatched values (default: 0) |
| On failure | Column values do not match expected constant value. |
| Default severity | Fail |
How it works: Counts every record where the column value differs from the expected constant. The test fails when this count exceeds the threshold.
When to use: This test is valuable for columns that should always contain a fixed value — such as a default status, a country code for a single-region dataset, or a version identifier. Sometimes new data reveals that a value is not truly constant, even though only one value was present at profiling time. In that case, disable this test and consider using Value Match instead.
Value Match¶
Tests that all column values appear in the expected list of values identified during baseline profiling.
Auto-generated when profiling finds a Boolean, Code, or Category column with 2 to 10 distinct values.
| Scope | Column |
| Measures | Count of records with values not in the expected list |
| Threshold | Maximum acceptable count of non-matching records (default: 0) |
| On failure | Values not matching expected list of values from baseline. |
| Default severity | Fail |
How it works: Counts every record where the column value (excluding NULLs and empty strings) does not appear in the expected value list. The test fails when this count exceeds the threshold.
When to use: This test is relevant when the list of allowable values is small and not expected to change often. Even if new values might occasionally be added, it provides an early warning that downstream assumptions and logic may need to change. The expected value list is populated automatically from profiling results but can be edited.
Pattern Match¶
Tests that all values in a column match the alphanumeric pattern identified in baseline data.
Auto-generated when profiling finds a single consistent alphanumeric pattern in an attribute, ID, phone, period, or datetime stamp column with more than 10 distinct values.
| Scope | Column |
| Measures | Count of pattern mismatches |
| Threshold | Maximum acceptable count of pattern mismatches (default: 0) |
| On failure | Alpha values do not match consistent pattern in baseline. |
| Default severity | Fail |
How it works: Compares each value in the column against the baseline pattern. Counts how many values do not conform. The test fails when the count exceeds the threshold.
When to use: This test is appropriate for character fields expected to appear in a consistent format, such as IDs, phone numbers, or date stamps stored as text. The expected threshold is the number of records that fail to match the defined pattern.
Email Format¶
Tests that non-blank email values match the standard email address format.
Auto-generated when profiling identifies the column as an email pattern.
| Scope | Column |
| Measures | Count of invalid email addresses |
| Threshold | Maximum acceptable count of invalid emails (default: 0) |
| On failure | Invalid email address formats found. |
| Default severity | Fail |
How it works: Counts the number of non-blank values that do not match the standard email address format (e.g., user@domain.tld). The test fails when the count exceeds the threshold.
When to use: Apply this test to any column identified as containing email addresses. It validates structural format only — it does not verify that the address exists or can receive mail.
Street Address¶
Tests that a sufficient percentage of values match a standard US street address pattern.
Auto-generated when profiling identifies the column as a street address pattern.
| Scope | Column |
| Measures | Percentage of values matching the address pattern |
| Threshold | Minimum acceptable match percentage (default: 75%) |
| On failure | Percent of values matching standard street address format is under expected threshold. |
| Default severity | Fail |
How it works: Evaluates each non-blank value against a standard US street address pattern and calculates the percentage that match. The test fails when the match percentage drops below the threshold.
When to use: The street address pattern should match the vast majority of US addresses. Adjust the threshold based on the results you observe — you may want to tighten it to make the test more sensitive to invalid entries.
US State¶
Tests that values in a column are valid two-letter US state codes.
Auto-generated when profiling finds a 2-character text column whose name contains "state" with fewer than 70 distinct values.
| Scope | Column |
| Measures | Count of values that are not US state abbreviations |
| Threshold | Maximum acceptable count of non-matching values (default: 0) |
| On failure | Column values found that are not valid US states. |
| Default severity | Fail |
How it works: Validates each value against a fixed list of two-character US state and territory codes (including Armed Forces designations). Counts the number of values that do not match. The test fails when the count exceeds the threshold.
When to use: Apply this test to columns that should contain US state abbreviations. It validates against the standard set of state, territory, and Armed Forces codes.
Valid US Zip¶
Tests that postal codes match the 5-digit or 9-digit standard US formats.
Auto-generated when profiling identifies the column as a zip code.
| Scope | Column |
| Measures | Count of invalid zip codes |
| Threshold | Maximum acceptable count of invalid zip codes (default: 0) |
| On failure | Invalid US zip code formats found. |
| Default severity | Warning |
How it works: Checks each value against the standard US zip code formats: 5-digit (12345) and 9-digit with hyphen (12345-6789). Counts values that do not match either format. The test fails when the count exceeds the threshold.
When to use: Apply this test to columns that should contain US postal codes. It validates format only — it does not verify that the zip code corresponds to a real location.
Valid US Zip-3¶
Tests that postal codes match the 3-digit format of a US regional prefix.
Auto-generated when profiling identifies the column as a Zip-3 prefix.
| Scope | Column |
| Measures | Count of invalid Zip-3 prefixes |
| Threshold | Maximum acceptable count of invalid Zip-3 codes (default: 0) |
| On failure | Invalid 3-digit US zip code regional prefix formats found. |
| Default severity | Warning |
How it works: Checks each value against the 3-digit numeric format expected for US zip code regional prefixes. Counts values that do not match. The test fails when the count exceeds the threshold.
When to use: Zip-3 prefixes are often used to roll up zip code data to a regional level and may be used to anonymize detailed data. This test ensures the column contains only the expected 3-digit prefix format.
Maximum Date¶
Tests that the latest date referenced in a column is no later than the baseline maximum.
This test is not auto-generated. You can create it manually from the Test Definitions page.
| Scope | Column |
| Measures | Count of dates after the baseline maximum |
| Threshold | Maximum acceptable count of dates after the baseline maximum (default: 0) |
| On failure | Latest date value found is after latest value at baseline. |
| Default severity | Fail |
How it works: Counts the number of date values that fall after the baseline maximum date. The test fails when the count exceeds the threshold. On a timestamp column, a maximum entered without a time means midnight at the start of that day, so values later that day count as after it. To allow the whole day, enter the maximum with a time.
When to use: This test is appropriate where the latest date is a real ceiling, such as a closed historical range or a table that is no longer loaded. In a table that is still being added to, the latest date moves forward with every load, so a maximum taken from the baseline data fails on the next load. Use Recency to check that dates keep advancing, or Past Dates to check that no date is later than the test date. For an exact latest date, use Match Maximum Date.
Maximum Value¶
Tests that the maximum numeric value in a column is no higher than the baseline maximum.
This test is not auto-generated. You can create it manually from the Test Definitions page.
| Scope | Column |
| Measures | Count of values above the baseline maximum |
| Threshold | Maximum acceptable count of values above the baseline maximum (default: 0) |
| On failure | Maximum column value greater than baseline. |
| Default severity | Fail |
How it works: Counts the number of values that fall above the baseline maximum. The test fails when the count exceeds the threshold.
When to use: This test is appropriate for any measure with an absolute maximum that makes sense for valid data, such as a percentage that cannot exceed 100. For an exact maximum, use Match Maximum Value.
Text Length Range¶
Tests that the character length of every value in the column falls within the set minimum and maximum.
This test is not auto-generated. You can create it manually from the Test Definitions page.
| Scope | Column |
| Measures | Count of values outside the length range |
| Threshold | Maximum acceptable count of values outside the range (default: 0) |
| On failure | Values found outside set length range. |
| Default severity | Fail |
How it works: Counts the values whose length in characters is shorter than the minimum length or longer than the maximum length. Leave either bound blank to check one side only. Empty strings have a length of zero, and null values are not counted. The test fails when the count exceeds the threshold.
When to use: Use this test for codes and identifiers with a known length range, such as a product code of 6 to 8 characters, or to catch values that will not fit a downstream column.
Value Match All¶
Tests that column values exactly match a predefined list — every value must be in the list, and every list value must appear at least once.
This test is not auto-generated. You can create it manually from the Test Definitions page.
| Scope | Column |
| Measures | Pipe-delimited string of all distinct values found |
| Threshold | Sorted list of all distinct expected values, pipe-delimited inside one pair of single quotes |
| On failure | Column values do not exactly match expected list of values. |
| Default severity | Fail |
How it works: Collects all distinct column values into a sorted, pipe-delimited string and compares it to the expected string. The test fails if the two strings differ — meaning either an unexpected value appeared or an expected value is missing.
When to use: This is a stricter form of Value Match. While Value Match only checks that no unexpected values exist, Value Match All also verifies that every expected value is represented at least once. This is appropriate for tables where all category values should appear in each data refresh.
Custom Condition¶
A user-defined test that evaluates a SQL expression against each record in the table and counts how many records match the failure condition.
This test is not auto-generated. To add it, see Add Custom SQL Tests.
| Scope | Custom |
| Measures | Count of records matching the failure condition |
| Threshold | Maximum acceptable count of failing records (default: 0) |
| On failure | Values found not matching defined condition. |
| Default severity | Fail |
How it works: Evaluates a user-defined SQL expression against every record in the table. The expression should return TRUE when a record fails. The test counts all matching records and fails when that count exceeds the threshold.
When to use: Custom Condition is ideal for business-rule checks that TestGen cannot automatically infer, especially conditions that involve multiple columns in the same record. For example, you might test that quantity_shipped <= quantity_ordered, or that end_date >= start_date. Because it runs as part of the aggregate test batch, performance remains fast.
Reference Match¶
Tests that column values or combinations found in the source table also exist in a reference table.
This test is not auto-generated. You can create it manually from the Test Definitions page.
| Scope | Referential |
| Measures | Count of missing value combinations |
| Threshold | Expected count of non-matching value combinations (default: 0) |
| On failure | Column value combinations are not found in reference table values. |
| Default severity | Fail |
How it works: Retrieves the distinct values (or value combinations) from the source table and removes any that also appear in the reference table. The remaining unmatched values are counted. The test fails when this count exceeds the threshold.
When to use: Use this test to verify referential integrity between related tables — for example, confirming that every product code in a fact table exists in the product dimension. Both tables must be accessible at the same time.