3. Cleaning Medicare Hospital Readmission Data with SQL

Now that the raw Medicare data has been imported into SQL Server, the next step is to clean and prepare it for analysis. Real-world healthcare data often contains missing values, inconsistent formats, and columns stored with incorrect data types. Cleaning the data is therefore an important part of the analytics workflow.

Objective

• Review the structure of the raw data
• Identify missing or nonnumeric values
• Create a cleaned table with appropriate data types
• Validate the cleaned table before using it for 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 columns, I checked how many values were missing in several important fields.

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 in the source data contain values such as N/A or Too Few to Report. These values cannot be converted directly to numeric data types. I used TRY_CAST so that SQL Server would return NULL instead of producing a conversion error.

Step 3: Validate the Cleaned Table Schema

Before loading the cleaned data, I reviewed the destination table using SQL Server’s sp_help command.

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 identify the problem before I continued to Power BI.

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

The following statement inserts the data into the cleaned table while converting the numeric and date fields to 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;

I also compared the row counts in the raw and cleaned tables to confirm that all source records were loaded.

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 data.
• Transform: Convert the fields to appropriate data types and preserve unavailable values as NULL.
• Load: Insert the transformed records into a cleaned analytical table.

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 through the use of TRY_CAST.

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

💡 What I Learned

Validating the destination table schema before loading data can prevent insert errors and save debugging time later. Even when a query looks correct, the destination table should always be reviewed to ensure the column definitions match the data being inserted.