Can I use Snowflake's Row Access Policies with Fivetran?

Question

I want to implement row-level security using Snowflake's row access policies. Can I use this feature with Fivetran?

Environment

Destination: Snowflake

Answer

Yes, you can use Snowflake's row access policies with Fivetran, but with an important caveat: you should not apply these policies to the Fivetran user or role in Snowflake.

Fivetran uses merge operations to update data in Snowflake. These operations rely on seeing all rows in the target table to accurately determine which rows need to be updated, inserted, or deleted.

If you apply row access policies to the Fivetran user or role, Fivetran may not be able to see all the rows in the target table. This can cause merge operations to fail or, in some cases, create duplicate rows, compromising data integrity and accuracy.

Recommendation

Fivetran recommends the following:

• Exclude Fivetran: When setting up the row access policies, explicitly exclude the Fivetran user or role to ensure Fivetran has complete visibility of the data.

• Apply policies to end users: Apply the row access policies to your end-user roles to implement row-level security for your users.

• Monitor and test: After implementing the row access policies, monitor your syncs and validate the data integrity.