Calculating Warehouse Credit Usage and Querying ACCOUNT_USAGE

1.Warehouse size, run time and per-second billing

Virtual warehouses are the compute you control, and Snowflake charges for them by size and run time. Each step up in size doubles the credits per hour, starting at 1 credit per hour for an X-Small Gen1 standard warehouse:

These are hourly rates, but billing is per second, with a 60-second minimum each time a warehouse starts or resumes. An X-Small that runs for 10 minutes uses 0.167 credits. One that runs for 5 seconds is billed for a full minute: 0.017 credits. A suspended warehouse uses no credits at all.

The minimum is easy to overlook. Suspending and resuming within the first minute leads to multiple charges, because the 1-minute minimum starts over at every resume. A workload that wakes a warehouse many times an hour for a few seconds each time pays a full minute every time. The auto-suspend and auto-resume settings, both on by default, decide how often that happens. A longer auto-suspend period keeps the warehouse running between short jobs instead of starting it again for each one.

Resizing has its own rule. Moving to a larger size bills 1 minute, but only for the additional compute. Going from Small (2 credits/hour) to Medium (4 credits/hour) bills 1 minute's worth of the extra 2 credits. Resizing down from 5X-Large or 6X-Large to 4X-Large or smaller briefly bills both the old and new resources while the old ones shut down. These figures are for Gen1. Gen2 rates are in the Snowflake Service Consumption Table.

Sources12

2.Multi-cluster warehouses and the cloud services adjustment

With a multi-cluster warehouse, an Enterprise Edition feature, multiply the size rate by the number of clusters running during each period. A 3X-Large (64 credits/hour) that runs 1 cluster for an hour and then 2 clusters for the next hour bills 64 + 128 = 192 credits.

Cloud services, the layer that handles authentication, query compilation and optimisation, also uses credits. You're billed for it only on days when cloud services consumption is more than 10% of that day's warehouse usage. Snowflake calculates this daily, in UTC: it multiplies daily warehouse usage by 10%. The adjustment can never be larger than that day's actual cloud services usage, so the monthly total adjustment can come out well under 10%. Serverless compute isn't part of this calculation.

The result is a gap between credits consumed and credits billed. Resource monitors and some usage views report consumption, before the adjustment. Billing applies it.

Sources12

3.The ACCOUNT_USAGE schema

To see these numbers after the fact, you query the shared SNOWFLAKE database. Its ACCOUNT_USAGE schema holds views of object metadata and usage metrics for your account. If you're a Secure Data Sharing provider, a separate READER_ACCOUNT_USAGE schema holds a small subset of these views for your reader accounts. ACCOUNT_USAGE views use the same structure and naming as their Information Schema counterparts, but they behave differently in three ways:

Most ACCOUNT_USAGE views have up to 2 hours of latency, and these are maximums, so a query can show recent activity sooner. Many object views have a DELETED column, plus ID columns that tell apart objects dropped and recreated under the same name. So use ACCOUNT_USAGE for trend analysis, such as six months of warehouse spend, and the Information Schema when you need data with no delay.

The views most relevant to cost are WAREHOUSE_METERING_HISTORY (hourly credits per warehouse), METERING_HISTORY (hourly credits for the account), METERING_DAILY_HISTORY, WAREHOUSE_LOAD_HISTORY and RESOURCE_MONITORS. All of them have a latency of up to 3 hours or less.

Sources3

4.Reading warehouse credits, and granting access

WAREHOUSE_METERING_HISTORY returns hourly credit usage for one warehouse or all warehouses, going back 365 days. Its CREDITS_USED column is CREDITS_USED_COMPUTE plus CREDITS_USED_CLOUD_SERVICES. That's the consumed figure, before the cloud services adjustment, so it can be higher than what you're billed. For billed credits, query METERING_DAILY_HISTORY. CREDITS_ATTRIBUTED_COMPUTE_QUERIES counts only query execution and leaves out idle time. Subtracting it from compute credits shows what idle warehouses cost:

SELECT
  (SUM(credits_used_compute) -
    SUM(credits_attributed_compute_queries)) AS idle_cost,
  warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('days', -10, CURRENT_DATE())
  AND end_time < CURRENT_DATE()
GROUP BY warehouse_name;

Every user can see the SNOWFLAKE database, but querying its schemas needs a grant from ACCOUNTADMIN. There are two ways: grant IMPORTED PRIVILEGES on the whole database, or grant a SNOWFLAKE database role to an account role. Snowflake recommends database roles for ACCOUNT_USAGE access, so you don't accidentally expose organization-level data.

USE ROLE  ? ; GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE SYSADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE customrole1;

Sources43

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Resizing a warehouse to a larger size bills a full minute at the new size's rate.Why is that wrong?

   The 1-minute charge on resizing up covers only the additional compute.

   Covered in Warehouse size, run time and per-second billing

2. 2.ACCOUNT_USAGE views are real-time, so a query that finished a minute ago will already show up.Why is that wrong?

   ACCOUNT_USAGE views have up to 45 minutes to 3 hours of latency. Only the Information Schema has none.

   Covered in The ACCOUNT_USAGE schema

3. 3.CREDITS_USED in WAREHOUSE_METERING_HISTORY is what appears on the bill.Why is that wrong?

   CREDITS_USED is before the cloud services adjustment and can be higher than billed credits. Use METERING_DAILY_HISTORY for billed credits.

   Covered in Reading warehouse credits, and granting access