Loading

Amazon Redshift live connection may return incorrect aggregate function results when a Primary Key is defined

Publish Date: Apr 13, 2024
Description

When a PRIMARY KEY is defined on the Amazon Redshift database, an aggregate function may return incorrect results when the workbook is using a live connection.  For example, the result of the COUNTD function may be incorrect with a live connection after publishing the workbook.

Cause

Amazon Redshift uses PRIMARY KEY and FOREIGN KEY as information to generate more efficient query plans. However, the query plan assumes that the constraints in tables are valid as defined, even though they are not enforced by Amazon Redshift (They do not actually function as constraints in Amazon Redshift).

For example, the PRIMARY KEY implies that other tables can rely on this set of columns as a unique identifier for rows, but if the actual values are not unique, some queries could return incorrect results. For additional information about this behavior, refer to the third-party links below*.
Define primary key and foreign key constraints
Defining table constraints

*Although we make every effort to ensure links to external websites are accurate, up to date, and relevant, Tableau cannot take responsibility for the accuracy or freshness of pages maintained by external providers. Contact the external site for answers to questions regarding its content.
Resolution

Option 1

Do not define primary and foreign key constraints in DDL ｗhen creating tables in Amazon Redshift.

Option 2

Create and use an extract in Tableau.

For more details, refer to Create an extract and Create Extracts on the Web.

Option 3

Edit the data source, generate and use a custom SQL query in Tableau by converting a Connection to Custom SQL.

For more details, refer to Converting a Connection to Custom SQL.

Option 4

Set the capability customizations in the Tableau Datasource Customization (TDC) file to prevent Tableau from attempting to read metadata describing primary and foreign key constraints.

For more details, refer to Tableau Capability Customizations Reference.

 
Additional Resources
Depending on the version of the Amazon Redshift driver, the results of the aggregate function may be correct for older versions.
Knowledge Article Number

001497191

 
Loading
Salesforce Help | Article