Understanding BigQuery Cost

This article explains the two pricing models—on-demand and capacity—for the compute component of the BigQuery pricing, the challenges of calculating chargeback on compute, and what Unravel can do.

1. On-demand compute pricing: You are charged based on the billed bytes used by your queries. So if the price is $X/TiB and a query uses Y-TiB of billed bytes, you will be billed $(X*Y) for that query.
2. Capacity compute pricing: You buy slots and you are charged based on the number of slots and the time for which slots were made available. 

The following section describes compute pricing in more detail.

Capacity Pricing

To use capacity pricing, you start by creating reservations. You can create one or more reservations inside an admin project. All the costs related to the slots for the reservations will be attributed to the admin project in the bill.

Reservations

While creating a reservation, you need to specify “baseline slots” and “max slots,” in multiples of 100. You will be charged for the baseline slots at the minimum for the duration of the reservation. When all the baseline slots have been utilized by queries running in that reservation, more slots can be made available via autoscaling. Autoscaling happens in multiple of 100 slots. The number of slots available for autoscaling is (max slots – baseline slots). 

You can modify the baseline and max slots after you create the reservation. You can increase the values at any point in time, but you cannot decrease the values within 1 hour of creating a reservation or updating the reservation.

Assignments

After you have created a reservation, to enable the queries in projects to use slots from that reservation, you have to create assignments. You can assign projects, folders ,or organizations to a reservation. Whenever a query is started, it will first try to use the slots from this reservation. You can create or delete assignments at any point in time.

Pricing

BigQuery provides 3 editions —Standard, Enterprise, and Enterprise Plus. These editions have different capabilities and different pricing rates. The rates are defined in terms of slot hours. For example, the rate is $0.04 per slot hour for Standard edition in the US and $0.06 per slot hour for Enterprise edition in the same region.

In capacity pricing, you are charged for the number of slots made available and the time for which slots are made available. Suppose you have a reservation with 100 baseline slots and 500 max slots in the Standard edition. Consider the following usage:

• In the first hour, no queries are running, so the slot requirement is 0.
• In the second hour, there are queries running, but the slot requirement is less than 100.
• In the third hour, more queries are running and the slot requirement is 150.

In the first 2 hours, even though the slot requirement is less than 100, you will still be charged for 100 slots—the baseline slots—for each of the first 2 hours.

In the third hour, we need 50 more slots than the baseline, so autoscaling kicks in to provide more slots. Since autoscaling only scales up or down in multiples of 100, 100 more slots are added. Hence, a total of 200 slots (100 baseline + 100 from autoscaling) are made available in this hour. 

The number of slot-hours from this 3-hour period is 100 + 100 + 200 = 400. With a rate of $0.04 per slot-hour for Standard edition, you will be charged 0.04*400 = $16 for this usage.

Pay-as-you-go

Recall that you can create/delete/update reservations and baseline/max slots whenever you want. Also, you will be charged for just the number of slots made available to you and for the time the slots are made available. This model is called pay-as-you-go, as you are paying for the usage you are using. 

Capacity Commitment

If you expect to use a certain number of slots over a long period of time, you can make a commitment of X slots over a 1-year or 3-year period, for a better rate. You will be charged for those slots for the entire period regardless of whether you use them or not. This model is called capacity commitment.

Consider the following example of capacity commitment. Let’s say you have:

• 1-year commitment of 1600 slots in the Enterprise edition. 
• Created 1 reservation with max size of 1500 slots and baseline of 1000 slots. 
• Hence your autoscaling slots are 1500-1000 = 500.
• Pay-as-you-go price for enterprise edition is $0.06 per slot-hour.
• 1-year commitment price for the enterprise edition is $0.048 per slot hour.

Consider this scenario:

• In the first hour, the requirement is less than 1000 slots.
• In the second hour, the requirement is 1200 slots.
• In the third hour, the requirement is 1800 slots.

In the first hour, the baseline slots of 1000 are made available for the reservation; these slots are available from the commitment slots. Since we have a commitment of 1600 slots, all the 1600 slots are actually available. The 1000 slots are available for the reservation as baseline. The remaining 600 are called idle slots and are also charged. So for the first hour, we are charged for 1600 slots as per commitment price, with a cost of $(1600 * 0.048).

In the second hour, since the requirement is 1200 slots, there is an additional requirement of 200 slots beyond the baseline of 1000 slots. Since 600 idle slots are available from the committed capacity, the additional requirement of 200 slots will come from these idle slots, while the remaining 400 slots will remain idle. Notice that autoscaling was not needed in this case. Before going for autoscaling, BigQuery will try to use idle slots (unless ignore_idle_slots config is set to True for that reservation). So how much are we charged for the second hour? The answer is 1600 slots, since that is what is committed. These 1600 slots are charged as per the commitment price, so the cost for the second hour is $(1600 * 0.048).

