Build your own data quality rules with AWS Glue DataBrew
Tags: aws, awsthai, bigdata, datascience
Published: 2021-11-21 09:53:31

In previous post, I have showed How AWS Glue DataBrew can help you to [Handling PII data](https://dev.to/awscommunity-asean/identifying-and-handling-personally-identifiable-information-pii-dwy-aws-glue-databrew-13ed) (The post is Thai language) For an English reader, the screenshot speaks for itself, you can easily follow.

There was a recent announcement AWS Glue DataBrew users can now develop data quality rules, which are customized validation tests that set business needs for specific data, according to the company. As a result, Any data person who does not want to invest in a high-cost DQ licensing product or does not want to use an open-source framework that requires coding knowledge can define their own quality rules and populate them in a data quality dashboard and validation report, allowing customers to quickly view rule outcomes and determine whether their data is fit for use. As a Builder, with New two announcement AWS GlueDataBrew with [DQ](https://aws.amazon.com/about-aws/whats-new/2021/11/aws-glue-databrew-data-quality-rules-validate-requirements/), and [PII](https://dev.to/awscommunity-asean/identifying-and-handling-personally-identifiable-information-pii-dwy-aws-glue-databrew-13ed) Handing will help us a lot in term of design DQ process at scale, as it's no compute server to maintenance. In this post I will walk you though How to do validate your data quality with AWS Glue DataBrew.


[What is AWS Glue Databrew?]
(https://aws.amazon.com/glue/features/databrew/)
[What do you mean by Data Quality rule?]
(https://docs.aws.amazon.com/databrew/latest/dg/profile.data-quality-rules.html)


## Table Of Contents
* [Architecture Diagram] (#arc)
* [Pre-requisites] (#pre)
* [Preparing dataset] (#load)
* [Run data profile without DQ ruleset] (#profile-nodq)
* [Create DQ ruleset] (#dq)
* [Re-run data profile with DQ ruleset] (#profile-dq)
* [View DQ dashboard] (#dqboard)
* [Conclusion] (#conclusion)



### Architecture Diagram <a name="arc"></a>
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x144lnkhg1a3w9j505kz.png)


### Pre-requisites <a name="pre"></a>
* AWS Account❗ 
* Download Data [here] (https://synthetichealth.github.io/synthea-sample-data/downloads/10k_synthea_covid19_csv.zip)
* Unzip, and upload only patient.csv upload to Amazon S3.



### Preparing dataset <a name="load"></a>
Go to AWS Glue DataBrew console [here](https://console.aws.amazon.com/databrew/home?region=us-east-1#landing)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wyfcw6a1cjysiamnkpkq.png)

On the left you click [Datasets](https://console.aws.amazon.com/databrew/home?region=us-east-1#datasets) to create Dataset for Glue DataBrew  
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tlukia8aa3cgs4e9e087.png)

click [Create new dataset](https://console.aws.amazon.com/databrew/home?region=us-east-1#create-dataset)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s60ps64thufsqa2tawbd.png)

Choose patient.csv that you just uploaded, you also have an option to get the data from Glue data catalog, Amazon Redshift, Appflow, or Snowflake as well. Click Create dataset
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6fao7lcm7d5lm592lvqu.png)



### Run data profile <a name="profile-nodq"></a>
At Datasets, choose Patient dataset, and Click "Run data profile"
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k60w63dmvio4aik2riuy.png)

You will wait few minutes for data profile to populate Data profile overview, Column statistics, Data quality rules suggestion, and Data lineage, once it's done click "View data profile"
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s8dg7hq1ufjj0lr6ievc.png)


You expect to see all the data statistics that useful to understand your dataset as follow

Dataset preview
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uny4jbbp887qmmzd9tt7.png)

Data profile overview
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ylkk2a3i2yinkg4e7o0s.png)

Potential PII detection
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z7ist9hg4djkhlnjmx1c.png)

Attribute Correlations
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jm2onqc8l1spipkqldxk.png)

Individual Column level statistic
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/997mdy45rhim9knhqiqz.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yv10deneqjqmdfpa9l8b.png)



All Columns level statistic summary such as min, max, distribution, column type, unique value, and etc
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bd3p3iv39js8od25a5k7.png)

You can also drill down to columns that identified as PII as well in All Column level statistic
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y1jv7e8ktzasd5yepy5n.png)

You can also see Data lineage as well!
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uw1cn1dv81927bdbboip.png)

Enough for Data exploration. The purpose of this post related to Data quality, let's take a look on the Data quality tab, you will see nothing, as you don't have a rule yet! but after you run Data profile job, it suggest you based on standard DQ such as uniqueness, completeness, and etc on the right hand side
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9ii96dm1c9gk7vhkftwj.png)

### Create DQ ruleset <a name="dq"></a>

You can pick what that reasonable to your business criteria, Again there's zero code require! I choose to check uniqueness in my Id, SSN, and length for SSN, and etc
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v06n8s2xx1tvzdxbzf14.png) 

Again this is recommendation from AWS DataBrew. you can create your own DQ rule later, after I have done, I just click "Create ruleset"

I can add, remove, adjust, or review DQ rule I have just added
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wvy8rsd78ciqakxwtpzs.png)

I just click "Create ruleset", I can see DQ rulesets [here](https://console.aws.amazon.com/databrew/home?region=us-east-1#rulesets)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nhhmuc0pcgvy8a45imv1.png)

click DQ rulesets name, in my case is "DQ Rulesets for Patient data" I should be able to review all my DQ rules.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oyq04btu6zhbe1t6j3te.png)

Associate DQ rulesets, with Data profile job, Click [here](https://console.aws.amazon.com/databrew/home?region=us-east-1#jobs?tab=profile) to see all your Data profile job

For my case it's "Patients - Data Profile job, choose, and click Actions, and Edit
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jbwjfvu5b6eyk9d4rjf7.png)

Search for "Data quality rules", and click "Apply data quality ruleset"
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/issvxe4yvygqn03bbdjl.png)

Choose your DQ Rulesets, and click Apply selected rulesets, and click Save
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p7e9akxchdw04fip2p75.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dj76mjzokdoir68d6wv7.png)



### Re-run data profile with DQ ruleset <a name="profile-dq">
You choose your Profile jobs, and Click "Run job"
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0xt2egkjawrglavdclu0.png)

### View DQ dashboard <a name="dqboard"></a>
Wait a few minutes, you should be able to see Result from Data profile job, with DQ result, based on your DQ rules!
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kvwj7gdmgzul1eql52tf.png)
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h7rjjgs1x9llhjbxvaju.png)

My dataset has 4 pass, with 6 failed, based on My DQ rules, obviously I have to fix my data, before downstream process will consume!
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7s9gd0ul0uvx2egupzbg.png)



### Conclusion <a name="conclusion"></a>
AWS Glue DataBrew is a visual data preparation tool that makes it easy to clean and normalize data using over 250 pre-built transformations, all without the need to write any code, with New feature for DQ, and [PII handing](https://dev.to/awscommunity-asean/identifying-and-handling-personally-identifiable-information-pii-dwy-aws-glue-databrew-13ed) it will help you to add your own data quality rules, and PII tokenization in your automated data pipelines (AWS managed airflow, or AWS step function) to make sure your data are clean, and protected.

You can think about How add to PII obfuscate, and DQ zone like this
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jezuvlc1g7sa4e4scjop.png)

![good_stuff](https://i.pinimg.com/originals/88/71/3c/88713c217a98cac0d46781b38f11c019.gif)