I’m trying to understand the best way to set up incremental refresh and its limitations. Let’s say I have an Orders table and a Users table.
If I create a dataset by joining those 2 tables, QuickSight won’t let me use incremental refresh. If I join them by writing a custom SQL query instead, I can use incremental refresh but I end up with two CreateTimestamp columns and it’s not clear which one I should use when configuring the incremental refresh. I’m guessing this is not really the right way to do it.
If I create an Orders dataset and a Users dataset, I can set up incremental refresh on both and then create a UserOrders dataset that joins the two previous datasets. When I refresh the UserOrders dataset, my understanding is that QuickSight will ingest the data from SPICE instead of ingesting from my database. If my Orders dataset and Users dataset don’t refresh at the same time, does it mean that I could potentially end up with a record in UserOrders where I have a UserId for my order but no corresponding FirstName and LastName?
This is a simple example involving just 2 tables. A more realistic use case for me would likely involve at least 5-6 different tables. If I create a dataset from 6 child datasets, are there any best practices I should follow?