Present information reliability in Amazon Redshift at scale utilizing Nice Expectations library


Guaranteeing information reliability is among the key aims of sustaining information integrity and is essential for constructing information belief throughout a company. Information reliability signifies that the info is full and correct. It’s the catalyst for delivering trusted information analytics and insights. Incomplete or inaccurate information leads enterprise leaders and information analysts to make poor selections, which may result in destructive downstream impacts and subsequently could lead to groups spending precious money and time correcting the info afterward. Subsequently, it’s at all times a greatest apply to run information reliability checks earlier than loading the info into any targets like Amazon Redshift, Amazon DynamoDB, or Amazon Timestream databases.

This submit discusses an answer for working information reliability checks earlier than loading the info right into a goal desk in Amazon Redshift utilizing the open-source library Nice Expectations. You’ll be able to automate the method for information checks by way of the in depth built-in Nice Expectations glossary of guidelines utilizing PySpark, and it’s versatile for including or creating new personalized guidelines in your use case.

Amazon Redshift is a cloud information warehouse resolution and delivers as much as 3 times higher price-performance than different cloud information warehouses. With Amazon Redshift, you may question and mix exabytes of structured and semi-structured information throughout your information warehouse, operational database, and information lake utilizing customary SQL. Amazon Redshift allows you to save the outcomes of your queries again to your Amazon Easy Storage Service (Amazon S3) information lake utilizing open codecs like Apache Parquet, to be able to carry out further analytics from different analytics providers like Amazon EMR, Amazon Athena, and Amazon SageMaker.

Nice Expectations (GE) is an open-source library and is out there in GitHub for public use. It helps information groups remove pipeline debt by information testing, documentation, and profiling. Nice Expectations helps construct belief, confidence, and integrity of knowledge throughout information engineering and information science groups in your group. GE presents a wide range of expectations builders can configure. The device defines expectations as statements describing verifiable properties of a dataset. Not solely does it provide a glossary of greater than 50 built-in expectations, it additionally permits information engineers and scientists to put in writing customized expectation capabilities.

Use case overview

Earlier than performing analytics or constructing machine studying (ML) fashions, cleansing information can take up loads of time within the undertaking cycle. With out automated and systematic information high quality checks, we could spend most of our time cleansing information and hand-coding one-off high quality checks. As most information engineers and scientists know, this course of may be each tedious and error-prone.

Having an automatic high quality test system is essential to undertaking effectivity and information integrity. Such programs assist us perceive information high quality expectations and the enterprise guidelines behind them, know what to anticipate in our information evaluation, and make speaking the info’s intricacies a lot simpler. For instance, in a uncooked dataset of buyer profiles of a enterprise, if there’s a column for date of start in format YYYY-mm-dd, values like 1000-09-01 can be appropriately parsed as a date sort. Nevertheless, logically this worth can be incorrect in 2021, as a result of the age of the individual can be 1021 years, which is unimaginable.

One other use case could possibly be to make use of GE for streaming analytics, the place you need to use AWS Database Migration Service (AWS DMS) emigrate a relational database administration system. AWS DMS can export change information seize (CDC) information in Parquet format to Amazon S3, the place these information can then be cleansed by an AWS Glue job utilizing GE and written to both a vacation spot bucket for Athena consumption or the rows may be streamed in AVRO format to Amazon Kinesis or Kafka.

Moreover, automated information high quality checks may be versioned and likewise convey profit within the type of optimum information monitoring and diminished human intervention. Information lineage in an automatic information high quality system may point out at which stage within the information pipeline the errors have been launched, which may also help inform enhancements in upstream programs.

Resolution structure

This submit comes with a ready-to-use blueprint that mechanically provisions the required infrastructure and spins up a SageMaker pocket book that walks you step-by-step by the answer. Moreover, it enforces the very best practices in information DevOps and infrastructure as code. The next diagram illustrates the answer structure.

