Help users understand query implications (surface query costs)

[riordan]

Users often run queries without any awareness of the impact (in shared resource use or cost). This can be particularly expensive on Google’s BigQuery, where their default on-demand pricing that bills by bytes loaded rather than compute time means its really easy to write a query (that can return super fast) but incurs massive costs. (I once saw a $37k USD query that was `SELECT * FROM logs* LIMIT 1').

Bigquery's SQLalchemy has a dry-run mode (see: googleapis/python-bigquery-sqlalchemy#56) that tests the query validity against the real data schema AND returns the estimated query cost. With this you can both inform the user if their query will run AND what it'll cost. I've seen BQ SQL UI's (including some of Google's own) that send the query to dry-run each time the user stops typing for 500ms. At my last company, we saw this helped all classes of sql users write more efficient queries without a drop in user productivity.

[eschutho]

Hi @riordan we have query cost estimation enabled in some dbs, but not for BQ yet. If you'd like to see this feature built, and I agree it would be great to have, you can convert this discussion to an issue and someone can pick it up to work on. In the meantime, if you're not already using this, you can also pass the maximum_bytes_billed argument to the sqlalchemy string to cap the cost at a certain amount: https://cloud.google.com/bigquery/docs/best-practices-costs#limit_query_costs_by_restricting_the_number_of_bytes_billed