How to access Historical Data using Time Travel in BigQuery

Free AI web copilot to create summaries, insights and extended knowledge, download it at here

1644

Abstract

-566c2299c435">versioning</a> [2].</p><p id="ea75">You can do this very easily using SQL. This clause uses a constant timestamp expression and references the version of the table that was current at that timestamp. The table must be stored in BigQuery, so it cannot be an external table.</p><div id="2828"><pre><span class="hljs-keyword">SELECT</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">FROM</span> bigquery<span class="hljs-operator">-</span>public<span class="hljs-operator">-</span>data.samples.shakespeare <span class="hljs-keyword">FOR</span> <span class="hljs-built_in">SYSTEM_TIME</span> <span class="hljs-keyword">AS</span> <span class="hljs-keyword">OF</span> TIMESTAMP_SUB(<span class="hljs-built_in">CURRENT_TIMESTAMP</span>(), <span class="hljs-type">INTERVAL</span> <span class="hljs-number">1</span> <span class="hljs-keyword">HOUR</span>);</pre></div><p id="b19b">In this example, I applied it to the Shakespeare public dataset. As you can see below you will get the result as usual.</p><figure id="4fcb"><img src="https://cdn-images-1.readmedium.com/v2/resize:fit:800/1*3XXp9gR5rmrxuA03YE3jYw.png"><figcaption>Results — Image by Author</figcaption></figure><p id="5aac">You can then use the result for analysis purposes or, in case of accidental deletion, save it as a table and restore it.</p><p id="ca47">To me, that’s a super cool feature if you’re not already historizing data anyway. But even here it could happen that you accidentally delete this data, now Google offers you to restore this data for seven days. In any case, a plus point in terms of data security.</p> <figure id="05a

Options

6"> <div> <div> <img class="ratio" src="http://placehold.it/16x9"> <iframe class="" src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fgiphy.com%2Fembed%2F3o7qDON1FYhi5eoqgU%2Ftwitter%2Fiframe&display_name=Giphy&url=https%3A%2F%2Fmedia.giphy.com%2Fmedia%2F3o7qDON1FYhi5eoqgU%2Fgiphy.gif&image=https%3A%2F%2Fi.giphy.com%2Fmedia%2F3o7qDON1FYhi5eoqgU%2Fgiphy.gif&key=a19fcc184b9711e1b4764040d3dc5c07&type=text%2Fhtml&schema=giphy" allowfullscreen="" frameborder="0" height="237" width="435"> </div> </div> </figure></iframe></div></div></figure><h2 id="5e95">Sources and Further Readings</h2><p id="1a51">[1] Google, <a href="https://cloud.google.com/bigquery/docs/table-snapshots-intro">Introduction to table snapshots</a> (2021)</p><p id="2402">[2] Christian Lauer, <a href="https://readmedium.com/collaborate-better-with-data-versioning-566c2299c435">Collaborate Better with Data Versioning</a> (2021)</p><div id="a48e" class="link-block"> <a href="https://readmedium.com/mlearning-ai-submission-suggestions-b51e2b130bfb"> <div> <div> <h2>Mlearning.ai Submission Suggestions</h2> <div><h3>How to become a writer on Mlearning.ai</h3></div> <div><p>medium.com</p></div> </div> <div> <div style="background-image: url(https://miro.readmedium.com/v2/resize:fit:320/1*ib0DX0UzRoFcNuZILb7rNA.jpeg)"></div> </div> </div> </a> </div></article></body>

Restore and Analyze deleted or changed Data

In case you haven’t heard about it — For some time now, BigQuery has been offering time travel to access stored data that has been modified or deleted. You can access the data at any point in time within the last seven days. You can use time travel to retrieve data that has been updated or deleted, restore a deleted table, or restore a table that has expired.

I think it’s a super handy feature because it comes out of the box and allows you to view the history of data, which can be interesting for compliance but also to understand business processes or even to detect fraud. Provided of course that the seven days are enough. For the use case that you have accidentally deleted something should be enough in any case. However, if you want to keep data and the changes to it longer, there is either the possibility to create snapshots [1] or to realize the whole thing via versioning [2].

You can do this very easily using SQL. This clause uses a constant timestamp expression and references the version of the table that was current at that timestamp. The table must be stored in BigQuery, so it cannot be an external table.

SELECT *
 FROM bigquery-public-data.samples.shakespeare FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

In this example, I applied it to the Shakespeare public dataset. As you can see below you will get the result as usual.

Results — Image by Author

You can then use the result for analysis purposes or, in case of accidental deletion, save it as a table and restore it.

To me, that’s a super cool feature if you’re not already historizing data anyway. But even here it could happen that you accidentally delete this data, now Google offers you to restore this data for seven days. In any case, a plus point in terms of data security.