Snowflake Hospital Data Platform

What this project is

Snowflake Hospital Data Platform is a demonstrative healthcare data platform built on Snowflake to integrate clinical, administrative, and IoT data. The project combines multi-schema modeling, cleaning pipelines, governance, RBAC, sensitive-data masking, and operational analytics queries.

Technical context

A hospital system must manage clinical, administrative, and IoT-generated data while preserving consistent relationships among patients, admissions, diagnoses, staff, and vital signs.

The challenge goes beyond storage. Healthcare data includes sensitive personal information, requires differentiated access controls, and must remain useful for operational analytics without exposing unnecessary details.

The project therefore addresses three connected needs: organizing multiple domains into one model, validating potentially dirty IoT readings, and applying governance rules suitable for a healthcare context.

Objective

The objective was to design a demonstrative Snowflake data platform covering the full data lifecycle:

• separate clinical, administrative, IoT, and audit data into dedicated schemas;
• simulate sensor readings containing nulls, anomalies, and duplicates;
• transform staging data into a validated table through a SQL stored procedure;
• define different permissions for administrators, physicians, nurses, and analysts;
• mask tax codes, email addresses, and phone numbers according to the active role;
• produce dashboard queries for clinical and operational monitoring;
• demonstrate recovery and environment isolation with Time Travel and Zero-Copy Cloning.

The result is not a single table, but a small governed data system in which modeling, quality, security, and analytics belong to the same architecture.

Architecture

The HOSPITAL_DB database is organized into five logical schemas:

• CLINICAL_DATA for patients, admissions, and diagnoses;
• ADMINISTRATIVE for staff, departments, appointments, and billing;
• IOT_STAGING for raw sensor readings;
• IOT_DATA for clean and validated vital signs;
• AUDIT for access tracking.

Workloads are separated between two virtual warehouses. WH_ETL_LOAD handles loading and transformation, while WH_ANALYTICS is dedicated to dashboard and analytical queries. Both use auto-suspend and auto-resume.

IoT pipeline and data quality

The demo generates 150 IoT readings and adds three intentional duplicates. The raw dataset also contains null values and out-of-range measurements, including impossible heart rates, abnormal temperatures, and oxygen saturation values above 100%.

The SP_CLEAN_IOT_DATA stored procedure transfers data from staging to the final table while applying:

• clinical range validation for each measurement;
• conversion of invalid measurements to NULL;
• alert flag recalculation;
• mandatory field checks;
• deduplication through QUALIFY ROW_NUMBER() by patient and timestamp.

After cleaning, the final table contains 150 unique, queryable readings. A clustering key on patient and timestamp represents the intended pattern for a growing IoT table.

Governance and security

Security is modeled through four roles:

• HOSPITAL_ADMIN with full access;
• HOSPITAL_MEDICO with access to clinical and IoT data;
• HOSPITAL_INFERMIERE with read-only clinical access;
• HOSPITAL_ANALYST focused on analytics without direct PII exposure.

The role hierarchy allows physicians to inherit nurse privileges and administrators to inherit physician privileges. Three masking policies protect tax codes, email addresses, and phone numbers, returning complete, partially masked, or hidden values based on the current role.

The AUDIT schema completes the model with immutable access logs containing user, patient, action, resource, authorization status, and access reason.

Analytics

The project includes five analytical queries with CSV outputs:

1. patients with critical vital signs;
2. department occupancy and performance;
3. most frequent diagnoses and severity distribution;
4. hourly vital-sign trends and change from the previous hour using LAG;
5. clinical KPIs by physician, admissions, and case severity.

These queries show how the model can support operational monitoring and aggregate analytics through cross-schema joins, window functions, conditional aggregations, and derived indicators.

Continuity and environments

The project demonstrates two native Snowflake capabilities for resilience and development:

• Time Travel, tested by simulating an accidental patient deletion and recovering the record from a previous table state;
• Zero-Copy Cloning, used to create HOSPITAL_DB_TEST, verify change isolation, and preserve masking policies in the cloned environment.

Implemented scope and production scenario

The executed demo uses synthetic data generated directly inside Snowflake. The cleaning procedure, data model, RBAC, masking, Time Travel, cloning, and dashboard queries are part of the operational SQL script.

AWS S3 integration, Directory Tables, and Snowpipe are documented as a production extension but were not executed in the demo. The 15-minute task is also created but left suspended to avoid reprocessing static data; in a continuous workflow it should be paired with Snowflake Streams to process only new records.

Results

• 5 schemas separate the system's data domains;
• 2 warehouses isolate ETL and analytics workloads;
• 153 raw IoT records are reduced to 150 validated, unique readings;
• 4 roles define different access responsibilities;
• 3 masking policies protect key personal data fields;
• 5 dashboard queries generate clinical and operational outputs;
• Time Travel and Zero-Copy Cloning are verified through recovery and isolation scenarios.

Future evolution

• use a storage integration and secret management instead of static S3 credentials;
• activate Snowpipe with real notifications and Snowflake Streams for incremental processing;
• add secure views dedicated to the analyst role;
• introduce automated, versioned data quality tests;
• connect analytical queries to a complete BI dashboard;
• add monitoring, alerting, and CI/CD for Snowflake objects.

Technical FAQ

Why separate data into five schemas?

The separation makes boundaries among clinical, administrative, raw IoT, validated IoT, and audit data explicit. This improves readability, privilege management, and model maintenance.

How are invalid IoT values and duplicates handled?

The stored procedure checks accepted ranges, converts impossible measurements to null values, and uses QUALIFY ROW_NUMBER() to keep one reading per patient and timestamp.

What is the difference between RBAC and masking?

RBAC controls which objects and operations each role can access. Masking controls the value returned by a sensitive column, enabling differentiated visibility on the same table.

Is Snowpipe actually running?

No. The SQL documents the S3 stage, file format, and Snowpipe as a production scenario, while the demo uses synthetic data generated directly in Snowflake.

Why use Time Travel and Zero-Copy Cloning?

Time Travel helps recover accidentally deleted or modified data. Zero-Copy Cloning creates an isolated test environment without an initial full physical copy of the database.