Como automatizar métricas de qualidade de dados para diferentes plataformas?

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.

The business requirements are pivotal in defining data quality dimensions and metrics. In fact, without the discerning lens of the business context, the results of a data quality metric carry no substantial value. For instance, saying 60% of customers postal addresses are outdated lack meaningful impact without clarifying the business needs. Therefore, a careful evaluation of the business context as well as understanding the business problems and their impacts, and defining the scope of the data, allows us to make a more informed selection of the appropriate data quality dimensions. The outcome of assessing a data quality dimension should yield actionable insights for the business to act upon

As I am working in Business Intelligence data quality metrics crucial task for me. To achieve this, one can leverage a combination of tools and techniques. Firstly, employ data integration and ETL (Extract, Transform, Load) pipelines to bring data from different sources into a centralized data warehouse. Next, implement data quality checks and metrics within the ETL process. For structured data, calculate metrics such as missing values, duplicate records, and data outliers. Visualization tools like Tableau or Power BI can help create interactive dashboards to monitor data quality in real time. Lastly, documentation is key. This documentation aids in understanding the data lineage and facilitates collaboration across teams.

In my opinion, the first step should be identification of the critical data elements and then defining which data quality dimensions relevant for the CDEs and the threshold needed for the purpose intended. Focussed approach is critical for an effective data quality framework.

Determining Data Quality Dimensions is the most vital step in establishing a Data Quality Framework in organizations that deliver. While identifying these dimensions, the most important factors to take into account are: Alignment with business objectives (The contribution of metrics to business objectives). Clarity in measurement (How easy or difficult is it to measure metrics across teams) - Prioritization and resource allocation (which of the metrics are most important, otherwise valuable resources could be spent on unnecessary things)

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...

There are three types of data quality checks, depending on the scale of data that is measured. 1) Row-level rules These are validation rules that are evaluated for each row. They mostly fall in the validity DQ dimension. Row-level checks can be verified inside ETL tools and in data streaming systems. 2) Table-level rules They calculate an overall measure of the whole table and can be calculated after the data loading process finishes. For example, a percentage of null values. They must be run from a workflow. 3) Time-series rules These are anomalies that detect inconsistent metrics over time. They need a separate metrics store to collect these metrics, such as the percentage of null values. You need a data observability tool to detect them.

Often, companies already extensively measure and monitor their data quality without them even realising. Indeed, many large organisations have regular audits to check whether their most important data matches with their real-world counterpart. These audits can be used as a sample to say something about the error distribution in the actual data (e.g. how big is the average error, how many records do we think are in error, ...).

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.

Leveraging dashboards like Tableau CRM, Power BI, etc. helps to bring the data quality metrics in real time. For example, for accounts, and contacts master data in Salesforce CRM, different dimensions of quality were defined in terms of completeness, timeliness, validity, etc. The data was fed on a nightly basis to the dashboard using the batch jobs and on a need basis, if the business wanted a refresh on demand, it was doable. The results were exported out in an excel and sent over an email to all the users. Access to the dashboard was provided to check the visuals too.

Data quality incidents must not overload the teams. The incident reporting platform must group similar data quality issues as a single incident until the incident is resolved. Otherwise, the notification system will send hundreds of similar data quality issue notifications every day, reporting issues that are known. Other issues that require external parties to fix could be open for weeks until they are finally solved. The data quality issues could be prioritized, different issues could be assigned a higher or lower severity level. Finally, the issues that are just anomalies should be muted once they are assessed.

In addition to leveraging automations for communicating data quality performance, setting up alerts is pivotal. For instance, if data completeness drops below 60% against the established threshold of 90%, automated alerts can swiftly notify relevant stakeholders. These alerts serve as proactive notifications, enabling stakeholders to promptly assess the situation, identify root causes, and take corrective actions. This would ensure timely responses to dealing with the issue.

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.