Hi @john.bridge,
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.