In the third hour, the requirement is 1800 slots: the first 1600 slots will come from commitment slots, and the other 200 will now come from autoscaling slots. The 1600 slots will be charged as per 1-year commit pricing, and the 200 slots coming from autoscale slots will be charged as per pay-as-you-go pricing at $0.06/slot-hour in this case. Therefore, the cost for the third hour is $((1600 * 0.048) + (200 * 0.06)).

Notes

Some points to note regarding capacity pricing:

1. The slots are charged with a maximum granularity of 1 second, and the charge is for a minimum of 1 minute.
2. Autoscaling always happens in increments/decrements of 100 slots.
3. Queries running in a reservation automatically use idle slots from other reservations within the same admin project. Unless ignore_idle_slots is set to True for the reservation.
4. The capacity commitment is specific to a region, organization, and edition. The idle slots can’t be shared across regions or editions.
5. Idle slot capacity is not shared between reservations in different admin projects.

The Cost Chargeback Problem

In an organization, typically there are one or more capacity commitments, one or more admin projects, and multiple reservations in these admin projects. GCP provides billing data that gives you the hourly reservation costs for a given admin project, edition, and location combination. However, there are multiple possible ways to map a team to a reservation: a team can be assigned to one reservation, multiple teams can be assigned to the same reservation, or multiple teams can be assigned to multiple reservations.

In any case, it is a tricky task to find out which team or user is contributing how much to your cost. How do you map the different teams and users to the different projects, editions, and locations? And how do you then track the cost incurred by these teams and users? Common chargeback approaches—such as chargeback by accounts, projects, and reservations—simply cannot provide clarity at the user or team level. There is also no direct data source that gives this information from GCP.

Unravel provides cost chargeback at the user and team levels by combining data from different sources such as billing, apps, and tags. The crux of our proprietary approach is providing an accurate cost estimate at the query level. We then associate the query costs with users and teams (via tags) to derive the user-level or team-level chargeback. 

Computing query-level cost estimates involves addressing a number of challenges. Some of the challenges include:

• A query may have different stages where chargeback could be different.
• A query may use slots with commitment pricing or pay-as-you-go pricing.
• Capacity is billed at one minute minimum.
• Autoscaling increments by multiples of 100 slots.
• Chargeback policy for idle resource.

Let’s understand these challenges further with a few scenarios. In all the examples below, we assume that we use the Enterprise edition in the US region, with rates of $0.06/slot-hour for pay-as-you-go, and $0.048/slot-hour for a 1-year commitment.

Scenario 1: Slot-hours billed differently at different stages of a query 

Looking at the total slot-hours for chargeback could be misleading because the slot-hours at different stages of a query may be billed differently.

Consider the following scenario:

• A reservation with a baseline of 0 slot and max 200 slots.
• No capacity commitment.
• Query Q1 runs from 5am to 6am with 200 slots for the whole hour.
• Query Q2 runs from 6am to 8am in 2 stages:
  • In the first stage, from 6am to 7am, it uses 150 slots for the whole hour.
  • In the second stage, from 7am to 8am, it uses 50 slots for the whole hour.

Both queries use the exact same slot-hours total, i.e., 200 slot-hours, and use the same reservation and edition. Hence we may think the chargeback to both queries should be the same.

But if you look closely, the two queries do not incur the same amount of cost.

Q1 uses 200 slots for 1 hour. Given the reservation with a baseline of 0 slot and a max of 200 slots, 200 slots are made available in this hour, and the cost of the query is $(200*0.06) = $12.

In contrast, Q2’s usage is split into 150 slots for the first hour and 50 slots for the second hour. Since slots are autoscaled in increments of 100, to run Q2, 200 slots are made available in the first hour and 100 slots are made available in the second hour. The total slot-hours for Q2 is therefore 300, and the cost is $(300*0.06) = $18.

Summary: The cost chargeback to a query needs to account for how many slots are used in different stages of the query and not just the total slot-hours (or slot-ms) used.

Scenario 2: Capacity commitment pricing vs. pay-as-you-go pricing from autoscaling

At different stages, a query may use slots from capacity commitment or from autoscaling that are charged at the pay-as-you-go price. 

Consider the following scenario:

• 1 reservation with a baseline of 100 slots and max slots as 300 slots.
• 1-year capacity commitment of 100 slots.
• Query Q1 runs from 5am to 6am and uses 300 slots for the whole 1 hour.
• Query Q2 runs from 6am to 8am in 2 stages. It uses 100 slots from 6am to 7am, and uses 200 slots from 7am to 8am.

Once again, the total slot-hours for both queries are the same, i.e., 300 slot hours, and we might chargeback the same cost to both queries.

