Completeness Check

A completeness check is a data quality validation that measures and verifies the degree to which expected data values are present and not missing (null) in a dataset. It operates by comparing the actual data against a defined expectation, such as a schema or a business rule, to identify null values, empty strings, or default placeholders that indicate missing information.

• Mechanism: Typically implemented as a rule that flags records where specific mandatory fields are null.
• Scope: Can be applied at the column level (e.g., customer_id must never be null) or the record level (e.g., a transaction record must have at least one non-null field from a set).
• Outcome: Produces a metric, often completeness percentage (e.g., 98.5% of records have a non-null email field), which is a key data quality metric.

Completeness checks are implemented through various technical patterns depending on the data pipeline stage and technology stack.

• Schema-Level Enforcement: Defined in database constraints (e.g., NOT NULL in SQL) or schema definitions like Avro Schema, JSON Schema, or Protocol Buffers.
• Pipeline Validation: Executed within ETL/ELT jobs using frameworks like Great Expectations, dbt tests, or custom Spark validations.
• Programmatic Rules: Coded as data quality rules in validation scripts, often checking for IS NULL conditions or string length zero.
• Observability Platforms: Integrated into data observability platforms that continuously monitor data freshness and trigger alerts on completeness breaches.

Incomplete data directly degrades the reliability of analytics and machine learning models, leading to tangible business costs.

• Analytic Bias: Aggregations (SUM, AVG) and joins fail or produce misleading results when key dimensions are missing.
• Model Performance: Machine learning models trained on datasets with missing values can develop biased weights or fail during inference, a critical concern for evaluation-driven development.
• Operational Failures: Downstream applications expecting non-null values may crash, causing data incidents.
• Compliance Violations: Regulatory reports (e.g., financial, healthcare) often mandate complete fields, making checks part of enterprise AI governance.

For example, a retail hyper-personalization engine with incomplete customer postal codes cannot execute location-based campaigns.

Completeness is one dimension of data quality, closely intertwined with other validation types.

• Schema Validation: Ensures data structure matches a formal definition; a NOT NULL constraint is a completeness rule within a schema.
• Nullability Check: A specific type of completeness check verifying if a field is allowed to be null, as defined in the data contract.
• Data Integrity: The broader goal encompassing completeness, accuracy, and consistency.
• Data Profiling: The exploratory process that often reveals completeness issues by analyzing value distributions and null counts.
• Anomaly Detection: Sudden spikes in null counts for a typically complete field can be flagged as a data drift anomaly.

Effective completeness checking requires nuance beyond simple null detection.

• Semantic Nulls: Identifying default values (e.g., 'N/A', 'Unknown', 0, -1) that are functionally null but pass a basic IS NULL check. This requires business logic.
• Conditional Completeness: A field may only be required if another field has a specific value (e.g., shipping_address is mandatory only if purchase_made is true).
• Temporal Aspects: Data freshness monitoring ensures data arrives on time; a late-arriving file manifests as a completeness issue for the expected time window.
• Schema Evolution: As schemas change (schema evolution), completeness rules must be versioned and managed to avoid breaking pipelines due to schema drift.

Several open-source and commercial tools provide specialized capabilities for implementing completeness checks.

• Great Expectations: An open-source Python framework where you can define Expectation suites containing assertions like expect_column_values_to_not_be_null.
• dbt (data build tool): Allows defining data tests in YAML or SQL, including built-in not_null tests.
• Apache Spark: Developers can use DataFrame operations (filter, isNull) or the validate method in Delta Live Tables to enforce completeness.
• Soda Core: An open-source data quality tool that uses YAML checks, such as missing_count(column_name) = 0.
• Data Observability Platforms: Integrated platforms like Monte Carlo, Acceldata, or BigEye provide UI-driven configuration for monitoring completeness alongside other data quality metrics.

The most fundamental completeness check is a nullability constraint enforced at the database or schema level. This rule explicitly defines whether a column or field is permitted to contain a NULL value.

• Database-Level: Implemented using NOT NULL clauses in SQL CREATE TABLE or ALTER TABLE statements.
• Schema-Level: Defined in structured formats like JSON Schema ("required": ["fieldName"]), Avro Schema, or Protobuf message definitions.
• Impact: Prevents the insertion of records where mandatory fields are missing, ensuring referential integrity and application logic do not fail due to unexpected nulls.

This check validates that the total number of records processed in a batch or streaming job matches an expected count. Discrepancies indicate data may have been lost or duplicated during extraction or transfer.

• Use Case: Verifying an ETL job ingested all rows from a source table after a daily snapshot.
• Implementation: Compare COUNT(*) between source and target systems or against a control total provided by the source application.
• Advanced Form: Checksum validation on record identifiers can provide a more robust verification than a simple count.

Beyond simple null checks, this involves verifying that specific critical fields contain valid, non-default values. It often combines completeness with basic validity checks.

