QuickSight Dataset is set to Direct Query on one screen but set to SPICE in Dataset Editor

This appears to be an inconsistency in how the dataset's import mode is being displayed between different views in QuickSight.

In Amazon QuickSight, datasets can be configured to use either SPICE (Super-fast, Parallel, In-memory Calculation Engine) or Direct Query mode. SPICE imports and caches data in QuickSight's in-memory engine, while Direct Query sends queries directly to the data source in near real-time when users access dashboards.

The issue you're experiencing might be related to how the dataset was created through duplication. When you copied another dataset and changed the database connection, there may have been some metadata inconsistency that wasn't properly updated.

This could potentially be related to the dataset's internal configuration where the ImportMode parameter (which can be set to either "SPICE" or "DIRECT_QUERY") might have conflicting values in different parts of the system.

To resolve this issue, you could try:

1. Exporting the dataset using the QuickSight Asset Bundle feature and then reimporting it with a new prefix. This process often helps reset metadata and fix inconsistencies.

2. Creating a new dataset from scratch instead of duplicating the existing one.

3. If you have admin access, you might want to check if there are any scopedown policy configurations that could be affecting how the dataset behaves, as these can sometimes cause unexpected behavior with datasets.

If these approaches don't resolve the issue, you may need to contact AWS Support as this appears to be an unusual edge case where the UI is displaying contradictory information about the dataset's query mode.
Sources
QuickSight Error: NO_SCOPEDOWN_POLICY_CONFIGURED – Unable to Refresh Manually, Duplicate, or Edit Dataset | AWS re:Post
DataSet - Amazon Quick Sight
Best practices for Amazon QuickSight SPICE and direct query mode | AWS Business Intelligence Blog