Snowflake Database - Snowflake Queries to Monitor Snowflake

Name: Snowflake Database - Snowflake Queries to Monitor Snowflake
Rating: 4.3 (30 reviews)

Over two dozen queries that enable you to monitor all aspects of Snowflake

Created byRandy Minder

Last updated 9/2021

English

What you'll learn

Queries to monitor all aspects of role-based access
Queries to monitor users, logins, failed login and failed login rates
Long running queries
Longest running queries
Blocked queries
Virtual warehouse usage and credit consumption
Large partition scan queries
Data Transfers and ingestion

Course content

7 sections • 34 lectures • 2h 57m total length

Introduction1:54
Introduction to the course

Privileges3:20
In this lecture we show a query to list all privileges in your account for a particular database
Roles2:16
Basic role related queries
Privileges granted to roles6:57
A query that shows all privileges granted to all roles, with column explanations
Roles and privileges granted to users 13:40
A query that generates all roles and privileges granted to users. But, not quite right.
Roles and privileges granted to users 25:47
A little more thorough query to generate roles and privileges granted to users using recursion
Average query execution time by role5:46

List of active / inactive users, active users and information about a user4:22
In this lecture we show several different ways of generating user information
Users never logged in and "stale" users5:02
Queries to demonstrate how to find users who have never logged into their account and "stale" users
Logins and failed logins7:50
In this lecture we demonstrate some queries to analyze user login history and failure rates

Queries that monitor virtual warehouse events8:34
In this lecture we create some very useful queries for monitoring various warehouse events
Determining warehouse load4:38
A query to determine warehouse load on one or more warehouses
Warehouse credits consumed - Part 113:04
In this lecture we discuss several queries that calculate warehouse credits consumed
Warehouse credits consumed - Part 27:12
More queries that calculate warehouse credits consumed

Monitoring data transfers2:57
A query to monitor data transfers from one cloud provider to another or to/from the same provider but in a different geographical region.
Monitoring ingestion via COPY INTO and Snowpipe3:35
A query to monitor ingestion activity occurring when using COPY INTO and Snowpipe
Columns and deleted columns6:47
A couple queries that deal with finding columns in any database and deleted columns
Tables and deleted tables8:51
Queries that deal with finding tables in any database and deleted tables
Databases and deleted databases1:28
Queries that deal with listing databases and deleted databases
Finding long running queries5:45
A query to identify queries that are currently executing and retrieve those that have exceeded a certain amount of execution time
Find longest running queries2:16
A query to identify the longest running queries
Large partition scan queries8:37
In this lecture we show a query that can find user queries that scan all or certain percentage of partitions in a table.
Query counts by time period4:49
Here we show some ways to show number of queries executed per month and week
Blocked queries2:48
A query to find any blocked query, then show a list of active transactions and a way to kill a transaction

Requirements

Some basic experience with Snowflake would be useful

Description

Snowflake was written from the ground up to be a novel and advanced cloud data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional offerings.

One of the most important activities any Snowflake administrator has to perform, or any developer who has been given the responsibility of monitoring the Snowflake environment, is knowing how to actually monitor Snowflake, and what support is available in the Snowflake SQL language to monitor the environment.

Of course, some aspects of Snowflake can be monitored via the Web UI. But there is a whole host of views and table functions available to monitor your entire Snowflake account. That is the focus of this course.

This course demonstrates how to create queries (several dozen in total) to monitor all the major areas of Snowflake including role-based access, users and logins, virtual warehouse usage and credit consumption and many miscellaneous queries to monitor Snowflake activity.

When you complete this course you will have a suite of queries to monitor virtual warehouse usage, credit spending, monitoring ingestion with COPY INTO and Snowpipe, users and logins, long running queries, blocked queries and much more.

Who this course is for:

All levels of Snowflake administrators and developers who need to monitor some aspect of their Snowflake environment will find this course useful

Snowflake Database - Snowflake Queries to Monitor Snowflake

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 2min

Snowsight3 lectures • 32min

Role-based Access6 lectures • 28min

Users and logins3 lectures • 17min

Virtual Warehouses4 lectures • 33min

Miscellaneous Queries10 lectures • 48min

Practice queries7 lectures • 17min

Requirements

Description

Who this course is for: