Snowflake Data Sharing Limitations, Tips, & Considerations

In this blog, I am going to list the current limitations and tips that many customers overlooked so that you can be aware of your data sharing design considerations.

Number of Managed Account 

If you reached the limit of the managed account number and you try to create a new reader account, it will fail with the following error message:

You may want to evaluate why you need so many managed accounts and consider redesigning to reduce it since the limit is typically high enough for most cases. If you do think you need that many managed accounts, you will need to reach out to Snowflake support to increase the number with the justification.

Across Region/Cloud Data Sharing 

When you are trying to share data from one region to another region, or one cloud to another cloud, you can NOT share them directly. For example, let’s do a data share from account MIGPROD in region  EU-WEST-1 to account MIGDEV in region US-WEST-2.

-- in provider account MIGPRO
create share MYSHARE;
grant usage on database DBSHARE1 to share MYSHARE;
grant usage on schema DBSHARE1.SCSHARE1 to share MYSHARE;
grant select on TABLE DBSHARE1.SCSHARE1.ORDER_HISTORY to share MYSHARE;

ALTER SHARE MYSHARE ADD ACCOUNT=MIGDEV;
-- SQL compilation error: Following accounts cannot be added to this share: MIGDEV.D        

Data sharing across region/across clouds require data replication. Direct data sharing is only allowed within the same region. Global Snowflake utilizes database replication to allow data providers to securely share data with data consumers across different regions and cloud platforms. You can follow the steps in the following document for Sharing Data Securely Across Regions and Cloud Platforms:https://docs.snowflake.com/en/user-guide/secure-data-sharing-across-regions-plaforms.html#sharing-data-securely-across-regions-and-cloud-platforms

Grant permission to function UDF from UI

When you try to grant access to secure UDF’s via GUI in the marketplace as a provider, you will get the below error: 

“Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.”

In the UDF definition, you need to use a fully qualified name for your object. this situation to avoid this error in the UI. For example,

-- A GEO Function DBINFO.PUBLIC.IP_GEO(VARCHAR) is defined as
SELECT country FROM location 
WHERE join_key = to_join_key(ip) 
AND to_int(ip) BETWEEN start_ip_int AND end_ip_int:        

After changing the definition to use fully qualified name for table LOCATION, it fixes the issue.

SELECT country FROM DBINFO.PUBLIC.location
WHERE join_key = to_join_key(ip) 
AND to_int(ip) BETWEEN start_ip_int AND end_ip_int;         

The current error message is misleading and it need to report a more meaningful error message like:

“Please full qualify the objects in your user defined function ‘Function Name’ with the format <database_name>.<schema_name>.<object_name>.

Share different tables from the same schema to the same consumer account 

This limitation is specific to this scenario:

There are 2 tables table1 and table2 in the same schema DBA.SCA. Table1 is shared to SHARE1 to a consumer account ACCT1, while table2 is shared to SHARE to the same account ACCT1. From the consumer account ACCT, create a database DB_A from the share SHARE1, DB_B from the share SHARE2. However you can see both table1 and table2. The reason is that table2 is shared via another share to the same reader account. 

The below script demonstrates the scenario. For Example:

-- On the producer account

create managed account TESTACCOUNT
admin_name = TEST , admin_password = 'Snow1234' ,
type = reader;

create database DBSHARE1 ;
use database DBSHARE1;
CREATE SCHEMA SCSHARE ;

CREATE TABLE TBSHARE(C1 INT, C2 INT);
INSERT INTO TBSHARE VALUES(1,2),(1,3);


CREATE TABLE TBSHARE2(C1 INT, C2 INT);
INSERT INTO TBSHARE2 VALUES(1,2),(1,3);

create or replace share my_share1  comment = 'Testing Data Sharing 1';
grant usage on database DBSHARE1 to share my_share1;
grant usage on schema dbshare1.scshare to share my_share1;
grant select on table dbshare1.scshare.TBSHARE to share my_share1;
show grants to share my_share1;
alter share my_share1 set accounts =’TESTACCOUNT’;

