Nashville Housing Data Cleaning Project- SQL Server Studio

📂 GitHub Repository: View Full SQL Script & Reproducible Files on GitHub

Executive Summary & Business Impact

Raw real estate transaction records frequently suffer from missing values, unformatted date strings, embedded delimited addresses, and duplicate rows that invalidate downstream market reporting.

This project implements an end-to-end data cleaning pipeline in Microsoft SQL Server (T-SQL) to transform over 56,000 raw housing records into a standardized, production-ready relational dataset.

• Target Dataset: ~56,000 Residential Property Records (Nashville, TN)

• Core Tools: SQL Server Management Studio (SSMS), T-SQL, DDL Schema Design, Window Functions, String Manipulation

• Key Outcome: Resolved 100% of missing property addresses via relational self-joins, eliminated duplicate rows, parsed unstructured address strings into queryable attributes, and enforced clean schema naming.

  
  

Key Technical Workflows & Code Snippets

1. Reproducible Schema Creation & BULK INSERT

To ensure complete pipeline reproducibility, a explicit table schema was constructed using DDL and populated directly from the raw source CSV using RFC 4180 parsing parameters.

-- 1. Table Creation & Bulk CSV Ingestion
IF OBJECT_ID('dbo.NashvilleHousing', 'U') IS NOT NULL
	DROP TABLE dbo.NashvilleHousing;
Go
CREATE TABLE dbo.NashvilleHousing (
    UniqueID VARCHAR(255),
    ParcelID VARCHAR(255),
    LandUse VARCHAR(255),
    PropertyAddress VARCHAR(255),
    SaleDate VARCHAR(255),
    SalePrice VARCHAR(255),
    LegalReference VARCHAR(255),
    SoldAsVacant VARCHAR(255),
    OwnerName VARCHAR(255),
    OwnerAddress VARCHAR(255),
    Acreage VARCHAR(255),
    TaxDistrict VARCHAR(255),
    LandValue VARCHAR(255),
    BuildingValue VARCHAR(255),
    TotalValue VARCHAR(255),
    YearBuilt VARCHAR(255),
    Bedrooms VARCHAR(255),
    FullBath VARCHAR(255),
    HalfBath VARCHAR(255)
);
GO

BULK INSERT dbo.NashvilleHousing 
FROM "C:\Analyst Projects\Excel Operations\Nashville Housing Data for Data Cleaning.csv"
WITH (
    FORMAT = 'CSV',             -- RFC 4180 CSV parser
    FIELDQUOTE = '"',           -- Handles embedded commas inside text
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);
GO

2. Standardizing Date Formats & Imputing Missing Addresses

Date records were cast to a strict DATE data type. Missing property address records were identified and imputed by performing a relational self-join matching on ParcelID across distinct UniqueID values.

-- 2. Standardize Date Format
ALTER TABLE dbo.NashvilleHousing ADD SaleDateConverted DATE;
GO

UPDATE dbo.NashvilleHousing
SET SaleDateConverted = CONVERT(Date, SaleDate);

-- 3. Populate Missing Property Address Data via Self-Join
UPDATE a
SET PropertyAddress = ISNULL(a.PropertyAddress, b.PropertyAddress)
FROM dbo.NashvilleHousing a
JOIN dbo.NashvilleHousing b
    ON a.ParcelID = b.ParcelID
   AND a.UniqueID <> b.UniqueID
WHERE a.PropertyAddress IS NULL;

3. Parsing Delimited Strings (SUBSTRING, CHARINDEX, PARSENAME)

Unstructured address strings were broken into individual, normalized relational columns (Address, City, State) using position-based string functions for property addresses and object-name parsing (PARSENAME) for owner addresses.

-- 4. Split PropertyAddress (Street, City) & OwnerAddress (Street, City, State)
ALTER TABLE dbo.NashvilleHousing
ADD PropertySplitAddress NVARCHAR(255),
    PropertySplitCity NVARCHAR(255),
    OwnerSplitAddress NVARCHAR(255),
    OwnerSplitCity NVARCHAR(255),
    OwnerSplitState NVARCHAR(255);
