Custom Data Quality Rules Not Working

Custom SQL assertions or data quality rules created in DataHub are not executing properly, returning incorrect results, or failing with query errors. This commonly occurs due to SQL syntax issues specific to the target platform, incorrect connection configuration, or insufficient query permissions.

Common Error Messages:

• SQL syntax error in assertion query
• Table or view not found in assertion
• Permission denied executing assertion query
• Assertion query timeout

You Might Be Asking:

• How do I write custom SQL assertions?
• What SQL dialect should I use?
• Why is my assertion query failing?

1. Create custom SQL assertion with correct syntax:

-- For Snowflake assertions
-- Must return 0 for pass, > 0 for fail
SELECT COUNT(*) 
FROM analytics.sales 
WHERE amount < 0 
  OR amount IS NULL;

-- For completeness checks
SELECT CASE 
  WHEN COUNT(*) >= 1000 THEN 0  -- Pass
  ELSE 1  -- Fail
END 
FROM analytics.daily_sales 
WHERE date = CURRENT_DATE;

2. Configure assertion in DataHub UI:

1. Navigate to Dataset → Quality tab
2. Click "Create Assertion"
3. Select "Custom SQL"
4. Choose connection/platform
5. Enter SQL query (must return single numeric value)
6. Set schedule and notification preferences
7. Test assertion before saving

3. Use platform-specific SQL dialects:

Examples for different platforms. Adapt the SQL syntax to your specific data platform.

# Snowflake
SELECT COUNT(*) FROM table WHERE condition;

# BigQuery  
SELECT COUNT(*) FROM `project.dataset.table` WHERE condition;

# Postgres
SELECT COUNT(*) FROM schema.table WHERE condition;

# Redshift
SELECT COUNT(*) FROM schema.table WHERE condition;

4. Create assertion via API:

Example for Snowflake. This can be adapted for other data sources/connectors.

from datahub.emitter.mce_builder import make_dataset_urn
from datahub.metadata.schema_classes import (
    AssertionInfoClass,
    SqlAssertionInfoClass,
    DatasetAssertionScopeClass
)

assertion_info = AssertionInfoClass(
    type="SQL",
    sqlAssertion=SqlAssertionInfoClass(
        entity=make_dataset_urn("snowflake", "db.schema.table"),
        statement="SELECT COUNT(*) FROM db.schema.table WHERE invalid_column IS NULL",
        operator="EQUAL_TO",
        parameters={"value": "0"}
    ),
    datasetAssertion=DatasetAssertionScopeClass(
        dataset=make_dataset_urn("snowflake", "db.schema.table"),
        scope="DATASET_COLUMN"
    )
)

5. Debug assertion execution:

# Check DataHub Actions logs for assertion execution
kubectl logs -f deployment/datahub-actions | grep "assertion"

# Query assertion results via GraphQL
query {
  dataset(urn: "YOUR_URN") {
    assertions(start: 0, count: 10) {
      assertions {
        urn
        info {
          type
        }
        runEvents(limit: 5) {
          result {
            type
            nativeResults
          }
        }
      }
    }
  }
}

Custom SQL assertions run on your data source directly, so query performance depends on your database. Consider adding appropriate indexes and limiting query complexity. Always test queries in your database before creating assertions.

• Assertions Guide
• Custom SQL Assertions