Verification Techniques: Using Spreadsheets and SQL to Catch Repeated Errors

1) Purpose of This Verification Stage

The goal of verification is to confirm that data-cleaning was done correctly and that the dataset is fully ready for analysis.
At this stage, the focus is on finding repeated or systematic errors that may not be obvious through simple manual checks.

Verification ensures the data can be trusted with confidence, similar to safety testing before a product is released.

---

2) Revisiting the Original vs. Cleaned Data

A key verification step is comparing:

• The original (dirty) dataset
• The cleaned dataset

This helps identify:

• Whether known issues were fully resolved
• Whether similar errors still appear elsewhere

Some problems can be fixed manually (e.g., removing an extra quotation mark), while others require tools for repeated patterns.

---

3) Automatic Tools for Common Errors

Some errors are frequent and can be resolved efficiently with built-in tools:

• TRIM
  Removes leading, trailing, and repeated spaces
• Remove Duplicates
  Automatically finds and deletes duplicate records

These tools are effective for well-defined, common problems.

---

4) When Errors May Be Repeated: Using Pivot Tables

If an error might appear multiple times, pivot tables are especially useful.

What Is a Pivot Table?

A pivot table is a data summarization tool that can:

• Group data
• Count occurrences
• Aggregate values (sum, average, count)

Pivot tables provide a high-level view that helps detect inconsistencies.

---

5) Example: Verifying Supplier Names with a Pivot Table

Scenario:

• A business works with four suppliers
• During verification, one supplier name appears misspelled

Key question:

• Is this a one-time typo, or does it appear multiple times?

Pivot Table Strategy

• Group data by supplier name
• Count occurrences using COUNTA
• Confirm whether the number of unique suppliers matches expectations

If the count does not match the expected number, it signals a data issue.

---

6) COUNTA vs. COUNT

Understanding the difference is important:

• COUNTA
  Counts all non-empty values (text + numbers)
• COUNT
  Counts only numeric values

For text-based fields (like supplier names), COUNTA must be used.

---

7) Find and Replace for Repeated Text Errors

Another way to handle repeated text errors in spreadsheets is Find and Replace.

Use cases:

• Correct known misspellings
• Standardize labels

Options:

• Find only (to inspect occurrences)
• Replace all (to fix every instance)

This method is fast and effective when the incorrect value is known.

---

8) Verifying Results After Correction

After correcting errors:

• Re-run pivot tables
• Re-check counts
• Confirm that categories now match expectations

Verification is complete only when results align with known facts (e.g., number of suppliers).

---

9) Handling Repeated Errors in SQL with CASE

When working in SQL, repeated errors can be handled using a CASE statement.

What Is CASE?

CASE evaluates conditions and returns a value when a condition is met.

It allows analysts to:

• Correct errors dynamically in query results
• Standardize values without modifying source tables

---

10) Example: Correcting Misspelled Names with CASE

Scenario:

• A customer’s name appears as both “Tony” and “Tnoy”
• Personalized communication requires correct names

CASE Logic (Conceptual)

• WHEN first_name = ‘Tnoy’ → return ‘Tony’
• ELSE → return the original first_name
• Output the corrected name as a new field

CASE can handle:

• One correction
• Multiple corrections within the same query

---

11) Why CASE Is Useful in Verification

CASE statements:

• Fix repeated errors consistently
• Avoid changing original database records
• Improve output accuracy for reporting and analysis

They are especially useful when:

• Data comes from shared or read-only databases
• Errors are known but cannot be permanently edited

---

12) Key Takeaways

• Verification must address repeated and systematic errors.
• Pivot tables help identify repeated issues in spreadsheets.
• COUNTA is used for counting text values.
• Find and Replace efficiently corrects known text errors.
• CASE statements handle repeated errors in SQL queries.
• Verification confirms that cleaned data matches expectations.
• These techniques ensure data integrity before analysis begins.