How do you automate data quality metrics for different platforms?

Bad Data is any data that effects the decision it’s being used for. This could be 👉missing data attribution values 👉missing completeness 👉ambiguous context 👉late arrival or timeliness But understanding the use case fir the data will determine its effectiveness to be used.

Definition should include threshold. For different business rules each dimension (accuracy, completeness, consistency, timeliness, validity, and uniqueness ) can have different thresholds. Determine scope of Data Quality. Data Quality from Data Governance and ANALYTICS Governance perspective End to end DQ for source, DQ for source aligned Data Products, DQ for user aligned Data Products , DQ for semantic layer , DQ for Data Products used for Machine learning AI use-cases. Also Determine Data Quality Scoring for each of the Dimensions at Table level, Data set Data Product Level, And entity level Also determine the Data Quality Observability for different Personas Data Product owners, Data Steward, Data Governance , Business

A prerequisite for defining data quality dimensions is to define business rules. Unless you have defined business concepts, business definitions and business rules you can't define data quality dimensions. Data quality issues are violations of business rules. Different types of business rules directly translate to data quality dimensions. Some examples Computational Rules: Rules that concern calculations. For example, "the sales tax for a product is 5% of its price." Constraint Rules: For example, "employees are not allowed to approve their own expense reports." Decision Rules: For example, "if a loan applicant's credit score is below 600, then their loan application is automatically denied." Data Integrity Rules and many more

Having being inspired by @joe reis talk on data modeling - data quality can also borrow from modeling. We need to understand whether the data is believable/trusted and useful and that defines high quality data. This at the end of the day is defined by how does the business operates. So it could be one or all dimensions dependant on the data element. If e.g. The quality requirement on your customer data will be dependant on the uses you have for that data - which will be different for advertising targeting, direct marketing opt-out compliance and customer lifetime analytics

Before measuring data quality, learn how the data quality dimensions are defined. Make a business glossary of data quality dimensions that is used across the organization. Otherwise, the same type of issues will be categorized under different names. Once you have a common definition of the data quality dimensions that you want to measure, build a shared library of data quality checks that are used across the organization. Doing these two steps will save a lot of time and confusion later on when the data quality metrics are compared between tables, systems, or even departments.

When identifying critical data elements, it is important to know the use cases of the data for a business user. For instance data to be used by actuarial or pricing teams are critical than data used for operational decision making since the impact is higher both financially and business wise.

Thresholds are nice, but are they really useful? There are two key reasons why thresholds might give you a false sense of security. First of all, the impact of a data quality issue is almost always relative to the data registration or dataset in which it occurs. For example, if you have a dataset of the following registrations: 40cm, 30cm and 10000cm and want to calculate the average, the impact will be larger if the last registration was missing. In this case, it would be smarter to do a sensitivity analysis instead of defining a threshold. Second, with the rise of business intelligence and OLAP, we are never sure what our data will be used for in the future. In this regard, the threshold that we define today might be too low tomorrow.

Data Quality metrics are hierarchical in nature: 1) Financial data must be held to a higher standard than everything else — every penny counts, there must be no errors at all. 2) Missing values share must be monitored first and foremost. Nothing else matters if the needed data is missing. 3) Distribution shift of numerical measures must be quickly discovered by an automated solution. For example, if the number of clicks deviates more than 2 standard deviations from the past 30 days there might be an issue with data quality. 4) Unstructured and categorical variables can be numerically transformed to be trackable using the previous approach.

Sometimes a particular metric does not have a significant impact on the operation of the system, but in combination with others it can reduce its functionality. For example, a search for a client can be done by email, phone number or SSN. A client can be found by any of these three, but if all three are not filled out, it will not be possible to perform a search. In this case, in my practice we used complex (integral) metrics, consisting of several basic completeness metrics. You can use the simple average formula, you can use the weight of indicators if the degree of their influence on DQ is different. For the above example, you can use the binary form (OR), i.e. return 0 if all three are empty, and 1 if at least one of them is filled.

If you want to have a comprehensive rating in the end you may need to balance the scores carefully. In practice, it's not the weight of a metric determines it's importance but the weight times its statistical range (difference between the maximum score and minimum score) is most important. Imagine there are 2 dimensions A and B. A is weighted 80% but the lowest score is 95 over 100. B is weighted 20% but the score will vary from 60 to 100. The consolidated result will still reflect the ranking of dimension B mostly which is not the purpose of the design.

