Hi all!

We hope you’re enjoying our DQ Rules best practice series and finding useful tips & tricks to implement in your day-to-day work. In this post, we will cover aggregation rules and give an overview of creating and configuring aggregation rules to unlock the grouping capabilities.

Aggregation rules in Ataccama's ONE can be used in various types of data quality rules, such as Validity, Uniqueness, Completeness, Timeliness, and Accuracy. Let's dive in!
 

How to create aggregation rules?

To create an aggregation rule, follow these steps:

1. Select "Aggregation rule" as the rule type in the ONE web application.

   

    

2. The "Group by" section will now be available, allowing you to define the grouping criteria.

You can create aggregation rules using either the condition builder or ONE expressions, both of which offer flexibility and ease of use. These methods allow you to select input attributes, apply transformations to create variables, and aggregate data based on the desired grouping.
 

No Code Aggregation Rules

Follow these steps to create a no-code aggregation rule:

1. Go to "Data Quality > Rules" in the ONE web application and select "Create."
2. Provide a name for your rule and add any additional information.
3. In the "Implementation" tab, define the input attributes and variables, along with their transformations if needed.
4. Select the rule type, such as Validity or Completeness.
5. Choose "Aggregation rule" from the drop-down menu.

   

    

6. A new section named "Group by" will appear. Use this section to group the data based on the desired attributes or variables.

   

    

7. Select the "Condition Builder" option to define the outcome of the rule based on the uniqueness of the group.

   

    

Advanced Expression Aggregation Rules

To create an advanced expression aggregation rule, follow these steps:

1. Go to "Data Quality > Rules" in the ONE web application and select "Create."
2. Provide a name for your rule and add any additional information.
3. In the "Implementation" tab, define the input attributes and variables, along with their transformations if needed.
4. Select the rule type, such as Validity or Completeness.
5. Choose "Aggregation rule" from the drop-down menu.

   

    

6. A new section named "Group by" will appear. Use this section to group the data based on the desired attributes or variables.
7. Switch from the "Condition Builder" to the "Advanced Expression" option.
8. You can now use aggregate functions in the expression field to perform calculations on the grouped data.
    

Use Cases

Here are three use cases that demonstrate how to configure aggregation rules for specific scenarios:

1. Unique Group: Determine if a certain combination of values in the table appears more than once.

   

    

2. avg Aggregate Function: Check if a transaction value is within 20% of the average transaction value for a customer.
3. sum Aggregate Function: Verify that the correct number of transactional records per day exists.
    

For each use case, the documentation provides step-by-step instructions on how to configure the aggregation rule and defines the expected outcome.
 

By following these best practices, you can effectively leverage aggregation rules in Ataccama's ONE web application to ensure data quality in your projects.

As always, let us know if you have any questions in the comments below