How To Audit Views In Snowflake?

Photo by Javardh on Unsplash

In this blog we are going to discuss on how we can set up audit on views. Earlier we can track the dml operation on tables which was useful, but we can’t do the same on views. Now with the recent introduction of streams on view now we can extend this capability to view as well with some limitation which will eventually go away in due course.

What is Stream?

You can read my blog on Table stream in below link. This will give you all the insight you need on stream.

Table Stream in Snowflake

What are the use cases for this feature?

There can be several use cases like below:

1. Trace the DML operation on view(and underline tables).
2. Trigger alerts based on certain kind of operation on views.
3. Send email notification based on certain threshold data.
4. Execute task based on business logic of data arrives in views(via underline tables)….many more.

How can we audit snowflake views?

With the recent enhancement on stream to include local views and secure views along with tables we can now track all DML operation on view’s underline native tables.

Note:

Materialized view is not supported at the moment. Only standard view & secure view are supported.

Are there any limitation which needs to be considered while planning for Streams on Views ?

Yes, few considerations are there to be taken into account before planning for this new enhancement.

1. All underline table must be native table only.
2. All operation on view is not permitted, below features are only supported at the moment-

— Union All

— Only Cross Join & Inner Join are supported for now.

— All kind of projection is supported.

— As many filters can be used.

— Nested subquery in from clause is supported provided they follow the other above restriction.

— Function are supported in selection, but they must be system-defined, scalar functions.

3. Below operation are not yet allowed:

• GROUP BY clauses
• QUALIFY clauses
• Subqueries not in the FROM clause
• Correlated subqueries
• LIMIT clauses

Is there any prerequisite to use stream on views?

As such there is no prerequisite to use stream on view except that either one of them is true:

1. Change tracking is already enabled on underline table if you are not owner of any underline table.
2. If you are owner of an underline table than creating stream will automatically enable change tracking for all the underline table.

How to validate if change tacking is enabled or not for table or view?

To check same for table or view you can look for change_tracking column value, it should be ON

SHOW TABLES LIKE 'EMPLOYEE';

SHOW VIEWS LIKE 'EMPLOYEE_DTL';

Table:

View:

What kind of privilege we need to create stream on view?

How about a quick demo on same?

Hope this blog helps you to get insight into new enhancement in streams on view. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find Me:

Subscribe to my YouTube Channel: https://www.youtube.com/c/RajivGuptaEverydayLearning

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me on LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/

#Keep learning #Keep Sharing #RajivGuptaEverydayLearning #SnowflakeDataSuperhero #RajivGupta