Nashville Housing Data Cleaning & Transformation (MySQL)

Overview

This is an end-to-end SQL data cleaning project built in MySQL using the Nashville Housing dataset from Kaggle (~56,636 rows, 29 columns). The project walks through creating a database, importing raw CSV data, building a staging copy, identifying quality issues, and systematically cleaning the data using core SQL DDL and DML operations.

Business Context

Data cleaning is a foundational skill in any analytics role – real-world datasets rarely arrive analysis-ready. This project exists in the portfolio to fill the DDL/DML gap left by the BigQuery e-commerce analysis project, which focuses on SELECT-side analytics (CTEs, window functions, joins, aggregations). Together, the two SQL projects cover the full spectrum of practical SQL skills: querying and transforming data.

Objectives

• Demonstrate CREATE DATABASE, CREATE TABLE, ALTER TABLE, UPDATE, DELETE operations
• Apply string functions (TRIM, STR_TO_DATE) and type conversions
• Use self-joins to populate missing data from related rows
• Identify and remove duplicate rows using ROW_NUMBER() and temporary tables
• Build reusable views, performance indexes, and stored procedures

Dataset Overview

Nashville Housing Data from Kaggle (CC0 Public Domain), published by Timothy James. ~56,636 rows of Nashville property sale records from 2013–2016.

Key Columns

• Parcel ID, Land Use, Property Address, Property City
• Sale Date, Sale Price, Legal Reference, Sold As Vacant
• Owner Name, Address, City, State
• Acreage, Land Value, Building Value, Total Value
• Year Built, Bedrooms, Full Bath, Half Bath

SQL Techniques Demonstrated

• CREATE DATABASE, CREATE TABLE
• ALTER TABLE (ADD COLUMN, MODIFY COLUMN, DROP COLUMN)
• UPDATE...SET, UPDATE...JOIN (self-join)
• DELETE
• CREATE VIEW, CREATE INDEX, CREATE PROCEDURE
• STR_TO_DATE, TRIM
• ROW_NUMBER() OVER (PARTITION BY)
• CTE, CASE statements
• INFORMATION_SCHEMA queries
• EXPLAIN (query performance analysis)
• Staging table pattern (raw → staging → clean workflow)

View full SQL script

Created the nashville_housing database, imported the CSV via MySQL Workbench's Table Data Import Wizard, and created a staging copy to preserve the raw data. All columns were imported as TEXT to avoid import errors from mixed or inconsistent formatting.

Create Database

CREATE DATABASE nashville_housing;
USE nashville_housing;

Import CSV

-- import csv via import wizard

Verify Row Count

SELECT COUNT(*) FROM housing_raw;
-- Result: 56,636 rows

Create Staging Copy

CREATE TABLE housing_staging AS
SELECT * FROM housing_raw;

Why a staging table? The raw data stays untouched in housing_raw as a safety net. All cleaning and transformation happens on the housing_staging copy, so the original import can always be referenced or re-used if needed.

Before cleaning, I explored the data to establish a baseline and identify quality issues that needed to be addressed.

NULL & Blank Value Counts

SELECT
    SUM(CASE WHEN `Parcel ID` IS NULL OR TRIM(`Parcel ID`) = '' THEN 1 ELSE 0 END) AS ParcelID_nulls,
    SUM(CASE WHEN `Land Use` IS NULL OR TRIM(`Land Use`) = '' THEN 1 ELSE 0 END) AS LandUse_nulls,
    SUM(CASE WHEN `Property Address` IS NULL OR TRIM(`Property Address`) = '' THEN 1 ELSE 0 END) AS PropertyAddress_nulls,
    SUM(CASE WHEN `Property City` IS NULL OR TRIM(`Property City`) = '' THEN 1 ELSE 0 END) AS PropertyCity_nulls,
    SUM(CASE WHEN `Sale Date` IS NULL OR TRIM(`Sale Date`) = '' THEN 1 ELSE 0 END) AS SaleDate_nulls,
    SUM(CASE WHEN `Sale Price` IS NULL OR TRIM(`Sale Price`) = '' THEN 1 ELSE 0 END) AS SalePrice_nulls,
    SUM(CASE WHEN `Sold As Vacant` IS NULL OR TRIM(`Sold As Vacant`) = '' THEN 1 ELSE 0 END) AS SoldAsVacant_nulls,
    SUM(CASE WHEN `Owner Name` IS NULL OR TRIM(`Owner Name`) = '' THEN 1 ELSE 0 END) AS OwnerName_nulls,
    SUM(CASE WHEN `Address` IS NULL OR TRIM(`Address`) = '' THEN 1 ELSE 0 END) AS OwnerAddress_nulls,
    SUM(CASE WHEN `Acreage` IS NULL OR TRIM(`Acreage`) = '' THEN 1 ELSE 0 END) AS Acreage_nulls,
    SUM(CASE WHEN `Year Built` IS NULL OR TRIM(`Year Built`) = '' THEN 1 ELSE 0 END) AS YearBuilt_nulls,
    SUM(CASE WHEN `Bedrooms` IS NULL OR TRIM(`Bedrooms`) = '' THEN 1 ELSE 0 END) AS Bedrooms_nulls
