Virtual dataset "sync columns" requires too much manual Intervention

[gidinetapp]

Bug description

When updating a virtual dataset column names (adding or modifying), after saving the dataset, I need to go to "edit dataset" again and click on "sync columns from source" and save again. Only then can I use the new columns in charts.
There are a few issues regarding this process:

1. Syncing Before Saving: Currently, syncing columns requires too much manual intervention: save the dataset, re-enter it to sync the columns and save again. Allowing column syncing before saving the dataset would save time for users.
2. Syncing with No Data Bug: If the query in the dataset returns no data, clicking "sync columns" shows that there are no columns at all. This requires making a fake query that will return any result, syncing columns, and then changing back the query.
3. Automatic Syncing: Once the syncing with no data bug is solved, it would be very helpful if the columns could be synced automatically when the dataset is saved. This would streamline the workflow and ensure that the dataset is always up-to-date without requiring additional steps.

Screenshots/recordings

Superset version

4.0.2

Python version

3.11

Node version

I don't know

Browser

Chrome

Additional context

Similar issue for superset 2.0.0 (closed because version was no longer supported)

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot[bot]]

Hey @gidinetapp! 👋 I'm here to help you with any bugs, questions, or contributions. Let's squash those bugs together!

I found two similar closed issues related to the syncing of columns in virtual datasets:

1. Issue 21907: This issue discusses the problem where syncing columns from the source does not work unless the dataset is saved first. No solution was found in the comments ^[1].

2. Issue 20907: This issue addresses the need to ignore columns when syncing datasets that already contain calculated columns with the same name. No solution was found in the comments ^[2].

To address the concerns you've raised:

1. Syncing Before Saving: Currently, there isn't a built-in mechanism to sync columns before saving the dataset. However, you could consider modifying the syncMetadata function to allow for pre-save syncing by integrating it into the dataset editing workflow.

2. Syncing with No Data Bug: You can improve the syncMetadata function to handle cases where there is no data more gracefully. This involves checking for the presence of data before attempting to update columns and providing informative feedback to the user ^[3].

3. Automatic Syncing: There is an existing automated process that can be leveraged to trigger column syncing automatically upon saving a dataset. The tableChangeAndSyncMetadata function can be used to validate and call the syncMetadata function when there is a change in the datasource properties, specifically the table_name attribute ^[3].

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[dosubot[bot]]

A similar issue was previously reported in the Superset repository, titled "Sync columns from source does not sync columns unless dataset is saved first" ^[1]. This issue was closed without a solution being found in the comments.

Regarding your concerns:

1. Syncing Before Saving: Currently, the 'sync columns' button is inactive when the dataset is in edit mode, which means you need to save the dataset first before syncing ^[2].

2. Syncing with No Data Bug: Unfortunately, I couldn't find specific information on how Superset handles datasets with queries that return no data in relation to column syncing.

3. Automatic Syncing: There is a mechanism for syncing columns when a dataset is saved, but it seems to require manual intervention as described in your message ^[3].

These points highlight areas where the current process could be streamlined to improve user experience.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[rusackas]

Converting this to a discussion since it sounds like there's more proposal here than bug report. Happy to convert it back if you can demonstrate that this is actually a bug rather than a workflow that could use some ironing out.

Syncing Before Saving: Currently, syncing columns requires too much manual intervention: save the dataset, re-enter it to sync the columns and save again. Allowing column syncing before saving the dataset would save time for users.yn

Syncing columns affects the dataset, and thus can affect any number of charts, affecting any number of dashboards. It's kind of a big thing to commit to, so I'm not sure if we want to allow this prior to committing to the dataset changes. Perhaps a "preview results" or "test query" button of some kind would suffice? Or some workflow where it's easier to take the dataset to and from SQL Lab?

Syncing with No Data Bug: If the query in the dataset returns no data, clicking "sync columns" shows that there are no columns at all. This requires making a fake query that will return any result, syncing columns, and then changing back the query.

If the query returns no data, the fact that there's no columns sounds perfectly accurate and reasonable. I'm not sure what you mean about needing to make a fake query, can you explain more here?

Automatic Syncing: Once the syncing with no data bug is solved, it would be very helpful if the columns could be synced automatically when the dataset is saved. This would streamline the workflow and ensure that the dataset is always up-to-date without requiring additional steps.

Again, this action can be pretty impactful (e.g. affecting thousands of charts/users with the click of a button) but I could see a couple of options here:

1. Nex to the Save button for the dataset, we could have a "sync columns when saving" checkbox, with a default set by config.
2. We could add a "Save and Sync Columns" button as either a dropdown-button, or another button next to Save.

Curious how @yousoph or @kasiazjc or @mistercrunch would feel about any of this. I agree the current workflow can be a bit confusing/tedious, but it's also a highly impactful/dangerous one.

[gidinetapp]

Thanks for the quick response.
The idea of a "sync columns when saving" checkbox, with a default set, sounds great. this improvement will be very helpful.

Clarification on Syncing with No Data: When using other db tools (e.g. DataGrip), running a query that returns no data still displays the column names. In Superset, if the dataset query returns no data, the "sync columns" feature shows that there are no columns at all. This behavior is problematic because:

1. Datasets might occasionally return no data but are expected to have data in the future.
2. It forces users to create a temporary query that returns any data just to sync the columns (this is what i meant by "fake query"). e.g. the gif I attached to the post shows how I had to remove a "where" clause to have the query return results, sync, and then re-adding it without syncing.

Thanks again