sayle-doit

/*
 *  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

  --
  --  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

  --
  --  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

/*
 *  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

/*
 *  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

/*
 *  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

/*
 *  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.