Skip to main content

Rule types and failed rows validations

Data Quality Studio provides rule types to validate data quality in Snowflake, Databricks, and BigQuery. Each rule type targets specific data quality dimensions and generates a SQL query that provides failed rows when violations occur.

How failed rows validation works​

When a data quality rule fails, Atlan automatically generates SQL queries that help you identify and inspect the problematic rows in your source system.

Query behavior:

  • Filters rows that violate the rule condition
  • Available for rules with per-row conditions (including Min/Max). Not available for aggregate-only metrics (Average, Standard Deviation, Row Count, Freshness, and all Reconciliation rules). For Custom SQL, failed rows are available only when your SQL returns invalid rows.

Engine support:

  • All rule types work with Snowflake, Databricks and BigQuery
  • Column type restrictions vary by engine (see individual rule details below)

Accessing failed rows​

Failed rows SQL queries can be accessed from the Data quality tab in Atlan. Select any failed rule execution to view the generated SQL query to investigate the specific rows that violate the rule.

Data quality failed rows SQL

Available rule types​

Rule types are organized by data quality dimension. Each rule type includes supported column types for Snowflake, Databricks and BigQuery, along with details about the failed rows SQL it generates.

  • Completeness: Blank Count, Blank Percentage, Null Count, Null Percentage
  • Statistical: Max Value, Min Value, Average, Standard Deviation
  • Uniqueness: Unique Count, Duplicate Count
  • Validity: Regex, String Length, Valid Values, Reference
  • Timeliness: Freshness
  • Volume: Row Count
  • Consistency: Row Count Reconciliation, Average Reconciliation, Sum Reconciliation, Duplicate Count Reconciliation, Unique Count Reconciliation
  • Custom: Custom SQL

Completeness checks​

Completeness rules identify missing or empty data in your columns.

Blank CountCompleteness
Optional

Counts blank values in a column.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE column = ''

Blank PercentageCompleteness
Optional

Calculates the percentage of blank values in a column.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE column = ''

Null CountCompleteness
Optional

Counts NULL values in a column.

Allowed values:
DATEFLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREALTIMESTAMP_LTZTIMESTAMP_NTZDATETIMETIMESTAMPLTZTIMESTAMPNTZTIMESTAMP_TZVARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE column IS NULL

Null PercentageCompleteness
Optional

Calculates the percentage of NULL values in a column.

Allowed values:
DATEFLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREALTIMESTAMP_LTZTIMESTAMP_NTZDATETIMETIMESTAMPLTZTIMESTAMPNTZTIMESTAMP_TZVARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE column IS NULL

Statistical checks​

Statistical rules validate numeric values by comparing them to minimum, maximum, average, and standard deviation thresholds.

Max ValueStatistical
Optional

Computes the maximum value and validates it against a threshold. Provides failed rows that violate the threshold (for example, when using ≥ X, returns rows less than X).

Allowed values:
FLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREAL
Examples:
  • SELECT * FROM table WHERE column > X
  • SELECT * FROM table WHERE column <= X

Min ValueStatistical
Optional

Computes the minimum value and validates it against a threshold. Provides failed rows that violate the threshold (for example, when using ≥ X, returns rows less than X).

Allowed values:
FLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREAL
Examples:
  • SELECT * FROM table WHERE column < X
  • SELECT * FROM table WHERE column >= X

AverageStatistical
Metric-only

Calculates the average value of numeric columns. Failed rows SQL query is not available.

Allowed values:
FLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREAL

Standard DeviationStatistical
Metric-only

Calculates standard deviation for numeric columns. Failed rows SQL query is not available.

Allowed values:
FLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREAL

Uniqueness checks​

Uniqueness rules identify duplicate values and measure distinct value counts in columns.

Unique CountUniqueness
Optional

Counts the number of unique non-NULL values in the selected column.

Allowed values:
DATEFLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREALTIMESTAMP_LTZTIMESTAMP_NTZDATETIMETIMESTAMPLTZTIMESTAMPNTZTIMESTAMP_TZVARCHARSTRINGTEXT
Example:SELECT DISTINCT column FROM table

Duplicate CountUniqueness
Optional

Counts distinct values that appear more than once.

Allowed values:
DATEFLOATNUMBERDECIMALDECNUMERICINTINTEGERBIGINTSMALLINTFLOAT4FLOAT8DOUBLEDOUBLE PRECISIONREALTIMESTAMP_LTZTIMESTAMP_NTZDATETIMETIMESTAMPLTZTIMESTAMPNTZTIMESTAMP_TZVARCHARSTRINGTEXT
Example:SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1

