Query SPICE dataset for recent data and DIRECT QUERY dataset for historic data

Yes, it is possible to achieve what you're looking for with Amazon QuickSight, though it requires some careful setup and consideration.

QuickSight supports using multiple datasets in a single dashboard, which allows you to combine SPICE and direct query mode datasets. This approach can help you optimize performance for recent data while still maintaining access to historical data.

Here's how you can set this up:

1. Create two datasets:

   • A SPICE dataset containing the last two months of trade data, which you can schedule to refresh daily or as needed.
   • A direct query dataset using Athena for your historical data.

2. In your QuickSight analysis, you can use these datasets on different sheets or visuals within the same dashboard:

   • Use the SPICE dataset for visuals that show recent trade data (last two months).
   • Use the direct query dataset for visuals that require historical data or when users need to access older trades.

3. To make this work seamlessly, you can use calculated fields and parameters to switch between datasets based on the date range selected by the user. For example, you could create a parameter for the date range and use it in calculated fields to determine which dataset to use for each visual.

4. For visuals that need to span both recent and historical data, you may need to use custom SQL queries in your direct query dataset to union the recent data with the historical data. This way, you can create a single visual that seamlessly combines data from both sources.

Keep in mind that this approach may lead to some data inconsistencies across different pages or visuals, as one dataset will be using daily refreshed data while the other uses near real-time data. It's a good practice to add a note in the dashboard explaining the data refresh schedules for different sections.

While this solution allows you to leverage the speed of SPICE for recent data and the flexibility of direct query for historical data, it does require more complex setup and maintenance. You'll need to ensure that your SPICE dataset is regularly updated and that your calculations correctly handle the transition between datasets.

Remember that as your data volume grows, you may need to adjust your strategy. Consider using incremental refreshes for your SPICE dataset to minimize data transfer and processing time. You might also explore options like data partitioning in your source database to further optimize query performance for both recent and historical data.

Sources
Best practices for Amazon QuickSight SPICE and direct query mode | AWS Business Intelligence Blog
Refreshing SPICE data - Amazon QuickSight