SOWFLAKE SECURE DATA SHARING LAB demo

 

Provider:

-----------------------------------------------------------------------------------------------------------

--                                    SOWFLAKE SECURE DATA SHARING LAB                                   --

--               All commands/statements can be found via the link in the Video Description              --

-----------------------------------------------------------------------------------------------------------

-- Create a database & Schema

CREATE DATABASE product_db;

CREATE SCHEMA product_db.core_schema;

-- Create a database & Schema

CREATE OR REPLACE DATABASE product_db;

CREATE OR REPLACE SCHEMA product_db.core_schema;

-- Switch to the newly created database and schema

USE DATABASE product_db;

USE SCHEMA product_db.core_schema;

-- Create Products table

CREATE OR REPLACE TABLE products (

    product_id INTEGER,

    product_name VARCHAR(100),

    category VARCHAR(50),

    color VARCHAR(50),

    weight DECIMAL(10, 2)

);

-- Create Customer table

CREATE TABLE customers (

    customer_id INTEGER PRIMARY KEY,

    full_name VARCHAR(100),

    email VARCHAR(100),

    phone_number VARCHAR(20)

);

-- Create Orders tableGLOBAL_WEATHER_DB

CREATE TABLE orders (

    order_id INTEGER PRIMARY KEY,

    customer_id INTEGER,

    order_date DATE,

    total_amount DECIMAL(10, 2)

);

-- Insert data into Products table

INSERT INTO products (product_id, product_name, category, color, weight)

VALUES

    (1, 'Laptop', 'Electronics', 'Silver', 1.8),

    (2, 'T-shirt', 'Clothing', 'Red', 0.2),

    (3, 'Kitchen Mixer', 'Home & Kitchen', 'White', 3.5),

    (4, 'Headphones', 'Electronics', 'Black', 0.3),

    (5, 'Jeans', 'Clothing', 'Blue', 0.5);

   

---------------------------------------------------------------------------------------------------------------------------------

------------------------------- Please execute the commands above to create some objects for practice ---------------------------

---------------------------------------------------------------------------------------------------------------------------------

-- Set role to ACCOUNTADMIN

USE ROLE ACCOUNTADMIN;

-- Create or replace a share

CREATE OR REPLACE SHARE products_share;

-- Grant USAGE permissions on database and schema

GRANT USAGE ON DATABASE product_db TO SHARE products_share;

GRANT USAGE ON SCHEMA product_db.core_schema TO SHARE products_share;

-- Grant SELECT permissions on database and schema

GRANT SELECT ON TABLE product_db.core_schema.products TO SHARE products_share;

-- Manage consumer accounts

ALTER SHARE products_share ADD ACCOUNTS = YYJFVCA.GV88781;    -- Multiple accounts can be added using commas

-- ALTER SHARE products_share REMOVE ACCOUNTS = YYJFVCA.GV88781; -- To stop sharing with an account

ALTER SHARE products_share ADD ACCOUNTS = CW81583

Consumer:

-- Create a database & Schema

CREATE OR REPLACE DATABASE sales_db;

CREATE OR REPLACE SCHEMA sales_db.sales_schema;

-- Switch to the newly created database and schema

USE DATABASE sales_db;

USE SCHEMA sales_db.sales_schema;

-- Create Sales table

CREATE TABLE sales (

    sale_id INTEGER,

    product_id INTEGER,

    sale_date DATE,

    sale_quantity INTEGER,

    total_sale_amount DECIMAL(10, 2)

);

-- Insert data into Sales table

INSERT INTO sales (sale_id, product_id, sale_date, sale_quantity, total_sale_amount)

VALUES

    (1, 1, '2024-06-15', 2, 1999.98),

    (2, 2, '2024-06-15', 3, 59.97),

    (3, 1, '2024-06-16', 1, 999.99),

    (4, 3, '2024-06-16', 2, 699.98),

    (5, 4, '2024-06-17', 1, 149.99);

   

---------------------------------------------------------------------------------------------------------------------------------

------------------------------- Please execute the commands above to create some objects for practice ---------------------------

---------------------------------------------------------------------------------------------------------------------------------

-- Switch to Account Admin Role

USE ROLE ACCOUNTADMIN;

-- Show existing shares

SHOW SHARES;

-- Create database from share and set context

CREATE DATABASE products_db_shr FROM SHARE pffzvfj.ge15565.products_share;

-- Select and display data from products table

USE DATABASE products_db_shr;

USE SCHEMA core_schema;

SELECT * FROM products;

-- Joining products table from shared database and sales from consumer's own databse