create or replace share my_share2  comment = 'Testing Data Sharing 2';
grant usage on database DBSHARE1 to share my_share2;
grant usage on schema dbshare1.scshare to share my_share2;
grant select on table dbshare1.scshare.TBSHARE2 to share my_share2;
alter share my_share2 set accounts =’TESTACCOUNT’;

--On Consumer account 
create or replace database db_share from share SFCSUPPORT2.MY_SHARE1 ;
GRANT IMPORTED PRIVILEGES ON DATABASE db_share TO ROLE "ACCOUNTADMIN";

use database db_share;
show schemas;
show tables in schema SCSHARE; -- Shows 1 table and its expected

create or replace database db_share2 from share SFCSUPPORT2.MY_SHARE2 ;
GRANT IMPORTED PRIVILEGES ON DATABASE db_share2 TO ROLE "ACCOUNTADMIN"

use database db_share2;
show schemas;
show tables in schema SCSHARE; 
-- Shows two tables. If you execute show tables on DB db_share, it shows 2 tables.        

This is a known issue that will be addressed soon. Even though share1 can see the table name on share2 or share can see the table name on share1 but only object name was seen, not the actual data. The producer can split the tables into two separate schemas: one schema for sh1 and another for sh2. In this case, the show command will return the correct entities.

Write access for consumer account on the underlying shared table

There is a limitation of providing Write access for consumer accounts on the underlying shared table.

Below operations are restricted on the shared database:

• INSERT
• UPDATE
• DELETE
• MERGE
• COPY INTO <table>
• CREATE PIPE
• CREATE SHARE
• CREATE STAGE

However, you can create a table in another database in the reader account (locally) and copy the table data from the shared database. For example,

create or replace table sharetest.public.dbsharetable a
select * from db_share.scshare.vwshare2;        

Where sharetest.public.dbsharetable is a table in a newly created database in share and db_share.scshare.vwshare2 is a table from database db_share created by share.

More details on this can be found in:

https://snowflakecomputing.atlassian.net/wiki/spaces/EN/pages/369524813/Managed+reader+account

Why do you need to write access to the table in the producer account? What is the use case? Typically you are not allowed to modify the producer’s table because the actual data belongs to the producer. So you might want to check with your producer for your use case and there might be legal enforcement or process to go through. 

Grant on a shared database can’t use regular Grant command

If you try to grant on a shared database from the consumer account with a regular grant command, it will fail with a compilation error. For example, 

grant usage on database xandr_consent_db to role mech_etl_ddl_rtg_role ;
-- SQL compilation error: Granting individual privileges on imported databases is not allowed. Use 'GRANT IMPORTED PRIVILEGES' instead        

“Grant usage” command is the syntax for a regular database [non-share database]. Grant usage on a shared database is not allowed from the consumer, instead, the correct syntax is “grant imported privileges on '' a shared database.

You can refer to snowflake official documentations for more details, such as some links as follows: 

https://docs.snowflake.com/en/user-guide/data-exchange-marketplace-privileges.html#granting-the-imported-privileges-privilege-to-other-roles

https://docs.snowflake.com/en/user-guide/data-share-consumers.html#granting-privileges-on-a-shared-database 

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html 

Grants below share database level are not allowed

If you try to grant on schema and table in a shared database from the consumer account.

And Grant is not executed with the error message “Insufficient privileges”. For example, 

grant usage on schema xandr_consent_db.consent to role mech_etl_ddl_rtg_role ; -- xandr_consent_db is a share databas
-- Grant not executed: Insufficient privileges.e        

Grant on children objects within a database created from a share is not allowed from the consumer, as they belong to the producer account. 

There are different workarounds/approaches with what we have today:

• If both producer and consumer belong to you, you can consider data replication and use Disaster Recovery strategy, and keep the secondary in sync with the primary database.
• Create one secure view per table, grant to different shares on the consumer side.

Hopefully, the list of these limitations, Tips, and considerations can save you some time from the “trap” that most users can easily step on.  

Disclaimer: As my personal blog, any views or opinions or advice represented in this blog are my personal views and belong solely to me.