GO

-- Parse Property Address using SUBSTRING & CHARINDEX
UPDATE dbo.NashvilleHousing
SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) - 1),
    PropertySplitCity    = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1, LEN(PropertyAddress));

-- Parse Owner Address using PARSENAME
UPDATE dbo.NashvilleHousing
SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress, ',', '.'), 3),
    OwnerSplitCity    = PARSENAME(REPLACE(OwnerAddress, ',', '.'), 2),
    OwnerSplitState   = PARSENAME(REPLACE(OwnerAddress, ',', '.'), 1);

4. Binary Flag Standardization & Window Function Deduplication

Categorical fields were updated to handle both numeric (1/0) and string (Y/N) flags. Duplicate transaction records were partitioned and removed using a Common Table Expression (CTE) combined with the ROW_NUMBER() window function.

-- 5. Standardize 'Sold as Vacant' Flags
ALTER TABLE dbo.NashvilleHousing ALTER COLUMN SoldAsVacant VARCHAR(10);
GO

UPDATE dbo.NashvilleHousing
SET SoldAsVacant = CASE
    WHEN SoldAsVacant IN ('Y', '1') THEN 'Yes'
    WHEN SoldAsVacant IN ('N', '0') THEN 'No'
    ELSE SoldAsVacant
END;

-- 6. Remove Duplicate Records via CTE & ROW_NUMBER()
WITH RowNumCTE AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY ParcelID,
                         PropertyAddress,
                         SalePrice,
                         SaleDate,
                         LegalReference
            ORDER BY UniqueID
        ) AS row_num
    FROM dbo.NashvilleHousing
)
DELETE FROM RowNumCTE WHERE row_num > 1;

5. Final Schema Refinement (sp_rename)

LESSONS LEARNED / DOCUMENTATION NOTE: During initial execution, an attempt to rename a column using UPDATE instead of sp_rename overwrote data in place. Table was re-ingested and processing resumed smoothly.

Redundant staging columns were removed, and new split attributes were renamed using system stored procedures (sp_rename) to deliver clean, production-ready column names for downstream BI integration.

• Key SQL Concepts: Common Table Expressions (CTEs), Window Functions (ROW_NUMBER()), PARTITION BY

-- 7. Drop Unused Staging Columns
ALTER TABLE dbo.NashvilleHousing
DROP COLUMN OwnerAddress, TaxDistrict, PropertyAddress, SaleDate;
GO

-- 8. Rename Cleansed Columns for Final Presentation
EXEC sp_rename 'dbo.NashvilleHousing.PropertySplitAddress', 'Address', 'COLUMN';
EXEC sp_rename 'dbo.NashvilleHousing.PropertySplitCity', 'City', 'COLUMN';
EXEC sp_rename 'dbo.NashvilleHousing.OwnerSplitAddress', 'OwnerAddress', 'COLUMN';
EXEC sp_rename 'dbo.NashvilleHousing.OwnerSplitCity', 'OwnerCity', 'COLUMN';
EXEC sp_rename 'dbo.NashvilleHousing.OwnerSplitState', 'OwnerState', 'COLUMN';
EXEC sp_rename 'dbo.NashvilleHousing.SaleDateConverted', 'SaleDate', 'COLUMN';
GO

-- Final Verification Query
SELECT TOP 100 * FROM dbo.NashvilleHousing;

Key Validation & Data Quality Results

• Null Imputation Check: Successfully recovered 100+ missing property addresses using matching ParcelID keys.

• Flag Consistency: Verified 100% value standardization across the SoldAsVacant column (Yes / No).

• Record Integrity: Eliminated duplicate records across matching parcel, price, date, and legal reference keys, preventing artificial inflation of housing market sales volume.