Incremental Extract Refresh Does Not Get the Exact Rows of New Data

Issue

Based on a query against a live data source, incremental extract refreshes are expected to get, for example:100 rows of new data, but only get 80 rows or less of new data.

A "manual run" on the scheduled incremental extract refresh on Tableau Server does not make a difference, it is still getting less new data.

Environment

Tableau Server 10.5
Tableau Server 2018.1
Oracle Linux 7
New rows can be added into the live data source any time during the day.

Resolution

When creating a extract data in Tableau Desktop, users can choose which dimensions to include for incremental refresh (see screenshot below), in this case it is "Order Date".

identify new rows

When checking the data type of "Order Date" in Tableau Desktop (see screenshot below), you notice that the correct data type is not chosen in the data source..The data type was set to "Date" and not the default "Date & Time".

Default Data Setting

Once changed to the default "Date & Time", the next incremental extract refresh returns the correct number of rows of new data.

Cause

When data type is set to "Date", the daily incremental extract refresh loads and compares new data based on the date format "YYYY-MM-DD".

The comparison is made only down to date level and does not take time into account. Therefore, an incremental extract refresh run today, at for example 6:00AM, will not include any new data created between today 12:00AM and 6:00AM.

When data type is set to "Date & Time", the daily incremental extract refresh loads and compare new data based on the date format "YYYY-MM-DD HH24:MI:SS'". This is the default setting and will include the most accurate incremental data.