Credit usage monitoring in Snowflake - Step-by-Step Execution

Process Flow - Credit usage monitoring

Start Monitoring

↓

Query Credit Usage Table

↓

Fetch Current Credit Usage

↓

Compare Usage to Threshold

Yes No↓

Alert Admin

↓

End or Continue Monitoring

The flow shows how Snowflake credit usage is monitored by querying usage data, checking thresholds, and alerting if limits are exceeded.

Execution Sample

Snowflake

SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE START_TIME >= DATEADD(day, -1, CURRENT_TIMESTAMP())
GROUP BY WAREHOUSE_NAME;

This query sums credits used by each warehouse in the last day to monitor credit consumption.

Process Table

Step	Action	Query/Check	Result	Next Step
1	Start Monitoring	N/A	Monitoring started	Query credit usage
2	Query Credit Usage Table	Run SELECT on WAREHOUSE_METERING_HISTORY	Returns credits used per warehouse	Fetch current usage
3	Fetch Current Credit Usage	Sum credits for last 24 hours	Warehouse A: 50, Warehouse B: 30	Compare usage to threshold
4	Compare Usage to Threshold	Is any usage > 40 credits?	Warehouse A: Yes, Warehouse B: No	Alert admin for Warehouse A
5	Alert Admin	Send alert for Warehouse A	Alert sent	Wait and repeat monitoring
6	Wait and Repeat	Pause for monitoring interval	Wait complete	Start Monitoring again
7	End Monitoring	Condition to stop monitoring met	Monitoring stopped	N/A

💡 Monitoring stops when manual stop or condition met; otherwise repeats.

Status Tracker

Variable	Start	After Step 3	After Step 4	Final
Warehouse A Credits	0	50	50	50
Warehouse B Credits	0	30	30	30
Alert Sent	No	No	Yes	Yes

Key Moments - 3 Insights

Why do we sum credits used over the last 24 hours instead of total credits?

Summing credits over the last 24 hours (see execution_table step 3) helps monitor recent usage and detect spikes quickly, rather than looking at all-time totals which may hide current trends.

What happens if no warehouse exceeds the threshold?

If no warehouse exceeds the threshold (execution_table step 4 shows 'No' for Warehouse B), no alert is sent and the system waits before checking again, ensuring no false alarms.

How often should the monitoring repeat?

The monitoring repeats after a pause (step 6), which can be set based on business needs, e.g., every hour or day, to balance timely alerts and resource use.

Visual Quiz - 3 Questions

Look at the execution_table at step 4, which warehouse triggers an alert?

A Both Warehouse A and B

B Warehouse B

C Warehouse A

D No warehouse triggers alert

💡 Hint

Check the 'Result' column at step 4 showing usage compared to threshold.

At which step does the system send an alert to the admin?

A Step 5

B Step 2

C Step 4

D Step 6

💡 Hint

Look for the step labeled 'Alert Admin' in the execution_table.

If Warehouse B's credits increased to 45, how would the execution_table change at step 4?

A Alert would be sent only for Warehouse B

B Warehouse B would also be marked 'Yes' for exceeding threshold

C No change, only Warehouse A triggers alert

D Monitoring would stop immediately

💡 Hint

Refer to step 4 where usage is compared to threshold for each warehouse.

Concept Snapshot

Credit Usage Monitoring in Snowflake:
- Query WAREHOUSE_METERING_HISTORY for credits used
- Sum credits over recent period (e.g., last 24 hours)
- Compare usage to alert threshold
- Send alert if usage exceeds threshold
- Repeat monitoring at set intervals

Full Transcript

Credit usage monitoring in Snowflake involves running a query on the warehouse metering history to sum credits used by each warehouse over a recent time frame, such as the last 24 hours. The system then compares these sums to a predefined threshold. If any warehouse exceeds the threshold, an alert is sent to notify administrators. If not, the system waits for a set interval before repeating the monitoring process. This cycle continues until monitoring is stopped manually or by condition.

Practice

1. What is the main purpose of monitoring credit usage in Snowflake?

easy

A. To control and optimize Snowflake costs

B. To increase the number of warehouses automatically

C. To backup data to external storage

D. To improve query performance by caching

Solution

1. Step 1: Understand credit usage concept

   Credit usage monitoring tracks how many credits Snowflake consumes, which directly affects billing.

2. Step 2: Identify the goal of monitoring

   The goal is to control and optimize costs by understanding usage patterns.

3. Final Answer:

   To control and optimize Snowflake costs -> Option A

4. Quick Check:

   Credit monitoring = Cost control [OK]

Hint: Credit usage monitoring helps manage costs effectively [OK]

Common Mistakes:

• Confusing credit monitoring with performance tuning
• Thinking it automatically scales warehouses
• Assuming it backs up data

2. Which Snowflake view provides detailed credit usage information for warehouses?

