Skip to content

Instantly share code, notes, and snippets.

/*
* This query will return back the project, location, job_type, and a counter of the number of jobs running in the combination
* of them for all projects included in the BQ audit log.
*
* This will assist in tracking down jobs running outside of expected regions or show an overview of where job types are
* are running throughout your organization.
*
* Instructions:
* Replace <project_id> and <audit_log_dataset> with the project and dataset name where your BQ audit log sink is located.
* If you have not created one yet, follow the instructions located here:
@sayle-doit
sayle-doit / bq_tables_in_project.sql
Last active October 30, 2023 22:26
Query that returns out all BigQuery tables inside of a project.
--
-- This query will pull out all tables that exist inside of the currently
-- selected project.
--
-- Note by default this hits the US multi-region, so if using a different
-- region then change region-us below to that region name.
--
SELECT
CONCAT(table_catalog, ':', table_schema, '.', table_name) AS table_name
-- This line uses the dot notation, so if you need that versus traditional
@sayle-doit
sayle-doit / bq_tables_queried_count.sql
Last active October 31, 2023 13:53
Query to count out the number of query jobs that reference each table in a project.
--
-- This query will pull out a count of all tables referenced over the past
-- 7 days (configurable) by query jobs in the current project.
-- Note this will pull in queries that query the information schema views.
--
-- Note by default this hits the US multi-region, so if using a different
-- region then change region-us below to that region name.
--
DECLARE start_time TIMESTAMP DEFAULT
@sayle-doit
sayle-doit / bq_per_table_storage_billing_recommendation.sql
Last active March 4, 2024 11:37
BigQuery storage billing model recommender on a per table basis
/*
* This query will look at a single project (by default in US multi-region) and
* calculate the logical and physical billing prices for each table in all datasets
* contained inside of it then provide a recommendation on if it is better to put it
* into a dataset that uses the logical (uncompressed) or physical (compressed)
* storage billing models.
*
* Physical (also called compressed) Storage went GA on July 5, 2023. It is set at
* the dataset level, so if two or more tables are better suited for different
* billing models in the same dataset it may be best to split them into separate
@sayle-doit
sayle-doit / bq_project_storage_model_recommendation.sql
Last active October 26, 2023 09:18
BigQuery project dataset storage model recommendations
/*
* This query will look at a single project (by default in US multi-region) and
* calculate the logical and physical billing prices for each dataset inside of it
* then provide a recommendation on whether to keep it on logical storage
* or switch to the physical billing model.
*
* Physical (also called compressed) Storage will be GA and released for public
* consumption on July 5, 2023.
*
* It also includes inside of the storage CTE lots of extra values that can be used
@sayle-doit
sayle-doit / bq_job_editions_cost_comparison_with_autoscaler.sql
Last active June 3, 2024 14:46
Compare BigQuery job costs when running a job on either BigQuery Editions with the autoscaler or on-demand with both new and old pricing models.
/*
* This query will look at the past 30 days of job history to analyze it for costs under
* BigQuery Editions while utilizing the new autoscaling feature that was introduced.
* It does this for those using both PAYG (Pay As You Go) and commitment models.
* It will also compare this versus running the query with the on-demand model.
*
* Note that this query utilizes some math modeling behaviors that the BigQuery
* autoscaler uses. Namely these are the up to 10 seconds "slot scale up time,"
* the minimum of 60 seconds "slot scale down time," and the behavior that the
* autoscaler scales up and down in factors of 100 slots for each job.
@sayle-doit
sayle-doit / bq_storage_across_org.sql
Last active June 16, 2023 17:55
Determine BigQuery Storage Costs Across an Organization for Both Compressed (Physical) and Uncompressed (Logical) Storage
/*
* This query will run across an entire organization looking at tables across every project
* and shows how they will compare on compressed and uncompressed storage.
*
* Region Notes:
* This query will only read from a single region or multi-region at a time. It's
* currently not possible to read this data from across all
*
* By default this reads from the US multi-region, so this might need to be changed if
* your data lives elsewhere.