How prevent your data team from spending too much on Bigquery?

Bigquery is a dream for a data team. The more they use it, the more they use it, and that's when the expensive queries come in. Don't panic, Biq Blue is there.

Focus on expensive users

Do you have a precise idea of the usage of each of your users? Use the INFORMATION_SCHEMA.JOBS view with your SKU for each request, grouped by user.

SELECT
    user_email,
    (SUM(total_bytes_billed)/1024/1024/1024/1024) * 6.25 AS cost, -- $6.25 / TB on-demand (depends of your SKU)
FROM `region-us.INFORMATION_SCHEMA.JOBS` -- set your region
WHERE 
    DATE(creation_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)

    -- Focus on users
    AND user_email NOT LIKE "%iam.gserviceaccount.com" -- exclude service accounts

    -- Bigquery invoices only successful queries
    AND job_type = 'QUERY'
    AND state = 'DONE'
    AND error_result IS NULL
    AND (statement_type != "SCRIPT" OR statement_type IS NULL)

A simpler way is to use Biq Blue to display the list of users.

Focus on expensive queries

Give your users direct access to Biq Blue, so they can see the cost of their queries, analyze them and follow optimization recommendations.

Setup alerting

Biq Blue checks every hour if a user has spend too much money, then fires an event. Use the BIQBLUE_ALERT_WEBHOOK_URL webhook to intercept the event and send it to your favorite alerting channel (Email, Slack, Teams, etc).

And if you're still not convinced, Biq Blue shows you the money you could have saved in the past with effective alerting.

Conclusion

It's by giving your team the levers to understand their consumption and proposing solutions that you'll quickly see the difference in your Bigquery bill.