{"id":19096,"date":"2023-08-04T07:37:44","date_gmt":"2023-08-04T11:37:44","guid":{"rendered":"https:\/\/qxf2.com\/blog\/?p=19096"},"modified":"2023-08-04T07:37:44","modified_gmt":"2023-08-04T11:37:44","slug":"validating-data-easy-soda-core","status":"publish","type":"post","link":"https:\/\/qxf2.com\/blog\/validating-data-easy-soda-core\/","title":{"rendered":"Validating Data Made Easy: A Dive into Soda Core"},"content":{"rendered":"

This post is a hands-on example to help you start writing data quality checks with Soda Core<\/a>. We realize a lot of our readers are fairly new to the topic of writing data quality checks. So, in this post, we will also go over some of the most common data checks you might want to implement on your structured data. We hope the ideas and the practical guide in this post help you get started with writing data quality checks at your company.<\/p>\n

Qxf2<\/a> has noticed that writing data validation checks is becoming important skill for testers. Our experience with Great Expectations<\/a> has been phenomenal, and we’ve shared our insights through blog posts<\/a>. In order to expand our testing capabilities, we ventured into using Soda Core (previously known as Soda SQL) with another client in mid-2022. We successfully utilized Soda Core to validate data in a RedShift Database, which served as a crucial entry point into a complex data pipeline. <\/p>\n

About Soda Core<\/h3>\n

Soda Core<\/a> is an open-source SQL-based data auditing tool that helps users to analyze and audit their data. It is designed to be used with various databases, data warehouses, and data lakes, such as PostgreSQL, MySQL, Redshift, BigQuery, and Snowflake.<\/p>\n

\"this<\/a><\/p>\n

Some of the features of Soda Core include:
\na) Custom tests: You can write custom tests in SQL or Python to validate the quality of our data.<\/p>\n

b) Data profiling: Soda can generate data profiles for our tables, columns, and schemas to give you an overview of the data.<\/p>\n

c) Automated monitoring: You can set up soda to run tests automatically on a schedule and get alerts when a test fails.<\/p>\n

d) Data lineage tracking: Soda can track changes to our data and help you understand the impact of those changes on downstream processes.<\/p>\n

Setup to play along with this post<\/h3>\n

We created some synthetic data for you to play long. You can find the schema and the data in the form of a SQL dump file here<\/a>. Make sure that you import our data into your MySQL instance. The provided schema and data can be used to carry out practical exercises and gain experience. This will improve your understanding and abilities by enabling you to apply the ideas you have learned in a practical setting.<\/p>\n

We are using MySQL as the data source in this post. However, if you prefer a different data source, modify this section to suit your needs. You can find easily configuration instructions in the library documentation<\/a>. Additionally, make sure to set up the necessary credentials and connection parameters for your chosen data source. <\/p>\n

Run the following command to get the Python module for soda-core that connects to MySQL. <\/p>\n

 pip install soda-core-mysql <\/pre>\n
You are now set to play along with this post.<\/p>\n
Getting connected to a data source<\/h3>\n
After installing Soda Core, you must make a configuration.yml <\/strong>file with the necessary information so that Soda Core can connect to your data source (except Apache Spark DataFrames, which does not use a configuration YAML file).<\/p>\n
Set the value of a property that the configuration YAML file uses in a system variable that you can access from your command-line interface.<\/p>\n
\r\nexport MYSQL_USERNAME=\r\nexport MYSQL_PASSWORD=\r\n<\/pre>\n
By using the echo command, you can verify that the system can retrieve the value you entered.<\/p>\n
\r\necho $MYSQL_USERNAME\r\necho $MYSQL_PASSWORD\r\n<\/pre>\n
Set environment variable value in YAML configuration.<\/p>\n
 data_source movie_rentals:\r\n  type: mysql\r\n  host: 127.0.0.1\r\n  username: ${MYSQL_USERNAME}\r\n  password: ${MYSQL_PASSWORD}\r\n  port: 3306\r\n  database: movie_rentals <\/pre>\n