• Examples:
  • A customer_id field must be a non-zero integer.
  • A transaction_date field must be a valid date string, not an empty string or '1900-01-01'.
  • An email field must contain an "@" symbol.
• Business Logic: Enforces that fields essential for downstream analytics or operations are meaningfully populated, not just technically non-null.

For temporal data, completeness is measured by the continuity of the time series. This check identifies missing intervals (e.g., hours, days) where expected data points do not appear.

• Method: Generate a sequence of expected timestamps and perform an anti-join with the actual data to find gaps.
• Key Metrics: Data freshness (latency of the latest point) and coverage (percentage of expected intervals present).
• Critical For: Monitoring IoT sensor feeds, financial market data, and application performance telemetry where missing intervals skew analysis.

A specialized completeness check ensuring that all foreign key values in a child table have a corresponding primary key in a parent table. Missing references indicate orphaned records and broken data relationships.

• SQL Check: SELECT child.fk FROM child LEFT JOIN parent ON child.fk = parent.pk WHERE parent.pk IS NULL;
• Schema Tool: Enforced by FOREIGN KEY constraints in relational databases, which prevent the violation from occurring.
• Data Contract Implication: A core guarantee in a data contract between producer and consumer, ensuring join operations will not fail.

Quantifying completeness at the dataset or column level using metrics that provide an ongoing health score, crucial for data observability.

• Primary Metric: Completeness Rate = (Non-Null Count / Total Count) * 100%.
• Profile-Based: Established during data profiling to set a baseline non-null percentage for each column.
• Monitoring: Tracked over time to detect schema drift or source system degradation that increases null rates. A drop in the completeness rate triggers a data quality incident.

The process of verifying that a data structure conforms to a predefined formal specification, or schema. This defines the expected format, data types, and structural constraints (e.g., required fields, nested object shapes). It is a prerequisite for a completeness check, as the schema defines what data is expected to be present.

• Examples: Validating a JSON payload against a JSON Schema, ensuring an Apache Avro record matches its registered schema, or checking a database table's columns against a DDL (Data Definition Language) statement.
• Key Difference: While schema validation confirms structure, a completeness check specifically measures the presence of data within that validated structure.

A specific validation rule that verifies whether a data field is allowed to contain a null (or empty) value, as defined by the data schema or business logic. It is a binary component of a completeness assessment.

• Implementation: A field defined as NOT NULL in a database schema automatically enforces this check. In application code, it's often a guard clause (e.g., if value is None: raise error).
• Relationship to Completeness: A completeness check aggregates the results of nullability checks across multiple fields or records to calculate a percentage (e.g., 95% of records have a non-null customer_id).

A formal, testable assertion that defines a constraint or condition data must satisfy to be considered fit for use. A completeness check is one type of data quality rule.

• Other Rule Types:
  • Accuracy: Does the data reflect the real-world entity? (e.g., city = 'London' for a UK postal code).
  • Validity: Does the data conform to a defined format? (e.g., regex for email, enum for status).
  • Uniqueness: Are values in a column distinct? (e.g., primary key constraint).
  • Consistency: Does the data agree with other trusted sources? (e.g., order_total equals sum of line_item totals).
• Frameworks: Tools like Great Expectations, Soda Core, and dbt tests allow these rules to be codified and executed programmatically.

A formal agreement between data producers and consumers that specifies the schema, semantics, quality guarantees (including completeness SLOs), and service-level expectations for a data product. It turns implicit assumptions into explicit, enforceable guarantees.

• Components: Typically includes the schema (e.g., Protobuf, Avro), freshness SLA (e.g., data delivered within 5 minutes), and quality metrics (e.g., completeness > 99.9% for key fields).
• Role of Completeness: A data contract codifies the acceptable threshold for missing data, making completeness a measurable service-level objective (SLO). Breaching this threshold constitutes a contract violation, triggering alerts or SLAs.

The automated process of examining existing data to collect statistics and metadata. It is a discovery activity that often identifies the need for completeness checks and other quality rules.

• Generated Metrics:
  • Completeness Rate: Percentage of non-null values per column.
  • Distinct Count & Uniqueness: Number of unique values.
  • Data Type Inference: Detected vs. expected types.
  • Pattern Frequency: Distribution of string formats (e.g., phone numbers).
  • Statistical Summaries: Min, max, mean, standard deviation for numeric columns.
• Tools: Apache Griffin, Amazon Deequ, and integrated profiling in data catalogs (e.g., Alation, Collibra) perform this analysis.

The process of verifying the correctness, completeness, and quality of data as it moves through the Extract, Transform, and Load stages of a pipeline. Completeness checks are a critical subset of this validation.

• Checkpoints:
  • Source Extraction: Verify expected record counts are pulled.
  • Post-Transformation: Ensure no data loss occurred during joins, filters, or calculations.
  • Pre-Load: Confirm all required fields for the target system are populated.
  • Post-Load: Reconcile counts and aggregates between source and destination.
• Objective: To prevent silent data corruption where pipelines run without error but produce incomplete or inaccurate outputs.