But if you look closely, the queries do not incur the same amount of cost.

For Q1, 100 slots come from committed capacity and are charged at the 1-year commit price ($0.048/slot-hour), whereas 200 are autoscale slots that are charged at the pay-as-you-go price ($0.06/slot-hour). So the cost of Q1 is  $((100*0.048) + (200*0.06)) = $16.80.

For Q2, from 6-7am, 100 slots come from committed capacity and are charged at the 1-year commit price ($0.048/slot-hour), so the cost for 6-7am is  = $(100*0.048) = $4.80.

From 7-8am, 100 slots from committed capacity are charged at 1-year commit price ($0.048/slot-hour), and the other 100 are autoscale slots charged at pay-as-you-go price ($0.06/slot-hour). So the cost from 7-8am is  = $(100*0.048) + (100*0.06) = $10.80.

Hence the cost between 6-8am (duration when Query-2 is running) is = $4.80 + $10.80 = $15.60.

Summary: The cost chargeback to a query needs to account for whether the slots come from committed capacity or from autoscaling charged at pay-as-you-go price. A query may use both at different stages.

Scenario 3: Minimum slot capacity and increment in autoscaling

A query may be billed for more than the resource it actually needs because of the minimum slot capacity and the minimum increment in autoscaling.

• 1 reservation with a baseline of 0 slots and max slots as 300 slots.
• No capacity commitment.
• Query Q1 uses 50 slots for 10 seconds between 05:00:00 to 05:00:10.
• There is no other query running between 04:59:00 to 05:02:00.

If you were to chargeback by slot-ms, you would say that the query uses 50 slots for 10 seconds, or  500,000 slot-ms.

However, this assumption is flawed because of these two conditions:

1. Slot capacity is billed for a minimum of 1 minute before being billed per second.
2. Autoscaling happens in increments of 100 slots.

For Q1, 100 slots (not 50) are actually made available, for 1 minute (60,000 ms) and hence you will be charged for 6,000,000 slot-ms in your bill. 

Summary: The cost chargeback needs to account for minimum slot capacity and autoscaling increments. 

Scenario 4: Chargeback policy for idle resource

In the previous scenario, we see that a query that actually uses 500,000 slot-ms is billed for 6,000,000 slot-ms. Here we make the assumption that whatever resource is made available but not used is also included in the chargeback of the queries running at the same time. What happens if there are multiple queries running concurrently, with unused resources? Continuing with the example in Scenario 3, if there is another query, Q2, that uses 50 slots for 30s, from 05:00:10 to 05:00:40, then: 

• Q1 still uses 500,000 slot-ms like before.
• Q2 uses 1,500,000 slot-ms.
• The total bill remains 6,000,000 slot-ms as before, because slot capacity is billed for a minimum of 1 min and autoscaling increments by 100 slots.

There are several ways to consider the chargeback to Q1 and Q2:

1. Charge each query by its actual slot-ms, and have a separate “idle” category. In this case, Q1 is billed for 500,000 slot-ms, Q2 is billed for 1,500,000 slot-ms, and the remaining 4,000,000 slot-ms is attributed to the “idle” category.

1. Divide idle resources equally among the queries. In this case, Q1 is billed 2,500,000 slot-ms, and Q2 is billed 3,500,000 slot-ms.

1. Divide idle resources proportionally among the queries based on the queries’ usage. In this case, Q1 uses 1,833,333 slot-ms, while Q2 uses 4,166,667 slot-ms.

Summary: Chargeback policy needs to consider how to handle idle resources. Without a clear policy, there could be mismatches between users’ assumptions and the implementation, even leading to inconsistencies, such as the sum of the queries costs deviating from the bill. Moreover, different organizations may prefer different chargeback policies, and there’s no one-size-fits-all approach.

Conclusion

To conclude, providing accurate and useful chargeback for an organization’s usage of BigQuery presents a number of challenges. The common approaches of chargeback by accounts, reservations, and projects are often insufficient for most organizations, as they need user-level and team-level chargeback. However, chargeback by users and teams require us to be able to provide query-level cost estimates, and then aggregate by users and teams (via tags). Computing the query-level cost estimate is another tricky puzzle where simply considering the total slot usage of a query will not work. Instead, we need to consider various factors such as different billing for different stages of the same query, commitment pricing cs. pay-as-you-go pricing from autoscaling, minimum slot capacity and minimum autoscaling increments, and idle policy.

Fortunately, Unravel has information for all the pieces of the puzzle. Its proprietary algorithm intelligently combines these pieces of information and considers the scenarios discussed. Unravel recognizes that chargeback often doesn’t have a one-size-fits-all approach, and can work with customers to adapt its algorithm to specific requirements and use cases.