Maria Pulawska
On top of creating rule instances from predefined rules, we added the option to create custom rules using SQL queries. This gives you the flexibility to test anything you need and design rules tailored to your specific requirements. Read on to learn how to grant permissions for creating custom SQL rules and how to set them up.
Permissions
Custom rules allow direct querying of your data. To ensure security and restrict access to authorized users, we’ve introduced a new role: Power Data Steward. This role is required to create and manage custom SQL queries. It includes all Data Steward capabilities, plus the additional Manage External Queries role action.
Creation flow
The entry point is the same as for predefined rules. Click the CTA button to create a rule instance, either in Data Governance → Data Quality → Rule Instances or directly on a table or column.
If you have the required Power Data Steward role, a popup will appear, prompting you to choose between a predefined rule from the rule list or a Custom SQL Rule. This time, select Custom SQL Rule.
The first step in creating a custom rule is defining the database and entering the SQL query.
Start by selecting the data source where the SQL rule will be applied. A dropdown menu will display the available data sources, restricted to those permitted for Data Quality execution.
Next, define the Custom SQL Rule by entering your SQL query in the provided text area. This input field allows you to enter a custom SQL query that defines the condition to be checked by the rule. The query you provide should return the rows that do not meet the specified data quality criteria, marking them as incorrect. The rule will be applied to the data filtered by the WHERE clause of your query. Any rows that are returned by the query will be flagged as failed during the rule execution, helping you identify and address data quality issues effectively.
Optionally, you can specify a Tested Rows Count Definition by entering a SQL query to count the rows that will be tested by the custom rule. This step helps in understanding the scope of the rule but is not required. This input field allows you to enter an optional SQL query to specify the number of rows your custom SQL rule will evaluate. The query should return the count of rows that meet the criteria defined in the WHERE clause of your custom SQL query. If this field is left blank, the rule will not include the number of rows in the results. This feature provides additional control over the scope of your rule evaluation.
Both fields include syntax highlighting for better readability and allows you to write complex queries without automatic validation, giving you full control over the SQL logic.
Next, select the Object Type to specify whether the rule applies to a column or a table.
If you select a column, suggested columns will be displayed along with input fields for both the table and column names. If you select a table, suggested tables will be shown with an input field to specify the table name.
Our parser analyzes the query you provided in the first step, identifying and matching existing tables and columns in the selected data source. In the Suggested objects based on your query section, you'll see these matched objects as recommendations for linking your rule to the appropriate table or column.
A search input allows you to quickly find the desired object, streamlining the selection process.
We've added the ability to save failed rows for custom SQL rules, giving you more control over your data quality checks.
By default, failed rows are not saved. To enable this feature, simply activate the Save Failed Rows option. This allows you to store rows that don’t meet your custom SQL rule criteria for further analysis.
The final step in creating a rule instance is similar to selecting one from the predefined rule list. The key difference is that you need to provide a name and an optional description for your custom rule.
Additionally, you’ll need to assign the rule to a Library, which helps you organize and group your rules based on your preferences.
