Least Privilege for Pipeline item to ingest data from SQL Server to Snowflake

Hello harrybao0901,

1. ADLS Gen2 staging

This is expected behavior.

When using Snowflake as the sink, Fabric Copy activity does not support configuring ADLS Gen2 as a staging location. The staging process is handled internally by Fabric and Snowflake, so you cannot create or select an external container for this scenario.

References:
https://learn.microsoft.com/fabric/data-factory/copy-data-activity
https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake

So this is not a permission or configuration issue on your side.

2. Role grant order (Role A vs Role B)

Snowflake does not use the order of role grants to determine access.

From Snowflake access control model:
https://docs.snowflake.com/en/user-guide/security-access-control-overview

• Access is evaluated at runtime
• Only the active role matters
• Grant timing (before or after another role) has no effect on execution

So differences between schema X and schema Y are not related to when roles were granted.

3. Original issue (least privilege for Pipeline ingestion)

For Fabric Pipeline (SQL Server → Snowflake), the role needs:

• Warehouse: USAGE
• Database/Schema: USAGE
• Table: INSERT, SELECT (DELETE/TRUNCATE optional depending on load pattern)
• Schema-level: CREATE STAGE, CREATE FILE FORMAT (if Fabric creates them)
• If external staging is used: USAGE on storage integration + stage access

Incremental load is supported, but it is not automatic. You need to implement it using:

• watermark column logic, or
• CDC-based approach

Summary

• ADLS staging is not supported in this scenario (by design)
• Role grant order does not affect Snowflake execution
• Pipeline permissions depend on correct alignment of table, schema, and staging access
• Incremental load must be implemented manually

This aligns with Fabric and Snowflake behavior for this scenario.