# Five Useful Queries to Get BigQuery Costs

At PeerDB, we are building a fast and cost-effective way to replicate data from Postgres to Data Warehouses such as BigQuery. So our customers are heavy Data Warehouse users. One topic that comes up in almost each and every engagement is Data Warehouse costs. In recent months, we've explored BigQuery costs and optimization strategies in detail. We're eager to share our learnings.

This first blog post on **Five Useful Queries to Get BigQuery Costs** will be helpful for any company who uses BigQuery and and wants to keep their costs in a check.

## Total Cost by day in the last 7 days

Let's start by examining the overall cost estimates overtime for your BigQuery instance. This would help you understand the query load over various days, and give you a comprehensive view of the costs over each day.

```sql
SELECT
DATE_TRUNC(jobs.creation_time, DAY) AS job_date,
SUM(jobs.total_bytes_billed/1024/1024/1024/1024 * 5) AS job_cost_usd_total
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
WHERE
    jobs.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY job_date
ORDER BY job_cost_usd_total DESC;
```

**NOTE:** Adjust the [region-qualifier](https://cloud.google.com/bigquery/docs/information-schema-intro#region_qualifier) based on the location of your Warehouse.

**Example Output:**

![](https://lh7-us.googleusercontent.com/BR4hEDv-pJL2BrAhilrbUdI7PWIP4uVyaRMsgQUAReogpF-37IhmJ5Tp0WgED_fQx8TasFQDJwVE45lCo-OI-Vc3lcus_aGYxWaMiYsNrLb3YeaV2ecuvfpn5kYv7FYuXhHNDzkzXBQCv0vdCr2VOTU align="left")

# Total Costs per Application or User

When applications query BigQuery, they usually do so using service accounts, each associated with an email. Similarly, when users in your organization access the console and run a query, it is associated with the user's email. To understand the impact of an application or a user on your BigQuery costs, the following query would be useful.

```sql
SELECT
user_email, 
SUM(jobs.total_bytes_billed/1024/1024/1024/1024 * 5) AS job_cost_usd_total
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
WHERE
    jobs.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_email
ORDER BY job_cost_usd_total DESC;
```

**Example Output:**

![](https://lh7-us.googleusercontent.com/88hlIAVBKHWQavjS-4xrJk_WuaIgUU2bUS-8MaWZx2lH-tQo9jqEbW65f9W0LLC7Jtfhf1S_IiJkenc-JVm7zFEnv0WlDoVGjzrt_PnItc9TvO8fanfnBcYx7_Si6yz7Ogu3xxVi8HoVCCss0rTc25k align="left")

# Cost Analysis Based on Query Types

Typically, one would examine this to understand which query types are dominating costs. This is useful, for example, to determine if `MERGE` queries, commonly generated by transformation tools like [dbt](https://www.getdbt.com/) are driving up costs. This enables you to fine tune specific query types

```sql
SELECT
statement_type, 
SUM(jobs.total_bytes_billed/1024/1024/1024/1024 * 5) AS job_cost_usd_total
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
WHERE
    jobs.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY statement_type
ORDER BY job_cost_usd_total DESC;
```

**Example Output:**

![](https://lh7-us.googleusercontent.com/LiIAc-BVOP_EBq2QkOTmPoTfvlzgNHkDzE__y4xPDmkgd9OSXku0gnYHt5d-ELwwAvFouausKWKbqHz--pQVJMY9qmxaBv9z6CoMoMgjvnJhZ40oe1eJz_dJpPui838BgH8V7TdjEbUHPm-54R9O0Xk align="left")

# Total Cost by Project in the last 7 days

You can analyze BigQuery costs per project to ensure efficient resource allocation and budget management. This analysis helps you identify high-cost projects, enabling targeted optimizations to reduce expenses.

```sql
SELECT
project_id, SUM(jobs.total_bytes_billed/1024/1024/1024/1024 * 5) AS job_cost_usd_total
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT jobs
WHERE
    jobs.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY project_id
ORDER BY job_cost_usd_total DESC;
```

# Query Dry Runs with no costs to get costs

In cases where you are concerned about a specific query being intensive and incurring high costs, it is useful to run the query in dry-run mode before executing it. This will show you the amount of data that the query will process, helping you estimate the costs. Additionally, you can test your data modeling optimizations – for example, clustering and partitioning – to see how they impact the amount of data processed and the costs in this way.

Run the below query using [BigQuery's cli `bq`](https://cloud.google.com/bigquery/docs/running-queries)

```bash
bq query --format=prettyjson --dry_run \
--use_legacy_sql=false "<query_string>"
```

**Example Output:**

```bash
{
  "configuration": {
    "dryRun": true,
    "jobType": "QUERY",
    ...
    "totalBytesProcessed": "74113430156" 
  },
  "status": {
    "state": "DONE"
  },
  ...
}
```

The above output shows that ~74GB of data would be processed by the query, and the [estimated costs](https://cloud.google.com/bigquery/pricing) would be ~37 cents.

**Reference:** [Here](https://cloud.google.com/knowledge/kb/analyse-bigquery-query-costs-000004270) goes the official documentation on BigQuery costs. We found this to be a useful resource while coming with the blog.

## Closing Thoughts...

Hope you enjoyed reading the blog. More blogs on BigQuery costs and optimizations are coming soon. At PeerDB, we implement mechanisms such as [auto partitioning and clustering](https://github.com/PeerDB-io/peerdb/pull/915) to minimize BigQuery costs while replicating data from Postgres. Few of our customers have seen 5x reduction in Warehouse costs. If you wanted give PeerDB a shot, below resources would be helpful:

1. Try out the our [fully managed offering](https://auth.peerdb.cloud/en/signup) at no cost. **OR**
    
2. Visit our [**GitHub**](https://github.com/PeerDB-io/peerdb) repository to Get Started.