> For the complete documentation index, see [llms.txt](https://academy.dnanexus.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://academy.dnanexus.com/data-quality-manager/data-refinery.md).
# Data Refinery
The Data Refinery is where you define, organize, and apply remediation logic to improve data quality in a governed and repeatable way.
### Create New Rules
A rule defines what condition to check in the data and what action to take when that condition is met. Rules are the core building blocks for data remediation and can be reused across datasets.
Each rule has a status that helps you manage and govern its lifecycle. By default, a newly created rule is marked as Pending. You can update the status to Reviewed or Approved once the rule has been validated. This allows you to clearly label rules, track their readiness, and manage them more effectively across datasets and remediation runs.
#### SQL Rules
SQL Rules are remediation rules defined using SQL syntax. They give you fine-grained control to detect, fix, or transform data directly at the column or row level.
You need to define:
* A **condition** (SQL expression or WHERE clause) that identifies problematic records. Conditional expression is evaluated as DuckDB boolean expression.
* For example: `"patients".name = 'Patient_1'`
* An **action** (SQL update or expression) that specifies how the data should be corrected using DuckDB expression syntax.
* For example: `"visits".visit_date = '2026-01-01'`
These rules are applied to a specific table and column(s), and follow standard SQL semantics.
SQL rules are best for
* Fixing missing or invalid values (e.g. replacing NULLs, correcting out-of-range values)
* Standardizing formats (dates, strings, numeric precision)
* Deduplicating records or resolving simple inconsistencies
* Applying custom, dataset-specific remediation logic
Typical use cases
* Set default values when a field is missing
* Normalize text fields (trim, lowercase, pattern-based fixes)
* Correct invalid dates or numeric ranges
SQL Rules are ideal when you want a quick, familiar way to define remediation logic using SQL syntax, leveraging prebuilt expressions powered by DuckDB.
#### Python Rules
Python Rules allow you to define custom remediation logic using Python, providing maximum flexibility for advanced data transformations that are not easily expressed using SQL or predefined rules.
A Python Rule operates on tables within a dataset and uses the Daft DataFrame API to read, transform, and write data.
Rule Structure:
Each Python Rule follows a standard structure:
* The `do_work` function is the main entry point where your transformation logic is implemented.
* The dataset is represented as a **graph**, where each table is a node.
* You can access a specific table using `graphlib.get_node`, then retrieve its data as a DataFrame using `datalib.get_node_data`.
* After applying transformations, you must write the updated DataFrame back using `datalib.set_node_data` .
Rule Template:
```
class MyTransform(Operator):
def do_work(self, task_info, execution_context):
graph = execution_context["graph"]
# Access any table via graphlib.get_node(graph, "table_name")
node = graphlib.get_node(graph, "my_table")
df = datalib.get_node_data(node)
# Write your transformation here using the Daft DataFrame API
datalib.set_node_data(node, df)
return True
```
#### Python UDF Rules
Python UDF (User-Defined Function) Rules allow you to implement custom row-level validation and remediation logic using Python. They are ideal for scenarios where SQL expressions are not sufficient, such as complex business logic, cross-table lookups, or custom algorithms.
When creating a Python UDF Rule, you only need to implement the function body. The app automatically wraps your code as:
`user_udf(values, graph)`
where:
* `values` – A list of values from the selected column, with one element for each row.
* `graph` – The execution graph representing the dataset, allowing access to other tables.
The following helper functions are available:
* `table_dict(name)` – Returns another table as a Python dictionary in the form `{column: [values]}.`
* `table_df(name)` – Returns another table as a Daft DataFrame for more advanced processing.
Python UDF Rules support two execution modes:
* **Count Violation** – Return a `list[bool]`, where each True indicates that the corresponding row violates the rule.
* **Remediate** – Return one output value for each input row. The returned values must have the same data type as the target column.
During development, you can use `print(...)` statements to debug your logic. Debug messages are displayed in the dry-run results and job logs with the `[PYTHON_UDF]` prefix.
Best Practice: When checking membership in a set, avoid expressions such as `allowed and value not in allowed`. Instead, use `len(allowed) > 0 and value not in allowed` to ensure consistent behavior during execution.
#### System Rules
System Rules are pre-built, standardized data processing actions that allow you to remediate common data quality issues without writing complex SQL or Python code. You simply select a target table and column, choose the desired built-in action, and configure the necessary parameters.
System Rules are categorized into the following functional areas:
**1. Data De-identification**
* Remove sensitive values: Clears or drops specific sensitive entries or PHI fields.
* Mask sensitive values: Obfuscates string values (e.g., replacing patient names or IDs with placeholders or masked patterns).
* Generalize sensitive values: Aggregates granular values into broader categories or ranges to preserve privacy.
**2. Text Cleanup**
* Trim & normalize case: Removes leading/trailing whitespace and standardizes text to uppercase, lowercase, or title case.
* Strip special characters: Cleans string fields by removing unwanted symbols or non-alphanumeric characters.
**3. Date & Time**
* Strip day & month (year only): Truncates dates to preserve only the birth or event year for privacy protection.
* Nullify future dates: Replaces invalid or impossible future timestamps with NULL.
* Standardize datetime format: Converts mixed date/time strings into a unified standard datetime format.
**4. Missing Values & Outliers**
* Fill missing categorical values: Imputes missing (NULL) values in categorical fields with a default indicator or mode.
* Clamp numeric outliers: Restricts extreme numeric values within minimum and maximum threshold boundaries (winsorization).
Typical Use Cases:
* Quickly masking PHI or redacting direct identifiers before exporting datasets.
* Standardizing date formats and string casing prior to running Apollo phenotypic ingestion.
* Handling missing values and capping extreme lab measurement outliers automatically.
#### Transformation Rules
Transformation Rules are used to map, join, or reshape data between source and target tables to align with a desired structure or schema.
They focus on data alignment rather than direct correction.
You need to define:
* Join options:
* We support the optional options: "select left columns" and "select right columns.
* The list of columns from the left/right table to include in the output.
* If not specified, all columns will be included.
* Actions:
* Add Column
* Remove Column
* Rename Column
* Select Table
* Select Columns
* Copy Table
These rules describe how data should flow and be transformed from source to target.
Below is an example of join options in a Transformation rule
```
[
{
"left table": "table_a",
"right table": "table_b",
"join type": "inner",
"left columns": ["col1"],
"right columns": ["col2"],
"output table name": "table_a_b"
},
{
"left table": "table_a_b",
"right table": "table_c",
"join type": "left",
"left columns": ["col_2"],
"right columns": ["col_3"],
"output table name": "table_a_b",
"inplace": true
}
]
```
Transformation rules are best for
* Harmonizing data across multiple tables or datasets
* Aligning source data with standardized models or schemas
* Preparing data for downstream analytics or reporting
Typical use cases
* Mapping raw clinical data to standardized domain tables
* Joining reference or lookup tables to enrich datasets
* Transforming source structures into analysis-ready formats
Transformation Rules are ideal when you need structural consistency and schema alignment across datasets.
### Create New Rule Sets
A Rule Set is a logical collection of rules that can be applied together to a dataset. Rule Sets help you organize remediation logic, reuse rules across datasets, and apply consistent data quality standards efficiently.
You can create a new Rule Set in two ways:
#### Option 1: Create from the Rule Sets Page
1. Navigate to Rule Sets.
2. Click New Rule Set.
3. Enter a name (and optional description) for the Rule Set.
4. Select the available rules that you want to include.
5. Click Save to create the Rule Set.
6. You can change the order of rules, add or remove rules in a rule set.
#### Option 2: Create from the Rule Library
1. Navigate to the Rule Library.
2. Select one or more existing rules.
3. Click Create Rule Set.
4. Provide a name (and optional description) for the new Rule Set.
5. Click Save.
6. You can change the order of rules, add or remove rules in a rule set.
Once created, Rule Sets can be reused, edited, and applied during Data Remediation runs, making it easier to manage complex remediation workflows and maintain consistency across jobs.
### Import and Export Rule Sets
You can export and import Rule Sets to reuse remediation logic across different projects.
* Export Rule Set: Allows you to download a Rule Set configuration in Excel (.xlsx), CSV, or JSON format.
* Import Rule Set: Allows you to upload a previously exported Rule Set.
This feature is especially useful for promoting rule development from a test environment to production. You can build and validate rules in a test project, export the finalized Rule Set, and then import it into a production project to ensure consistency and reduce rework.
You can export and import Rule Sets to reuse remediation logic