easy

A. INFORMATION_SCHEMA.TABLES

B. ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY

C. ACCOUNT_USAGE.LOGIN_HISTORY

D. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

Solution

1. Step 1: Identify views related to credit usage

   ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY tracks credit consumption by warehouses.

2. Step 2: Exclude unrelated views

   INFORMATION_SCHEMA.TABLES shows table info, LOGIN_HISTORY tracks logins, QUERY_HISTORY tracks queries but not credit usage.

3. Final Answer:

   ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY -> Option B

4. Quick Check:

   Warehouse credit data = WAREHOUSE_METERING_HISTORY [OK]

Hint: Warehouse credit data is in WAREHOUSE_METERING_HISTORY view [OK]

Common Mistakes:

• Choosing QUERY_HISTORY which tracks queries, not credits
• Confusing LOGIN_HISTORY with credit usage
• Selecting INFORMATION_SCHEMA views unrelated to credits

3. What will the following query return?

SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY GROUP BY WAREHOUSE_NAME ORDER BY TOTAL_CREDITS DESC;

medium

A. List of warehouses with their current running queries

B. Total credits used by each user in the account

C. Total credits used by each warehouse, sorted from highest to lowest

D. A syntax error due to missing WHERE clause

Solution

1. Step 1: Analyze the SELECT and GROUP BY

   The query sums credits used grouped by warehouse name.

2. Step 2: Understand ORDER BY clause

   Ordering by TOTAL_CREDITS DESC sorts warehouses from highest to lowest credit usage.

3. Final Answer:

   Total credits used by each warehouse, sorted from highest to lowest -> Option C

4. Quick Check:

   SUM credits by warehouse, ordered DESC = Total credits used by each warehouse, sorted from highest to lowest [OK]

Hint: GROUP BY warehouse sums credits; ORDER BY DESC sorts high to low [OK]

Common Mistakes:

• Thinking it shows user credit usage
• Assuming it lists running queries
• Believing missing WHERE causes syntax error

4. You run this query but get no results:

SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WHERE START_TIME > CURRENT_DATE() GROUP BY WAREHOUSE_NAME;

What is the likely cause?

medium

A. Missing GROUP BY clause

B. Syntax error due to missing alias for SUM

C. Incorrect table name used

D. No credit usage recorded today yet

Solution

1. Step 1: Check the WHERE condition

   Filtering START_TIME > CURRENT_DATE() means only today's data is shown.

2. Step 2: Consider data availability

   If no credits used today yet, query returns empty result, not error.

3. Final Answer:

   No credit usage recorded today yet -> Option D

4. Quick Check:

   Empty result = no data matching date filter [OK]

Hint: Empty results often mean no data matches the filter [OK]

Common Mistakes:

• Assuming missing alias causes error
• Thinking table name is wrong
• Believing GROUP BY is missing

5. You want to monitor credit usage by service type (e.g., WAREHOUSE, CLOUD_SERVICES) for the last 7 days. Which query correctly achieves this?

hard

A. SELECT SERVICE_TYPE, SUM(CREDITS_USED) AS TOTAL_CREDITS FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE()) GROUP BY SERVICE_TYPE ORDER BY TOTAL_CREDITS DESC;

B. SELECT SERVICE_TYPE, SUM(CREDITS_USED) FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE()) GROUP BY SERVICE_TYPE;

C. SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE()) GROUP BY WAREHOUSE_NAME;

D. SELECT SERVICE_TYPE, CREDITS_USED FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE());

Solution

1. Step 1: Identify correct view for service type credit data

   ACCOUNT_USAGE.METERING_HISTORY contains credit usage by service type; WAREHOUSE_METERING_HISTORY is only for warehouses.

2. Step 2: Check query filters and grouping

   Filtering last 7 days with DATEADD and grouping by SERVICE_TYPE is correct; ordering by total credits helps see highest usage.

3. Final Answer:

   SELECT SERVICE_TYPE, SUM(CREDITS_USED) AS TOTAL_CREDITS FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE()) GROUP BY SERVICE_TYPE ORDER BY TOTAL_CREDITS DESC; -> Option A

4. Quick Check:

   Use METERING_HISTORY + SERVICE_TYPE + last 7 days = SELECT SERVICE_TYPE, SUM(CREDITS_USED) AS TOTAL_CREDITS FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY WHERE START_TIME >= DATEADD(day, -7, CURRENT_DATE()) GROUP BY SERVICE_TYPE ORDER BY TOTAL_CREDITS DESC; [OK]

Hint: Use METERING_HISTORY view and group by SERVICE_TYPE for service credit usage [OK]

Common Mistakes:

• Using WAREHOUSE_METERING_HISTORY for service types
• Not grouping by SERVICE_TYPE
• Selecting credits without aggregation