Custom data quality checks for high-load payments provider

About project:

Client overview

A global financial services provider sought to validate and ensure data completeness across their data warehouse models, requiring a robust solution to verify ETL pipeline accuracy and maintain data integrity throughout their business intelligence infrastructure.

Tech Stack:

Manual data validation, SQL queries, basic ETL tools

Tech stack after migration:

Automated cross-check system, Python, Apache Airflow, SQL, Alerts in Slack

Time to deliver project:

4-6 Weeks

Problem

  • The client lacked confidence in the completeness of data within their DWH models due to potential errors in the ETL pipelines. This uncertainty made it difficult to trust the accuracy of their data.

Inspection

  • We identified that the data was being loaded into tables that mirrored the source tables, with model creation and data loading handled by separate processes using different, independent logic. The models were built using Python but could be logically expressed in SQL. To address this, we created SQL views that mirrored the models and displayed data over specific time periods, such as the past few months. These views were based on the source-like tables, allowing for a direct comparison between the view data and the model data to ensure consistency and completeness.

Recommendation

  • We recommend automating these checks to focus on small, relevant time periods to avoid overloading the system. Larger, more comprehensive checks can be scheduled during off-peak times to maintain data integrity without impacting performance.

Resolution

We implemented an automated cross-check system that compares the data between the source-like tables and the models. This system regularly validates data consistency, boosting confidence in the data’s completeness without overloading the database. It has also allowed for more extensive checks during periods of low database activity, ensuring ongoing data accuracy.

Similar projects

Do you want
the same one?

Leave a request and our manager will contact you to discuss your project and give an assessment of a similar project.

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Our website use cookies
Read our Privacy Policy.
Order an audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order Black box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order White box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number