QuickSight Dataset Refresh Failing, Causing Missing Fields in Visuals

[kaivanwadia]

A few datasets are failing to refresh in QuickSight. These datasets come from TinyBird data source pipes, where there was a change in the downstream datasource ID. However, there were no changes to the columns in the TinyBird pipe.
Despite this, QuickSight still references the old datasource ID in the dataset, leading to the following error: ERROR: clickhouse_fdw:Code: 60. DB::Exception: Unknown table expression identifier.

We found that removing and re-adding the table in the Edit Dataset flow resolves the dataset refresh issue. However, once the dataset is fixed, multiple visuals start showing Missing fields errors.

Is there a way to identify which missing fields QuickSight is referring to in the visuals? This would help us efficiently restore the affected analyses without manually checking each visual. Any guidance on troubleshooting this would be greatly appreciated.

Thanks

Kaivan

[Brett]

Hi @kaivanwadia and welcome to the QuickSight community!
Unfortunately, there’s no great way to identify what the fields being referred to are as it’s referencing an old ID that no longer exists.
I would suggest that after restoring the next time, storing a copy of the analysis through the CreateTemplate API.

And regarding your issue with TinyBird datasets not refreshing, I would suggest potentially creating a support ticket to get additional assistance from the AWS support staff so that you can try and avoid these issues in the future!

[Giridhar.Prabhu]

Hi @kaivanwadia

When you remove a table in the Dataset it has an impact on the fields used in the Analysis. So, even if you restored a table with same names and saved the dataset, the visuals have already lost reference to the field and show as missing fields.

Your dataset refresh issue also looks similar. But I am surprised QuickSight allowed you to delete the older dataset if it was being referred in another dataset.

I am not sure if it applies to your case; but you were changing the source of your data you could probably use DescribeDataset and UpdateDataset CLI commands as described in the following post

Changing Data Source for a Dataset

Hi @dan.kainz - It sounds like you could be looking for 1 of 2 things. If you need to update the DataSource connection properties you can follow the steps outlined here Editing a database data source. Updating the DataSource used by a DataSet. This is not currently possible in the UI. As a workaround, you can update the DataSet using the QuickSight API. The rough process; DescribeDataset a. Update the property DataSourceArn to your new DataSource Arn UpdateDataset

Regards,
Giri

[Brett]

Hi @kaivanwadia,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @kaivanwadia,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!