Validity checks​

Validity rules verify that data values conform to expected formats, patterns, and constraints.

RegexValidity
Optional

Validates column values using REGEXP function. Returns rows that don't satisfy the regex condition.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE NOT REGEXP(column, 'pattern')

String LengthValidity
Optional

Validates string length using comparison operators. Returns rows where string length violates the threshold. Supported operators: between, =, >, ≥, <, ≤.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE LENGTH(column) > X

Valid ValuesValidity
Optional

Validates values using allowed or disallowed lists. Returns rows with values outside the allowed list or inside the disallowed list.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT * FROM table WHERE column NOT IN ('value1', 'value2', 'value3')

ReferenceValidity
Optional

Validates column values against a reference table and column. Returns rows where values don't exist in the reference dataset. Useful for validating foreign key relationships or ensuring values match a master data list.

Allowed values:
VARCHARSTRINGTEXT
Example:SELECT count(*) FROM table WHERE column NOT IN (SELECT DISTINCT ref_column FROM reference_table)

Timeliness checks​

Timeliness rules verify data freshness by checking when data was last updated.

FreshnessTimeliness
Metric-only

Validates data recency using date or timestamp columns. Failed rows SQL query is not available.

Allowed values:
DATETIMESTAMP_LTZTIMESTAMP_TZTIMESTAMPLTZTIMESTAMPTZ

Volume checks​

Volume rules measure the total number of rows in tables.

Row CountVolume
Metric-only

Counts total rows in a table. Operates at table level (no column selection required). Failed rows SQL query not available.

Consistency checks​

Consistency rules validate that data matches between source and target tables by comparing aggregate metrics. These rules are useful for data reconciliation scenarios where you need to confirm that data pipelines maintain integrity.

Row Count ReconciliationConsistency
Metric-only

Compares row counts between source and target tables. Returns the percentage or absolute difference. Failed rows SQL query is not available.

Average ReconciliationConsistency
Metric-only

Compares average values between source and target columns. Returns the percentage or absolute difference. Failed rows SQL query is not available.

Allowed values:
NUMBERINTEGERBIGINTDECIMALFLOATDOUBLE

Sum ReconciliationConsistency
Metric-only

Compares sum values between source and target columns. Returns the percentage or absolute difference. Failed rows SQL query is not available.

Allowed values:
NUMBERINTEGERBIGINTDECIMALFLOATDOUBLE

Duplicate Count ReconciliationConsistency
Metric-only

Compares duplicate counts between source and target columns. Returns the percentage or absolute difference. Failed rows SQL query is not available.

Allowed values:
NUMBERINTEGERBIGINTDECIMALFLOATDOUBLE

Unique Count ReconciliationConsistency
Metric-only

Compares unique value counts between source and target columns. Returns the percentage or absolute difference. Failed rows SQL query is not available.

Allowed values:
NUMBERINTEGERBIGINTDECIMALFLOATDOUBLE

Reconciliation rule configuration​

Reconciliation rules require configuring both a source and target for comparison:

  • Source table/column: The primary table or column you want to validate
  • Target table/column: The reference table or column to compare against
  • Allowed difference: The maximum acceptable difference, configured as either:
    • Percentage of source: Difference as a percentage of the source value (e.g., 1% means the rule fails if the difference exceeds 1% of the source)
    • Absolute value: Exact numeric difference allowed between source and target
Incremental processing

Reconciliation rules support row scope filtering for incremental processing. When enabled, the filter applies to both source and target tables, allowing you to compare recent data loads without scanning entire tables.

Custom checks​

Custom rules let you define your own SQL-based metrics tailored to specific validation requirements.

Custom SQLCustom
Conditional

Custom SQL supports two modes: Invalid rows (default) — your SQL returns the violating rows; Atlan counts them and shows failed rows; and Numeric value — your SQL returns a single number; failed rows aren't available.

Custom SQL return types​

The two return type modes determine how your SQL query results are processed and whether failed rows are available.

Invalid rowsCustom
Optional

Default mode. Write SQL that returns the invalid rows. Atlan wraps your SQL as `SELECT COUNT(*) FROM (<your-sql>) AS t` to evaluate thresholds and enables failed-rows SQL using your original query.

Numeric valueCustom
Metric-only

Write SQL that returns a single numeric result (one row, one numeric column). Failed-rows SQL is not available in this mode.

See also​