FROM housing_staging;

Results

Sold As Vacant Distribution

SELECT `Sold As Vacant`, COUNT(*) AS count
FROM housing_staging
GROUP BY `Sold As Vacant`
ORDER BY count;
-- Result: Yes (4,895) and No (51,741) – already clean, no Y/N inconsistency

Duplicate Count

SELECT COUNT(*) AS duplicate_count
FROM (
    SELECT `Parcel ID`, `Property Address`, `Sale Price`, `Sale Date`, `Legal Reference`,
        ROW_NUMBER() OVER (
            PARTITION BY `Parcel ID`, `Property Address`, `Sale Price`, `Sale Date`, `Legal Reference`
            ORDER BY `Parcel ID`
        ) AS row_num
    FROM housing_staging
) duplicates
WHERE row_num > 1;
-- Result: 104 duplicate rows

Summary of Data Quality Issues

• 159 blank Property Addresses – fixable via Parcel ID self-join
• 104 duplicate rows – to be removed
• Sold As Vacant already clean – only Yes/No values, no Y/N inconsistency
• Sale Date stored as TEXT – needs DATE type conversion
• ~30,000–32,000 rows missing Owner/Acreage/YearBuilt/Bedrooms – these are condo entries and are not fixable from within the dataset
• Whitespace issues – leading/trailing spaces in multiple text columns

The Sale Date column was stored as TEXT after the CSV import. I added a new DATE column and converted the values using STR_TO_DATE.

Convert Sale Date to DATE Type

ALTER TABLE housing_staging
ADD COLUMN SaleDateClean DATE;

UPDATE housing_staging
SET SaleDateClean = STR_TO_DATE(`Sale Date`, '%Y-%m-%d');

Verify Data Types

SELECT
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'nashville_housing'
    AND TABLE_NAME = 'housing_staging'
    AND COLUMN_NAME IN ('Sale Date', 'SaleDateClean');
-- Result: Sale Date = text, SaleDateClean = date

Why does DATE type matter? Proper DATE typing enables date comparisons, date arithmetic (e.g., calculating days between sales), YEAR/MONTH extraction, and efficient indexing – none of which work reliably on TEXT columns.

159 rows had blank Property Addresses. Since rows with the same Parcel ID share the same address, I used a self-join to populate blanks from matching rows that already had an address.

Preview: Blank Addresses with Matching Parcel IDs

SELECT a.`Parcel ID`, a.`Property Address`, b.`Property Address`
FROM housing_staging a
JOIN housing_staging b
    ON a.`Parcel ID` = b.`Parcel ID`
    AND a.`Property Address` != b.`Property Address`
WHERE TRIM(a.`Property Address`) = ''
LIMIT 10;

Update Blank Addresses via Self-Join

UPDATE housing_staging a
JOIN housing_staging b
    ON a.`Parcel ID` = b.`Parcel ID`
    AND a.`Property Address` != b.`Property Address`
SET a.`Property Address` = b.`Property Address`
WHERE TRIM(a.`Property Address`) = '';
-- Result: 16 rows updated

Verify Remaining Blanks

SELECT
    SUM(CASE WHEN TRIM(`Property Address`) = '' OR `Property Address` IS NULL THEN 1 ELSE 0 END) AS remaining_blank_addresses
FROM housing_staging;
-- Result: 143 remaining (159 - 16 = 143)

Only 16 rows were fixed because those were the only blank-address rows whose Parcel ID matched another row with a populated address. The remaining 143 rows had Parcel IDs that did not appear elsewhere in the dataset with a valid address – so there was nothing to pull from. In a production setting, these could be resolved through external data sources, geocoding lookups, or manual research.

Trimmed leading and trailing whitespace from all key text columns using TRIM.

UPDATE housing_staging
SET
    `Property Address` = TRIM(`Property Address`),
    `Property City` = TRIM(`Property City`),
    `Owner Name` = TRIM(`Owner Name`),
    `Address` = TRIM(`Address`),
    `City` = TRIM(`City`),
    `State` = TRIM(`State`),
    `Land Use` = TRIM(`Land Use`),
    Grade = TRIM(Grade),
    `Exterior Wall` = TRIM(`Exterior Wall`),
    `Foundation Type` = TRIM(`Foundation Type`);
-- Result: 24,172 rows changed – nearly half the dataset had whitespace issues

Used ROW_NUMBER() with PARTITION BY to identify duplicates, created a temporary table of duplicate row IDs, then deleted them. MySQL doesn't support DELETE directly from a CTE, so the temporary table approach was used as a workaround.

Add Unique Row ID

ALTER TABLE housing_staging
ADD COLUMN row_id INT AUTO_INCREMENT PRIMARY KEY;

Create Temp Table of Duplicate Row IDs

