Remove an unused column from a dataset, but full refresh will give error

Question & Answer
, ,
1

After removing an unused column from a dataset, full refresh gives error:
The column(s) in this query are not found. Verify the column(s) specified in the dataset or in the custom SQL.

I can create a new dataset with the unused column removed and then replace dataset in the analysis. BUT is there a direct approach to refresh existing dataset successfully?

2

Hi @lbl ,
do I understand correctly that after removing an unused column from your dataset, you encounter an error during the full refresh, stating that the column(s) are not found? You’re asking if there’s a direct way to refresh the existing dataset without having to create a new one and replace it in the analysis.

When you remove a column from a dataset, especially if you’re using custom SQL, the dataset may still reference that column in the underlying SQL query or schema, causing the error during the refresh. Here’s a more detailed solution:

  1. Check for Custom SQL: If your dataset uses a custom SQL query to define its structure, you’ll need to manually update the SQL query to remove any references to the deleted column. To do this:
  • Open your dataset.
  • Go to the “Data source” section and find the custom SQL query.
  • Edit the query to remove the column that was deleted, and then save the changes.This ensures that QuickSight will no longer look for a column that doesn’t exist.
  1. Update Dataset Schema: If you’re not using custom SQL but the dataset schema was manually adjusted or relies on database views, ensure that the schema reflects the column removal. This may require re-importing the dataset or refreshing the metadata.
  2. Refresh the Dataset: Once the SQL or schema has been updated, perform a full refresh of the dataset. This should now work without any errors because QuickSight will no longer try to query the removed column.
  3. Verify Analysis: After the dataset refresh is successful, ensure that any analyses or visualizations that previously referenced the deleted column are updated to avoid errors in those reports.

This approach allows you to refresh the existing dataset directly, without having to create a completely new dataset and replace it in your analysis.

Regards,
Nico

If this helped you, please mark my answer as solution. That helps the community to find solutions faster.

3

Hi @lbl

Do you have any calculated fields in the dataset? It is possible that the column removed was being used in a calculated field?

Regards,
Giri

4

@Giridhar.Prabhu
There is no calculated field in dataset. Unused column means not used in calculated field or analysis. Thanks.

5

Hi @Nico
Custom SQL is not used. Data source is Athena table. When open the dataset in QuickSight, I can see the data schema shown correctly (ie unused column) being removed, but got error when Refresh Now. Do you have the same behaviour?

Thanks

1 Like
6

Hi @lbl

Have you checked the Fields section in your dataset? From the message it appears that the field is referenced in the dataset’s definition though it is removed from the source.

Regards,
Giri

7

hi @Giridhar.Prabhu

When Edit Dataset, it is showing the new data structure whereby unused field is removed.
Where can I get to the dataset’s definition and removed the unused field to align with the above?

Thanks.