CompTIA Data+ DA0-002 · Free study guide

Objective 2.2 — Explore data for inconsistencies

Exploration is the inspection that happens before an analyst decides how to clean a data set. The goal is not to make every column look tidy. It is to discover where the data disagrees with its documented meaning, business rules, relationships, or expected population.

A defensible sequence is profile, investigate, classify the issue, and only then choose treatment. Immediate deletion can hide the evidence needed to distinguish a collection failure from a genuine rare event.

Profile the table and its columns

Data profiling summarizes structure and content. At the table level, record row count, column count, candidate grain, keys, source period, and refresh time. At the column level, inspect type, null count, distinct count, minimum, maximum, common values, length, patterns, and distribution.

The grain states what one row represents. “One row per order” differs from “one row per order line.” Many apparent duplicates and inflated aggregates come from misunderstanding grain rather than from corrupt data.

Profiles work best when compared with something: a contract, prior successful load, source total, or business threshold. Ten thousand rows may be reasonable in isolation and evidence of a failed load when the source normally delivers ten million.

Useful questions include:

• Did every expected file, partition, date, or region arrive?
• Do inferred types match the documented schema?
• Are keys unique at the stated grain?
• Did null rates or category frequencies change sharply?
• Do totals reconcile with a trusted control value?

Profiling identifies symptoms. It does not by itself prove their cause.

Missing values and completeness

A missing value may mean unknown, not collected, not yet available, not applicable, suppressed, or lost. Those meanings require different treatment. An online order with no physical store is structurally inapplicable; a shipment with no promised date may indicate a process failure.

Sources sometimes encode missingness with sentinels such as 999, N/A, 1900-01-01, or an empty string. If those values remain ordinary data, they distort ranges, averages, and dates. Convert them to an explicit representation only after confirming their documented meaning.

Completeness asks whether required data is present. Measure it at the appropriate scope: field completeness, record completeness, period completeness, and population completeness are different. A table can have no nulls and still be incomplete because an entire region was omitted.

Do not equate complete with accurate. A phone field filled on every row is complete even if half the numbers no longer work.

Duplicates and redundancy

A duplicate is an unintended repeated representation of the same event or entity at the relevant grain. Exact duplicate rows are easy to detect. Near duplicates such as Jon Smith and John Smith require standardized fields, stable identifiers, or similarity methods followed by review.

Repeated values are not automatically duplicates. Ten order lines can legitimately share an order ID. Confirm the key and grain before deleting anything.

Redundancy means the same fact is stored or derivable in more than one place. A table that stores quantity, unit price, and total price may contain a useful control or a redundant field that drifts out of agreement. Customer state repeated on every transaction may be intentional snapshot history or uncontrolled duplication. Investigate how the field is governed.

Redundancy can create inconsistency when copies update at different times. Compare repeated facts and identify the authoritative source rather than choosing whichever value is convenient.

Outliers and plausible ranges

An outlier is an observation unusually distant from others under some rule. It is not a synonym for error. A record-breaking sale, fraud event, or equipment failure may be the most important valid record in the data set.

Use statistical and domain checks together. Quartile fences or standard-score thresholds can flag candidates, while physical limits and business rules determine plausibility. A temperature of 500 degrees may be possible in a furnace and impossible in an office sensor.

Investigate source record, unit, timestamp, device, and related fields. Common causes include wrong units, shifted decimals, duplicate ingestion, sensor failure, or a real exceptional event. Document whether the value is corrected, retained, excluded, or analyzed separately.

Validation techniques

Validation compares data with explicit expectations.

Validation may reject, quarantine, warn, or record an exception. Rejecting every imperfect record can make a data set less complete, while accepting every record spreads defects. The response should match the rule’s severity and the ability to correct the source.

Source validation asks whether the delivered data matches the source, while business validation asks whether the source itself represents a valid event. Both matter. A perfectly copied negative quantity may still violate the business rule.

Scenario: exploring a new order feed

A new daily file is expected to contain one row per order line. The first profile shows 400,000 rows instead of the normal 4 million, 999 in the customer-age field, repeated order IDs, a few negative quantities, and unusually large purchases from one account.

The row-count drop triggers a completeness investigation before cleaning. Perhaps nine partitions never arrived. Repeated order IDs are expected at order-line grain, so they are not duplicates unless the line key repeats. The age sentinel should become missing only after source documentation confirms its meaning. Negative quantities might be valid returns or invalid sales; related transaction type determines the rule. The large purchases are outlier candidates, not automatic deletions. Source totals, file delivery logs, referential checks, and prior profiles provide the evidence needed to classify each symptom.

Exam traps

• Cleaning before recording the original profile and source evidence.
• Assuming no nulls means a complete data set.
• Treating a sentinel value as a real extreme value.
• Deleting repeated keys without understanding row grain.
• Calling every repeated attribute a duplicate instead of examining redundancy.
• Removing all statistical outliers without domain investigation.
• Confusing valid format with accurate content.
• Rejecting records so aggressively that a failed pipeline appears clean.
• Using a dashboard total as the source control when the dashboard depends on the same broken pipeline.

Readiness checklist

• I can describe table- and column-level profiling measures.
• I can state the grain of a data set before testing duplicates.
• I can distinguish missingness from incompleteness and identify sentinel values.
• I can explain the difference between duplication and redundancy.
• I can identify outliers without assuming they are errors.
• I can combine statistical flags with domain plausibility.
• I can apply type, range, domain, format, uniqueness, referential, cross-field, and reconciliation checks.
• I can choose reject, quarantine, warn, or investigate based on risk.
• I can preserve evidence and document the decision for every inconsistency.