Data Specialist building with AI

Overview

A comprehensive data cleaning exercise using the Nashville Housing dataset, demonstrating SQL proficiency for data quality improvement. This project showcases essential data engineering skills for preparing real-world datasets for analysis.

Data Quality Issues Addressed

1. Date Format Standardization

ALTER TABLE NashvilleHousing
ALTER COLUMN SaleDate DATE;

2. Category Unification

Consolidated inconsistent land use categories:

UPDATE NashvilleHousing
SET LandUse = 'VACANT RESIDENTIAL LAND'
WHERE LandUse = 'VACANT RES LAND';

3. Missing Value Imputation

Used self-joins to populate missing property addresses from matching ParcelIDs:

UPDATE a
SET PropertyAddress = ISNULL(a.PropertyAddress, b.PropertyAddress)
FROM NashvilleHousing a
JOIN NashvilleHousing b
  ON a.ParcelID = b.ParcelID
  AND a.[UniqueID] <> b.[UniqueID]
WHERE a.PropertyAddress IS NULL;

4. Address Decomposition

Split combined address fields into granular components using PARSENAME:

-- PropertyAddress → PropertySplitAddress, PropertySplitCity
-- OwnerAddress → OwnerSplitAddress, OwnerSplitCity, OwnerSplitState

5. Value Standardization

Converted Y/N to Yes/No for consistency:

UPDATE NashvilleHousing
SET SoldAsVacant = CASE 
  WHEN SoldAsVacant = 'Y' THEN 'Yes'
  WHEN SoldAsVacant = 'N' THEN 'No'
  ELSE SoldAsVacant
END;

6. Column Cleanup

Removed redundant columns after extracting useful data:

ALTER TABLE NashvilleHousing
DROP COLUMN OwnerAddress, TaxDistrict, PropertyAddress;

Stored Procedure

Created a reusable procedure for identifying low-priced properties:

CREATE PROCEDURE small_prices
  @Unique_ID_param FLOAT
AS
BEGIN
  SELECT *
  FROM NashvilleHousing
  WHERE SalesPrice <= 150000
    AND UniqueID = @Unique_ID_param;
END

Skills Demonstrated

• ALTER TABLE operations for schema modifications
• UPDATE with JOIN for data imputation
• PARSENAME for string parsing
• CASE statements for value transformation
• Stored Procedures with parameters
• Data quality assessment and remediation

Why This Project?

Unable to share SQL work from previous roles due to confidentiality, I chose this well-known dataset to demonstrate data cleaning fundamentals—a critical skill for any data engineering or analytics position.