gopidesupavan commented on code in PR #69575: URL: https://github.com/apache/airflow/pull/69575#discussion_r3575180649
########## providers/common/dataquality/src/airflow/providers/common/dataquality/skills/dataquality-rule-authoring/SKILL.md: ########## @@ -0,0 +1,173 @@ +--- +name: dataquality-rule-authoring +description: "Generate a RuleSet/DQRule JSON payload for Apache Airflow's dataquality provider from a table's column definitions. Use this whenever asked to write, generate, or suggest data quality rules, checks, or a ruleset for a table or dataset -- especially when the output is structured JSON handed to DQCheckOperator, @task.dq_check, asset_quality(), or RuleSet.from_file()." +--- + +<!-- SPDX-License-Identifier: Apache-2.0 + https://www.apache.org/licenses/LICENSE-2.0 --> + +# dataquality rule authoring + +You are producing a **RuleSet**: a JSON object naming a table's data quality checks. This +document is the schema and the full list of valid values -- do not guess field names or check +names, and do not invent checks that aren't in the catalog below. + +## Output shape + +```json +{ + "name": "orders_quality", + "rules": [ + { + "name": "order_id_not_null", + "check": "null_count", + "column": "order_id", + "condition": {"equal_to": 0} + } + ] +} +``` + +`name` (ruleset name) and `rules` (array) are the only top-level keys. Every rule name must be +unique within the ruleset. + +## `DQRule` fields + +| Field | Required | Notes | +|---|---|---| +| `name` | yes | Unique within the ruleset. Shows up in data quality results and logs. | +| `check` | yes | One of the catalog names below, or `"custom_sql"`. Exact string match -- there is no fuzzy matching. | +| `condition` | yes* | See `Condition` grammar below. *Every catalog check currently requires one explicitly (no defaults) -- always include it. | +| `column` | check-dependent | Required for every catalog check except `row_count`. Not used with `custom_sql`. | +| `sql` | `custom_sql` only | A SQL statement returning a single scalar. May reference the checked table as `{table}`. Invalid together with any catalog check. | +| `severity` | no | `"error"` (default) or `"warn"`. `"error"` fails the task on a failing rule (subject to the operator's `fail_on`); `"warn"` only records it. | +| `partition_clause` | no | Extra SQL predicate ANDed into this rule's `WHERE` clause, e.g. `"region = 'EU'"`. | +| `previous_name` | no | Only set when told a rule is being renamed, to keep its history continuous. | +| `description` | no | Human-readable text shown in data quality results. Use it when a clear business meaning is known; otherwise omit it and let Airflow generate a default description. | +| `dimension` | no | One of `completeness`, `uniqueness`, `validity`, `freshness`, `volume`, `consistency`. Defaults to the check's catalog dimension (`validity` for `custom_sql`) -- only set this explicitly when a `custom_sql` rule measures something the default doesn't capture (e.g. a freshness check written as `custom_sql` should set `"dimension": "freshness"`). Leave it unset for every built-in check. | + +**Do not add any key not listed above.** The schema rejects unrecognized fields. + +## Built-in check catalog + +| `check` | SQL expression | needs `column` | typical use | +|---|---|---|---| +| `null_count` | `SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END)` | yes | count of nulls in a column | +| `null_ratio` | `SUM(CASE WHEN {column} IS NULL THEN 1.0 ELSE 0.0 END) / COUNT(*)` | yes | fraction of nulls, 0.0-1.0 | +| `distinct_count` | `COUNT(DISTINCT {column})` | yes | number of distinct values | +| `unique_violations` | `COUNT({column}) - COUNT(DISTINCT {column})` | yes | 0 means the column is fully unique | +| `min` | `MIN({column})` | yes | minimum value | +| `max` | `MAX({column})` | yes | maximum value | +| `mean` | `AVG({column})` | yes | average value | +| `row_count` | `COUNT(*)` | no | total row count -- omit `column` entirely | + +These are the *only* built-in check names. Anything else -- comparing two columns, joining +another table, checking a format/regex, freshness against `now()`, referential integrity -- must +be written as `custom_sql`. + +## `Condition` grammar + +`condition` is an object with these optional numeric keys; at least one is required: + +- `equal_to` -- exact match. Cannot be combined with any other key below. +- `greater_than` / `geq_to` -- lower bound, exclusive / inclusive. +- `less_than` / `leq_to` -- upper bound, exclusive / inclusive. +- `tolerance` -- a fraction (e.g. `0.1` for 10%) that widens the comparison bounds. + +Combine `greater_than`/`less_than`/`geq_to`/`leq_to` freely to express a range, e.g. +`{"geq_to": 0, "leq_to": 100}`. Never combine `equal_to` with another comparison key (other than +`tolerance`). + +## `custom_sql`: when a catalog check doesn't fit Review Comment: true.. updated -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
