3. Cleaning Medicare Hospital Readmission Data with SQL

After exploring the CMS Medicare Hospital Readmissions dataset, I found that several columns contained missing values, nonnumeric values, and data stored in formats that required cleaning before analysis. Preparing the data ensured the dataset was reliable for subsequent SQL analysis and Power BI reporting.

Objective

In this step, I focused on:

• Reviewing the structure of the raw dataset
• Identifying missing and nonnumeric values
• Creating a cleaned table with appropriate data types
• Validating the cleaned table before using it for SQL analysis and Power BI

Step 1: Check the Raw Table Structure

The following query displays the column names, data types, and nullability settings in the imported table.

SELECT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Hospital_Readmissions'
ORDER BY ORDINAL_POSITION;

Step 2: Check for Missing Values

Before converting the data types, I checked several key columns for missing values.

SELECT
    COUNT(*) AS Total_Rows,
    COUNT(CASE
        WHEN Number_of_Discharges IS NULL THEN 1
    END) AS Missing_Discharges,
    COUNT(CASE
        WHEN Number_of_Readmissions IS NULL THEN 1
    END) AS Missing_Readmissions,
    COUNT(CASE
        WHEN Predicted_Readmission_Rate IS NULL THEN 1
    END) AS Missing_Predicted_Rate
FROM dbo.Hospital_Readmissions;

Some fields contained values such as “N/A” and “Too Few to Report.” Because these values cannot be converted directly into numeric data types, I used TRY_CAST, which safely returns NULL instead of generating a conversion error.

Step 3: Validate the Cleaned Table Schema

Before loading the cleaned data, I reviewed the destination table using sp_help.

sp_help 'dbo.Hospital_Readmissions_Cleaned';

During this review, I discovered that an earlier version of the cleaned table did not include several fields needed for later analysis: Expected_Readmission_Rate, Start_Date, and End_Date.

Because the number of values in the INSERT statement did not match the number of columns in the destination table, SQL Server returned an error. Reviewing the table schema helped me identify the problem before continuing with the Power BI portion of the project.

Step 4: Create the Complete Cleaned Table

I recreated the cleaned table with all of the columns required for future SQL analysis and Power BI reporting.

DROP TABLE IF EXISTS dbo.Hospital_Readmissions_Cleaned;

CREATE TABLE dbo.Hospital_Readmissions_Cleaned
(
    Facility_Name               NVARCHAR(300),
    Facility_ID                 INT,
    State                       VARCHAR(2),
    Measure_Name                NVARCHAR(300),
    Number_of_Discharges        INT,
    Number_of_Readmissions      INT,
    Excess_Readmission_Ratio    FLOAT,
    Predicted_Readmission_Rate  FLOAT,
    Expected_Readmission_Rate   FLOAT,
    Start_Date                  DATE,
    End_Date                    DATE,
    Cleaned_Date                DATETIME DEFAULT GETDATE()
);

The Cleaned_Date column records when the data was loaded into the cleaned table. Because it has a default value of GETDATE(), SQL Server automatically populates it during the insert.

Step 5: Load the Cleaned Data

Next, I loaded the cleaned data into the new table while converting the numeric and date fields to their appropriate data types.

INSERT INTO dbo.Hospital_Readmissions_Cleaned
(
    Facility_Name,
    Facility_ID,
    State,
    Measure_Name,
    Number_of_Discharges,
    Number_of_Readmissions,
    Excess_Readmission_Ratio,
    Predicted_Readmission_Rate,
    Expected_Readmission_Rate,
    Start_Date,
    End_Date
)
SELECT
    Facility_Name,
    Facility_ID,
    State,
    Measure_Name,
    TRY_CAST(Number_of_Discharges AS INT),
    TRY_CAST(Number_of_Readmissions AS INT),
    TRY_CAST(Excess_Readmission_Ratio AS FLOAT),
    TRY_CAST(Predicted_Readmission_Rate AS FLOAT),
    TRY_CAST(Expected_Readmission_Rate AS FLOAT),
    TRY_CAST(Start_Date AS DATE),
    TRY_CAST(End_Date AS DATE)
FROM dbo.Hospital_Readmissions;

Step 6: Verify the Cleaned Data

After loading the data, I reviewed a sample of the cleaned records.

SELECT TOP 20 *
FROM dbo.Hospital_Readmissions_Cleaned;

Finally, I compared the number of rows in the raw and cleaned tables to verify that every record had been loaded successfully.

SELECT
    (SELECT COUNT(*)
     FROM dbo.Hospital_Readmissions) AS Raw_Row_Count,

    (SELECT COUNT(*)
     FROM dbo.Hospital_Readmissions_Cleaned) AS Cleaned_Row_Count;

ETL Workflow

Import Raw CMS Data
        ↓
Review Data Quality
        ↓
Create the Cleaned Table
        ↓
Validate the Table Schema
        ↓
Convert Data Types
        ↓
Load the Cleaned Data
        ↓
Verify the Results

This is a basic ETL workflow:

Extract
Import the raw CMS Medicare dataset into SQL Server.

Transform
Clean the data, convert fields to appropriate data types, and preserve unavailable values as NULL.

Load
Insert the transformed records into a cleaned table for SQL analysis and Power BI.

Summary

In this step, I created a cleaned table named Hospital_Readmissions_Cleaned with appropriate numeric and date data types. Values such as “N/A” and “Too Few to Report” were preserved as NULL using TRY_CAST.

Before loading the data, I also validated the destination table schema. This helped identify missing columns and prevented an incomplete table from being used in later SQL analysis and Power BI reporting.

💡 What I Learned

Validating the destination table before loading data saved time during debugging. This experience reminded me that even when a SQL query is correct, the destination table should also be reviewed to ensure its structure matches the data being inserted.