Snowflake Healthcare Data Warehouse

A production-ready Snowflake data warehouse architecture for healthcare analytics, demonstrating enterprise patterns including RBAC, dynamic data masking, and HIPAA-compliant data handling.

Built entirely with infrastructure-as-code (Terraform), version-controlled transformations (dbt), and code-based BI (Evidence).

🎯 Overview

This project demonstrates:

• Infrastructure as Code: All Snowflake resources provisioned via Terraform
• Data Modeling Best Practices: dbt with staging → intermediate → marts pattern
• Enterprise Security: Role-based access control (RBAC) and dynamic data masking
• HIPAA Compliance Patterns: PII protection with role-specific data views
• Code-Based BI: Evidence dashboards with version-controlled SQL

Target Audience

Healthcare technology companies needing scalable, secure analytics infrastructure.

Demo Purpose

Showcase Snowflake architecture skills for fractional consulting opportunities.

📊 Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│                        SNOWFLAKE DATA WAREHOUSE                              │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐    ┌────────────┐ │
│  │   RAW_DB     │    │  STAGING_DB  │    │   MARTS_DB   │    │ANALYTICS_DB│ │
│  │              │    │              │    │              │    │            │ │
│  │  ┌────────┐  │    │  ┌────────┐  │    │  ┌────────┐  │    │            │ │
│  │  │SYNTHEA │  │───▶│  │HEALTH  │  │───▶│  │ CORE   │  │    │  Secure    │ │
│  │  │ Schema │  │    │  │ CARE   │  │    │  │ Schema │  │    │  Shares    │ │
│  │  └────────┘  │    │  └────────┘  │    │  └────────┘  │    │            │ │
│  │              │    │              │    │  ┌────────┐  │    │            │ │
│  │  Raw CSVs    │    │  Cleaned &   │    │  │ANALYT  │  │    │            │ │
│  │  from        │    │  Standardized│    │  │ ICS    │  │    │            │ │
│  │  Synthea     │    │  Data        │    │  │ Schema │  │    │            │ │
│  │              │    │              │    │  └────────┘  │    │            │ │
│  └──────────────┘    └──────────────┘    └──────────────┘    └────────────┘ │
│         │                   │                   │                           │
│         ▼                   ▼                   ▼                           │
│  ┌─────────────────────────────────────────────────────────────────────────┐│
│  │                        ROLE HIERARCHY                                    ││
│  │                                                                          ││
│  │                        ACCOUNTADMIN                                      ││
│  │                             │                                            ││
│  │                         SYSADMIN                                         ││
│  │                    ┌────────┼────────┬────────────┐                      ││
│  │                    ▼        ▼        ▼            ▼                      ││
│  │               LOADER    TRANS-    ANALYST     READER                     ││
│  │               _ROLE    FORMER     _ROLE       _ROLE                      ││
│  │                        _ROLE                                             ││
│  │                                                                          ││
│  │   Write RAW    Read RAW      Read MARTS      Read via                    ││
│  │   Only         Write all     (masked)        Data Share                  ││
│  └─────────────────────────────────────────────────────────────────────────┘│
│                                                                              │
│  ┌─────────────────────────────────────────────────────────────────────────┐│
│  │                      DYNAMIC DATA MASKING                                ││
│  │                                                                          ││
│  │   TRANSFORMER_ROLE sees:        ANALYST_ROLE sees:                       ││
│  │   SSN: 999-12-3456              SSN: ***-**-3456                         ││
│  │   Name: John Smith              Name: J*** S***                          ││
│  │   DOB: 1985-03-15               DOB: 1985-01-01 (year only)              ││
│  └─────────────────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────────────────┘

🛠 Technology Stack

📋 Prerequisites

Before you begin, ensure you have:

• Snowflake Account - Sign up at snowflake.com for a 30-day trial
• Terraform v1.5+ - Install guide
• Python 3.9+ - Download
• Node.js 18+ - Download
• Java (for Synthea) - OpenJDK
• OpenSSL - Usually pre-installed on macOS/Linux
• Git - Install guide

🚀 Quick Start

1. Clone the Repository

git clone https://github.com/yourusername/snowflake-healthtech-warehouse.git
cd snowflake-healthtech-warehouse

2. Generate RSA Key Pair for Snowflake Authentication

# Create directory for keys
mkdir -p ~/.snowflake

# Generate private key (PKCS8 format, no password)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake/rsa_key.p8 -nocrypt

# Generate public key
openssl rsa -in ~/.snowflake/rsa_key.p8 -pubout -out ~/.snowflake/rsa_key.pub

# View public key (you'll need this for Snowflake)
cat ~/.snowflake/rsa_key.pub

3. Configure Snowflake User

Log into Snowflake and run:

-- Replace 'your_user' with your actual username
-- Copy the public key content (without the BEGIN/END lines)
ALTER USER your_user SET RSA_PUBLIC_KEY='MIIBIjANBgkq...';

4. Configure Environment

# Copy example environment file
cp .env.example .env

# Edit .env with your settings
nano .env  # or use your preferred editor

Required settings:

SNOWFLAKE_ACCOUNT=xy12345.us-east-1   # Your account identifier
SNOWFLAKE_USER=your_username           # Your username
SNOWFLAKE_PRIVATE_KEY_PATH=~/.snowflake/rsa_key.p8

5. Run Setup Script

./init.sh

This will:

• Validate prerequisites
• Load environment variables
• Create Python virtual environment
• Install dependencies
• Initialize Terraform
• Set up Evidence

