Snowflake Data Sharing - Complete video tutorial

Snowflake's data sharing feature lets you share your data with other account holders. Since data is shared as readonly, consumers do not incur any charges but they only pay for the compute resource they use.

Checkout the detailed instructions video on my channel

LInk to this video - https://www.youtube.com/watch?v=GcrEK-VFEB8&list=PLUxVA4dlJgcz90Vbufh6o6p8Rrew-lnE3

Snowflake Secure Views

Snowflake's secure views let you abstract the logic and hide the underlying tables structures and their relations. These secure views help you manage data security and privacy.

In this video i reviewed all the details related to secure views and demo of using them.

Commands used in the video to demonstrate snowflake's secure views. Checkout the detailed video on my channel - https://www.youtube.com/watch?v=CRZQjxP10G0&list=PLUxVA4dlJgcz90Vbufh6o6p8Rrew-lnE3&index=3


--LIST PATIENTS
SELECT * FROM PATIENTS;


CREATE OR REPLACE SECURE VIEW ORG_PATIENTS AS
SELECT * FROM PATIENTS ;


SELECT * FROM ORG_PATIENTS ;

SHOW VIEWS;


SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID,USER_ACCESS.ACCOUNT_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID;

insert into USER_ACCESS values ('Baylor Hospital','');
insert into USER_ACCESS values ('Mayo Clinic','');




create table USER_ACCESS
(
    ORG_ID VARCHAR(50),
    ACCOUNT_ID varchar(50)
);

insert into USER_ACCESS values ('Baylor Hospital','MN30689');
insert into USER_ACCESS values ('Mayo Clinic','PO51568');

SELECT * FROM USER_ACCESS; 

TRUNCATE  TABLE USER_ACCESS SET ACCOUNT_ID='PO51568' WHERE 
ORG_ID='Mayo Clinic';

SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID,USER_ACCESS.ACCOUNT_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID;


select CURRENT_ACCOUNT();
select CURRENT_ROLE ();


--CREATE A SECURE VIEW
CREATE OR REPLACE SECURE VIEW ORG_PATIENTS AS
SELECT FULLNAME,CITY,USER_ACCESS.ORG_ID FROM patients 
INNER JOIN USER_ACCESS ON PATIENTS.ORG_ID=USER_ACCESS.ORG_ID
WHERE ACCOUNT_ID=CURRENT_ACCOUNT();



select * from ORG_PATIENTS;

SHOW VIEWS;
describe view ORG_PATIENTS;

describe view mycustomerview;
describe view ORG_PATIENTS;


select get_ddl('view', 'ORG_PATIENTS') view_defn;

Snowflake Time Travel - A complete guide

In the recent years IT Roles such as Database administrators are becoming  less and less relevant as most cloud based databases and datawarehouses are managed by IT engineers needing very little or no administration.

Snowflake already has very little administration. You do not need to do typical DBA tasks like index tables, partitioning or deal with space related issues. Time travel is another feature that lets developers self serve dba related task of reverting or restoring the database after you do issue some inadvertent commands.

In this detailed hands on video I explained about parameters that affect time travel and how to utilize this feature to revert the accidental or intentional database changes. 

Here is the video link - https://www.youtube.com/watch?v=CRZQjxP10G0&list=PLUxVA4dlJgcz90Vbufh6o6p8Rrew-lnE3&index=3

Here are the list of commands used in the video

-- DATA_RETENTION_TIME_IN_DAYS

show parameters;
show parameters in database SALES;
show parameters in warehouse XMALLFORFINANCE;
show parameters in account;
show parameters like '%DATA%';
show parameters like 'DATA%' in account;
--CHANGES DATA RETENTION FOR THE DATABASEshow parameters in database SALES;alter DATABASE SALES set DATA_RETENTION_TIME_IN_DAYS = 1 ;

--CHANGES DATA RETENTION FOR THE WHOLE ACCOUNTshow parameters like 'DATA%' in account;alter ACCOUNT set DATA_RETENTION_TIME_IN_DAYS = 1 ;

select * from PATIENTS;
INSERT INTO PATIENTS values  (5,'TEST PATIENT','AMSTERDAM','Mayo Clinic');select * from PATIENTS at(offset => -60*5);
delete from PATIENTS where id=5;drop table sales.snow.PATIENTS ;
--Over a dayselect * from PATIENTS at(offset => -60*500000);

select * from PATIENTS at(timestamp => 'Sat, 16 Mar 2019 16:20:00 -0700'::timestamp);

select * from user_access;
--Before changes make by a specific queryselect * from patients before(statement => '025e545d-fc23-4e8d-9ac5-335943a1bec2');
create table restored_table clone user_access  at(timestamp => 'Mon, 09 May 2019 01:01:00 +0300'::timestamp);    create table restored_table clone user_access  at(offset => -60*10);    select * from restored_table;  drop table restored_table;    --Schema as existed 1 hour before  create schema restored_schema clone snow at(offset => -3600);  drop schema restored_schema;    create database restored_db clone sales  before(statement => '025e545d-fc23-4e8d-9ac5-335943a1bec2');  drop database restored_db;  show tables history  in sales.snow;show tables history  in sales;---Restoring objects
undrop table patients;
undrop schema snow;
undrop database sales;

More details are available in snowflake's documentation page - https://docs.snowflake.net/manuals/user-guide/data-time-travel.html

Check out my youtube channel for more videos - https://www.youtube.com/channel/UCT3bqK2QL93j-IFYFYbvjWQ