Personally I'm a big fan of data quality checks implemented into ETL-pipelines. Why insert the data if it's incorrect? The only issue being job performance but it's a worthy tradeoff. Similar to how integration tests are used in CI/CD processes, data quality checks can be executed as a part of an ETL-pipeline. However this kind of testing must be supplemented with a separate ongoing monitoring process which checks for consistency across sources and deeply checks for distribution drift.

In my experience, the closer the quality checks are to the source the better, particularly in cases where you have or are planning a data warehouse or data lake. This does add overhead to your ETL pipelines, but it ensures that the stored data utilised in downstream systems is always ready for analysis.

Although ideally you would like to check data as close to the source as possible, in practice this is not always feasible. Bad data may be the result of calculations by the system itself, indicating inaccuracies in the algorithms. Sometimes data can only be checked after it has been uploaded to a warehouse from the system being checked. For various reasons, it may be impossible to build data quality checks into the pipeline. In these cases, iterativity and periodicity of the data quality check process is very important. As well as tracking its dynamics At the same time, it is necessary to control processes aimed at correcting the causes of bad data: optimization of business processes, technical algorithms, ETL processes, etc.

Data Quality checks should be implemented in different stages of a data pipeline starting from raw/staging layer, followed by model layer and finally ending at metric layer. At each stage different type of checks can be performed for example : Basic checks like identifying nulls and duplicates,data completeness can be performed on staging layer. Tests to check business logic, referential integrity and data consistency should be performed in reporting or metric layers.

Understanding the data lifecycle is essential for devising a comprehensive plan for data capture and evaluation. This includes determining scope of the data to be assessed, how and where to assess the data, the scheduling, the sampling technique, identifying the necessary parties to be involved...

Automation of execution and storing of DQ results, especially data profiling. Adding observability tracking over time enables to see the trends of your data quality, either declining or improving (e.g. percentage of null values within a column value increases dramatically overtime should lead to challenging the business value of such column). Combined with proper alerting system and dedicating teams to monitor and encourage maintainance of such tests ensures the real value of data.

One other step is to provide visibility to data quality metrics output. Doing this will save both engineers and other stakeholders time that would have been used in searching through error logs and troubleshooting quality issues. Example of such dashboard-enabling platforms is elementary and re_cloud.

It is possible to automatically publish Data Quality scores to Data catalog as meta data for each Data Product registered in Data Catalog. So that every user ( consumers -BI expert or AI expert) hunting for Data products can decide to use ( confidence) Data products based on Data Quality Scores. Expectation ( Thresholds) of Data Quality scores can be agreed between Producers ( On prem source team or sources Data Product producers) and Consumers in codified Data Contracts.

Reports must be: 1) As visual as possible. It's easier to quickly resolve a data quality issue if all parties are initially presented with a colorful visualization. 2) As quick as possible. The faster the team sees the problem the easier it would be to fix it. 3) As rare as possible. If alerting is a constant buzz of unresolved problems people stop taking it into account. There must really be an issue every time people get a notification.

This is one area that is greatly benefited by building your quality checks into your ETL pipelines. By logging the results of the quality checks as the data enters, you can capture these details in realtime. This enables you to create alerts to notify you if and when there’s a significant deviation in data quality from a particular source so it can be addressed immediately. In addition, your teams have up-to-the-minute data to pull into your visualisation tools for cadences reviews with your stakeholders.

We also should not forget about the delegation of these data quality actions. We can implement the best quality rules and automated warning systems if there isn’t any ownership for the actual correction of the data errors. This is more like a data governance topic but the data owners should be clearly identified and the necessary resources should be allocated to them to ensure that the data quality incidents can be averted or resolved.

We should first understand the data lineage to know the data flow along with data modeling which explains to us the different data attributes. This leads to identifying the data sources and anomalies in it. By doing this we can access the data on a few dimensions like accuracy, consistency, timeliness, and completeness. Post this, we can implement continuous data quality monitoring by creating dashboards/reports. Scheduling automated checks at regular intervals on these dashboards/reports to monitor the health of the data provides us with a better understanding and auditing of the data trail leading to a continuous improvement of the data quality metrics.

Bear in mind that your data is not static, models are constantly evolving, and you should build in to your processes the intelligence of how to deal with data evolutions.

Addressing data quality issues as close to the origin as possible is imperative for maintaining data integrity and preventing the propagation of errors. By addressing issues at the source, organisations can minimize the downstream impact on data quality and ensure that data remains reliable and accurate throughout its lifecycle. This principle should also guide the selection of data quality metrics to track. Metrics should be chosen based on their relevance to the source data and the potential impact on downstream processes.