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

1 Like
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.

1 Like
8

Hi @lbl,
Have you tried creating a copy of your dataset and removing the unused field(s). Then create a copy of your analysis and replace the dataset with the new version that does not have the unused fields.
Maybe that will work for your case?

Alternatively, let us know if you found a work around for your case. If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

9

Hi @Brett

Please refer to the original post which already stated the method to create another dataset and replace dataset which is not efficient. It requires additional effort to re-schedule the refresh and change the dataset id in the analysis json file.

And I am looking for method to refresh dataset. Since unused columns are not referenced in calculated fields or visuals, why does QuickSight even throw error?

Thanks!

10

Hey @lbl

Are you still working on this or were you able to find a solution?

What steps did you take to remove the columns from your dataset? Are you doing this in the data prep layer or are you doing this prior to ingestion on your data source?

11

Hello @lbl

Are you still working on this issue or were you able to find a solution?

12

Hello @lbl

This post is being archived because it has been some time since we have heard from you.

If you still need help with this issue please feel free to post a new topic at the top of the community so that it will be a priority for community experts. You can link this topic in your new post.