​

Tests in the OpenMetadata 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 we currently support and how to configure them in the OpenMetadata UI.

• Table 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, 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

​

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

​

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

​

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

​

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

​

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

​

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. OpenMetadata 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

​

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

​

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