Data Validation and Profiling

Purpose of This Lesson

Data validation and profiling help determine whether a dataset is complete, consistently represented, accurate, and structurally trustworthy before it is used for analysis or decision-making. For the AWS Certified Data Engineer Associate context, the key skill is recognizing which quality dimension is failing and selecting an appropriate validation or remediation approach.

Key Concepts

Data Completeness

Completeness asks whether all expected data is present and whether essential fields contain usable values. Common checks include:

• Counting missing or null values in each column.
• Calculating the percentage of records affected.
• Comparing the result with an accepted data-quality threshold.
• Determining whether to remove incomplete rows or impute replacement values.

A missing value must not automatically be treated as a real value. For example, using zeros for unreported salaries can significantly reduce an average salary and produce misleading conclusions.

Data Consistency

Consistency asks whether the same type of data is represented in the same way across tables and source systems. Cross-field or cross-source validation can expose mismatched formats, scales, or value ranges.

For example, one source may rate movies from 1 to 5 while another uses a 1-to-10 scale. Combining the values without normalization makes the numbers difficult to interpret and can lead to incorrect comparisons.

Data Accuracy

Accuracy asks whether the data is correct, reliable, and representative of what it is intended to measure. The strongest validation method is comparison with a trusted ground-truth source. When ground truth is unavailable, sanity checks and examination of the overall distribution can reveal values that do not match expected real-world behavior.

Data Integrity

Integrity concerns whether relationships between data elements remain valid over time. In relational data, foreign key checks help verify that references between tables still point to valid related records. Changes in one table can cause relationships to become invalid or out of sync, reducing trust in the dataset.

Data Quality Validation Workflow

A practical validation process can follow these steps:

1. Profile the data: Inspect columns, null counts, value ranges, distributions, and relationships.
2. Define acceptance criteria: Decide what level of missingness or variation is acceptable for each field.
3. Validate completeness: Identify missing values and determine whether they affect essential fields or calculations.
4. Validate consistency: Compare representations across tables and sources, including units, formats, and value ranges.
5. Validate accuracy: Compare against ground truth where possible, or perform sanity checks against expected real-world patterns.
6. Validate integrity: Check foreign key relationships and other links between data elements.
7. Remediate deliberately: Drop affected rows or impute values only when that choice is appropriate for the analysis and documented.
8. Recheck after changes: Validation should be repeated after transformations, joins, updates, or other changes that could alter relationships or values.

Exam- or Assessment-Relevant Takeaways

• Missing or null values indicate a completeness issue; calculate their count and percentage rather than assuming they are harmless.
• Inconsistent scales or formats across sources indicate a consistency issue and can make joined data misleading.
• Comparison with a trusted reference is the clearest way to test accuracy.
• Sanity checks and distribution analysis are useful when no ground-truth dataset exists.
• Foreign key checks help test integrity between related tables.
• A value such as 0 may be a legitimate measurement or an encoded missing value; treating it as real data can distort results.
• Data-quality validation is multidimensional: completeness, consistency, accuracy, and integrity address different failure modes.

Tool / Feature Decision Guide

Situation	Appropriate validation or response	Decisive reason
A column contains unexpected blanks	Count nulls and calculate the affected percentage	Establishes the scale of the completeness problem
Missing values affect a required analysis	Drop rows or impute values based on the use case	Missing-data treatment changes the result and must be chosen deliberately
Two sources use different value ranges	Perform cross-source or cross-field consistency checks and reconcile the representations	Equal-looking values may have different meanings
Values may not reflect reality	Compare with ground truth or perform sanity and distribution checks	Accuracy is about correctness, not merely format
Related records no longer match	Run foreign key or relationship validation	Preserves data integrity across tables
A source has changed over time	Reprofile and revalidate affected fields and relationships	Previously valid assumptions may no longer hold

Common Traps / Misconceptions

• Treating missing data as zero: This can bias aggregates such as averages.
• Assuming matching column names imply consistency: The same field may use different units, scales, or formats in different sources.
• Confusing consistency with accuracy: Data can be consistently formatted and still be wrong.
• Checking only individual rows: A dataset may pass row-level checks while its overall distribution is unrealistic.
• Ignoring relationships after updates: Foreign key relationships can become invalid as source tables change.
• Deleting incomplete records automatically: Dropping rows is only one possible response; it may remove too much useful data.
• Assuming a successful join proves integrity: A join can conceal mismatched meanings or incomplete relationships unless those relationships are explicitly validated.

Real-World Engineer / Analyst Notes

• Record whether a null, blank, sentinel value, or zero represents missing information. The representation affects profiling and downstream calculations.
• Set field-specific expectations instead of applying one universal missing-data threshold to every column.
• Validate data before and after joins, transformations, and source-system changes.
• Inspect distributions, not just minimum and maximum values. A plausible range does not guarantee a plausible dataset.
• Preserve the original data when applying imputation or filtering so the remediation remains auditable.
• Treat data-quality results as input to downstream decisions. A dataset can be technically available while still being unsuitable for a particular analysis.

