Tests in the Collate UI
Here you can see all the supported tests definitions and how to configure them in the 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
Tests applied on top of a Table. Here is the list of all table tests:- Table Row Count to Equal
- Table Row Count to be Between
- Table Column Count to Equal
- Table Column Count to be Between
- Table Column Name to Exist
- Table Column to Match Set
- Table Custom SQL Test
- Table Row Inserted Count To Be Between
- Compare 2 Tables for Differences
- Table Data to Be Fresh [Collate]
Table Row Count to Equal
Validate that the total number of rows in a table exactly matches an expected value.**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
Test Logic
Table Row Count to be Between
Ensure that the total number of rows in the table falls within an expected range.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
At least one of these values is required to run the test.
Test Logic
Table Column Count to Equal
Validate that the table contains exactly the expected number of columns.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
Test Logic
Table Column Count to be Between
Validate that the number of columns in a table falls within a defined range.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
Test Logic
Table Column Name to Exist
Ensure that a specific column is present in the table schema.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
Test Logic
Table Column to Match Set
Validate that a table’s column names match a predefined set — with or without order sensitivity.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
Test Logic
Table Custom SQL Test
Use this test to define your own validation logic using a custom SQL expression.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
Test Logic
Table Row Inserted Count To Be Between
Check that the number of rows inserted during a defined time window falls within an expected range.**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
Test Logic
Compare 2 Tables for Differences
Use this test to verify data consistency between two tables, even across different platforms or services.When to Use
- After data replication or migration (for example, Snowflake → Redshift).
- To validate data integrity between source and target systems.
Test Summary
Test Logic
🌐 Supported Data Sources
- Snowflake
- BigQuery
- Athena
- Redshift
- Postgres
- MySQL
- MSSQL
- Oracle
- Trino
- SAP Hana
Table Data to Be Fresh [Collate]
Ensure that table data is being updated frequently enough to be considered fresh.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
Test Logic
Column Tests
Tests applied on top of Column metrics. Here is the list of all column tests:- Column Values to Be Unique
- Column Values to Be Not Null
- Column Values to Match Regex
- Column Values to not Match Regex
- Column Values to Be in Set
- Column Values to Be Not In Set
- Column Values to Be Between
- Column Values Missing Count to Be Equal
- Column Values Lengths to Be Between
- Column Value Max to Be Between
- Column Value Min to Be Between
- Column Value Mean to Be Between
- Column Value Median to Be Between
- Column Values Sum to Be Between
- Column Values Standard Deviation to Be Between
- Column Values To Be At Expected Location
Column Values to Be Unique
Ensures each value in a column appears only once.Dimension
Uniqueness
When to Use
- Primary keys or natural identifiers
- Fields like email, username, or ID
Behavior
Column Values to Be Not Null
Ensures there are no NULL entries in the column.Dimension
Completeness
When to Use
- Mandatory fields such as
email,amount,created_at - Required keys or business-critical columns
Behavior
Column Values to Match Regex
Specify how many values in a column must match a certain regex expression. For certain databases, Collate falls back to SQLLIKE expression. The databases supporting regex pattern as of 0.13.2 are:
- redshift
- postgres
- oracle
- mysql
- mariaDB
- sqlite
- clickhouse
- snowflake
Dimension
Validity
When to Use
- Emails, zip codes, IDs, structured formats
Behavior
Column Values to not Match Regex
Specify values in a column that must not match a certain regex expression. If the test finds values matching theforbiddenRegex, the test will fail. For certain databases, Collate falls back to SQL LIKE expression. The databases supporting regex pattern as of 0.13.2 are:
- redshift
- postgres
- oracle
- mysql
- mariaDB
- sqlite
- clickhouse
- snowflake
LIKE expression
Ensures values do not match a restricted regex pattern.
Dimension
Validity
When to Use
- Prevent forbidden values, test strings, or patterns
Behavior
Column Values to Be in Set
Ensures values are within a predefined whitelist.Dimension
Validity
When to Use
- Enum values:
status,currency,country_code
Behavior
Column Values to Be Not In Set
Ensures values are not in a specified blacklist.Dimension
Validity
When to Use
- Block invalid values like
"NA","Unknown",-1
Behavior
Column Values to Be Between
Validates numeric values of a column are within a given range.Dimension
Accuracy
When to Use
- Username length, field input length validation
Behavior
Column Values Missing Count to Be Equal
Ensures total missing values (NULL + defined “missing” strings) match a target count.Dimension
Completeness
When to Use
- Auditing known missing values
- Accounting for
"NA","N/A","null"
Behavior
Column Values Lengths to Be Between
Ensures that the length of each string value in the column is within a defined character range.Dimension
Accuracy
When to Use
- To validate field length constraints like
name,address, ordescription - To catch too-short or too-long values that may break UI or downstream logic
Behavior
Column Value Max to Be Between
Validates the maximum value of a column lies within a range.Dimension
Accuracy
When to Use
- Cap validation for
score,amount,age
Behavior
Column Value Min to Be Between
Validates the minimum value of a column lies within a range.Dimension
Accuracy
When to Use
- Threshold validation for
discount,price, and so on
Behavior
Column Value Mean to Be Between
Validates that the mean (average) value is in the expected range.Dimension
Accuracy
When to Use
- Check dataset drift or pipeline behavior
Behavior
Column Value Median to Be Between
Validates the median value is in the expected range.Dimension
Accuracy
When to Use
- Median income, score, latency checks
Behavior
Column Values Sum to Be Between
Validates the total sum of values in a column is within a defined range.Dimension
Accuracy
When to Use
- Revenue, units sold, total scores, and so on
Behavior
Column Values Standard Deviation to Be Between
Validates the standard deviation (spread) of values is acceptable.Dimension
Accuracy
When to Use
- Monitoring variance in numeric datasets
Behavior
Column Values To Be At Expected Location
Validates latitude/longitude values are within a defined area.Dimension
Accuracy
When to Use
- Verifying address coordinates
- Mapping regional data
Behavior
