Learnings

  BigQuery matins a 7-day history and allows you to query a point-in-time snapshot of your data by using either table decorators or SYSTEM_TIME AS OF in the FROM clause. You can easily revert changes without having to request recovery from backups To revert a change you can either use standard SQL or bq command-line tools. For example, bq cp project:dataset.table@-36000 project:dataset.table This command will replace the table with data from the same table 1 hour ago. SELECT * FROM `project:dataset.table ` FOR SYSTEM TIME AS OF '2020-08-28 23:00:00-07:00' This command will retrieve the data at a point in time for the given table. You can then replace the incorrect table with the point in time data. This works even after the dataset is deleted. Similarly, using standard SQL dialogue, you can query the data 1 hour ago SELECT * FROM `project.dataset.table` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) or create the new table using data 1 hour ago CR...

BigQuery is a fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data. Despite its scalable nature, there exist tips and tricks in writing Bigquery SQL to further improve the query performance 1. Use standard SQL instead of legacy SQL 2. Best practices for query structure 2.1 select necessary columns Original code content_copy select * from dataset.table Optimized content_copy select dim1, metric1 from dataset.table Reason Only select the columns necessary, especially in inner queries.  SELECT *  is cost-inefficient and may also hurt performance If the number of columns to return is large, consider using  SELECT * EXCEPT  to exclude unneeded columns 2.2 ORDER BY with LIMIT Original code content_copy select t.dim1, t.dim2, t.metric1 from dataset.table order by metric1 desc Optimized code content_copy select t.dim1, t.dim2, t.metric1 from dataset.table order by metric1 desc limit 1000 Reason Writing ...