Creating rule instances

The Dataedo data quality feature offers more than 80 predefined rules that can be applied to columns in tables from supported connectors, ensuring your data meets specific quality standards. The key components of Dataedo data quality feature are Rules and Instances:

Rule: A rule defines the criteria for what constitutes "good" or “acceptable” data. For example, the "allowed values" rule checks whether column values match a predefined list you provide. These rules serve as essential guidelines for effective data validation.
Instance: An instance refers to the application of a rule to a specific column in your dataset. It represents the evaluation of data based on the parameters you define for that column.

In Portal, you can select a rule, assign it to a column, and view the results for the corresponding instance. A single column can have multiple rules, but each rule must be assigned individually, as each may require different parameters. Additionally, you can edit or remove rules independently, providing flexibility in how they are managed.

Creating instance entry points

You can create an instance in Dataedo portal using three different methods:

Method 1: From a column

Search for the column you want to assign rules to.
Navigate to its Data Quality tab.
Click + Add Data Quality check and choose Assign rule.
The column is preselected in the Select objects step. Click Next to choose a rule.

Method 2: From a table

Find the table you're interested in.
Navigate to its Data Quality tab.
Click + Add Data Quality check and choose Assign rule.
Select one or more columns from the table, then choose the rule you want to apply.

From the Data Quality tab

Navigate to the Data Quality tab in the main menu.
Go to the Rule Instances tab.
Click + Add Data Quality check and choose Assign rule.
In the Select objects step, select a data source and one or more columns, then choose a rule.

Once you've chosen the method to create your rule instance, you’ll move on to assigning the rule.

Assigning a rule

If you want to create a custom SQL rule, check out the Custom SQL rules guide.

Step 1: Select a rule

After selecting a column, the first step is to choose a rule. Each rule has the following attributes:

Name and description: Explains what the rule checks.
Library: The rule belongs to a specific library. In the future, you'll be able to create your custom rule library.
Applicable column types:
- All: Can be assigned to any column type.
- Text: For string-type columns only.
- Date: For date-type columns only.

info

