Credit usage monitoring in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to query the total credits used in the current month.

Snowflake

SELECT SUM(credits_used) AS total_credits FROM snowflake.account_usage.warehouse_metering_history WHERE usage_date >= DATE_TRUNC('month', CURRENT_DATE) AND usage_date <= [1];

Drag options to blanks, or click blank then click option'

ACURRENT_DATE

BCURRENT_TIMESTAMP

CCURRENT_TIME

DSYSDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURRENT_TIMESTAMP causes type mismatch errors.

Using SYSDATE is not supported in Snowflake.

The CURRENT_DATE function returns the current date without time, which matches the date type of usage_date. This ensures the date range filter works correctly.

2fill in blank

medium

Complete the code to filter credit usage for a specific warehouse named 'COMPUTE_WH'.

Snowflake

SELECT usage_date, credits_used FROM snowflake.account_usage.warehouse_metering_history WHERE warehouse_name = [1];

Drag options to blanks, or click blank then click option'

ACOMPUTE_WH

B"COMPUTE_WH"

C`COMPUTE_WH`

D'COMPUTE_WH'

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes causes syntax errors.

Not using quotes treats the value as a column name.

String literals in SQL must be enclosed in single quotes. Double quotes or backticks are not valid for string literals in Snowflake SQL.

3fill in blank

hard

Fix the error in the code to correctly calculate daily credit usage.

Snowflake

SELECT usage_date, SUM(credits_used) [1] daily_credits FROM snowflake.account_usage.warehouse_metering_history GROUP BY usage_date ORDER BY usage_date;

Drag options to blanks, or click blank then click option'

AAS

BBY

CTO

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using BY instead of AS causes syntax errors.

Omitting the alias keyword leads to unclear column names.

The AS keyword is used to assign an alias to a column or expression in SQL.

4fill in blank

hard

Fill both blanks to create a query that shows credit usage per warehouse for the last 7 days.

Snowflake

SELECT warehouse_name, SUM(credits_used) [1] total_credits FROM snowflake.account_usage.warehouse_metering_history WHERE usage_date >= DATEADD(day, -7, [2]) GROUP BY warehouse_name ORDER BY total_credits DESC;

Drag options to blanks, or click blank then click option'

AAS

BCURRENT_DATE

CCURRENT_TIMESTAMP

DSYSDATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURRENT_TIMESTAMP causes time mismatches.

Forgetting the AS keyword causes syntax errors.

Use AS to alias the sum column as total_credits. Use CURRENT_DATE to get the current date for the date filter.

5fill in blank

hard

Fill all three blanks to create a query that lists warehouses with credit usage greater than 100 in the last 30 days.

Snowflake

SELECT warehouse_name, SUM(credits_used) [1] total_credits FROM snowflake.account_usage.warehouse_metering_history WHERE usage_date >= DATEADD(day, -30, [2]) GROUP BY warehouse_name HAVING total_credits [3] 100 ORDER BY total_credits DESC;

Drag options to blanks, or click blank then click option'

AAS

BCURRENT_DATE

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > filters the wrong warehouses.

Omitting AS causes syntax errors.

Using CURRENT_TIMESTAMP causes date mismatches.

Alias the sum column with AS. Use CURRENT_DATE for the date filter. Use the greater than operator > to filter warehouses with credit usage above 100.

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