Data Cleansing and Standardization Techniques

Data Cleansing and Standardization Techniques

Why Cleansing Matters

Raw operational data is rarely ready for analysis or reporting. Fields arrive with inconsistent formatting, missing values, duplicate rows, and values that violate business rules. Data cleansing is the process of detecting and correcting — or rejecting — those problems so that the target table meets defined quality standards. In a staging-to-target ETL (Extract, Transform, Load) pattern, you never modify the source system. Instead, you land raw data in a staging area, apply corrections there, and promote only acceptable records to the target table. Records that cannot be fixed are quarantined with a documented reason.

The Staging-to-Target Pattern

A staging table is a temporary or permanent holding area that mirrors the structure of the source. Working in staging protects the source and gives you a repeatable, auditable trail:

1. Extract — copy raw data into the staging table as-is.

2. Transform — apply cleansing rules in staging: fix what can be fixed, flag what cannot.

3. Load — INSERT only clean records into the target table; route rejected records to a quarantine (rejected_records) table with a `rejection_reason` column.

This approach supports rollback, re-processing, and audit requirements that governance frameworks such as DAMA-DMBOK expect.

Common SQL Cleansing Operations

NULL substitution — Replace a NULL with a known default only when the business rule permits it. Use `COALESCE` or an `UPDATE … WHERE column IS NULL` statement. Document why the substitution is acceptable.

Whitespace trimming — Leading and trailing spaces cause silent join failures and inflate distinct-value counts. Use `TRIM(column)` or `BTRIM(column)` in PostgreSQL. Apply as an UPDATE on the staging table before any other transformation.

Date standardization — Store dates as the PostgreSQL `DATE` or `TIMESTAMP` type, never as free-text. Use `TO_DATE(string, 'format')` to parse common patterns such as `MM/DD/YYYY` or `DD-MON-YYYY`, and insert the result into a properly typed column.

Phone number standardization — Strip all non-digit characters with `REGEXP_REPLACE(phone, '[^0-9]', '', 'g')`, then validate that the result is exactly 10 digits for a US number. Records with fewer or more digits are unfixable and go to quarantine.

Flagging uncorrectable records — When a value violates a rule and no safe correction exists, insert the row into `rejected_records` with a plain-language `rejection_reason`, then DELETE it from staging so it never reaches the target.

Deduplication — Use a `ROW_NUMBER()` window function partitioned by the natural key to identify duplicates, then delete all but the row with the lowest or most recent primary key.

Python-Based Standardization with pandas

SQL handles structured transformations well, but semi-structured fields such as free-text addresses and email addresses are better handled with regular expressions in Python.

pandas loads the staging table via `psycopg2` (or `SQLAlchemy`) into a `DataFrame`. You then apply `Series.str.replace()` or `Series.str.extract()` with compiled regex patterns. For email fields, a pattern such as `^[\w.+-]+@[\w-]+\.[a-zA-Z]{2,}$` validates format; rows that do not match are routed to a rejected DataFrame.

After transformation, use `DataFrame.to_sql()` or a `psycopg2` `executemany()` call to reload clean records into the PostgreSQL target table. Always use parameterized queries — never string-format user-supplied values into SQL, because that creates SQL injection risk.

Handling encoding issues — When reading CSVs, specify `encoding='utf-8'` explicitly. Unexpected characters can corrupt string comparisons and should be stripped or replaced before regex rules run.

Validating Results with Great Expectations

After loading the target table, re-run the same Great Expectations expectation suite you built in the profiling lab. Great Expectations returns a validation result that shows how many expectations passed and failed. Calculate the improvement delta:

• Before score = (expectations passed before cleansing) / (total expectations)
• After score = (expectations passed after cleansing) / (total expectations)
• Delta = After score − Before score

Document every expectation that still fails and explain whether the failure is a data problem or a rule that needs revision.

Safety and Data Integrity Practices

Database work carries specific risks that are analogous to physical safety rules:

• Always work in a non-production environment. Cleansing scripts run against staging, never directly against a live production database without a tested, approved change-control process.
• Take a backup or snapshot before running bulk UPDATEs or DELETEs. A single erroneous WHERE clause can destroy thousands of rows.
• Wrap destructive statements in a transaction. Use `BEGIN; … ROLLBACK;` to test the effect of a DELETE or UPDATE before committing.
• Use parameterized queries in Python. Hard-coded string concatenation in SQL is a security hazard recognized by OWASP and is contrary to secure coding standards.
• Log every transformation. Maintain an audit table that records which script ran, when, how many rows were affected, and the operator's username. Governance auditors and forensic reviews depend on this trail.
• Protect PII. Phone numbers, email addresses, and addresses are personally identifiable information. Do not log raw PII values to console output or unprotected log files. Follow your organization's data classification policy.

Putting It Together

A complete cleansing pipeline for this lab runs in this order: trim whitespace → standardize dates and phones in SQL → run Python regex rules on address and email fields → reload → quarantine rejects with reasons → validate with Great Expectations → document. The target table must pass at least 90 percent of expectation assertions to meet the lesson threshold. Any rejected record must appear in the `rejected_records` table with a populated `rejection_reason` column.