Refer to Snowflake Documentation for more details.
In Snowflake, we can setup either below two methods.
In a Snowflake federated environment, Snowflake serves as the SP.
The external, independent entity responsible for providing the following services to the SP:
Creating and maintaining user credentials and other profile information.
Authenticating users for SSO access to the SP.
// Query : 1. WH Credits by Month
// Query 2 : WH Credits by Week
// Query 3 : WH Credits by Month (Top 10)
// Query 4 : WH Credits by Week (Top 10)
// Query 5 : WH Credits used last 30 days
SELECT warehouse_name,
Round(Sum(credits_used_compute),0) AS "Compute Credits Used" ,
Round(Sum(credits_used_cloud_services),0) AS "Cloud Services Credits Used"
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time BETWEEN Date_trunc('day', Dateadd('day',-29,Convert_timezone('America/Chicago',CURRENT_TIMESTAMP()))) AND CURRENT_TIMESTAMP()
GROUP BY 1
ORDER BY 2 DESC;
// Query 6 : Top users by Execution Time (last 7 days)
// Query 7 : Top users by Query Count (last 7 days)
SELECT user_name,
Round(Sum(execution_time)/(10006060),1) exec_hrs,
Count(1) AS num_queries
FROM snowflake.account_usage.query_history
WHERE start_time > dateadd(day, -7 , CURRENT_DATE())
--AND user_name NOT LIKE '%SVC%'
--AND user_name NOT IN ('EDC_SCAN','SEENOSNOWMAN')
--AND role_name NOT LIKE '%SNOWFLAKE%'
AND warehouse_name = 'ENTPRS_ODS_WH'
GROUP BY 1
ORDER BY exec_hrs DESC limit 10;
// Query 8 Sum of credit consumption at the account level
SELECT Sum(credits_used)
FROM account_usage.metering_history
WHERE start_time = :daterange;
// Query 9 Total storage used till current date at account level
SELECT Avg(storage_bytes + stage_bytes + failsafe_bytes) / Power(1024, 4) AS
billable_tb
FROM account_usage.storage_usage
WHERE usage_date = CURRENT_DATE() - 1;
// Query 10 Total no of jobs executed in the account till current date
SELECT Count(*) AS number_of_jobs
FROM account_usage.query_history
WHERE start_time >= Date_trunc(month, CURRENT_DATE);
// Query 11 How much data is stored monthly for failsafe, database, stage
SELECT Date_trunc(month, usage_date) AS usage_month,
Avg(storage_bytes + stage_bytes + failsafe_bytes) / Power(1024, 4) AS billable_tb,
Avg(storage_bytes) / Power(1024, 4) AS Storage_TB,
Avg(stage_bytes) / Power(1024, 4) AS Stage_TB,
Avg(failsafe_bytes) / Power(1024, 4) AS Failsafe_TB
FROM account_usage.storage_usage
GROUP BY 1
ORDER BY 1;
// Query 12 Sum of credits that are consumed monthly at the account level
SELECT Date_trunc('MONTH', usage_date) AS Usage_Month,
Sum(credits_billed)
FROM account_usage.metering_daily_history
GROUP BY usage_month;
// Query 13 Total credit consumed by a particular Warehouse
SELECT warehouse_name,
Sum(credits_used) AS total_credits_used
FROM account_usage.warehouse_metering_history
WHERE start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC;
// Query 14 sum of credit that is consumed by the cloud service warehouse and compute warehouse
SELECT warehouse_name,
Sum(credits_used_cloud_services) credits_used_cloud_services,
Sum(credits_used_compute) credits_used_compute,
Sum(credits_used) credits_used
FROM account_usage.warehouse_metering_history
WHERE true
AND start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
// Query 15 Query execution time by different query types
SELECT query_type,
warehouse_size,
Avg(execution_time) / 1000 AS average_execution_time
FROM account_usage.query_history
WHERE start_time = :daterange
GROUP BY 1,
2
ORDER BY 3 DESC;
// Query 16 Average query execution time by a particular user
SELECT user_name,
( Avg(execution_time) ) / 1000 AS average_execution_time
FROM account_usage.query_history
WHERE start_time = :daterange
GROUP BY 1
ORDER BY 2 DESC;
// Query 17 Execution time is taken by the repeated query
SELECT query_text,
( Sum(execution_time) / 60000 ) AS exec_time
FROM account_usage.query_history
WHERE execution_status = 'SUCCESS'
GROUP BY query_text
ORDER BY exec_time DESC
LIMIT 25;
// Query 18 How much credit is consumed daily in terms of the dollar at the organizational level
SELECT usage_in_currency,
usage_date
FROM snowflake.organization_usage.usage_in_currency_daily;
// Query 19 Sum of Credit consumption based on Warehouse tags
Prerequisite:
To get the below chart, first apply tags on the warehouse based on your need and then execute the below query in snow sight to get the chart.
SELECT tag_value AS department,
Sum(credits_used) AS credits
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."warehouse_metering_history",
"SNO WFLAKE"."ACCOUNT_USAGE"."tag_references"
WHERE true
AND warehouse_name = object_name
AND tag_name = 'COST_CENTER'
AND tag_database = 'DEV_ACCIDENTS_DB'
AND tag_schema = 'LANDING_SCHEMA'
AND start_time >= Dateadd('days', -30, CURRENT_DATE()) GROUP BY 1 ORDER
BY 2 DESC;
// Query 20 Snowflake Accounts Usage in Currency
use role orgadmin;
USE WAREHOUSE ENTPRS_ODS_WH;
select account_name,sum(USAGE_IN_CURRENCY) as USAGE_IN_CURRENCY from snowflake.ORGANIZATION_USAGE.USAGE_IN_CURRENCY_DAILY group by account_name;
// Query 21 Monthly Usage in Currency on Accounts
USE Role Orgadmin;
SELECT Date_trunc('MONTH', usage_date) AS usage_month,
account_name,
Round(Sum(usage_in_currency), 2) AS total_usage
FROM snowflake.organization_usage.usage_in_currency_daily
GROUP BY 1,
2
ORDER BY 1,
2;
SELECT Date_trunc('MONTH', usage_date) AS usage_month,
account_name,
Upper(usage_type) AS usage_type,
Round(Sum(usage), 3) AS units_consumed,
Round(Sum(usage_in_currency), 2) AS total_usage
FROM snowflake.organization_usage.usage_in_currency_daily
--WHERE USAGE_MONTH = '2022-11-01' ---Feel free to change the month
GROUP BY 1,
2,
3
ORDER BY 1,
2,
3;
Step 1 : Create Warehouse
CREATE WAREHOUSE WH_TESTING WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD';
Step 2 : Create Database
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS DB_TESTING;
Step 3 : Create Database Roles
USE ROLE SECURITYADMIN;
CREATE DATABASE ROLE IF NOT EXISTS ROLE_DB_TESTING_READ;
CREATE DATABASE ROLE IF NOT EXISTS ROLE_DB_TESTING_WRITE;
Step 4 : Create Account Level Roles
USE ROLE SECURITYADMIN;
CREATE ROLE DEV_APP;
CREATE ROLE DEV_APP_DEVELOPER;
CREATE ROLE DEV_READ_ALL;
Step 5 : Grant Roles to SYSADMIN
GRANT ROLE ROLE_DB_TESTING_READ TO ROLE SYSADMIN;
GRANT ROLE ROLE_DB_TESTING_WRITE TO ROLE SYSADMIN;
Step 6 : Grant to DB Roles
USE ROLE SYSADMIN;
GRANT USAGE ON DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_READ;
GRANT USAGE ON DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_WRITE;
USE DATABASE DB_TESTING;
GRANT USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_READ;
GRANT USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;
GRANT CREATE TABLE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;
GRANT CREATE FILE FORMAT, CREATE PIPE, CREATE STAGE, CREATE SEQUENCE, MODIFY, MONITOR, USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;
GRANT USAGE ON FUTURE PROCEDURES IN DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_WRITE;
GRANT USAGE ON FUTURE PROCEDURES IN DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_READ;
Step 7 : Create Schema and provide Grants
USE ROLE SYSADMIN;
USE DATABASE DB_TESTING;
CREATE SCHEMA IF NOT EXISTS SCHEMA_DEV;
GRANT USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT CREATE FILE FORMAT, CREATE PIPE, CREATE STAGE, CREATE SEQUENCE, MODIFY, MONITOR, USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
GRANT CREATE TABLE, CREATE FUNCTION, CREATE TEMPORARY TABLE, CREATE VIEW, MODIFY, USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
USE ROLE SYSADMIN;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT SELECT ON ALL VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL TABLES IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL MATERIALIZED VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
USE ROLE SECURITYADMIN;
GRANT SELECT ON FUTURE TABLES IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT SELECT, REFERENCES ON FUTURE VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;
GRANT SELECT, REFERENCES ON FUTURE MATERIALIZED VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
USE ROLE SYSADMIN;
GRANT CREATE FILE FORMAT, CREATE STAGE, CREATE SEQUENCE, MONITOR, USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;
USE SCHEMA DB_TESTING.SCHEMA_DEV;
CREATE TABLE IF NOT EXISTS INCR_TABLES (TABLE_NAME VARCHAR(1000) NOT NULL,PRIMARY_KEY VARCHAR(1000),SQL_SELECT VARCHAR,SQL_WHERE VARCHAR,SRC_DB_SCHEMA VARCHAR(100),LAST_MODIFIED_FIELD VARCHAR(1000),LAST_MODIFIED_FIELD_VALUE TIMESTAMP);
CREATE TABLE IF NOT EXISTS RELOAD_TABLES (TABLE_NAME VARCHAR(1000),PRIMARY_KEY VARCHAR(1000),SQL_SELECT VARCHAR,SQL_WHERE VARCHAR,SRC_DB_SCHEMA VARCHAR(100),REFRESH_RATE NUMBER(5,0), NEXT_REFRESH_DATE TIMESTAMP);
USE ROLE SECURITYADMIN;
GRANT ROLE ROLE_DB_TESTING_WRITE TO ROLE DEV_APP;
GRANT ROLE ROLE_DB_TESTING_READ TO ROLE DEV_APP_DEVELOPER;
GRANT ROLE ROLE_DB_TESTING_READ TO ROLE DEV_READ_ALL;
In this sessions we will start by discussing setting up a Snowflake account, especially for those new to the Snowflake. With a Snowflake account readily available and a limited understanding of its system-defined roles, it usually becomes a challenge for a team lead or an admin to set up the environments with proper access controls to its developers or users.
In Snowflake, we have two Access Control Frameworks:
Discretionary Access Control (DAC): Each object has an owner, who can in turn grant access to that object.
Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users.
Roles are the entities to which privileges on securable objects can be granted and revoked. Roles are assigned to users to allow them to perform actions required for business functions in their organization. A user can be assigned multiple roles. This allows users to switch roles (i.e. choose which role is active in the current Snowflake session) to perform different actions using separate sets of privileges.
There are a small number of system-defined roles in a Snowflake account. System-defined roles cannot be dropped. In addition, the privileges granted to these roles by Snowflake cannot be revoked.
USERADMIN:
SECURITYADMIN:
SYSADMIN:
ACCOUNTADMIN:
ORGADMIN:
PUBLIC:
Snowflake has five system-defined roles that are
automatically created when a Snowflake account is provisioned:
These roles are designed to provide built-in access controls
and permissions for Snowflake objects and resources. The privileges of each
role are inherited by all roles above it.
The primary intended purpose of each role is:
[idmcd@LINUXIDMCDEV ~]$ cd
/app/idmcSecureAgent/
[idmcd@LINUXIDMCDEV idmcSecureAgent]$ ls -ltr
Download Secure Agent Binaries to Linux
Drive:
agent64_install_ng_ext.6504.bin
[idmcd@LINUXIDMCDEV idmcSecureAgent]$ ./agent64_install_ng_ext.6504.bin -i console
Preparing to install
Extracting the JRE from the installer
archive...
Unpacking the JRE...
Extracting the installation resources from the
installer archive...
Configuring the installer for this system's
environment...
Launching installer...
===============================================================================
Informatica Cloud Secure Agent (created with
InstallAnywhere)
-------------------------------------------------------------------------------
Preparing CONSOLE Mode Installation...
===============================================================================
Choose Install Folder
---------------------
Where would you like to install?
Default Install Folder: /home/idmcd/infaagent
ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
:
/app/idmcSecureAgent
INSTALL FOLDER IS: /app/idmcSecureAgent
IS
THIS CORRECT? (Y/N): Y
===============================================================================
Pre-Installation Summary
------------------------
Please Review the Following Before Continuing:
Product Name:
Informatica Cloud Secure Agent
Install Folder:
/app/idmcSecureAgent
Disk Space Information (for Installation
Target):
Required: 292,625,971 Bytes
Available: 1,091,265,638,400 Bytes
PRESS <ENTER> TO CONTINUE:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Generating Agent Token...
Please Wait
-----------
===============================================================================
Creating softlink...
Please Wait
-----------
===============================================================================
File permission
Please Wait
-----------
===============================================================================
Installation
Complete
---------------------
Congratulations.
Informatica Cloud Secure Agent has been successfully
installed to:
/app/idmcSecureAgent
To start the agent, go to directory "<agent_directory>/apps/agentcore" and
then run "infaagent startup". Check
the infaagent.log file
for errors, and then visit the Informatica
Cloud site to verify that the
agent is active.
PRESS <ENTER> TO EXIT THE INSTALLER:
[idmcd@LINUXIDMCDEV idmcSecureAgent]$
[idmcd@LINUXIDMCDEV idmcSecureAgent]$
[idmcd@LINUXIDMCDEV idmcSecureAgent]$
[idmcd@LINUXIDMCDEV idmcSecureAgent]$
Start Agent
·
To add the agent to its own Secure Agent group, use the following
command:
./consoleAgentManager.sh
configureToken <user name> <install token>
idmcd@LINUXIDMCDEV
agentcore]$
[idmcd@LINUXIDMCDEV
agentcore]$ ./consoleAgentManager.sh
configureToken idmcd
<Copy Token>
Login with token
Login succeeded.