I’m looking for guidance on best practices when using the Append feature in the new QuickSight dataset editor.
Specifically, is there any proven guidance, performance benefit, or drawback to appending a mixture of:
Static historical datasets already imported into SPICE
A current dataset that is refreshed regularly from an S3 manifest and stored in SPICE
For example:
1 Current Dataset (SPICE, refreshed daily)
2 Archive 2024 Dataset (SPICE, static)
3 Archive 2023 Dataset (SPICE, static)
It would seem logical that, where historical data is static, QuickSight should reference the existing SPICE datasets directly rather than the underlying data sources which would trigger a full refresh. My thinking is that the current data would continue to refresh from its source, while the archived data could simply be reused from SPICE without any dependency on the original source data.
Alternatively, is it considered better practice to append the underlying data sources instead of existing datasets?
I’m interested in understanding:
Recommended AWS best practices.
Any performance implications.
Refresh behaviour.
SPICE storage considerations.
Any real-world experience from others implementing a similar archive pattern.
Has anyone compared these approaches or found advantages to one over the other?
As per my understanding, in the new data prep experience, when you use the input step you can choose “Add Dataset” to bring an existing Quick dight dataset in as a source, rather than reconnecting to the raw underlying data. So for your archive pattern you can point Append at your existing SPICE datasets (Current, Archive 2024, Archive 2023) directly. Append itself just does a vertical combine, essentially a UNION ALL, matching columns by name and keeping all rows including duplicates, so the archive-plus-current pattern is a natural fit for it.
Something worth noting is that when you combine existing datasets into a new one, the output becomes its own separate SPICE dataset with its own copy of the data materialized in SPICE, and it does not stay automatically in sync with the parents. You set up your own refresh schedule on this new combined dataset, and that schedule is independent of the schedules on the source datasets.
So appending existing SPICE datasets does spare you from requerying the original source systems for the static archives, since the parents are already imported, but the combined dataset still has to pull data through from those parents on its own schedule. If you build directly on the underlying data sources instead, every refresh of the combined dataset re-executes those source queries, including for the static archive years, which I beielve is what you are trying to avoid. So for static historical data, building on the existing SPICE datasets is the more sensible choice. As for the SPICE storage, the tradeoff to keep in mind is duplication. Your archive data will exist once in each archive dataset and again in the combined dataset, so you are effectively storing that historical data twice.
A few more thing that might be of interest to you is that parent and child datasets have to live in the same experience, so you cannot mix a legacy experience dataset with new experience Append. Also, if you ever consider Direct query mode rather than SPICE for the combined dataset, note that direct query is supported for a single SPICE parent, but not for multiple SPICE parents combined together, so for a multi-dataset append you would be on SPICE with a scheduled refresh anyway.
Hopefully that answered your questions, but feel free to reply to this thread with clarifications! I also went ahead and attached some official AWS documentation and a community post about refreshes in case they provide extra clarity.