6. Deploy Infrastructure

cd terraform
terraform plan     # Review changes
terraform apply    # Deploy resources

7. Generate and Load Data

# Generate synthetic patient data
./data/scripts/generate_synthea.sh

# Load data into Snowflake
python scripts/load_data.py

8. Run dbt Transformations

source venv/bin/activate
cd dbt
dbt deps    # Install packages
dbt run     # Run models
dbt test    # Validate data

9. Start Evidence Dashboards

cd evidence
npm run dev
# Open http://localhost:3000

10. Verify Security & Infrastructure (Optional)

Run the comprehensive verification scripts to ensure everything is working:

source venv/bin/activate

# Verify all infrastructure was provisioned correctly
python scripts/verify_infrastructure.py

# Test RBAC permissions and data masking
python scripts/test_security.py

# Or run all tests at once
python scripts/run_all_tests.py

The security test demonstrates:

• RBAC: LOADER_ROLE can only access RAW_DB, ANALYST_ROLE can only access MARTS_DB
• Masking: Same query returns different results based on role:
  • TRANSFORMER_ROLE sees: SSN 999-12-3456, Name John Smith, DOB 1985-03-15
  • ANALYST_ROLE sees: SSN ***-**-3456, Name J***, DOB 1985-01-01

📁 Project Structure

snowflake-healthtech-warehouse/
├── .env.example              # Environment template (COMMITTED)
├── .env                      # Actual secrets (GITIGNORED)
├── .gitignore                # Comprehensive ignore rules
├── requirements.txt          # Python dependencies
├── init.sh                   # Setup script
├── README.md                 # This file
├── AGENT_NOTES.md            # Notes for AI agents
│
├── terraform/                # Infrastructure as Code
│   ├── main.tf               # Root module
│   ├── variables.tf          # Input variables
│   ├── outputs.tf            # Output values
│   ├── providers.tf          # Snowflake provider
│   ├── terraform.tfvars.example
│   └── modules/
│       ├── databases/        # RAW, STAGING, MARTS, ANALYTICS
│       ├── warehouses/       # LOADING, TRANSFORM, ANALYTICS
│       ├── roles/            # LOADER, TRANSFORMER, ANALYST, READER
│       ├── schemas/          # SYNTHEA, HEALTHCARE, CORE, ANALYTICS
│       ├── stages/           # Internal stages for loading
│       └── security/         # Masking policies
│
├── dbt/                      # Data transformations
│   ├── dbt_project.yml
│   ├── profiles.yml.example
│   ├── packages.yml
│   ├── models/
│   │   ├── sources.yml       # RAW_DB sources
│   │   ├── staging/          # 1:1 with source (views)
│   │   ├── intermediate/     # Business logic joins
│   │   └── marts/            # Dimensional models (tables)
│   │       ├── core/         # dim_patient, fact_encounters
│   │       └── analytics/    # Aggregated metrics
│   ├── tests/
│   ├── macros/
│   └── seeds/
│
├── data/                     # Data generation
│   ├── synthea/
│   │   └── csv/              # Generated patient data
│   └── scripts/
│       └── generate_synthea.sh
│
├── scripts/                  # Python utilities
│   └── load_data.py          # CSV to Snowflake loader
│
├── evidence/                 # BI dashboards
│   ├── package.json
│   ├── evidence.config.yaml
│   ├── sources/
│   │   └── snowflake.yaml.example
│   └── pages/
│       ├── index.md          # Executive dashboard
│       ├── patients.md       # Demographics
│       ├── encounters.md     # Visit analytics
│       └── security-demo.md  # Masking demonstration
│
├── .github/workflows/        # CI/CD
│   ├── terraform-plan.yml
│   ├── terraform-apply.yml
│   └── dbt-run.yml
│
└── docs/                     # Documentation
    ├── architecture.md
    ├── security.md
    └── demo-script.md

🔐 Security Features

Role-Based Access Control (RBAC)

Dynamic Data Masking

Same query, different results based on role:

SELECT patient_id, ssn, first_name, last_name, date_of_birth
FROM marts_db.core.dim_patient
LIMIT 3;

As TRANSFORMER_ROLE:

As ANALYST_ROLE:

📚 Documentation

• Architecture Details
• Security & RBAC
• Demo Script

🔄 CI/CD Setup

The project includes GitHub Actions workflows for automated deployment.

Required GitHub Secrets

Configure these secrets in your repository settings (Settings → Secrets and variables → Actions):

Setting Up the Private Key Secret

# Copy the entire private key file content
cat ~/.snowflake/rsa_key.p8 | pbcopy  # macOS
# Or manually copy the contents including BEGIN/END lines

Then paste as the SNOWFLAKE_PRIVATE_KEY secret value in GitHub.

Workflow Overview

Environment Protection

For production safety, configure an "environment" in GitHub:

1. Go to Settings → Environments → New environment
2. Name it production
3. Enable "Required reviewers" for apply workflows

🎬 Demo

A 5-7 minute video demonstration is available showing:

1. Terraform infrastructure deployment
2. Synthea data generation and loading
3. dbt transformations and testing
4. Dynamic data masking in action
5. Evidence dashboards with live data

🤝 Contributing

This is a portfolio project, but suggestions and feedback are welcome!

📄 License

MIT License - See LICENSE for details.

👤 Author

Built as a demonstration of Snowflake architecture patterns for healthcare analytics.

---

Note: This project uses synthetic data generated by Synthea. No real patient information is included.