How to Apply Row Access Policies with Multiple Columns in Snowflake: A Medical Claims Data Example

By Julie Heckman, Director of Data Engineering & Snowflake Data Superhero

In my last post, I walked through a simple example of how to apply row access policies to allow people from different departments to access data in the same table but restricted to only the rows that they were allowed to see. 

But what happens if your data governance policy is more complicated?  And frankly, we all know that a simple example Is rarely what you end up seeing when you start digging into real business requirements.

For this example, I’m going to use some sample medical claim data.  Do not worry – none of this data is real so no concerns with privacy issues here.

Let’s suppose there is a table that contains multiple columns that determine access for different types of data viewers.  We have both different facilities that want to see the claims data pertaining to their specific facility and payers that need to see claims data they paid.  In snowflake, you cannot attach two different row access policies to a table but you can create a row access policy on multiple columns and put the logic in the policy how to handle different columns.

• claim_id
• claim_line_segment
• claim_start_date
• claim_end_date
• facility
• payment_amount
• attending_physician_npi
• operating_physician_npi
• admission_date
• admitting_diagnosis_code
• discharged_date
• payer_code

Our business requirements are as follows:

1. A facility (ie a hospital) can see all the claims that occurred at their facility across multiple payers.
2. A payer can see all the claims that occurred from their plans across any facility.

So to create two different kind of access (one for payers and one for facilities) on the same table, we’ll create a row access policy that depends on both columns.

create or replace row access policy rap_claim as (col_facility varchar, col_payer varchar) returns boolean ->
  exists (
	select 1 from 
		role_facility_payer
	where
		(facility = col_facility OR payor = col_payer)
		and role = current_role()
);

This policy also uses a lookup table to create the mapping access from the role to the facility or payer. Below is an example of what that looks like. Each role is mapped to a facility or payer that it is allowed to see. This allows for flexility when adding new payers and facilities as they can just be added as rows to this table.

Want to try it out? All the code to create this example is in this github respository. I’d love to know what other situations you might have that could use this type of multi-column row access policy.