> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getcollate.io/llms.txt
> Use this file to discover all available pages before exploring further.
# Tests - UI Configuration | Quality Configuration Guide
> Define UI tests to validate data quality during ingestion and enforce expectations at column or table level.
# Tests in the Collate UI
A **Test Definition** is a generic definition of a test. This Test Definition then gets specified in a Test Case. This Test Case is where the parameter(s) of a Test Definition are specified.
In this section, you will learn what tests Collate currently supports and how to configure them in the Collate UI.
* [Table Tests](#table-tests)
* [Column Tests](#column-tests)
## Table Tests
Table tests validate the structure and shape of a table as a whole, rather than the values inside any single column. Use them to catch problems like an unexpected change in row or column count, a missing or renamed column, stale data, or two tables falling out of sync after a migration or replication job.
### Table Row Count to Equal
This test checks that a table always has exactly the number of rows you expect, no more and no fewer. It's useful for tables that should stay a fixed size, such as reference or lookup tables, where any change in row count usually signals a problem upstream.
* **When to Use**
* To monitor tables where row count is expected to remain fixed (for example, dimension tables).
* To catch over- or under-loading issues after ETL processes.
* To verify baseline data volumes for test/staging/prod comparisons.
* **Test Summary**
| Property | Description |
| ------------------ | -------------------------------------------------- |
| **Expected Value** | The exact number of rows the table should contain. |
* **Test Logic**
| Condition | Status |
| --------------------------------- | --------- |
| Actual row count = expected value | ✅ Success |
| Actual row count ≠ expected value | ❌ Failed |
Watch this video to see how to add a Table Row Count to Equal test case.
### Table Row Count to be Between
This test checks that a table's row count stays within a healthy range you define, rather than requiring an exact match. It helps you catch unusually large or small tables, for example after a failed load or an unexpected spike in incoming data.
* **When to Use**
* To monitor for abnormal growth or shrinkage in table size.
* To catch failed inserts, unintended truncations, or unexpected data surges.
* To set alerts based on historical data volume expectations.
* **Test Summary**
| Property | Description |
| ------------- | -------------------------------------------- |
| **Min Value** | Minimum expected number of rows (`minValue`) |
| **Max Value** | Maximum allowed number of rows (`maxValue`) |
At least one of these values is required to run the test.
* **Test Logic**
| Condition | Status |
| ---------------------------------------------- | --------- |
| Row count is between `minValue` and `maxValue` | ✅ Success |
| Row count is outside the defined range | ❌ Failed |
Watch this video to see how to add a Table Row Count to be Between test case.
### Table Column Count to Equal
This test checks that a table has exactly the number of columns you expect. It helps you catch accidental schema changes, such as a column being added or removed upstream without your knowledge.
* **When to Use**
* To detect unapproved schema changes (for example, columns being added or dropped).
* To enforce data contracts between teams or systems.
* To ensure structural consistency across environments.
* **Test Summary**
| Property | Description |
| ------------------ | -------------------------------------------- |
| **Expected Count** | Exact number of columns the table must have. |
* **Test Logic**
| Condition | Status |
| ------------------------------------ | --------- |
| Actual column count = expected count | ✅ Success |
| Actual column count ≠ expected count | ❌ Failed |
Watch this video to see how to add a Table Column Count to Equal test case.
### Table Column Count to be Between
This test checks that a table's column count stays within a minimum and maximum range you define. It's useful when some schema variation is expected, but you still want to catch major structural drift.
* **When to Use**
* To detect schema drift or changes in table structure.
* To ensure a table has a predictable number of columns across environments (for example, staging vs. production).
* **Test Summary**
| Property | Description |
| --------------- | -------------------------------------------------- |
| **Min Columns** | Minimum number of expected columns (`minColValue`) |
| **Max Columns** | Maximum number of allowed columns (`maxColValue`) |
* **Test Logic**
| Condition | Status |
| ------------------------------------------------ | --------- |
| Actual column count is within the defined range | ✅ Success |
| Actual column count is outside the defined range | ❌ Failed |
Watch this video to see how to add a Table Column Count to be Between test case.
### Table Column Name to Exist
This test checks that a specific, named column is present in the table. It's useful for making sure critical fields, such as an ID or timestamp column that downstream processes depend on, are never accidentally dropped.
* **When to Use**
* To validate that required schema fields exist (for example, `order_id`, `customer_id`).
* To monitor schema changes that might break downstream processes.
* To enforce critical column presence in governed datasets.
* **Test Summary**
| Property | Description |
| --------------- | ------------------------------------------------ |
| **Column Name** | Name of the column that must exist in the table. |
* **Test Logic**
| Condition | Status |
| --------------------------------------- | --------- |
| `columnName` exists in the table schema | ✅ Success |
| `columnName` is missing from the table | ❌ Failed |
Watch this video to see how to add a Table Column Name to Exist test case.
### Table Column to Match Set
This test checks that a table's column names match an exact, predefined list, optionally requiring the columns to appear in a specific order. It's useful when you need to guarantee the full structure of a table hasn't changed.
* **When to Use**
* To ensure schema alignment across different environments or pipeline stages.
* To detect unexpected column additions, deletions, or reordering.
* To enforce table contracts where the exact structure is critical.
* **Test Summary**
| Property | Description |
| ---------------- | ------------------------------------------------------------------------------- |
| **Column Names** | Comma-separated list of expected column names (for example, `col1, col2, col3`) |
| **Ordered** | Boolean flag (`true` or `false`), whether the order of columns must match. |
* **Test Logic**
| Ordered | Condition | Status |
| ------- | ------------------------------------------------ | --------- |
| `false` | All expected column names exist (any order) | ✅ Success |
| `true` | Column names match and appear in the exact order | ✅ Success |
| `false` | Some columns are missing or extra | ❌ Failed |
| `true` | Columns are present but order is incorrect | ❌ Failed |
Watch this video to see how to add a Table Column to Match Set test case.
### Table Custom SQL Test
This test lets you write your own SQL query to check for anything the built-in tests don't cover, such as business-specific rules, duplicate records, or unusual outliers. Collate runs your query and compares the result against a threshold you configure to decide whether the test passes or fails.
* **When to Use**
* To implement logic beyond predefined test definitions.
* To detect outliers, nulls, duplicates, or business-specific data anomalies.
* When you need full flexibility using SQL syntax.
* **Test Summary**
| Property | Description |
| ------------------ | --------------------------------------------------------------------------------------------- |
| **SQL Expression** | The SQL query used to evaluate the test. |
| **Strategy** | Defines how to interpret the result. Options: `ROWS` *(default)* or `COUNT`. |
| **Operator** | Comparison used against the threshold. Options: `==`, `>`, `>=`, `<`, `<=` *(default)*, `!=`. |
| **Threshold** | The value compared against the query result using `operator`. Default is `0`. |
* **Test Logic**
| Strategy | Condition | Status |
| -------- | ------------------------------------------------------------- | --------- |
| ROWS | Number of returned rows satisfies `operator threshold` | ✅ Success |
| ROWS | Number of returned rows does not satisfy `operator threshold` | ❌ Failed |
| COUNT | Count result satisfies `operator threshold` | ✅ Success |
| COUNT | Count result does not satisfy `operator threshold` | ❌ Failed |
Watch this video to see how to add a Table Custom SQL Test test case.
### Table Row Inserted Count To Be Between
This test looks at how many new rows were added to a table within a recent time window, such as the last hour or day, and checks that count falls within an expected range. It's a good way to catch pipelines that silently stop loading data, or that suddenly load far more or fewer rows than usual.
* **When to Use**
* To detect whether recent data ingestion volumes are within acceptable limits.
* To monitor time-partitioned tables for daily/hourly/monthly data drops or spikes.
* To validate pipeline freshness and completeness over time.
* **Test Summary**
| Property | Description |
| ------------------ | ------------------------------------------------------------------ |
| **Min Row Count** | Minimum number of inserted rows expected in the given range. |
| **Max Row Count** | Maximum number of inserted rows allowed in the given range. |
| **Column Name** | Timestamp column used to filter the inserted rows. |
| **Range Type** | Time granularity: `HOUR`, `DAY`, `MONTH`, or `YEAR`. |
| **Range Interval** | Number of units (for example, last `1 DAY`, `2 HOURS`, and so on). |
* **Test Logic**
| Condition | Status |
| ------------------------------------------------- | --------- |
| Row count within `min` and `max` for the interval | ✅ Success |
| Row count outside of the expected range | ❌ Failed |
Watch this video to see how to add a Table Row Inserted Count To Be Between test case.
### Compare 2 Tables for Differences
This test compares two tables row by row, even if they live on different database platforms, and reports how many rows differ between them. It's commonly used to confirm that data was replicated or migrated correctly, for example after moving a table from one warehouse to another.
* **When to Use**
* After data replication or migration (for example, Snowflake to Redshift).
* To validate data integrity between source and target systems.
* **Test Summary**
| Property | Description |
| -------------------------- | --------------------------------------------------------------------------------------------------- |
| **Key Columns** | Columns used as the row-matching key. Defaults to the table's primary key if not specified. |
| **Columns to Compare** | Subset of columns used for comparison. If not provided, all columns will be compared. |
| **Second Table** | Fully qualified name of the second table (for example, `redshift_dbt.dev.dbt_jaffle.boolean_test`). |
| **Threshold** | Maximum number of mismatched rows allowed. Default is `0` (strict equality). |
| **Filter Condition** | *(Optional)* A `WHERE` clause (for example, `id != 999`) to limit rows involved in the comparison. |
| **Case-Sensitive Columns** | Set to `true` if column name case must match exactly (default is `false`). |
* **Test Logic**
| Condition | Status |
| ------------------------------------ | --------- |
| Number of differing rows ≤ threshold | ✅ Success |
| Number of differing rows > threshold | ❌ Failed |
* **Supported Data Sources**
* Snowflake
* BigQuery
* Athena
* Redshift
* Postgres
* MySQL
* MSSQL
* Oracle
* Trino
* SAP Hana
* Databricks
* Unity Catalog
* AzureSQL
Watch this video to see how to add a Compare 2 Tables for Differences test case.
### Table Data to Be Fresh \[Collate]
This test checks how recently a table's data was last updated, and fails if that update happened too long ago. It helps you catch stalled pipelines and confirm that time-sensitive tables are meeting their expected refresh schedule.
* **When to Use**
* To monitor data pipelines for staleness or lag.
* To detect delays in scheduled batch updates.
* To ensure compliance with SLAs for near real-time data delivery.
* **Test Summary**
| Property | Description |
| --------------------- | --------------------------------------------------------------------------------------------- |
| **Column** | The datetime column used to determine the last update. |
| **Time Since Update** | Maximum age allowed for the most recent data entry, expressed in `timeUnit`. |
| **Time Unit** | Unit for the threshold: `SECONDS`, `MINUTES`, `HOURS`, `DAYS`, `WEEKS`, `MONTHS`, or `YEARS`. |
| **Timezone** | *(Optional)* Timezone used to evaluate freshness. Defaults to `UTC`. |
* **Test Logic**
| Condition | Status |
| ---------------------------------------------------- | --------- |
| Last update time ≤ `timeSinceUpdate` (in `timeUnit`) | ✅ Success |
| Last update time > `timeSinceUpdate` (in `timeUnit`) | ❌ Failed |
Watch this video to see how to add a Table Data to Be Fresh test case.
## Column Tests
Column tests validate the values inside a specific column, rather than the table as a whole. Use them to catch problems like duplicate or missing values, values that fall outside an expected range or pattern, or statistical properties (such as the mean, median, or standard deviation) drifting outside what's expected.
### Column Values to Be Unique
This test checks that every value in a column is distinct, with no duplicates anywhere. It's typically used on primary keys or other identifier columns, where duplicate values would indicate a data integrity problem.
* **Dimension**: `Uniqueness`
* **When to Use**
* Primary keys or natural identifiers.
* Fields like email, username, or ID.
* **Behavior**
| Condition | Status |
| ------------------------- | ------ |
| All values are unique | ✅ |
| Any duplicate value found | ❌ |
Watch this video to see how to add a Column Values to Be Unique test case.
### Column Values to Be Not Null
This test checks that a column never contains missing (NULL) values. It's useful for required fields where a missing value would break downstream reports or logic. It does not catch empty strings, which are not NULL — use [Column Values to Match Regex](#column-values-to-match-regex) for that.
* **Dimension**: `Completeness`
* **When to Use**
* Mandatory fields such as `email`, `amount`, `created_at`.
* Required keys or business-critical columns.
* **Behavior**
| Condition | Status |
| ---------------------- | ------ |
| No NULLs present | ✅ |
| Any NULL value present | ❌ |
Watch this video to see how to add a Column Values to Be Not Null test case.
### Column Values to Match Regex
This test checks that every value in a column matches a pattern you define using a regular expression, for example to confirm that a column only contains validly formatted email addresses or postal codes. On databases that don't support native regex matching, Collate automatically falls back to the SQL `LIKE` expression instead. Collate supports the regex pattern on the following databases:
* Redshift
* Postgres
* Oracle
* MySQL
* MariaDB
* SQLite
* ClickHouse
* Databricks
* Snowflake
* **Dimension**: `Validity`
* **When to Use**
* Emails, zip codes, IDs, structured formats.
* **Behavior**
| Condition | Status |
| ------------------------ | ------ |
| All values match regex | ✅ |
| Any value does not match | ❌ |
Watch this video to see how to add a Column Values to Match Regex test case.
### Column Values to not Match Regex
This test checks that no value in a column matches a pattern you want to block, defined using a regular expression (`forbiddenRegex`). If even one value matches that forbidden pattern, the test fails, which makes it useful for catching placeholder values, test data, or other known-bad formats that shouldn't reach production. On databases that don't support native regex matching, Collate automatically falls back to the SQL `LIKE` expression instead. Collate supports the regex pattern on the following databases:
* Redshift
* Postgres
* Oracle
* MySQL
* MariaDB
* SQLite
* ClickHouse
* Databricks
* Snowflake
* **Dimension**: `Validity`
* **When to Use**
* Prevent forbidden values, test strings, or patterns.
* **Behavior**
| Condition | Status |
| ---------------------------------- | ------ |
| No value matches forbidden pattern | ✅ |
| Any value matches the pattern | ❌ |
Watch this video to see how to add a Column Values to not Match Regex test case.
### Column Values to Be in Set
This test checks that every value in a column comes from a predefined allowlist of acceptable values. It's a good fit for columns that represent a fixed set of categories, such as a status or country code field, where any unexpected value likely points to bad data.
* **Dimension**: `Validity`
* **When to Use**
* Enum values: `status`, `currency`, `country_code`.
* **Behavior**
| Condition | Status |
| -------------------------------------------------- | ------ |
| All values in set (if `matchEnum = true`) | ✅ |
| Any value not in set (if `matchEnum = true`) | ❌ |
| Any value from set exists (if `matchEnum = false`) | ✅ |
| No values from set found (if `matchEnum = false`) | ❌ |
Watch this video to see how to add a Column Values to Be in Set test case.
### Column Values to Be Not In Set
This test checks that no value in a column matches an entry in a blocklist of values you want to exclude. It's useful for catching known placeholder or invalid entries, such as `"NA"` or `-1`, that shouldn't appear in clean data.
* **Dimension**: `Validity`
* **When to Use**
* Block invalid values like `"NA"`, `"Unknown"`, `-1`.
* **Behavior**
| Condition | Status |
| ---------------------------------- | ------ |
| No values from forbidden set | ✅ |
| Any value from forbidden set found | ❌ |
Watch this video to see how to add a Column Values to Be Not In Set test case.
### Column Values to Be Between
This test checks that every numeric value in a column falls within a minimum and maximum range you define. It's commonly used to catch out-of-range values in fields like price, age, or quantity that would otherwise indicate a data entry or calculation error.
* **Dimension**: `Accuracy`
* **When to Use**
* Range checks for `price`, `age`, `quantity`, and similar numeric fields.
* **Behavior**
| Condition | Status |
| ----------------------------------- | ------ |
| Value within `[minValue, maxValue]` | ✅ |
| Value \< minValue or > maxValue | ❌ |
Watch this video to see how to add a Column Values to Be Between test case.
### Column Values Missing Count to Be Equal
This test counts how many values in a column are missing, counting both true NULLs and any strings you've defined as representing a missing value (such as `"NA"` or `"N/A"`), and checks that this count matches a target you expect. It's useful when a small, known number of missing values is acceptable, but you still want to be alerted if that number changes.
* **Dimension**: `Completeness`
* **When to Use**
* Auditing known missing values.
* Accounting for `"NA"`, `"N/A"`, `"null"`.
* **Behavior**
| Condition | Status |
| ------------------------------ | ------ |
| Missing count = expected value | ✅ |
| Missing count ≠ expected value | ❌ |
Watch this video to see how to add a Column Values Missing Count to Be Equal test case.
### Column Values Lengths to Be Between
This test checks that every string value in a column has a character length within a minimum and maximum range you define. It helps catch values that are unexpectedly too short or too long, which can otherwise break UI display limits or downstream processing.
* **Dimension**: `Accuracy`
* **When to Use**
* To validate field length constraints like `name`, `address`, or `description`.
* To catch too-short or too-long values that may break UI or downstream logic.
* **Behavior**
| Condition | Status |
| ------------------------------------------ | ------ |
| All values have length within `[min, max]` | ✅ |
| Any value length \< min or > max | ❌ |
Watch this video to see how to add a Column Values Lengths to Be Between test case.
### Column Value Max to Be Between
This test looks at the single highest value in a column and checks that it falls within a range you define. It's a useful cap check for columns like `score`, `amount`, or `age`, where an unusually high maximum often signals bad or unvalidated input.
* **Dimension**: `Accuracy`
* **When to Use**
* Cap validation for `score`, `amount`, `age`.
* **Behavior**
| Condition | Status |
| ------------------------------- | ------ |
| Max value in range `[min, max]` | ✅ |
| Max \< min or Max > max | ❌ |
Watch this video to see how to add a Column Value Max to Be Between test case.
### Column Value Min to Be Between
This test looks at the single lowest value in a column and checks that it falls within a range you define. It's useful for catching unexpected negative or unusually low values in fields like `discount` or `price`.
* **Dimension**: `Accuracy`
* **When to Use**
* Threshold validation for `discount`, `price`, and so on.
* **Behavior**
| Condition | Status |
| ------------------------------- | ------ |
| Min value in range `[min, max]` | ✅ |
| Min \< min or Min > max | ❌ |
Watch this video to see how to add a Column Value Min to Be Between test case.
### Column Value Mean to Be Between
This test calculates the average (mean) of all values in a column and checks that it falls within an expected range. It's a good way to spot gradual shifts in your data, such as dataset drift or a pipeline that starts producing systematically different values.
* **Dimension**: `Accuracy`
* **When to Use**
* Check dataset drift or pipeline behavior.
* **Behavior**
| Condition | Status |
| -------------------------- | ------ |
| Mean value in `[min, max]` | ✅ |
| Mean \< min or Mean > max | ❌ |
Watch this video to see how to add a Column Value Mean to Be Between test case.
### Column Value Median to Be Between
This test calculates the median, the middle value when all values are sorted, of a column and checks that it falls within an expected range. Because the median isn't skewed by extreme outliers the way an average can be, it's a good complement to a mean check for fields like income, score, or latency.
* **Dimension**: `Accuracy`
* **When to Use**
* Median income, score, latency checks.
* **Behavior**
| Condition | Status |
| ----------------------------- | ------ |
| Median in range `[min, max]` | ✅ |
| Median \< min or Median > max | ❌ |
Watch this video to see how to add a Column Value Median to Be Between test case.
### Column Values Sum to Be Between
This test adds up every value in a column and checks that the total falls within a range you define. It's commonly used for financial or volume metrics, such as total revenue or units sold, where an unexpected total often points to missing or duplicated records.
* **Dimension**: `Accuracy`
* **When to Use**
* Revenue, units sold, total scores, and so on.
* **Behavior**
| Condition | Status |
| ------------------------- | ------ |
| Sum in range `[min, max]` | ✅ |
| Sum \< min or Sum > max | ❌ |
Watch this video to see how to add a Column Values Sum to Be Between test case.
### Column Values Standard Deviation to Be Between
This test measures how spread out the values in a column are, using standard deviation, and checks that this spread falls within an acceptable range. It's useful for catching data that has suddenly become far more variable or far more uniform than expected.
* **Dimension**: `Accuracy`
* **When to Use**
* Monitoring variance in numeric datasets.
* **Behavior**
| Condition | Status |
| ----------------------- | ------ |
| Std Dev in `[min, max]` | ✅ |
| Std Dev \< min or > max | ❌ |
Watch this video to see how to add a Column Values Standard Deviation to Be Between test case.
### Column Values To Be At Expected Location
This test checks that latitude and longitude values in a column fall within a defined geographic area, such as a radius around an expected location. It's useful for validating address or location data, for example confirming that store or delivery coordinates fall within the region they're supposed to.
* **Dimension**: `Accuracy`
* **When to Use**
* Verifying address coordinates.
* Mapping regional data.
* **Behavior**
| Condition | Status |
| ---------------------------------------------- | ------ |
| Coordinates within buffer of expected location | ✅ |
| Any record outside allowed radius | ❌ |
Watch this video to see how to add a Column Values To Be At Expected Location test case.