If there are any warnings (for example, rule compatibility issues with an older connector version, missing native support, or data types a rule can't run on, such as Oracle CLOB and NCLOB columns), you’ll see them here.

Step 2: Parameters and filters

Some rules require additional parameters to function, while others don't. For example:

Not null: No extra parameters are required; it simply checks if the selected column contains any null values.
Allowed values: Requires a list of valid values. The rule checks if the column data matches this list.
Value range: Requires defining a minimum and maximum value. Any data outside this range will be flagged.

After entering the required parameters, an optional Filter field will appear. This parameter allows you to limit which records are checked in the instance. For example:

You might skip checking email validity for records created before 2015, when your company started email validation.
You might want to only check invoices marked as high priority.

To apply a filter, you can create a query specific to the records you want to include. Filter syntax may vary depending on your connector. For example:

For SQL Server: Use [priority] = 1.
For MySQL: Use priority = 1.

info

Make sure to use the correct syntax supported by your connector.

In applying a rule instance to a text column, you might see a Treat whitespace values as passing rows checkbox. It's turned off by default, so empty strings and values made only of spaces are checked by the same rules as other values (eg: an empty value fails Valid email address). If you select this checkbox, whitespaces will be automatically treated as passing rows instead. The checkbox isn't available for Not empty string, date rules, and custom SQL rules.

The Parameters and filters step also lets you preview the queries used to check the column's data. On the right-hand side, open the Query preview tab and use the switch to view two types of queries:

Raw rule query: Displays the rule's definition with parameter placeholders.
Instance rule query: Shows the query with the table, column names, and applied parameters and filters.

The Rule test tab next to it lets you run the rule on your data before saving it. See Testing a rule before saving.

Step 3: Failed Rows

By default, Dataedo collects only numeric statistics about your data quality (e.g., how many rows were tested and how many passed or failed). In this step, you can choose to save the failed rows for easier identification and resolution.

If you activate this option, Dataedo saves some of the failed rows. The number of saved rows is 1000 by default, however you can increase it up to one million by adjusting the Amount of saved failed rows parameter.

The ID column(s) are required to uniquely identify each row. This field is prefilled with the Primary Key of the selected table. If there's no Primary Key, you'll need to specify one or more columns as unique identifiers.

The second field in this step is Additional columns, where you can list other columns to help identify the failed rows. Besides the rule's own table, you can pick columns from tables that it references with a foreign key (grouped by table, for example accounts.name next to account_prices.account_id). You can also sort the results by one or more specific columns.

There is also a source link field. You can use it to configure a link that will take you straight to your original Data Source if any failing rows are detected.

The link should be constructed by adding the URL to your original data source or its location in your CRM software, and the name of the row that contains the potentially failing cell. Typing @ in the textbox, brings up a list of available columns. If your Data Management software has a separate state for edition, you can add a suffix (for example: /edit) at the end of the link.

There is no need to input the rowID, it will be automatically supplied by Dataedo using available data.

The configured link will appear in the Failed Rows grid. You can use it to quickly fix the issue in your source.

info

If a foreign key used for an additional column or the source link is later removed from the data source, the column is skipped and the run ends with the Failed rows column skipped warning instead of an error.

Step 4: Set Threshold

By default, Dataedo marks a rule instance as fail if even a single row does not meet the rule’s conditions. In some scenarios, this may be too restrictive. To allow for a margin of error, you can configure a threshold that determines when an instance should be marked as fail versus ok.

When creating a rule instance, you can enable threshold configuration using a toggle. Once activated, two types of thresholds are available:

Percentage threshold – The instance will be marked as fail if the number of failed rows exceeds the specified percentage (e.g., 5%).
Numeric threshold – The instance will be marked as fail if the number of failed rows exceeds a fixed number (e.g., 100 rows).

If no threshold is set, the default behavior applies: even one failed row results in a fail status.

Thresholds can be edited at any time after saving the instance. You can also apply them in bulk to multiple existing instances, saving time when adjusting rules across larger datasets.

Step 5: Settings

The final step is to set up the instance. Here, you can choose the state of the instance:

Active: The rule will run during every scheduled Data Quality check.
Draft: The rule will be created but will only run once it's set to active. You can change the state at any time by editing the instance.

You can also define the severity of the instance, which helps prioritize rule runs. For example, you might schedule critical rules to run daily, while lower-severity rules could run weekly. Finally, the Instance description field lets you note important details, such as when a filter is applied. This helps business users understand that the rule only checks a specific subset of data.

Adding rules in bulk

If you assign a rule to multiple objects at once, you'll land on the rule instances list showing only the instances you just created, marked with the Created just now (N) filter. Click X on the filter to see all instances. The filter is temporary and disappears as soon as you refresh or leave the page.

Testing a rule before saving

info

Test Runs for Data Quality have the same prerequisites as regular Data Quality runs

You can test a rule instance on your data while you're still creating or editing it. To do so, open the Rule test tab on the right-hand side of the Parameters and filters or Failed rows step and click Test rule. For custom SQL rules, the test is available on the Definition step.

The test run is only a preview. Once a test run finishes, it doesn't appear in the run history, nor saves any data in your repository.

Test results

When the test completes, you'll see the rule status (OK or Fail), the number of tested rows, the fail rate, and the number of successful and failed rows.

If running the test from the Failed rows step, or the Definition step of custom SQL rules, you'll also see a sample of failed rows:

Users without permission to view data will see the counts but not the samples.

Troubleshooting

There are two main reasons for a test not passing:

Test failed: the query returned an error. You'll see the database's own error message, and you can click Show executed SQL to see the query that failed.
Test couldn't run: the test didn't reach a result, for example because the Agent is unavailable, the test timed out, or the connection was lost.

If you click Next after Test failed without changing the parameters, the Query error in last test popup asks you to confirm, because the rule will fail every time it runs. Click Continue to move on anyway, or Cancel to stay on the step and fix the rule.

Cancelling and testing again

To stop a running test, click Cancel. This also stops the query on your data source.
If you change the rule parameters after a test, the results are marked with Parameters changed since last test. Click Test again to get new results.

Testing multiple objects

When you assign a rule to multiple columns or tables at once, the grids on the Parameters and filters and Failed rows steps have a Rule test column. Open the three-dot menu in its header and choose:

Test all to test every object.
Test selected (N) to test only the selected rows.
Cancel to stop the tests that are still running. Results already obtained are kept.

Objects are tested one at a time. Each row shows its status. Click Details to see the full result, or Test again to rerun the test for that object.

Restricting who can test rules

Each test runs queries on your data source. On large or pay-per-query sources, frequent tests may increase your costs. By default, every user who can edit rules can also test them, but you can optionally limit testing to users with the Connection Manager role.

To do this go to Settings > System settings > General, and find the Data Access section. Once there, disable the allow all rule editors to test rules toggle.

Allow all rule editors to test rules toggle

Click Save to remember this choice.

With this option turned off, only Connection Managers can test rules.

Rule Examples

Dataedo offers over 90 built-in Data Quality rules, which can be freely reused by users when creating rule instances. Those rules cover most basic Data Quality scenarios, reducing the number of custom rules and extra work needed from your Data Stewards.

To give you a feel of what kinds of rules are available, let us provide a few examples:

Allowed Values -> Checks if all of column's values belong to a predefined set of allowed values. You can either use a closed list, or create a dynamic one using SQL queries

Is Fresh -> Checks if the column contains recent values. You define "recentness" by providing a number of days for which the cell is considered fresh (eg: last 7 days, or last 20 days, etc.)

Must be null -> Checks if the column's cell are null. Can help with detecting cells that should be empty but are not

No suspicious dates -> Checks if the column's cells contain well-formed, plausible dates

Row count between -> Checks if the Column's row number fits in a range defined by the User

Unique -> Checks if all of the column values are unique

Valid e-mail address -> Checks if values follow proper e-mail address formatting