The structure accommodates the next elements:

  1. Information lake – After we run the AWS CloudFormation stack, an open-source pattern dataset in CSV format is copied to an S3 bucket in your account. As an output of the answer, the info vacation spot is an S3 bucket. This vacation spot consists of two separate prefixes, every of which accommodates information in Parquet format, to tell apart between accepted and rejected information.
  2. DynamoDB – The CloudFormation stack persists information high quality expectations in a DynamoDB desk. 4 predefined column expectations are populated by the stack in a desk known as redshift-ge-dq-dynamo-blog-rules. Other than the pre-populated guidelines, you may add any rule from the Nice Expectations glossary based on the info mannequin showcased later within the submit.
  3. Information high quality processing – The answer makes use of a SageMaker pocket book occasion powered by Amazon EMR to course of the pattern dataset utilizing PySpark (v3.1.1) and Nice Expectations (v0.13.4). The pocket book is mechanically populated with the S3 bucket location and Amazon Redshift cluster identifier by way of the SageMaker lifecycle config provisioned by AWS CloudFormation.
  4. Amazon Redshift – We create inner and exterior tables in Amazon Redshift for the accepted and rejected datasets produced from processing the pattern dataset. The exterior dq_rejected.monster_com_rejected desk, for rejected information, makes use of Amazon Redshift Spectrum and creates an exterior database within the AWS Glue Information Catalog to reference the desk. The dq_accepted.monster_com desk is created as a daily Amazon Redshift desk through the use of the COPY command.

Pattern dataset

As a part of this submit, we’ve got carried out exams on the job candidates pattern dataset to show the info reliability checks utilizing the Nice Expectations library and loading information into an Amazon Redshift desk.

The dataset accommodates practically 22,000 completely different pattern data with the next columns:

  • nation
  • country_code
  • date_added
  • has_expired
  • job_board
  • job_description
  • job_title
  • job_type
  • location
  • group
  • page_url
  • wage
  • sector
  • uniq_id

For this submit, we’ve got chosen 4 columns with inconsistent or soiled information, specifically group, job_type, uniq_id, and location, whose inconsistencies are flagged based on the foundations we outline from the GE glossary as described later within the submit.


For this resolution, it is best to have the next stipulations:

  • An AWS account should you don’t have one already. For directions, see Signal Up for AWS.
  • For this submit, you may launch the CloudFormation stack within the following Areas:
    • us-east-1
    • us-east-2
    • us-west-1
    • us-west-2
  • An AWS Id and Entry Administration (IAM) consumer. For directions, see Create an IAM Consumer.
  • The consumer ought to have create, write, and browse entry for the next AWS providers:
  • Familiarity with Nice Expectations and PySpark.

Arrange the surroundings

Select Launch Stack to start out creating the required AWS assets for the pocket book walkthrough:

For extra details about Amazon Redshift cluster node varieties, see Overview of Amazon Redshift clusters. For the kind of workflow described on this submit, we advocate utilizing the RA3 Occasion Sort household.

Run the notebooks

When the CloudFormation stack is full, full the next steps to run the notebooks:

  1. On the SageMaker console, select Pocket book situations within the navigation pane.

This opens the pocket book situations in your Area. You need to see a pocket book titled redshift-ge-dq-EMR-blog-notebook.

  1. Select Open Jupyter subsequent to this pocket book to open the Jupyter pocket book interface.

You need to see the Jupyter pocket book file titled ge-redshift.ipynb.

  1. Select the file to open the pocket book and comply with the steps to run the answer.

Run configurations to create a PySpark context

When the pocket book is open, make sure that the kernel is about to Sparkmagic (PySpark). Run the next block to arrange Spark configs for a Spark context.

Create a Nice Expectations context

In Nice Expectations, your information context manages your undertaking configuration. We create a knowledge context for our resolution by passing our S3 bucket location. The S3 bucket’s identify, created by the stack, ought to already be populated inside the cell block. Run the next block to create a context:

from great_expectations.data_context.varieties.base import DataContextConfig,DatasourceConfig,S3StoreBackendDefaults
from great_expectations.data_context import BaseDataContext