After saving the configuration YAML file, run a scan to verify that Soda Core successfully connects to your data source.<\/p>\n
\r\nsoda scan -d movie_rentals -c configuration.yml\r\n<\/pre>\n
Getting started with basic validations <\/h3>\n
Soda performs tests called “checks” when it scans a dataset in your data source. These checks are written using the Soda Checks Language (SodaCL) and are stored in the checks.yml file. <\/p>\n
As you work through this section, please pay attention to the kind of data quality checks we are writing. We took some pains to make sure that we show cover of the most common kind of checks. For example, almost everyone will have at least some checks for schema, duplicate data, invalid data, null data in certain columns, etc. You can consider using similar checks on your data sources. <\/p>\n
 1. Schema Validations <\/h4>\n
a). Validate for missing column<\/strong><\/p>\n
\r\n# Checks for schema validations\r\nchecks for dim_customer:\r\n  - schema:\r\n      fail:\r\n        when required column missing:\r\n          - first_name\r\n          - last_name\r\n          - email_id  \r\n<\/pre>\n
Now execute the schema validation check that was prepared. <\/p>\n
\r\nsoda scan -d movie_rentals -c configuration.yml check_schema_validations.yml  \r\n<\/pre>\n
\"this<\/a><\/p>\n
Let us consider dim_customer table schema lacks an address column. We’ll look at how to validate this. We’ve included the column in the code. <\/p>\n
\r\n# Checks for schema validations\r\nchecks for dim_customer:\r\n  - schema:\r\n      fail:\r\n        when required column missing:\r\n          - first_name\r\n          - last_name\r\n          - email_id  \r\n          - address\r\n<\/pre>\n
\"this<\/a><\/p>\n
b) Validate Forbidden column <\/strong>
\nWe can now verify if any restricted column exists in the schema. Instead of marking a test case as failed, we might issue a warning that the schema contains a banned column.<\/p>\n
\r\n# Checks for schema validations\r\nchecks for dim_customer:\r\n  - schema:\r\n      warn:\r\n        when forbidden column present: [credit_card]\r\n      fail:\r\n        when required column missing:\r\n          - first_name\r\n          - last_name\r\n          - email_id  \r\n<\/pre>\n
The test will succeed since the schema no longer has a column with that name. Therefore, we will change the schema to ensure that the test will issue a caution rather than a failure message. We will add the column “credit_card” to the schema . <\/p>\n
\r\nALTER TABLE movie_rentals.dim_customer\r\nADD COLUMN credit_card VARCHAR(200);\r\n<\/pre>\n
Run the test now, and see what happens.
\n\"This<\/a><\/p>\n
Revert the change , If you prefer not to receive this warning again.<\/p>\n
\r\nALTER TABLE movie_rentals.dim_customer\r\nDROP COLUMN credit_card;\r\n<\/pre>\n
c) Validate column types <\/strong>
\nNext, we’ll proceed to validate the column type within the schema.<\/p>\n
\"\"<\/a><\/p>\n
Add to checks yml for the column type validation . <\/p>\n
\r\n# Checks for schema validations\r\nchecks for dim_customer:\r\n  - schema:\r\n      warn:\r\n        when forbidden column present: [credit_card]\r\n\r\n      fail:\r\n        when required column missing:\r\n          - first_name\r\n          - last_name\r\n          - email_id                                      \r\n        when wrong column type:\r\n          active: boolean\r\n<\/pre>\n
\"This<\/a><\/p>\n
2. Missing metrics <\/h4>\n
To identify missing values in your dataset, use a missing metric in a check.
\nLet’s create a file named “check_missing_count_dim_actor.yml” where we will perform validation to identify any occurrences of null values in both the “first_name” and “last_name” columns.<\/p>\n
\r\n# Checks for missing metrics \r\nchecks for dim_actor:\r\n   - missing_count(first_name) = 0\r\n   - missing_count(last_name) = 0\r\n<\/pre>\n
\"This<\/a><\/p>\n
Based on the result set, it appears that there is a row that failed in each category. Let’s proceed to perform the query and confirm.<\/p>\n
\"This<\/a><\/p>\n
3. Missing percent<\/h4>\n
“missing percent” refers to the percentage of missing or null values in a specific column of a dataset. We are validating against the address column here . A high missing percent in a column may indicate data quality issues, which could impact data analysis and decision-making.<\/p>\n
\r\n# Checks for missing_percent validations\r\nchecks for dim_address:\r\n  - missing_percent(address) > 5%\r\n<\/pre>\n
\"This<\/a><\/p>\n
\"This<\/a><\/p>\n
4. Data Freshness <\/h4>\n
By performing this check, you may determine how recent the data in a dataset is by looking at the age of the row that was most recently added to a table. “freshness” will check with the current date and time . “Row count” will check the number of rows in the table. We have around 2000 rows in the table. Create a file check_data_freshness_fact_rental.yml<\/p>\n
\r\n# Checks for data freshness \r\nchecks for fact_rental:\r\n- row_count > 1000\r\n- freshness(last_update) < 5d\r\n<\/pre>\n
\"This<\/a><\/p>\n
5. Duplicate count<\/h4>\n
To check the duplicate count for the film_id column, add the metric in the check_missing_count_dim_actor.yml file, which was created step 2.<\/p>\n
\r\n# Checks for missing metrics and duplicates\r\nchecks for dim_actor:\r\n   - missing_count(first_name) = 0\r\n   - missing_count(last_name) = 0:\r\n      missing values: [NA, n\/a, '0']\r\n   \r\n    - duplicate_count(film_id):\r\n      warn: \r\n        when between 1 and 99\r\n      fail: when > 100\r\n<\/pre>\n
\"This<\/a><\/p>\n
6. Invalid counts <\/h4>\n
You will validate metrics that specify which data values or formats are acceptable or unacceptable in accordance with your own business requirements.<\/p>\n
\r\n# Checks for Invalid counts\r\nchecks for dim_customer:\r\n  - invalid_count(active) = 0:\r\n      valid values: ['0', '1']\r\n<\/pre>\n
\"This<\/a><\/p>\n
Soda Core can work together with data orchestration tools like Airflow<\/a> to set up and validate data checks on a schedule. By connecting to Soda Cloud,<\/a> which has various additional features, we can enhance its functionality. SodaCL<\/a> also provides numerous ready-to-use checks that are easy to understand and allows us to create our own metrics and modify checks using SQL queries.<\/p>\n
\n
Hire technical testers from Qxf2<\/h3>\n
Hire Qxf2<\/a> for top-notch technical testers who excel in diverse testing challenges. We bring a wealth of experience to help you solve critical testing problems and provide expertise in testing modern technical stacks.<\/p>\n
\n","protected":false},"excerpt":{"rendered":"
This post is a hands-on example to help you start writing data quality checks with Soda Core. We realize a lot of our readers are fairly new to the topic of writing data quality checks. So, in this post, we will also go over some of the most common data checks you might want to implement on your structured data. […]<\/p>\n","protected":false},"author":32,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[314,186,374],"tags":[],"class_list":["post-19096","post","type-post","status-publish","format-standard","hentry","category-data-testing","category-database","category-soda-core"],"_links":{"self":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/19096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/users\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/comments?post=19096"}],"version-history":[{"count":52,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/19096\/revisions"}],"predecessor-version":[{"id":19402,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/posts\/19096\/revisions\/19402"}],"wp:attachment":[{"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/media?parent=19096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/categories?post=19096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qxf2.com\/blog\/wp-json\/wp\/v2\/tags?post=19096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}