Data Quality: Completeness, Accuracy, Consistency

Data quality is a measure of how well a dataset meets the requirements of its intended use. In the context of DP-900, data quality is assessed across several dimensions, with completeness, accuracy, and consistency being the three most frequently tested. These dimensions are not optional features; they are fundamental to ensuring that data-driven decisions are based on reliable information.

Data quality is a cross-cutting concern in Azure data services. For instance, Azure Data Factory pipelines often include data quality checks as part of data cleansing activities. Azure Synapse Analytics and Azure Databricks provide tools to profile data and identify quality issues. The exam expects you to know not only the definitions but also how to identify examples of each dimension in given scenarios.

Completeness is typically measured as a percentage: (number of non-null values in a field / total expected values) × 100. However, completeness can also refer to entire records. For example, a customer table with 100 rows but only 95 unique customer IDs indicates incomplete data if every customer should have a record.

In Azure, you can use Azure Data Factory's Data Flow transformations (e.g., Derived Column, Conditional Split) to check for NULLs and filter incomplete records. Azure Synapse provides built-in data profiling capabilities that report completeness percentages per column.

Common exam trap: Candidates often confuse completeness with accuracy. A record can be complete (all fields filled) but inaccurate (wrong values). For example, a customer record with a phone number that is not actually the customer's is complete but inaccurate.

Accuracy is harder to measure because it requires a trusted source of truth (e.g., a master data set or real-world verification). Accuracy is often expressed as a percentage of records that match the true value.

In Azure, accuracy checks are typically implemented through business rules. For example, you can use Azure Data Factory's Lookup activity to cross-reference data against a master database. Azure SQL Database supports CHECK constraints that enforce syntactic accuracy (e.g., CHECK (Email LIKE '%_@__%.__%')).

Exam trap: Candidates sometimes think accuracy is the same as precision. Precision refers to the granularity of data (e.g., 3.14159 vs. 3.14), while accuracy refers to correctness. A precise value can be inaccurate.

Consistency ensures that data values are the same across different systems or within the same system over time. Inconsistencies arise when:

Consistency is often enforced through: - Data standards: Defining common formats, codes, and naming conventions. - Referential integrity constraints: Foreign keys in relational databases. - ETL/ELT transformations: Standardizing data during ingestion.

In Azure, Azure Data Factory can enforce consistency by using mapping data flows to apply standard transformations. Azure SQL Database enforces referential consistency via foreign keys. Azure Cosmos DB uses consistency levels (strong, bounded staleness, session, consistent prefix, eventual) to balance consistency with performance.

Exam trap: Consistency is often confused with integrity. Integrity is a broader concept that includes consistency, accuracy, and completeness working together. Consistency specifically focuses on uniform representation.

Data quality is not binary; it is measured on a continuum. Common metrics include: - Completeness rate: e.g., 95% of email fields are non-null. - Accuracy rate: e.g., 98% of addresses match the USPS database. - Consistency rate: e.g., 99% of customer IDs are formatted as 'CUST-XXXX'.

In Azure, you can use Azure Purview (now Microsoft Purview) to scan data sources and generate data quality reports. Azure Synapse Studio has a 'Data Profiling' feature that automatically computes completeness and uniqueness statistics.

Data quality is not a one-time activity; it is a continuous process: 1. Define quality rules: What constitutes complete, accurate, consistent data? 2. Measure: Profile data to identify violations. 3. Analyze: Root cause analysis of quality issues. 4. Improve: Cleanse, standardize, or enrich data. 5. Monitor: Ongoing tracking using dashboards.

In Azure, this lifecycle can be implemented using Azure Data Factory pipelines that trigger on a schedule, running data quality checks and logging results to Azure SQL Database or Azure Data Lake.

Understanding these interactions helps you answer scenario-based questions on the DP-900 exam that ask you to choose the appropriate Azure service or technique to address a data quality problem.

A retail company is migrating its customer database from an on-premises SQL Server to Azure SQL Database. The source data has been accumulated over 20 years with minimal validation. Completeness: 12% of records lack phone numbers; 5% lack email. Accuracy: 8% of zip codes are invalid (e.g., 'ABCDE'). Consistency: State abbreviations appear as 'CA', 'Calif', 'California'. The data engineering team uses Azure Data Factory with a Copy activity to ingest data into a staging table in Azure SQL Database. Then, a Data Flow pipeline runs daily: it uses Conditional Split to separate records with missing email (incomplete) into a review table. A Lookup transformation cross-references zip codes against a USPS reference table; mismatches are flagged. Derived Column transformations standardize states using a lookup table mapping full names to abbreviations. The cleansed data is loaded into the production table. Performance considerations: the Data Flow runs on Azure Integration Runtime with 8 cores; processing 10 million records takes about 45 minutes. Common pitfalls: if the reference table is outdated, accuracy corrections may introduce new errors. Misconfiguration of the Data Flow's 'Row conditions' can cause too many records to be flagged, overwhelming the review queue.

A manufacturing company uses IoT sensors to monitor equipment temperature. Data streams into Azure Event Hubs and is processed by Azure Stream Analytics. Completeness: every reading should include a timestamp, sensor ID, and temperature. Accuracy: temperature must be between -50°C and 150°C. Consistency: timestamps must be in UTC. During a network outage, some messages arrive without timestamps (incomplete). The Stream Analytics job uses a TIMESTAMP BY clause to assign a system timestamp when missing. For accuracy, a WHERE clause filters out temperatures outside the valid range, routing them to a dead-letter queue for analysis. Consistency is enforced by the sensor firmware, but the Stream Analytics job also checks that timestamps are in UTC; if not, it applies a conversion using a UDF. This scenario tests real-time quality enforcement. Common issues: if the dead-letter queue is not monitored, quality issues go unnoticed. Also, the TIMESTAMP BY clause can cause out-of-order events if the system clock is unsynchronized.

A financial services firm stores transaction data in Azure Data Lake Storage Gen2. Analysts use Azure Databricks to run daily reports. Completeness: all transactions must have a transaction ID and amount. Accuracy: amounts must be positive. Consistency: currency codes must follow ISO 4217 (e.g., 'USD', 'EUR'). The Databricks notebook reads Parquet files, applies a schema with NOT NULL constraints on transaction ID and amount using DataFrame API (e.g., df.filter(col('amount').isNotNull())). For accuracy, it filters out rows where amount <= 0. For consistency, it uses a lookup table to validate currency codes. Bad records are written to a separate 'quarantine' table. The notebook runs as an automated job every hour. Performance: processing 100 GB of data takes about 20 minutes with a 10-node cluster. Pitfalls: if the schema evolves (e.g., new column added), the notebook may break or silently drop columns. Also, the quarantine table can grow large if quality issues are not addressed at the source.