Deduplication and Referential Integrity

Why Deduplication and Referential Integrity Matter

Dirty data costs organizations real money and produces misleading analysis. Two of the most common structural problems in relational datasets are duplicate records and referential-integrity violations. If a business-license table contains the same license twice, every count, rate, and trend you compute is wrong. If an orders table references a customer ID that does not exist in the customers table, joins silently drop rows or return NULL-filled results. Fixing these problems before analysis—and logging every change—is a professional obligation, not an optional step.

Exact Duplicates vs. Near-Duplicates

An exact duplicate is a row where every field matches another row byte-for-byte. pandas makes these easy to handle. `df.drop_duplicates()` with no arguments removes rows that are identical across all columns. The `subset` argument lets you define what "duplicate" means for your business context. For example, a business-license table might legitimately have two rows with the same business name if the business holds licenses for different locations. Dropping on `subset=['license_number']` would be appropriate; dropping on `subset=['business_name']` alone would incorrectly remove valid records. Always justify your subset choice in comments so a future analyst understands the decision.

A near-duplicate is harder: the same real-world entity appears with slight spelling differences, abbreviations, or data-entry errors (`"Smith & Sons LLC"` vs. `"Smith and Sons, LLC"`). These cannot be caught by exact matching. Fuzzy matching measures string similarity with a score from 0 to 100. The `rapidfuzz` library provides fast, production-ready fuzzy matching in Python. Its `fuzz.ratio` and `fuzz.token_sort_ratio` functions are the most commonly used scorers. `token_sort_ratio` sorts the tokens alphabetically before comparing, which helps with word-order differences.

Setting and Reviewing a Similarity Threshold

Choosing a threshold is a judgment call backed by domain knowledge. A common starting point is 85–90 out of 100. Matches above your threshold are likely the same entity; matches below are likely different. The dangerous zone is the borderline range (roughly 75–90), where you must review manually and record your accept or reject decision. Never auto-accept every match above an arbitrary cutoff without spot-checking a sample—false positives in fuzzy matching can silently merge records that should remain separate.

Document every decision in a change log CSV with at minimum these columns: `record_id`, `action` (removed / corrected / merged), `reason`, `analyst_initials`, `timestamp`. This log is your audit trail. If a stakeholder questions why a record is missing, the log is your defense.

Referential Integrity in Relational Databases

A foreign key in one table must match a primary key that actually exists in the referenced table. When it does not, you have an orphaned record. Orphaned orders (orders whose `customer_id` does not appear in the customers table) will disappear from any INNER JOIN, silently reducing your row counts. A NULL in a primary-key column is a separate violation—primary keys must be unique and non-null by definition.

To find orphaned foreign keys in SQL use a LEFT JOIN and filter for NULL on the right side:

```sql

SELECT o.order_id

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.customer_id

WHERE c.customer_id IS NULL;

```

To find NULL primary keys:

```sql

SELECT COUNT(*) FROM customers WHERE customer_id IS NULL;

```

Once you identify violations, resolution options are:

• DELETE the orphaned order if it is genuinely invalid.
• UPDATE the foreign key to a correct customer ID if you can determine the right value from other fields.
• INSERT a placeholder customer record if the business requires order history to be preserved.

Always run a COUNT query before and after your fix and record both numbers in your change log.

Safe Data-Modification Practices

Before running any DELETE or UPDATE in a production-like environment, follow these steps:

1. Back up or snapshot the table, or work in a sandbox schema.

2. Run a SELECT first using the exact same WHERE clause to confirm which rows will be affected.

3. Wrap statements in a transaction (`BEGIN; ... ROLLBACK;`) to preview effects before committing.

4. Never modify source data in place without version control or a backup. In pandas, work on a copy (`df.copy()`) so the original DataFrame remains intact for comparison.

These habits protect you and your organization from irreversible data loss. In a professional setting, destructive operations on shared databases typically require a supervisor sign-off recorded in a ticket or change-management system.

Connecting to Credentials

Deduplication and referential integrity appear across all four credentials you are working toward. CompTIA Data+ tests data-quality concepts directly. The Google Data Analytics Certificate covers data cleaning as a core competency. IBM Data Science Professional includes data wrangling with pandas. Microsoft PL-300 tests understanding of data model relationships, which depends on intact foreign keys. Mastering these skills now pays dividends on every exam and in every job.