SELECT

    s.sale_id,

    p.product_name,

    p.category,

    p.color,

    p.weight,

    s.sale_date,

    s.sale_quantity,

    s.total_sale_amount

FROM

    sales_db.sales_schema.sales s

JOIN

    products_db_shr.core_schema.products p ON s.product_id = p.product_id;

Hello Data Pros, and welcome back to another interesting part of our Snowflake learning series!

In our last video, we explored the concept of Cloning; and illustrated its functionality through easy-to-follow examples!

Today, we're shifting our focus to secure data sharing, and collaboration features offered by Snowflake!

Let’s jump right in!

 

Snowflake users often require sharing data across different Snowflake accounts. This includes sharing data between accounts within the same organization, such as from sales to marketing teams, as well as sharing data with external organizations like suppliers or vendors.

 

With its unique decoupled storage and compute architecture, Snowflake addresses these requirements more efficiently than any other data platform. When data is shared from one Snowflake account to another, the underlying data isn't physically copied; instead, sharing operates as a metadata operation.

 

This feature securely grants consumers access to the micro-partitions of shared objects in the provider account, and only creates necessary metadata on the consumer Snowflake account's cloud services layer.

When the consumer queries the shared data, they incur compute costs, but no additional storage charges are applied to their accounts.

 

Since no physical data movement is involved, setup is quick and easy.

Shared data remains current; any updates made in the provider Snowflake account are immediately available to the consumer accounts.

Providers have the flexibility to choose which accounts can access the data and can stop sharing at any time.

 

Snowflake data sharing operates on a provider and consumer model, and it's worth noting that the same Snowflake account can function as a provider for one dataset and as a consumer for another.

 

Consumer accounts accessing shared data have certain limitations:

Consumer accounts get read-only access to shared data. They can view and query data but cannot modify it or create new objects in the shared database. However, using fully qualified names, they can join shared tables with their own tables.

Cloning the shared database, schemas or tables is not allowed.

Time Travel is not available for the shared objects.

Shared databases and their objects cannot be shared again with other accounts.

Replicating shared databases isn't supported.

 

By default, Snowflake sharing only works within the same region and within the same cloud provider. However, providers can set up additional Snowflake accounts, and replicate the database manually, which will enable sharing across different regions or cloud providers.

 

In such cases, providers are charged for data transfer and additional storage related to the replication.

 

We will set up a share in our demo shortly, but for now please be aware that a share is an account-level Snowflake object, that includes information about which tables or objects are shared, and which Snowflake accounts are authorized to access it.

 

Not all Snowflake objects can be shared, you can only share the following Snowflake objects: Databases, Tables, Secure views, Secure materialized views, and Secure user-defined functions.

 

 

 

Let's explore the Snowflake UI and see sharing in action!

 

To manage secure data sharing in your Snowflake account, navigate to Data Products, and then Private Sharing.

The 'Shared With You' tab, lists all the shares that other Snowflake accounts have shared with you. These two shares are defaulted and come from Snowflake.

'Shared By Your Account' - displays what you have shared with other Snowflake accounts.

The 'Reader account' tab - allows you to create and manage your reader accounts, which we’ll cover later.

 

These actions can also be performed using SQL statements, which many organizations typically use; so let's proceed with the demo using SQL.

 

Imagine that I'm on the provider's Snowflake account. I already have existing objects in my account that I'll share with another Snowflake account as part of this exercise. However, if you want to create objects and set up some data, please execute these commands.

First, switch to the ACCOUNT ADMIN role.

Next, create a share named 'products-share' using the following command.

Then, proceed to grant the necessary privileges:

Grant USAGE privileges on both the underlying database and schema.

Grant SELECT privileges only to specific objects you intend to share.

 

Afterwards, Alter the share we created above, to add specific Snowflake accounts that you wish to grant access to.

 

You should get this account identifier from your consumers. This is the easiest way to find out the account identifier.

 

We’ve completed all necessary steps on the provider's end; now, let's switch to the consumer's Snowflake account.

Assume we already have a database named sales-db in the consumer account.

In sales-db, there is a sales table that only contains product-id, without additional product details such as name, category, etc.

Next, let's explore how to retrieve this information using the shared database from the provider's account.

 

At this point, when you execute SHOW SHARES, you should see the new share.

Copy the fully qualified name along with the owner's account details.

Using these details, let's create a new database from the share we have been granted access to.

Now, the consumer account can read the product data.

Moreover, the consumer can join this shared data with their own database tables to gain even greater insights.

 

That's all for today! Please stay tuned for our next video where we’ll explore more advanced Snowflake features!

If you found this video helpful, please give it a thumbs up, and hit that subscribe button to stay up-to-date!

Thank you for watching!