How I Secured PHI in ETL Pipelines While Powering AI in Snowflake

Why PHI Data Feels Like a Ticking Time Bomb

Healthcare data is both priceless and dangerous. Priceless, because it fuels analytics, machine learning, and better patient outcomes. Dangerous, because a single leak of Protected Health Information (PHI) can destroy trust and trigger massive compliance penalties.

Moving PHI through ETL pipelines is like carrying a glass of water across a busy highway — every hop (source → transform → warehouse → analytics) is a chance to spill. Most data platforms promise “encryption at rest and in transit.” That’s fine for compliance checkboxes, but it doesn’t stop insiders, misconfigured access, or pipeline leaks.

So I built a model that flips the script:

• Encrypt PHI at the source
• Keep it encrypted through every ETL stage
• Store it encrypted in Snowflake
• Only decrypt just-in-time for authorized users via secure views

The best part? I could still train ML models and run GenAI workloads in Snowflake — without ever exposing raw PHI.

---

The Architecture in One Picture

1. Source: Encrypt PHI columns (like Name, SSN) with a natural key.
2. ETL: Treat ciphertext as an opaque blob. No decryption mid-pipeline.
3. Snowflake: Store encrypted values in a raw schema.
4. Views: Secure views/UDFs decrypt only for authorized roles.

---

Step 1: Encrypt at the Source

I don’t let raw PHI leave the system. Example: exporting patients from an EHR → encrypt sensitive columns with AES, using a derived key from patient ID.

PatientID, Name_enc, SSN_enc, Diagnosis 12345, 0x8ae...5f21, 0x7b10...9cfe, Hypertension 

No plain names, no SSNs, just ciphertext.

---

Step 2: Don’t Break ETL with Encrypted Fields

ETL can still:

• Move, join, filter using deterministic encryption (if needed).
• Aggregate non-PII features as usual.
• Keep logs clean (never write ciphertext to debug logs).

---

Step 3: Store Encrypted in Snowflake

PHI lands in a raw_encrypted schema. Snowflake encrypts at rest too, so you get double wrapping.

Key management options:

• Passphrase hidden in a secure view
• External KMS with external functions
• Third-party proxy (Protegrity, Baffle, etc.)

---

Step 4: Secure Views for Just-in-Time Decryption

Authorized users query through views. Example:

CREATE OR REPLACE SECURE VIEW phi_views.patients_secure_v AS SELECT    patient_id,   DECRYPT(name_enc, 'SuperSecretKey') AS patient_name,   DECRYPT(ssn_enc, 'SuperSecretKey') AS ssn,   diagnosis FROM raw_encrypted.patients_enc; 

Unauthorized roles? They only see ciphertext.

---

Bonus Round: GenAI & ML Inside Snowflake

Encrypting doesn’t mean killing analytics. Here’s how I still run ML + GenAI safely:

• Snowflake ML trains models on de-identified features:

from snowflake.ml.modeling.linear_model import LogisticRegression model = LogisticRegression(...).fit(train_df) 

• Secure UDFs score patients without exposing PII.
• Cortex + Cortex Search powers GenAI summaries over masked notes:

SELECT CORTEX_COMPLETE(   'snowflake-arctic',    OBJECT_CONSTRUCT('prompt','Summarize encounters','documents',(SELECT TOP 5 ...)) ); 

PHI stays masked in indexes. If a doctor must see names, a secure view decrypts only at query time.

---

Why This Matters

• Compliance: Checks the HIPAA box (encryption at all times).
• Security: Insider threats can’t casually browse PHI.
• Analytics: ML and GenAI still work fine on de-identified data.
• Peace of Mind: Encrypt everywhere, decrypt last.

---

Final Thought

PHI isn’t just “data.” It’s someone’s life story. My rule: treat it like kryptonite. Encrypt it at the source. Carry it encrypted everywhere. Only decrypt at the final hop, when you’re sure the user should see it.

Snowflake’s ML and GenAI stack make it possible to get insights without breaking that rule. And that, in my book, is the future of healthcare data pipelines.ss