SELECT columnABC as column1,
valueABC as column2,
timestampA as last_updated_ts
FROM table_A
UNION ALL
SELECT columnXYZ as column1,
valueXYZ as column2
timestampB as last_updated_ts
FROM table_B
now, how does SPICE deal with the last_updated_ts column for incremental refresh in this case? does it inject a where clause for every query in the union or does it load everything and then does an outer select statement with a where clause for last_updated_ts?
The query will run as defined and incremental refresh applied on the result set( Amazon Athena is my datasource )
/* QuickSight 263cf835-aa26-4cb5-9f0e-96059fac9be1 */
SELECT "Training_Title", "Employee_ID", "Training_Date", "Training_Month", "Training_Year", "Completions_Goal"
FROM
(select 'Training A' as Training_Title,
'501' as Employee_ID,
cast('2022-03-14' as date ) as Training_Date,
'3' as Training_Month,
'2022' as Training_Year,
10 as Completions_Goal
union
select 'Training A' as Training_Title,
'607' as Employee_ID,
cast('2022-03-18' as date ) as Training_Date,
'3' as Training_Month,
'2022' as Training_Year,
10 as Completions_Goal
)
AS "training_goals_athena_sql"
WHERE "Training_Date" > from_unixtime(cast(substr(cast(1717970351817 as varchar), 1, 10) AS bigint))
This means the incremental refresh is not actually incremental. It will fetch all the data from the DB and then dump the records that are older than the window size.
If this is needed the case. then it is wise to limit the amount of data read by SPICE by applying a rolling date limit into the query itself. the rolling date limit should be larger than the SPICE window size. it will still fetch more than it needs but that will be more efficient than fetching the whole query 100% every “Incremental refresh” cycle.
Hello @Ali_B, since we have not heard back from you, I will mark @Koushik_Muthanna’s response as the solution. Please let us know if you have any remaining questions. Thank you!
