Amazon Redshift Extract Fails or Completes with Missing Data

Issue

Occasionally when refreshing large Redshift extracts, one of the following issues occurs:

The refresh is successful but only gathers a small subset of the data
For example, a Redshift extract that is expected to have 100s of millions of rows will show as "successful" in Background tasks for extract but the extract will only contain less than 50k rows.
The refresh will fail with the error, "The connection to the data source might have been lost"

Environment

Tableau Server
Tableau Desktop
Amazon Redshift

Resolution

As a workaround, use a .tdc file to disable the following CAP setting

CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY

Cause

This is related to the behavior of Redshift driver. There are two aspects to this issue:

1. A secondary, redundant, connection check is being executed with a prepare query and canceling the long-running query.

2. When the prepare query connection check is performed the long running query is canceled and the Redshift driver fails to return an error message to Tableau Server. Therefore, Tableau Server believes it is the end of the data and marks the extract as "Successful".

Additional Information

Basic .tdc file should include the redshift names and the CAP ODBC statement, as shown below:

<connection-customization class='redshift' enabled='true' version='9.1'>
  <vendor name='redshift' />
  <driver name='redshift' />
  <customizations>
<customization name='CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY' value='no' />
  </customizations>

See the attached redshift.tdc file for an example.