Based on the error message shared, there are two issues in the corrected rule, plus one in the logic.
Why sql_query is failing:
- condition_column must be a BOOLEAN column. DQX treats it as "TRUE = this row fails the check" and wraps it in a CASE WHEN to build the error message. You pointed it at desc_count, which is a BIGINT, hence "The first parameter requires the BOOLEAN type, however ... has the type BIGINT". Return a boolean from the query and point condition_column at that.
- Your GROUP BY makes the check a no-op. Grouping by TID, TIDDESCR makes COUNT(DISTINCT TIDDESCR) always 1 per group, so nothing would ever be flagged even after the type fix. To test "one TID maps to one TIDDESCR", group by TID only, and set merge_columns to TID only. merge_columns is how DQX joins the query result back to the input rows, so it must match the grain of your GROUP BY.
- In the second rule, "SEIECT" and "NUIl" look like a capital I / lowercase l typed in place of L. Worth checking, because that will fail to parse too.
A working version of the first rule:
- criticality: error
check:
function: sql_query
arguments:
query: |
SELECT TID, COUNT(DISTINCT TIDDESCR) > 1 AS condition
FROM {{ input_view }}
WHERE TID IS NOT NULL
GROUP BY TID
input_placeholder: input_view
merge_columns:
- TID
condition_column: condition
msg: TID has multiple TIDDESCR values
name: tid_tiddescr_one_to_one
For a true one-to-one mapping, add the mirror check (GROUP BY TIDDESCR, COUNT(DISTINCT TID) > 1, merge_columns TIDDESCR), since the rule above only covers one direction. The I/IDESC rule follows the same pattern.
The date rule doesn't need sql_query at all. It's a row-level condition, so a sql_expression check is simpler and far less error-prone, e.g. expression: effective_startdate < current_date() AND effective_enddate < current_date(). In sql_expression the expression is what must be TRUE for a valid row, which is the opposite of sql_query's condition_column.
Getting better output from the LLM:
- Put the contract in user_input explicitly, e.g. "Use Databricks SQL syntax. Only use sql_query for cross-row or aggregate rules; use built-in checks or sql_expression for row-level rules. For sql_query, reference the table only as {{ input_view }}, return a boolean column that is TRUE when the row violates the rule, set condition_column to that column, and set merge_columns to exactly the GROUP BY columns." Most of the unusable output you got (no merge_columns, no condition_column, a literal input_view) comes from the model not knowing that contract.
- Validate before applying anything. DQEngine.validate_checks(checks) catches structural problems. Then, for each sql_query check, replace {{ input_view }} with a temp view of your data and run spark.sql("EXPLAIN " + query) to catch syntax errors without executing it.
- If you're on a small serving endpoint, pointing the generator at a stronger model noticeably reduces broken SQL.
Treat the generated rules as a first draft. Once they pass validation, save them to YAML or a Delta table and load them from there in the pipeline, rather than regenerating on every run.
I hope this helps 🙂