CREATE TEMPORARY TABLE temp_duplicates AS
SELECT row_id
FROM (
    SELECT row_id,
        ROW_NUMBER() OVER (
            PARTITION BY `Parcel ID`, `Property Address`, `Sale Price`, `Sale Date`, `Legal Reference`
            ORDER BY row_id
        ) AS row_num
    FROM housing_staging
) ranked
WHERE row_num > 1;

Verify & Delete

SELECT COUNT(*) AS duplicates_to_delete FROM temp_duplicates;
-- Result: 104

DELETE FROM housing_staging
WHERE row_id IN (SELECT row_id FROM temp_duplicates);

SELECT COUNT(*) AS rows_after_dedup FROM housing_staging;
-- Result: 56,532 (56,636 - 104 = 56,532)

Removed columns that were redundant or not useful for analysis.

ALTER TABLE housing_staging
DROP COLUMN `Sale Date`,    -- replaced by SaleDateClean
DROP COLUMN `Tax District`, -- not useful for analysis
DROP COLUMN image,          -- not useful for analysis
DROP COLUMN row_id;         -- only needed for deduplication

Created a reusable VIEW with clean column aliases for downstream consumption, and added indexes on commonly queried columns to improve query performance.

Create Clean View

CREATE VIEW vw_housing_clean AS
SELECT
    `Parcel ID`,
    `Land Use`,
    `Property Address`,
    `Property City`,
    SaleDateClean AS SaleDate,
    `Sale Price`,
    `Legal Reference`,
    `Sold As Vacant`,
    `Multiple Parcels Involved in Sale`,
    `Owner Name`,
    `Address` AS OwnerAddress,
    `City` AS OwnerCity,
    `State` AS OwnerState,
    Acreage,
    `Land Value`,
    `Building Value`,
    `Total Value`,
    `Year Built`,
    Bedrooms,
    `Full Bath`,
    `Half Bath`
FROM housing_staging;

Add Performance Indexes

CREATE INDEX idx_parcel_id ON housing_staging(`Parcel ID`);
CREATE INDEX idx_sale_date ON housing_staging(SaleDateClean);
CREATE INDEX idx_property_city ON housing_staging(`Property City`(100));

What do indexes do? Indexes create optimized lookup structures that let MySQL find matching rows without scanning the entire table. These three columns were chosen because they are the most likely filter and join targets: Parcel ID for record lookups, SaleDateClean for date-range queries, and Property City for geographic filtering.

Before-and-after comparison showing every improvement made during the cleaning process:

EXPLAIN (Query Performance)

Used EXPLAIN to verify that the indexes are being used by the query optimizer:

EXPLAIN SELECT *
FROM housing_staging
WHERE `Property City` = 'NASHVILLE'
AND SaleDateClean BETWEEN '2014-01-01' AND '2014-12-31';

Key results from the EXPLAIN output:

• type: ref – MySQL is using an index to find matching rows (not scanning the full table)
• key: idx_property_city – confirms the Property City index is being used
• rows: ~28,488 – roughly half the table is scanned instead of all 56,532 rows

Transaction Control

The BEGIN / COMMIT / ROLLBACK pattern wraps destructive operations (like DELETE) in a safety net. Between BEGIN and COMMIT, nothing is saved permanently – if something goes wrong, ROLLBACK undoes the changes. This is especially important for DELETE operations, which are hard to recover from if performed incorrectly.

-- Example of how the deduplication DELETE should be wrapped in a transaction:
-- BEGIN;
-- DELETE FROM housing_staging WHERE row_id IN (SELECT row_id FROM temp_duplicates);
-- SELECT COUNT(*) FROM housing_staging; -- verify before committing
-- COMMIT; -- or ROLLBACK; if something went wrong

Stored Procedure

The full cleaning pipeline was wrapped in a stored procedure called clean_housing_data so it can be re-run on fresh data with a single command:

CALL clean_housing_data();

The procedure was created via the MySQL Workbench routine editor. It encapsulates all five cleaning steps (date conversion, address population, whitespace trimming, deduplication, and column drops) into a single reusable routine.

This project cleaned and transformed 56,000+ Nashville housing records from a raw CSV import into a structured, deduplicated, and indexed MySQL dataset – ready for downstream analysis or reporting.

The cleaning pipeline addressed date type conversions, missing address population via self-joins, whitespace trimming across 10 text columns, duplicate removal using ROW_NUMBER(), and column pruning. The final output includes a reusable VIEW, performance indexes, and a stored procedure that can re-run the entire pipeline on fresh data.

How this complements the BigQuery e-commerce project: The e-commerce analysis focuses on SELECT-side analytics – CTEs, window functions, aggregations, and business KPIs. This project covers the other half of SQL: DDL/DML operations that create, alter, update, and delete data. Together, the two projects demonstrate the full spectrum of practical SQL skills.

MySQL-specific syntax demonstrated includes STR_TO_DATE for date parsing, TRIM for whitespace cleaning, INFORMATION_SCHEMA queries for metadata inspection, and prefix-length indexing for TEXT columns.

View full SQL script