Snowflake: Streams on Views

Read Time:4 Minute, 15 Second

During this post we will discuss about the latest feature i.e., STREAMS on VIEWS. Consider the scenario you have a regular View in your database which is based on multiple tables. Due to security compliance these tables are not exposed to the Custom Role in system. Indeed, the Custom role has access to VIEW only. As part of the daily load, we are getting DELTA data in our tables which includes deletion and insertion of new records. These changes are readily available to the VIEW based on these underlying tables. Currently there is no way to track if the record has been deleted or inserted into the respective tables. The VIEW displays only the latest information with no audit of any changes. As per the business requirement we need to keep track of any DML operations performed on underlying tables and which reflects on to the VIEW. So in short need to audit the VIEWS and synch the changes to final table for history purpose.

So the requirement is:

Auditing should be available on the VIEW and keep track the DML operations performed on VIEW or indirectly to the underlying tables.
There is a target table with similar structure of VIEW in addition of three attributes:.

STARTDATE, ENDDATE, ACTIVE_FLG to track the changes.

Technical implementation:

Technical implementation:

Say we have PROD_TBL and ACCESSORY_DETAILS table in our database.

We have created the below VIEW on these tables.

CREATE OR REPLACE VIEW PROD_ACCESSORY_VIEW AS SELECT CUST_NUM,CUST_BAL,INV_NO,INV_AMT,CRID,SSN,ACCESSORY,STATUS,ACC_AMT FROM PROD_TBL A ,ACCESSORY_DETAILS B WHERE A.CUST_NUM = B.CUST_ID;

Now Create a Stream on the VIEW to track the changes.

create or replace stream VIEW_STREAM on view PROD_ACCESSORY_VIEW;

Create the Target table similar to VIEW with 3 additional columns. This will keep the history of all the records which are modified or deleted in the VIEW.

CREATE OR REPLACE TABLE PROD_ACCESSORY_TABLE ( CUST_NUM STRING,CUST_BAL decimal(10, 2), INV_NO varchar(50),INV_AMT decimal(10, 2), CRID STRING,SSN STRING, ACCESSORY STRING,STATUS STRING, ACC_AMT decimal(10, 2),startdate date, enddate date,active_flg char(1) )

Now populate the data to both tables and verify the records in VIEW.

Importantly observe the STREAM an d it will be having the same 75 records.

Now finally merge these changes to the target table using below SQL:

merge into PROD_ACCESSORY_TABLE T1 using(select * from VIEW_STREAM) T2 on T1.CUST_NUM = T2.CUST_NUM when matched and (T2.METADATA$ACTION='DELETE') THEN update set ENDDATE = TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP) , active_flg = 'N' when not matched and (T2.METADATA$ACTION='INSERT') THEN insert (CUST_NUM,CUST_BAL,INV_NO,INV_AMT,CRID,SSN,ACCESSORY,STATUS,ACC_AMT,STARTDATE,ENDDATE,active_flg) values(T2.CUST_NUM,T2.CUST_BAL,T2.INV_NO,T2.INV_AMT,T2.CRID,T2.SSN,T2.ACCESSORY,T2.STATUS,T2.ACC_AMT,TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP),NULL,'Y')

DELTA approach:

Assume on next day ,Customer and Accessory details got loaded into the tables and will see how STREAM would behave for this DELTA record.

Lets Insert one record in underlying table and observe the STREAM. If the Joining conditions satisfies for the VIEW then STREAM would capture the new record details.

INSERT INTO PROD_TBL VALUES ('C-1000','Active',3000,'I-1000',200,'ABCD','9876','7677777777','C-1000@support.com');

INSERT INTO ACCESSORY_DETAILS VALUES ('C-1000','Ink','In-use',200,'Y');

SELECT * FROM VIEW_STREAM;