Quick Reference Summary

Dimension	Core question	Typical check
Completeness	Is the expected data present?	Null counts and null percentages
Consistency	Is data represented uniformly across sources and tables?	Cross-field or cross-source comparison
Accuracy	Does the data correctly represent reality?	Ground-truth comparison, sanity checks, distribution analysis
Integrity	Are relationships between data elements still valid?	Foreign key and relationship checks

The central distinction is that completeness concerns presence, consistency concerns representation, accuracy concerns correctness, and integrity concerns relationships over time.

Flashcards

Q: A salary dataset uses 0 when a person does not report a salary. What quality risk does this create?

A: It creates a completeness and interpretation problem because the zero may be missing data rather than a real salary. Including it as a genuine value can pull the average downward.

Q: What should you calculate first when checking whether a column has missing data?

A: Calculate the null or missing-value count and the percentage of records affected, then compare those results with the accepted requirements.

Q: When might you drop rows with missing values, and when might you impute values?

A: Drop rows when their removal is acceptable for the analysis; impute when retaining the records is important and a defensible replacement method exists. Neither choice should be automatic.

Q: Two movie-rating sources use 1-to-5 and 1-to-10 scales. Which validation dimension is involved, and why?

A: Data consistency is involved because the same-looking rating values have different meanings across sources. They must be reconciled before being combined or compared.

Q: How is data accuracy different from data consistency?

A: Consistency asks whether values use a uniform representation, while accuracy asks whether the values are correct and reflect what they are intended to measure. Consistent data can still be inaccurate.

Q: What is the preferred way to validate accuracy when a trusted reference is available?

A: Compare the dataset with the ground-truth source. This provides stronger evidence of correctness than relying only on format or plausibility checks.

Q: What can you use to assess accuracy when no ground-truth source exists?

A: Use sanity checks and inspect the overall nature and distribution of the data to determine whether it matches expected real-world behavior.

Q: A record references a customer ID that no longer exists in the customer table. Which quality dimension is failing?

A: Data integrity is failing because the relationship between the records is no longer valid. Foreign key checks can detect this type of problem.

Q: Why should relationship checks be repeated over time?

A: Updates or changes in related tables can cause previously valid references to become out of sync. Revalidation helps preserve trust in the data.

Q: A dataset has no nulls and uses one consistent format, but its values do not resemble expected real-world behavior. Which dimension should receive attention?

A: Accuracy should be investigated. Completeness and consistency do not prove that the values are correct.

Q: What is the main purpose of data profiling before analysis?

A: Profiling reveals patterns such as missingness, distributions, value ranges, and relationships so that validation and remediation decisions are based on evidence.

Q: Why can a value-range check alone be insufficient for accuracy validation?

A: Values can fall within an apparently valid range while still having an unrealistic distribution or incorrect meaning. Distribution and sanity checks provide additional context.

Practice Questions

Question 1

An analyst combines two customer datasets. Both contain an engagement_score column, but one source uses values from 0 to 1 and the other uses values from 0 to 100. What should happen before comparing the scores?

A. Replace all values above 1 with nulls
B. Perform consistency validation and reconcile the scales
C. Use a foreign key check
D. Drop all records from the second source

Correct answer: B. The decisive clue is that the same field uses different scales. This is a consistency problem, not a relationship-integrity problem.

Question 2

A salary report calculates an unexpectedly low average. Profiling shows that 18% of salary fields contain 0, and the source documentation says employees may leave the field unreported. What is the most appropriate next step?

A. Treat every zero as a real salary
B. Ignore the zero values because the column is numeric
C. Determine a documented missing-data treatment, such as filtering or defensible imputation
D. Replace all zeros with the maximum salary

Correct answer: C. The zeros may encode missing information, so they must be handled deliberately before computing the average.

Question 3

A data engineer needs to determine whether sales amounts from a new source are reliable, but there is no trusted reference dataset. Which approach is most appropriate?

A. Check only whether the column contains no nulls
B. Compare the sales values with expected ranges and distributions using sanity checks
C. Run a foreign key check against the product table
D. Convert all amounts to strings

Correct answer: B. Without ground truth, sanity checks and distribution analysis are the available ways to identify implausible data and assess accuracy.

Question 4

After a customer cleanup job, some orders reference customer records that no longer exist. Which validation should be prioritized?

A. Null-percentage analysis on order descriptions
B. Cross-field validation of movie-rating scales
C. Foreign key and relationship-integrity checks
D. Salary-distribution analysis

Correct answer: C. The problem is an invalid relationship between orders and customers, which is a data-integrity failure.

WordPress Metadata

Suggested Slug:
aws-data-validation-and-profiling

Meta Description:
Learn how to validate data completeness, consistency, accuracy, and integrity, including null analysis, cross-source checks, sanity checks, and foreign key validation.

Tags:
AWS, data engineering, data validation, data profiling, data quality, data completeness, data consistency, data accuracy, data integrity, null handling, foreign keys, data analysis