KNOWLEDGE BASE

No New Rows are Being Added Despite Successfully Performing Incremental Refreshes of BigQuery Extracts


Published: 02 Nov 2017
Last Modified Date: 02 Nov 2017

Issue

When performing an incremental refresh of an extract connected to BigQuery, the job will be successful but no new rows will be added to the data source. Even though rows have been added to the database; repeated incremental refreshes will primarily continue this behavior, with occasional refreshes adding large amounts of rows.

Environment

  • Tableau Server
  • Tableau Desktop
  • Google BigQuery

Resolution

BigQuery does not cache the results when the "query uses non-deterministic functions; for example, date and time functions such as CURRENT_TIMESTAMP() and NOW()..." By adding the non-deterministic functions to the query, it can be guaranteed that BigQuery will not reply to these queries with cached results.

Alternatively, you can slow the rate in which incremental refreshes occur to ensure that new rows have been written to the table between each incremental refresh.

Cause

BigQuery will always attempt to reuse cached results when a duplicate query is run (with some exceptions). Since incremental refreshes send a query looking for new rows in the defined column, an incremental refresh will resend the same query until new rows appear.

As such, if a single incremental refresh occurs and no rows are reported, subsequent queries for new rows will receive the cached responses from BigQuery, even if rows have been added between the two incremental refresh attempts.

For more information about cached query results, see Using Cached Query Results in the Google Cloud Platform guides. 

Additional Information

Full refreshes and Live connections to BigQuery do not produce this behavior. 
Did this article resolve the issue?