title: Bigquery "FOR SYSTEM_TIME AS OF" feature guarantee for data recovery
body: <p>In Google BigQuery, it is possible to retrieve rows of a table (snapshot) in the past (at least in the last 7 days) :</p>
  
  <p>With Legacy SQL, we can use <a href="https://cloud.google.com/bigquery/table-decorators#snapshot_decorators" rel="nofollow noreferrer">snapshot decorators</a> :</p>
  
  <pre class="lang-sql prettyprint-override"><code>#legacySQL
  SELECT * FROM [PROJECT_ID:DATASET.TABLE@-3600000]
  </code></pre>
  
  <p>With Standard SQL, we can use <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#from-clause" rel="nofollow noreferrer"><code>FOR SYSTEM_TIME AS OF</code> in <code>FROM</code> clause</a> :</p>
  
  <pre class="lang-sql prettyprint-override"><code>#standardSQL
  SELECT *
  FROM `PROJECT_ID.DATASET.TABLE`
    FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
  </code></pre>
  
  <p>Both examples return snapshots of <code>PROJECT_ID.DATASET.TABLE</code> one hour ago.</p>
  
  <p>But I'm wondering if there is any guarantee of retrieving table data in the past.
  A colleague told me that he read somewhere (but he can't find it anymore) that this was a "best effort" feature, so potentially there may be some missing data.</p>
  
  <p>Is this feature usable in production environments for data recovery (for example if someone inadvertently truncates an important table), as long as the recovery in done within the 7 days after the mistake? Is there any guarantee that we can access <strong>the whole data</strong> stored at a particular time?</p>
  
  <p><strong>Update</strong></p>
  
  <p>As @Pentium10 correctly pointed out in a comment, recovering old data after doing <code>CREATE OR REPLACE</code> jobs on a table is not possible. After some tries, I will even add that executing jobs with one of these <a href="https://cloud.google.com/bigquery/docs/reference/auditlogs/rest/Shared.Types/QueryStatementType" rel="nofollow noreferrer">statement types</a> :</p>
  
  <ul>
  <li><code>CREATE_TABLE</code> (<code>CREATE OR REPLACE</code>)</li>
  <li><code>CREATE_TABLE_AS_SELECT</code></li>
  <li><code>DROP_TABLE</code></li>
  </ul>
  
  <p>completely removes the ability to retrieve data back in time for that particular table.</p>
  
  <p>But, supposing that we only use the following statement types to modify the table data :</p>
  
  <ul>
  <li><code>INSERT</code></li>
  <li><code>UPDATE</code></li>
  <li><code>DELETE</code></li>
  <li><code>MERGE</code></li>
  </ul>
  
  <p>Is there a guarantee that the snapshot data at <em>t</em> is <strong>exactly</strong> the data contained in the table at <em>t</em>? Or is this a "best effort" feature?</p>