What I Learned Working with Raw Healthcare Data(SQL + PowerBI)
Hi Everyone ! In this blog I will share my experiences working with real world data using SQL and PowerBI.
The Core Principle: Clean in SQL, Model in Power BI
Oftentimes we are given data in the raw form as .csv file which is messy and unstructured.
It is tempting to jump straight into visualization. However, to provide decision making insights from this data, we have to clean, transform and generate reports.
The general guideline is to follow the "Clean in SQL, Model in Power BI" principle. By leveraging SQL for heavy data lifting and Power BI for analytics, you create a high-performance BI solution.
Data Definition: More Than Just Column Names
The first step of any project is about understanding the data set which is the most important step. This step shapes how data is cleaned and transformed.
Initially, I thought this just meant knowing the column names and data types. I quickly learned that in healthcare data, you must:
Identify Units of Measurement: Checking documentation for units (e.g., mg/dL vs. mmol/L) is critical. Wrong units lead to wrong thresholds, which ruins the entire analysis.
Identify Normal Ranges: Knowing what a "clinically plausible" value looks like helps you spot outliers early.
Staging Strategy:
Though that’s enough to perform data cleaning, data transformation needs more understanding of the data beyond basics like meaning and values. Most of the columns are self-explanatory, but it is not understood how they could be related for analysis.
For that deeper knowledge, I loaded data into a Staging table as TEXT to ensure a smooth import using the COPY command,without the command failing due to malformed date or strings in numeric columns.
Once in SQL, I could
cast them into relevant datatypes during the cleaning phase.
query tables to flag implausible values, handle nulls (like imputing age across visits), and remove duplicates.
The "Constraint Trap": A Lesson Learned
Initially, I started grouping related columns as tables and identified those as Fact and Dimension tables with Primary Key and Foreign Key constraints.
Then started loading raw data (null values, missing values, outliers, implausible values are not handled yet) into smaller fact and dimension tables.
Later when I did data cleaning directly on fact and dimension tables, I naturally got errors in SQL code. When I tried re-loading raw data to test improvised cleaning code, it failed because of primary key and foreign key constraints.
This was my biggest learning moment.
My Lesson:
Don’t touch the master staging table. Keep a raw copy (e.g., rawdata.patient_data) untouched.
Clean before constraining. Perform all transformations in a separate public.transformeddata table.
Apply constraints last. Only set PK/FK relationships once the data is clean and structured.
This approach reduces multiple ALTER statements in cleaning code and avoids confusion.
I also learned that PK/FK constraints are for protecting clean data, not for managing raw, messy imports.
Finally, I could agree with the industry standard best Practise for ETL Pipeline as in the below flowchart
Efficient ETL with Stored Procedures
Cleaning healthcare data involves repetitive tasks like fixing invalid strings and casting types for dozens of columns. To avoid writing the same code repeatedly, I developed a Stored Procedure as in the below picture.
This not only saved time but ensured the transformation logic was reusable and consistent across the entire pipeline.
Schema Design: Star vs. Galaxy
After cleaning, I reworked on finalizing tables. These tables are to be used in PowerBI for generating reports which means we should need less joins when querying to show reports.
So, we have to be careful not to normalize to the highest level like 3NF though this is appropriate in Database Schema design.
PowerBI works best with STAR schema. If we normalize tables to 3NF, we might end up with a Snowflake schema which necessitates querying with joins for visualizations.
For the Schema Design, We often get confused while identifying fact and dimension tables.
The general rule is to model all the quantitative or measurable values in the Fact table and Descriptive values (who, what, when, where) for those facts in the Dimension table giving context for filtering and grouping.
Using the above tip, columns related to Patient Visits, Lab results, BMI, BP readings and so on can be grouped as fact tables. Demographics data like age, gender, ethnicity, past medical history can be grouped as dimension tables.
With this grouping we run into a situation where we either make a big Fact table or multiple Fact tables,
A big Fact table because STAR schema should have a center Fact table and dimension tables surrounding this Fact Table or
Multiple Fact tables because it looks correct to group different columns by the categories. With multiple Fact tables again there comes a question if the schema model is now Galaxy schema or can it be still called a STAR schema.
Though we create multiple small FactTables, all those logically belong to the same big Fact table.
In this Model View Diagram, Demographics and PatientHistory are the only Dimension tables. We have all other tables with Labs, Ophthalmological, walking test, cognitive testing, Medications and Blood Pressure monitoring data as different Fact tables. But they are all logically one big Fact table with all the measurable values that change for each patient.
Whereas in the below pic, we see that Ambulatory Visits and ReadmissionRegistry are two different departments that share a common Dimension table, PatientInfo. Such a schema is considered a Galaxy schema. Each STAR schema has a center Fact table (Ambulatory Visits, ReadmissionRegistry) connected to its Dimension tables.
After a schema model is designed, tables are created in PostgreSQL and loaded with cleaned data. Then Primary Key and Foreign Key constraints are set up to establish relationships and granularity.
With the messy data now structured, cleaned, and constrained in PostgreSQL, it is ready for Power BI. We can now start report generation with Exploratory Data Analysis, moving on to Descriptive, Diagnostic, Predictive, and Prescriptive analysis as needed. By doing the "dirty work" in SQL, the Power BI environment remains fast, the DAX stays simple, and the insights are reliable.
Conclusion
Working with raw healthcare data is as much about process as it is about technical skill. The journey from a messy .csv to a structured Star Schema taught me that data integrity isn't just about writing code—it's about the order in which you execute it.
By respecting the staging process, cleaning data before applying strict constraints, and choosing a schema that favors analytics over normalization, you create a pipeline that is both robust and easy to maintain. Whether you are identifying patient trends or predicting readmission risks, a solid SQL foundation is what makes high-level analysis possible.
Key Takeaways
Load as TEXT first: Prevent import errors by using TEXT for staging columns.
Preserve Raw Data: Always keep an untouched "Master Staging" schema before you start transformations.
Constraints Last: Apply Primary and Foreign Keys only after the data is cleaned to avoid SQL errors during the ETL process.
Favor the Star Schema: Avoid over-normalizing for Power BI; a Star Schema keeps your DAX simple and your reports fast.
Reusability: Use Stored Procedures to automate repetitive data type conversions across multiple columns.
Do Not categorize Numeric data: Never Categorize Numeric data (like BMI, hypertension readings) during cleaning and transformation. Numeric data is important for plotting correlations using Scatter plots.
Thank you for reading till the end. Hope you gained some knowledge from my experience !!
