SQL based quality rules allow you to define the conditions for data quality rules directly through SQL queries, thereby providing more flexibility to accelerate rule creation by using existing SQL logic and patterns to formulate rule expressions. By leveraging custom SQL queries, you can specify precise conditions to assess quality scores for the targeted dataset and field. You can configure the required row filters, and pass conditions by providing the relevant SQL queries, enabling control over data quality evaluations.
- Within the Target Assets section on the create quality rule page, navigate to the SQL tab.
- Select the specific field or dataset for which you want to apply the SQL rule.
- In the respective tab, click Select Field or Select Dataset.
- Use the search bar for a quick search, and apply filters to narrow down results. The default filter value is set to Any.
- You can sort the data based on any column parameter.
- Select the respective field or dataset to associate with the rule, then click Add Field or Add Dataset.
- The selected field or dataset will be added to the target assets section. Note: When selecting target assets using SQL, you can select a single dataset or field.
- Once the selected field or dataset is added to the Target Assets section, you can proceed to configure the row filters and pass condition or fail condition using SQL queries.Note: Row filters and pass conditions are evaluated independently when you define conditions using SQL queries. After successful rule evaluation, the evaluated count displays the total number of records filtered by the row filter conditions, while the passed count displays the total number of records that met the pass conditions defined in the rule.
- Test with AI generate data feature
- Show failed records sample feature
- Evaluate rules step within DQ pipelines
- Rule creation using Agentic AI and AI assist features
- Faster rule creation: Write quality rules directly in SQL without navigating through the interface
- Greater flexibility: Customize your logic to match your specific requirements
- Access to external assets: Reference assets that aren't cataloged in the Data Integrity Suite.
Example query:
SELECT m.MATNR FROM dis.dis.MARM m JOIN dis.dis.MARA a ON a.MATNR=m.MATNR WHERE a.MTART='FERT' AND m.MEINH='TON'
In this example, several tables are assessed with the Join query. These include both cataloged tables available in the Data Integrity Suite's catalog and those tables that are not cataloged. The Join query evaluates all of these tables collectively.