Skip to main content

Data Quality Assertion Training Errors and SQL Validation Issues

Angelo Bresci
Updated

Area: Product Issues

Sub-Area: Data Quality Assertions

Issue

Data quality assertions may display confusing error messages such as "Something unexpected happened during training" or fail to execute properly with SQL validation errors, particularly when using complex SQL queries with UNION operations or CTEs. These issues can occur even when the underlying SQL executes correctly in the data warehouse.

Error Messages

  • Something unexpected happened during training. If this persists, contact support.
  • Custom SQL validation failed: Custom SQL contains non-SELECT statement in CTE. Only SELECT statements are allowed.
  • Your custom SQL query didn't return the expected format.
  • Assertion results showing N/A instead of expected values

You Might Be Asking

  • Why do my assertions show training errors when the SQL works fine in my warehouse?
  • Why are my UNION ALL queries failing validation in assertions?
  • What does "Something unexpected happened during training" mean for my data quality checks?
  • Why do assertions return N/A when my SQL returns valid results?

Solution

  1. For "Training Error" Messages:
    • This error occurs during ML model training for smart assertions and typically indicates a transient issue
    • The assertion's rule-based logic continues to function even when training fails
    • Try forcing a retrain by navigating to the affected assertion and looking for a "retrain" or "reset" option
    • If the error persists, contact DataHub support for backend investigation
  2. For SQL Validation Errors with UNION/CTEs:
    • Update your Remote Executor to version 0.3.17.9 or later
    • Ensure your DataHub Cloud instance is on version 1.0.2 or later
    • These versions include fixes for SQL validation to properly support UNION operations and complex CTEs
  3. Upgrading Remote Executor: 
    # Update your remote executor deployment
# Replace with the latest version (0.3.17.9 or higher)
docker pull acryldata/datahub-executor:v0.3.17.9

# Update your deployment configuration
# Ensure version alignment with your DataHub Cloud instance
  4. Alternative Query Structure (Temporary Workaround):

    If you cannot upgrade immediately, restructure CTE queries as subqueries:

    -- Instead of:
WITH cte AS (
  SELECT id FROM table_a 
  UNION ALL 
  SELECT id FROM table_b
)
SELECT count(*) FROM cte WHERE condition;

-- Use:
SELECT count(*) FROM (
  SELECT id FROM table_a 
  UNION ALL 
  SELECT id FROM table_b
) AS subquery 
WHERE condition;
  5. Verification Steps:
    • Check that your assertion SQL returns exactly 1 row with 1 numeric value
    • Verify the SQL executes successfully in your data warehouse
    • Monitor assertion execution logs for specific error details
    • Test with a simple SELECT 1 query to isolate validation issues

Additional Notes

These issues were introduced by enhanced security measures and SQL validation in Remote Executor versions after 0.3.15.4. The stricter validation initially blocked legitimate SQL operations like UNION ALL and complex CTEs. DataHub Cloud customers should maintain Remote Executor versions aligned with their DataHub Cloud instance version for optimal compatibility. Training errors in smart assertions are typically transient and do not affect basic assertion functionality.

Related Documentation

Tags: assertions, data-quality, training-error, sql-validation, union-queries, remote-executor, snowflake, custom-sql, observability

Related articles