Data sharing with dynamic tables¶

How data is shared with dynamic tables¶

Providers can share selected dynamic tables with other Snowflake accounts using direct shares or listings, which allow data to be shared between accounts without physically moving it. The process involves creating a share of a database within the provider’s account, granting database access within the provider’s account, granting access privileges, and adding other objects to the share.

For more information, see About Secure Data Sharing and Data sharing and collaboration in Snowflake.

A data sharing provider can choose to grant the SELECT privilege on a single dynamic table or grant the SELECT privilege on all dynamic tables in a database, as shown in the following examples.

GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA mydb.public TO SHARE share1;

GRANT SELECT ON DYNAMIC TABLE mydb.public TO SHARE share1;

For more details, see GRANT <privilege> … TO SHARE.

Create a dynamic table to ingest shared data¶

When you use a dynamic table to ingest shared data, the query can’t select from a shared dynamic table or a shared secure view that references an upstream dynamic table.

To create a dynamic table to ingest shared data, do the following:

1. Ensure that you have the right privileges, and create a database from a share and grant privileges on it.

   CREATE DATABASE my_shared_db FROM SHARE provider_account.share1;
   

   Copy

2. Grant privileges to the shared database.

3. Create a shared dynamic table.

CREATE OR REPLACE DYNAMIC TABLE my_dynamic_table
  TARGET_LAG = '1 day'
  WAREHOUSE = mywh
  AS
    SELECT * FROM my_shared_db.public.mydb;

Copy

Note

Change tracking must be enabled on all underlying objects used by a dynamic table. To use a dynamic table to ingest shared data, the data sharing provider needs to enable change_tracking on the shared object. For more information, see Enable change tracking.