Cleaning Data in SQL and Integrating Sources

Why SQL Cleaning Matters

Raw operational data is almost never analysis-ready. Work-order systems, customer databases, and sensor logs are designed for transaction speed, not analytical accuracy. Before any chart or model can be trusted, you must resolve mismatched data types, inconsistent strings, unexpected NULLs, and records scattered across multiple tables. SQL is the most direct tool for this work because the cleaning happens inside the database, close to the data, without exporting files or risking version confusion.

This lab uses PostgreSQL, an open-source relational database used widely in government, healthcare, and technology. You will work in DBeaver or the psql command-line client.

Data Integrity and Workspace Safety

Before writing a single query, protect the source data. Always work on a copy of the dump, never the original file. In a professional environment, raw data is treated like evidence — altering it without an audit trail can invalidate analysis and, in regulated industries, create legal exposure. In this sandbox, your first action after loading the dump is to confirm you are connected to the correct schema and that you have only SELECT and CREATE VIEW privileges, not DROP or DELETE. If you are uncertain, ask your instructor before proceeding. This is the data-world equivalent of verifying a machine is de-energized before you touch it.

Understanding the Four Tables

The city work-order dataset arrives as four un-normalized tables:

• work_orders — one row per work order, with a numeric priority code and a status string
• locations — addresses and district codes for each site
• technicians — employee records including name fields entered by many different staff
• categories — lookup table mapping category codes to descriptions

Each table has its own quality problems. You will fix them individually using views before joining.

TRIM and LOWER: Standardizing Strings

`TRIM` removes leading and trailing whitespace from a string. Without it, the value `' Electrical '` will never match `'Electrical'` in a JOIN or a WHERE clause — they look the same on screen but differ in the database. `LOWER` converts all characters to lowercase so that `'SMITH'`, `'Smith'`, and `'smith'` collapse to one value.

Always apply both together on any string column used in a JOIN or GROUP BY:

```sql

SELECT TRIM(LOWER(technician_name)) AS technician_name

FROM technicians;

```

CAST: Fixing Data Types

When a numeric column is stored as `VARCHAR` — which happens when data is imported from CSV without a schema — arithmetic and comparisons break silently. `CAST` converts the stored value to the correct type:

```sql

SELECT CAST(estimated_hours AS NUMERIC(6,2)) AS estimated_hours

FROM work_orders;

```

If a value cannot be cast (for example, the string `'N/A'` cannot become a number), PostgreSQL raises an error. Inspect problem rows with a `WHERE` filter before casting the whole column.

COALESCE: Replacing NULLs

`COALESCE` returns the first non-NULL value from its argument list. It is the standard tool for filling gaps:

```sql

SELECT COALESCE(district_code, 'UNKNOWN') AS district_code

FROM locations;

```

In a LEFT JOIN, rows in the left table that have no match in the right table produce NULLs in every right-table column. Wrapping those columns in `COALESCE` prevents NULL propagation into your final dataset:

```sql

SELECT w.order_id,

COALESCE(c.category_desc, 'Uncategorized') AS category

FROM work_orders w

LEFT JOIN categories c ON w.category_code = c.category_code;

```

CASE-WHEN: Recoding Values

`CASE-WHEN` is SQL's conditional expression. Use it to translate numeric codes into meaningful labels that non-technical readers can understand:

```sql

CASE

WHEN priority_code = 1 THEN 'Critical'

WHEN priority_code = 2 THEN 'High'

WHEN priority_code = 3 THEN 'Medium'

ELSE 'Low'

END AS priority_label

```

Always include an `ELSE` clause. Without it, any value not matched by a `WHEN` branch returns NULL silently.

Saving Work as Views

A view stores a SELECT query under a name without copying data. After cleaning each table, save the result as a view so that your JOIN query references clean, consistent data:

```sql

CREATE OR REPLACE VIEW v_technicians AS

SELECT

tech_id,

TRIM(LOWER(first_name)) AS first_name,

TRIM(LOWER(last_name)) AS last_name

FROM technicians;

```

Validation: Checking Your Own Work

Cleaning without validation is incomplete. Run a final query that confirms three things: total row count, NULL counts in required columns, and referential consistency (every work order has a valid location). A simple approach:

```sql

SELECT

COUNT(*) AS total_rows,

COUNT(*) FILTER (WHERE district_code IS NULL) AS null_district,

COUNT(*) FILTER (WHERE technician_name IS NULL) AS null_tech

FROM v_analysis_ready;

```

Record every figure in the validation checklist. Zero NULLs in required columns and a row count matching the answer key are the acceptance criteria for this lab.