Converting a dataset into custom SQL

My source tables have changed and the dataset gives an error that one column is missing which is expected but I cant seem to find a resolution for it. Should it not ideally take whatever is the latest schema on its own?

Second part of my question is to check if there is a way I can convert it into a custom SQL?

Hi @mrinalspringpod

QuickSight does not automatically detect or adapt to schema changes in underlying data sources. The dataset will continue to look for the original schema.

  • Handle missing columns: When a previously used column is no longer present, you will get an error as you observed. To resolve it, you can edit the dataset and remove the reference to the missing column. This will allow the dataset to load again.

  • Use custom SQL: Yes, you can use custom SQL in the dataset to adapt to schema changes if and only if, obviously, the data source is SQL-based. When using custom SQL, you have full control over the query and can modify the SELECT clause to adapt to structural changes in the tables.

  • To convert to custom SQL:
    – Edit the dataset
    – Switch to the “Edit/Preview data” tab
    – Click on “Switch to custom SQL”
    – Write a SQL query to handle the new schema
    – Save the dataset

The key advantages of custom SQL are the flexibility to handle structural changes and having full control over the final query.

Let me know if you have any other questions!

Did this solution work for you? I am marking this as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

GL
:slight_smile:

Yes, thank you for the detailed response

1 Like

Hi Gillepa, I wonder if you could show screenshots of you converting a dataset to custom SQL, I can’t find any tab for edit/preview data, there is however an option to edit/preview data when creating a dataset but as far as I can tell there is no option if you’ve already created the dataset.

Hi @gillepa, I’m running into the same issue as JJBD here. I attempted to follow your instructions and I’m stuck on step 2. I just don’t see the “Edit/Preview data” tab in the dataset editor (I see the table queries on the grid view and can access the previews for each table, but otherwise there are no other tabs).

Is it possible this is role restricted? Would I need to reach out to my org’s BI team/admins for permission?