D1.1 - EXPLAIN HOW TO MANAGE SNOWFLAKE ACCOUNTS

Question 1

ACCOUNT_USAGE
Snowflake database is system defined read only shared DB provided by Snowflake. It contains 2 schemas and each schema contains a set of views

Answer 1

ACCOUNT_USAGE
- ACCOUNT_USAGE schemas contains views that display object metadata and usage metrics for YOUR account. It is generally similar to the information schema.

READER_ACCOUNT_USAGE
- READER_ACCOUNT_USAGE schema contains views for object metadata and usage metrics for all reader accounts created for your account. These are a small subset ACCOUNT_USAGE views.

Question 2

Account Access

Answer 2

• Discretionary: each object has an owner who can in turn grant access to that object
• Role based: accessing privilege’s are assigned to roles, and roles are assigned to users

Question 3

System Defined Roles

Answer 3

ORGADMIN: can create accounts in the org, view all accounts, and view usage information across organization

ACCOUNTADMIN: encapsulates the SYSADMIN and SECURITYADMIN role and is the top level role in the system

SECURITYADMIN: can manage any object grant globally and create, monitor, and manage users and roles. It has the MANAGE GRANTS privilege’s and inherits all privileges of the USERADMIN role

USERADMIN: it is granted CREATE USER and CREATE ROLE security privilege’s

SYSADMIN: role that has privileges’ to create warehouses and databases within the account

PUBLIC: auto granted to every user and role within the account

Question 4

INFORMATION SCHEMA

Answer 4

• Third schema within SNOWFLAKE DB but it can only be accessed by users with role = ACCOUNTADMIN
• Doesnt include dropped objects (ACCOUNT_USAGE does include dropped objects)
• No latency of data (ACCOUNT_USAGE has about 2 hour latency)
• Retains historical data for 7 days to 6 months (ACCOUNT_USAGE retains data for 1 year)