bucket_prefix = "ge-redshift-data-quality-blog"
bucket_name = "ge-redshift-data-quality-blog-region-account_id"
region_name="-".be a part of(bucket_name.substitute(bucket_prefix,'').break up('-')[1:4])
project_config = DataContextConfig(
        "my_spark_datasource": {
            "data_asset_type": {
                "class_name": "SparkDFDataset",//Setting dataset sort to Spark
                "module_name": "great_expectations.dataset",
            "spark_config": dict(spark.sparkContext.getConf().getAll()) //Passing Spark Session configs,
            "class_name": "SparkDFDatasource",
            "module_name": "great_expectations.datasource"
context = BaseDataContext(project_config=project_config)

For extra particulars on making a GE context, see Getting began with Nice Expectations.

Get GE validation guidelines from DynamoDB

Our CloudFormation stack created a DynamoDB desk with prepopulated rows of expectations. The information mannequin in DynamoDB describes the properties associated to every dataset and its columns and the variety of expectations you wish to configure for every column. The next code describes an instance of the info mannequin for the column group:

 "id": "job_reqs-organization", 
 "dataset_name": "job_reqs", 
 "guidelines": [ //list of expectations to apply to this column
   "kwargs": COMPLETE,
   "name": "expect_column_values_to_not_be_null",//name of GE expectation   "reject_msg": "REJECT:null_values_found_in_organization"
 "column_name": "group"

The code accommodates the next parameters:

  • id – Distinctive ID of the doc
  • dataset_name – Identify of the dataset, for instance monster_com
  • guidelines – Record of GE expectations to use:
    • kwargs – Parameters to cross to a person expectation
    • identify – Identify of the expectation from the GE glossary
    • reject_msg – String to flag for any row that doesn’t cross this expectation
  • column_name – Identify of dataset column to run the expectations on

Every column can have a number of expectations related that it must cross. You can even add expectations for extra columns or to current columns by following the info mannequin proven earlier. With this system, you may automate verification of any variety of information high quality guidelines in your datasets with out performing any code change. Other than its flexibility, what makes GE highly effective is the flexibility to create customized expectations if the GE glossary doesn’t cowl your use case. For extra particulars on creating customized expectations, see create customized Expectations.

Now run the cell block to fetch the GE guidelines from the DynamoDB consumer:

  1. Learn the pattern dataset and cross by validation guidelines.

After we’ve got the expectations fetched from DynamoDB, we will learn the uncooked CSV dataset. This dataset ought to already be copied to your S3 bucket location by the CloudFormation stack. You need to see the next output after studying the CSV as a Spark DataFrame.

To guage whether or not a row passes every column’s expectations, we have to cross the required columns to a Spark user-defined perform. This UDF evaluates every row within the DataFrame and appends the outcomes of every expectation to a feedback column.

Rows that cross all column expectations have a null worth within the feedback column.

A row that fails not less than one column expectation is flagged with the string format REJECT:reject_msg_from_dynamo. For instance, if a row has a null worth within the group column, then based on the foundations outlined in DynamoDB, the feedback column is populated by the UDF as REJECT:null_values_found_in_organization.

The method with which the UDF perform acknowledges a probably faulty column is finished by evaluating the end result dictionary generated by the Nice Expectations library. The era and construction of this dictionary depends upon the key phrase argument of result_format. Briefly, if the rely of sudden column values of any column is bigger than zero, we flag that as a rejected row.

  1. Break up the ensuing dataset into accepted and rejected DataFrames.

Now that we’ve got all of the rejected rows flagged within the supply DataFrame inside the feedback column, we will use this property to separate the unique dataset into accepted and rejected DataFrames. Within the earlier step, we talked about that we append an motion message within the feedback column for every failed expectation in a row. With this truth, we will choose rejected rows that begin with the string REJECT (alternatively, you may also filter by non-null values within the feedback column to get the accepted rows). When we’ve got the set of rejected rows, we will get the accepted rows as a separate DataFrame through the use of the next PySpark besides perform.

Write the DataFrames to Amazon S3.

Now that we’ve got the unique DataFrame divided, we will write them each to Amazon S3 in Parquet format. We have to write the accepted DataFrame with out the feedback column as a result of it’s solely added to flag rejected rows. Run the cell blocks to put in writing the Parquet information below acceptable prefixes as proven within the following screenshot.

Copy the accepted dataset to an Amazon Redshift desk

Now that we’ve got written the accepted dataset, we will use the Amazon Redshift COPY command to load this dataset into an Amazon Redshift desk. The pocket book outlines the steps required to create a desk for the accepted dataset in Amazon Redshift utilizing the Amazon Redshift Information API. After the desk is created efficiently, we will run the COPY command.

One other noteworthy level to say is that one of many benefits that we witness as a result of information high quality method described on this submit is that the Amazon Redshift COPY command doesn’t fail resulting from schema or datatype errors for the columns, which have clear expectations outlined that match the schema. Equally, you may outline expectations for each column within the desk that satisfies the schema constraints and may be thought of a dq_accepted.monster_com row.

Create an exterior desk in Amazon Redshift for rejected information

We have to have the rejected rows out there to us in Amazon Redshift for comparative evaluation. These comparative analyses may also help inform upstream programs concerning the standard of knowledge being collected and the way they are often corrected to enhance the general high quality of knowledge. Nevertheless, it isn’t smart to retailer the rejected information on the Amazon Redshift cluster, notably for big tables, as a result of it occupies additional disk area and enhance value. As a substitute, we use Redshift Spectrum to register an exterior desk in an exterior schema in Amazon Redshift. The exterior schema lives in an exterior database within the AWS Glue Information Catalog and is referenced by Amazon Redshift. The next screenshot outlines the steps to create an exterior desk.

Confirm and evaluate the datasets in Amazon Redshift.

12,160 data acquired processed efficiently out of a complete of twenty-two,000 from the enter dataset, and have been loaded to the monster_com desk below the dq_accepted schema. These data efficiently handed all of the validation guidelines configured in DynamoDB.

A complete 9,840 data acquired rejected resulting from breaking of a number of guidelines configured in DynamoDB and loaded to the monster_com_rejected desk within the dq_rejected schema. On this part, we describe the habits of every expectation on the dataset.

  • Count on column values to not be null in group – This rule is configured to reject a row if the group is null. The next question returns the pattern of rows, from the dq_rejected.monster_com_rejected desk, which are null within the group column, with their reject message.
  • Count on column values to match the regex record in job_type – This rule expects the column entries to be strings that may be matched to both any of or all of a listing of normal expressions. In our use case, we’ve got solely allowed values that match a sample inside [".*Full.*Time", ".*Part.*Time", ".*Contract.*"].
  • The next question exhibits rows which are rejected resulting from an invalid job sort.

Many of the data have been rejected with a number of causes, and all these mismatches are captured below the feedback column.

  • Count on column values to not match regex for uniq_id – Just like the earlier rule, this rule goals to reject any row whose worth matches a sure sample. In our case, that sample is having an empty area (s++) within the main column uniq_id. This implies we take into account a worth to be invalid if it has empty areas within the string. The next question returned an invalid format for uniq_id.
  • Count on column entries to be strings with a size between a minimal worth and a most worth (inclusive) – A size test rule is outlined within the DynamoDB desk for the location column. This rule rejects values or rows if the size of the worth violates the required constraints. The next
  • question returns the data which are rejected resulting from a rule violation within the location column.

You’ll be able to proceed to research the opposite columns’ predefined guidelines from DynamoDB or decide any rule from the GE glossary and add it to an current column. Rerun the pocket book to see the results of your information high quality guidelines in Amazon Redshift. As talked about earlier, you may also strive creating customized expectations for different columns.

Advantages and limitations

The effectivity and efficacy of this method is delineated from the truth that GE allows automation and configurability to an intensive diploma in comparison with different approaches. A really brute drive various to this could possibly be writing saved procedures in Amazon Redshift that may carry out information high quality checks on staging tables earlier than information is loaded into most important tables. Nevertheless, this method may not be scalable as a result of you may’t persist repeatable guidelines for various columns, as continued right here in DynamoDB, in saved procedures (or name DynamoDB APIs), and must write and retailer a rule for every column of each desk. Moreover, to just accept or reject a row based mostly on a single rule requires complicated SQL statements which will lead to longer durations for information high quality checks or much more compute energy, which may additionally incur additional prices. With GE, a knowledge high quality rule is generic, repeatable, and scalable throughout completely different datasets.

One other good thing about this method, associated to utilizing GE, is that it helps a number of Python-based backends, together with Spark, Pandas, and Dask. This supplies flexibility throughout a company the place groups may need abilities in several frameworks. If a knowledge scientist prefers utilizing Pandas to put in writing their ML pipeline characteristic high quality check, then a knowledge engineer utilizing PySpark can use the identical code base to increase these exams as a result of consistency of GE throughout backends.

Moreover, GE is written natively in Python, which implies it’s choice for engineers and scientists who’re extra used to working their extract, rework, and cargo (ETL) workloads in PySpark compared to frameworks like Deequ, which is natively written in Scala over Apache Spark and matches higher for Scala use instances (the Python interface, PyDeequ, can be out there). One other good thing about utilizing GE is the flexibility to run multi-column unit exams on information, whereas Deequ doesn’t assist that (as of this writing).

Nevertheless, the method described on this submit may not be essentially the most performant in some instances for full desk load batch reads for very giant tables. That is as a result of serde (serialization/deserialization) value of utilizing UDFs. As a result of the GE capabilities are embedded in PySpark UDFs, the efficiency of those capabilities is slower than native Spark capabilities. Subsequently, this method offers the very best efficiency when built-in with incremental information processing workflows, for instance utilizing AWS DMS to put in writing CDC information from a supply database to Amazon S3.

Clear up

A few of the assets deployed on this submit, together with these deployed utilizing the supplied CloudFormation template, incur prices so long as they’re in use. Make sure to take away the assets and clear up your work once you’re completed with a view to keep away from pointless value.

Go to the CloudFormation console and click on the ‘delete stack’ to take away all assets.

The assets within the CloudFormation template aren’t manufacturing prepared. If you need to make use of this resolution in manufacturing, allow logging for all S3 buckets and make sure the resolution adheres to your group’s encryption insurance policies by EMR Safety Finest Practices.


On this submit, we demonstrated how one can automate information reliability checks utilizing the Nice Expectations library earlier than loading information into an Amazon Redshift desk. We additionally confirmed how you need to use Redshift Spectrum to create exterior tables. If soiled information have been to make its method into the accepted desk, all downstream shoppers similar to enterprise intelligence reporting, superior analytics, and ML pipelines can get affected and produce inaccurate reviews and outcomes. The tendencies of such information can generate improper leads for enterprise leaders whereas making enterprise selections. Moreover, flagging soiled information as rejected earlier than loading into Amazon Redshift additionally helps cut back the effort and time a knowledge engineer may need to spend with a view to examine and proper the info.

We have an interest to listen to the way you wish to apply this resolution in your use case. Please share your ideas and questions within the feedback part.

In regards to the Authors

Faizan Ahmed is a Information Architect at AWS Skilled Companies. He likes to construct information lakes and self-service analytics platforms for his prospects. He additionally enjoys studying new applied sciences and fixing, automating, and simplifying buyer issues with easy-to-use cloud information options on AWS. In his free time, Faizan enjoys touring, sports activities, and studying.

Bharath Kumar Boggarapu is a Information Architect at AWS Skilled Companies with experience in massive information applied sciences. He’s captivated with serving to prospects construct performant and strong data-driven options and understand their information and analytics potential. His areas of pursuits are open-source frameworks, automation, and information architecting. In his free time, he likes to spend time with household, play tennis, and journey.